Not thinking it through - Part 4 .. the saga continues!
My previous posts under this heading and the How much memory relate to my experiences with Production SQL Servers that are managed within an out sourced data centre. I'm performance tuning one of these servers and having resolved some issues I was back to monitoring wait stats and blocking. Initial results indicated i/o completion problems on the transaction log drive, it's a SAN so no real surprise there then! I was looking through the initial results from my blocking report when I noticed that the transaction log backup job was causing blocking for users - strange I thought!!
Now the server is a little lacking in disk space and the transaction logs have bloated during index rebuilds causing the data centre problems, this is out of my control so I can only watch, see previous posts, so the latest "modification" is to apply a really savage shrink to the ldf file after every bakup, yes honestly. Typically the hourly logs average about 50 mb or so with some overnight processes taking the max size to 250mb, the added step in the job seeks to shrink the ldf file to 10mb . The whole job is bound within a cursor for good measure, even though the job is for one database. So during the shrink transactions can't be written to the log file, for a oltp database this is obviously a good move. Add to this the %age growth is set to 10% , so how many increments to interfere with performance to get to 50Mb ? Answers on a postcard to ....... I did a quick check after a backup and the 14mb ldf file was in 8 fragments already.
Obviously I'm in "observer mode", but I do feel somewhat disappointed, should one expect more from a data centre ? My client will be recruiting a permanent DBA , until I started to stick my nose in no-one had really monitored the servers in great detail, so things will improve I'm sure. So who watches the watchers?
ps. I should really file this under Very Grumpy!!!