October 2010 - Posts

While figuring out a consolidation strategy for Sql Server, I was concearned that the memory issues (http://blog.mischel.com/2008/10/14/copying-large-files-on-windows/)  affecting large file copies would also effect Sql server backups to a cifs location. The reason I wanted to do this was to consolidate the existing backup server onto the new hardware. It was ageing and was limited in its current throughput. As the backup disk was SAN based it should be easy to unplug from the old server and plug into the new server. However, knowing consolidation projects as intimately as I do, I know it could take a while to consolidate the remaining Sql Servers onto the new box. I didn’t want the backups from the old servers to starve the new server of memory as it does this.

So I ran some tests on the new server which was running:

  • Windows 2008 server
  • 48Gb RAM
  • 24 Cores


First I created a network share and backup folder. I then backed up a 40Gb file from a Sql 2005 Windows 2003 Server. The server had just rebooted and there was 40Gb of headroom. Monitoring the RAM usage I only observed a 1.8Gb increase in RAM and 100Mb increase in page file.


I then checked the impact that the Netbackup tape backup software  had in copying the file to tape and it appears that this process only took up 200Mb.


Pushing a file from the 2008 server consumed 1 Mb and pulling a file consumed 30 MB of RAM.


Job done, while I can see there is still and impact of copying and backing files to a server, its not huge compared to the file size its working with . As long as I leave an appropriate amount of free memory and lock pages in memory I am not likely to see major implications of this.

Emil Fridriksson also has a couple of extra tweaks that can be made if the problem becomes quite severe.




When I visit a client site to troubleshoot deadlocks, it is always a cause of annoyance when I cannot go through historical deadlock events to determine the frequency and causes of these deadlocks. I either have to talk them through enabling trace flag 1222 or explain to them how to enable profiler to capture specific deadlock graphs. Even then its not possible to retrospecivley go back and examine deadlocks before this point.

I recently came accross this article by Jonathan Kehayias about how to retrieve historical deadlock graphs with 2008 extended events. These scripts have proved invaluable for me in going back in time.

You may need to upgrade to Service Pack 2 to get it to work correctly, but heres a copy of the final script:

declare @xml xml

select @xml = target_data

from sys.dm_xe_session_targets

join sys.dm_xe_sessions on event_session_address = address

where name = 'system_health'

select CAST(


REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

'<victim-list>', '<deadlock><victim-list>'),


as xml) as DeadlockGraph


(select @xml as TargetData) AS Data

CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)