September 13, 2012 at 7:47 am
I have a package that's been in place for at least 6 months now. There isn't a lot to it, it generally completes in an hour, not generally more than 200,000 records, bulk of time is spent making the rounds of the different servers to get the data.
I don't know why it would suddenly be running long, eating log space. It kicks off at 1 am and today was still going at 9 am, and grew the log to eat all available space. We have a Real dba, he shrunk the db and the log and moved things to a larger drive.
I only have a couple years of SSIS and am more of an accidental dba vs trained so log space consumption is far from my forte but I need to figure out Why and how to solve it.
Any hints on what to look at are very much appreciated! Thank you in advance.
September 13, 2012 at 2:55 pm
If you haven't changed the package, then Something must have changed on the SQL Server.
My starting point would be to ask 'TheirRealDBAness' if there are any new indexes, or triggers on any of the tables you're using.
September 13, 2012 at 3:04 pm
Problem is, he doesn't touch the warehouse and is telling me to find out why !
But what you are saying is that potentially changes in the source servers could cause this in my package? I have to assume that indexing of the hosted systems is on-going as new features are always being added by the developers and we in BI sometimes ask for indexes to be added when we have pokey queries.
I appreciate the food for thought!!!
September 13, 2012 at 3:39 pm
herladygeekedness (9/13/2012)
But what you are saying is that potentially changes in the source servers could cause this in my package? I have to assume that indexing of the hosted systems is on-going as new features are always being added by the developers and we in BI sometimes ask for indexes to be added when we have pokey queries.
Wrong causal relationship. The source servers didn't cause anything in your package. Your package causes stuff in SQL Server.
If your package does stuff to the database, UPDATE, DELETE, INSERT, and there are new indexes, then SQL Server will maintain those new indexes. If there are new triggers, these will be fired.
Some other things to investigate. Is the logfile bloat definitely a result of running the package, or could it be something else was running at the same time? Is there a lot more data this time? Is there a lot more activity on the servers? When I've seen logfile bloat before - its because of 1) unexpectedly high activity level. and 2) a transaction (or several) changing a very large number of records in one go. The longer you wait to commit a transaction, the longer the logfile holds on to stuff before it can re-use the space. The DBA should be able to monitor open transactions while the package is running - and see if it is are hogging logspace, He can then relate that back to sql statements - and give you a place to start looking.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply