RSS Feed

Sports Analogies


March 15, 2012 by Mike Hillwig

I’m not exactly a sports fan. Not even close. But every once in a while, I will use a sports analogy that would make my dad proud.

A couple of weeks ago, I was troubleshooting a performance problem. Every fifteen minutes, a group servers would start paging and our IO waits would skyrocket. It was pretty easy to looking for the culprit. All we needed to do was find whatever processes were running every fifteen minutes. In this case, it was our transaction log backups.

The application on this group of servers uses a series of twenty databases (which is another post for another day) and the transaction logs backups were all configured to kick off at the same time. I wanted my servers to run them serially instead of all at once.  My boss is a golfer, and I explained that we were doing a scramble instead of a shotgun. She completely got it. You could say I hit it out of the park. Oh, wait. Mixing analogies probably isn’t a good idea here.

One of the lessons learned in this process (and I thank Kendra Little for her help in this) is that the log shipping engine on the target side wants the filename in a specific format. While I was at it, I converted the timestamp to UTC to match how the log shipping process names its file.

The second lesson learned here is that the schedule for the backup job should vary every so slightly than the LSCopy job on the target server. Assuming your servers have synchronized times, you will be doing a copy at the same time as the backup. That’s causing a little bit of disk contention. You also run the risk of your DR site being behind production the length of your repeat interval. In my case, that was fifteen minutes. What I did was set my backups to start at the top of the hour, repeating every 15 minutes. The LSCopy schedule to start three minutes after the top of the hour, repeating every fifteen minutes. That means my DR server is about three minutes behind production.

The third lesson here is that the SQL Server Agent doesn’t appear to be covered in the MIN and MAX RAM setting on your instance. Since we were copying some pretty big files across the network, that SQL Server Agent was sucking up a hefty amount of RAM, causing the server to page.  I had to lower the MAX RAM setting. Imagine explaining why you wanted to give a client’s database server less RAM than it already had.
DECLARE @v_LocalDrive nvarchar(1)
DECLARE @v_LocalFolder nvarchar(50)
declare @v_timestamp nvarchar(30)
SELECT @v_LocalDrive = ‘d’
SELECT @v_LocalFolder = ‘tlogs’
DECLARE @v_dbname nvarchar(100)
DECLARE @v_SQL nvarchar(1000)
select name
from sys.databases
where recovery_model = 1
and database_id > 4
ORDER BY [name]
OPEN c_databases
FETCH NEXT FROM c_databases INTO @v_dbname
WHILE (@@fetch_status <> -1)
IF (@@fetch_status <> -2)
select @v_timestamp = convert(nvarchar, GETUTCDATE(), 112) + RIGHT(’00’+ convert(varchar, datepart (hh, GETUTCDATE())),2)
+ RIGHT(’00’+ convert(varchar, datepart (minute, GETUTCDATE())),2) + RIGHT(’00’+ convert(varchar, datepart (ss, GETUTCDATE())),2)
SELECT @v_SQL = ‘BACKUP LOG [‘ + @v_dbname + ‘] to DISK = N”’ + @v_LocalDrive + ‘:\’ + @v_LocalFolder + ‘\’ + @v_dbname + ‘_’ + @v_timestamp + ‘.trn”’ +’ WITH NOFORMAT, NOINIT, NAME = N”’+ @v_dbname + ‘_’ + @v_timestamp + ”’, SKIP, REWIND, NOUNLOAD, STATS = 10′
exec (@v_SQL)
FETCH NEXT FROM c_databases INTO @v_dbname
CLOSE c_databases
DEALLOCATE c_databases[/SQL]


  1. Pretty! This was a really wonderful post. Thank you for your provided information.

  2. erjilopterin says:

    Good post. I learn one thing more challenging on completely different blogs everyday. It can at all times be stimulating to read content material from other writers and follow slightly something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll provide you with a hyperlink in your internet blog. Thanks for sharing.

  3. As a Newbie, I am always searching online for articles that can aid me. Thank you

  4. Hmm it seems like your blog ate my first comment (it was super long) so I guess I’ll just sum it up what I submitted and say, I’m thoroughly enjoying your blog. I too am an aspiring blog blogger but I’m still new to the whole thing. Do you have any helpful hints for beginner blog writers? I’d really appreciate it.

  5. Along with every little thing which seems to be developing within this specific subject matter, your points of view happen to be fairly refreshing. Nonetheless, I beg your pardon, but I do not subscribe to your entire plan, all be it refreshing none the less. It would seem to us that your remarks are actually not totally validated and in fact you are generally yourself not really wholly convinced of your assertion. In any case I did appreciate examining it.

  6. What i don’t realize is in fact how you are not actually much more well-favored than you might be right now. You are so intelligent. You recognize thus considerably when it comes to this subject, made me in my view imagine it from a lot of varied angles. Its like women and men are not fascinated except it is one thing to accomplish with Girl gaga! Your personal stuffs outstanding. At all times handle it up!

  7. Very good written story. It will be valuable to everyone who utilizes it, as well as yours truly :). Keep doing what you are doing – for sure i will check out more posts.

Leave a Reply

Your email address will not be published. Required fields are marked *