DTS and Transaction log 1

[nice95gle]

I was going to post this in the DTS forum, but activity is kind of low over there.

Is there a way to find out how much data a DTS package wrote to the trans log the last time it was ran?

Thanks guys and SQLSister

Well Done is better than well said
- Ben Franklin

 

[mrdenny]

There is some basic logging of the package execution that you can enable.

But there's no way to see how much data is logged to the transaction log specifically.

To do so you would need to capture the size and amount of free space in the log before and after execution and store the data in a table for later viewing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[nice95gle]

Thanks Denis,

Let me approach this from a different direction. We have a package that's killing the Trans log. What is the best way to find the suspect package?


Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Sorry...Thanks Denny,

Well Done is better than well said
- Ben Franklin

 

[mrdenny]

Your best bet will probably be to start up SQL Profiler and monitor for log extentions. You should see the log extents match up to when the package is running.

Are the packages loading data via data transformations, or running stored procs, etc?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[nice95gle]

I will try the profiler way to get me started.

There was/is no real standard. Some is using transformation (copy columns or ActiveX), some is using SP's, some is using bulk tasks...you get the picture.

I have to leave for the day. I will pick this up later.

But thanks again.

Well Done is better than well said
- Ben Franklin