New extended event to track writes to the snapshot sparse file
You might have seen our earlier blogs around different snapshot related errors and the techniques available to address them. Among all those, one piece that was missing is the ability to track the specific write activity that goes against the snapshot files. In the past we had to resort to predictive analysis using information from DMV’s like sys.dm_os_buffer_descriptors.
Starting with SQL Server 2008 SP2, a new Extended Event was added to accurately trace these write activities that go against the snapshot sparse files. The name of the event is file_written_to_replica. You can expect this new event to surface in SQL Server 2008 R2 in a future service pack. When you query the sys.dm_xe_object_columns, you can find out the different data columns that are available for this event.
Most of the information here is self explanatory. There are 2 custom data columns: path and io_data. The path refers to the full physical file path of the snapshot file. The io_data is the actual data that gets transferred. By default these two data columns are not captured. You have to enable them using the SET command in the create event session statement.
Here is the actual event data from a collection I did recently:
Using this event, now you will be able to find out the activity from the application that corresponds to the most usage of space in the snapshot databases as well as information like snapshot file fragmentation. Be careful while configuring the extended event session and add appropriate filters to reduce the amount of information that gets collected on busy systems for this event.
Here are some of our previous blogs on this topic:
https://blogs.msdn.com/psssql/archive/2008/02/07/how-it-works-sql-server-2005-database-snapshots-replica.aspx
https://blogs.msdn.com/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx
https://blogs.msdn.com/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx
https://blogs.msdn.com/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx
Thanks
Suresh Kandoth