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.
SET QUOTED_IDENTIFIER ON; 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) DECLARE c_databases CURSOR FOR 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) BEGIN IF (@@fetch_status <> -2) BEGIN 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) END FETCH NEXT FROM c_databases INTO @v_dbname END CLOSE c_databases DEALLOCATE c_databases