July 14, 2009 at 10:41 am
We have SQL Agent jobs to shrink some of our databases. Sometimes the job "cannot shrink" the database but the job will show as succeeded. I've found a few examples on how to query sysjobhistory and get the jobs that are suspect however, I'm looking for a query that will find these jobs and run sp_start_job for the ones that did not shrink. Any help would be greatly appreciated.
July 20, 2009 at 9:26 pm
Why are you shrinking your databases? One, this will cause excessive index fragmentation. Two, the files will simply grow again when the database needs more space that will result in file system fragmentation of the database files as well as potential performance issues when the datbase needs to grow.
July 22, 2009 at 7:39 am
Lynn Pettis (7/20/2009)
Why are you shrinking your databases? One, this will cause excessive index fragmentation. Two, the files will simply grow again when the database needs more space that will result in file system fragmentation of the database files as well as potential performance issues when the datbase needs to grow.
Regarding file system fragmentation, I had a large database I needed to tweak the performance on. After pulling my hair out, and creating clustered indexes on all the tables (there were no clustered indexes or primary keys...ughh!), I was still getting poor performance. On a lark, I took a look the drive it was on which only had the application and tempdb databases on it, two .mdf's, and there were 2500+ individual fragments on the drive. Needless to say, did a defragment on that and it helped greatly (tables moved from updating in 12+ hours to no more than 3 hours).
It is possible ("ahem...cough...blush"), that I inadvertantly caused this to occur by setting file growth to fixed increments rather than sizing the database to the final level it is now (about 50GB) although I didn't really know how big it would grow. In hindsight, sizing the file initially to, say 75% or 80% of the available space would have been a much better idea. I do that now for any production databases that are not sharing space with may other files, and size it up to a big chunk if I know there's going to be a lot of data coming in.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply