Test Attachment Cleaner for VS2010 - everything you need to know
- TFS2010: Test Attachment Cleaner and why you should be using it
2. Test Attachment Cleaner for Visual Studio Ultimate 2010 & Test Professional 2010 – download page
visualstudiogallery.msdn.microsoft.com/3d37ce86-05f1-4165-957c-26aaa5ea1010
3. A hotfix that can reduce the size of the test data saved to the TFS database is available for Team Foundation Server 2010 Service Pack 1
support.microsoft.com/kb/2608743
This is a hot-fix to reduce the size of the attachments added in TFS database – in another words after installing the hot-fix you will not see such growth in your TFS database
You will still need to run the TAC tool to clean your TFS Database from old attachments
4. These are the best practice recommendations for using the Test Attachment Cleaner:
Experiment with the tool in preview mode - this is sample command line
tcmpt.exe attachmentcleanup /mode:preview /collection:"" /teamProject:GeneralTests /settingsFile:"C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\SampleSettings\Test.xml" /outputFile:Output_preview.log
This will generate log similar to the following
tcmpt.exe attachmentcleanup /mode:preview /collection:"" /teamProject:GeneralTests /settingsFile:"C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\SampleSettings\Test.xml" /outputFile:Output_preview.log
<!-- Scenario1: View list of Attachments that are taking Db space of more than 1 GB per attachment -->
<DeletionCriteria>
<TestRun />
<Attachment>
<SizeInMB GreaterThan="1" />
</Attachment>
<LinkedBugs />
</DeletionCriteria>
----------- Command Line Parameters ------------
Team Project: GeneralTests
Mode: Preview
Settings File: C:\Program Files (x86)\Microsoft\Test Attachment Cleaner\SampleSettings\Test.xml
------ Settings File Parameters ------
TestRun: False
Attachment: True
Size Greater Than 1 MB
LinkedBugs: True
----------- Execution Details ------------
The following attachments have been identified for deletion:
AttachmentName Size LinkedTo Bug IDs
-------------- ---- ----------------
ScreenCapture.xesc, 1.6 MB ( 1645362 bytes),
ScreenCapture.xesc, 2.0 MB ( 2117644 bytes),
ScreenCapture.xesc, 59.3 MB ( 62219836 bytes),
ScreenCapture.xesc, 3.6 MB ( 3805764 bytes),
ScreenCapture.xesc, 2.3 MB ( 2397380 bytes),
ScreenCapture.xesc, 2.6 MB ( 2693314 bytes),
ScreenCapture.xesc, 15.0 MB ( 15766286 bytes),
ScreenCapture.xesc, 20.5 MB ( 21471042 bytes),
ScreenCapture.xesc, 11.4 MB ( 11982358 bytes),
ScreenCapture.xesc, 3.9 MB ( 4053566 bytes),
ScreenCapture.xesc, 25.6 MB ( 26815510 bytes),
ScreenCapture.xesc, 2.0 MB ( 2069308 bytes),
------------ Summary ------------
Number of attachments affected: 12
Total size of attachments: 149.8 MB
Elapsed time was 22.64 seconds
- Look at the total size of attachments to be deleted – make sure it is not bigger than 500 MB
- Run the same command line with the same settings in delete mode
- Check how long will take for SQL to clean the ghost records
- Once the ghost records are cleared, delete another chunk of ~ 500 MB
5. Here is a short write-up on the ghost cleanup task and what it does provided from our SQL Team:
What are ghost records?
Sql Server stores data in terms of 8 K pages. Each page belongs to a particular object i.e. user or system table.
When you delete data from Sql Server table, we shall change metadata in the page header to indicate that there are deleted rows in particular page.
In more technical terms, when a record is deleted from a clustered index data page or secondary index leaf page, the record is not always physically removed from the page. Rather, the record may be marked as a ghost record and removed later.
What is the ghost cleanup task?
Every Sql Server installation shall have one thread dedicated for doing ghost cleanup. Note that it is a single thread that is responsible for cleaning up ghost records across ALL databases on Sql Server. On a busy server with several databases, you may notice that the background ghost cleanup task never catches up with volume of deletes happening. Since this is a single task for the entire SQL Server and works on one database, ten PFS pages at a time, the number of ghost records may keep increasing with the task falling behind.
Who uses the ghost cleanup task?
Pretty much any delete activity shall result in ghost records. For LOB data, we typically do in place deletes i.e. do not enqueue for ghost cleanup rather de allocate in place. However, for the TAC tool database we have Read Committed Snapshot Isolation turned on – under which we actually end up enqueuing the page into ghost cleanup.
How to monitor the ghost cleanup task?
You can monitor the number of ghost records by table by using the following query
SELECT object_name(object_id) as Name, record_count, GHOST_RECORD_COUNT,Version_ghost_record_count,INDEX_TYPE_DESC, ALLOC_UNIT_TYPE_DESC
FROM sys.dm_db_index_physical_stats (DB_ID(N'<dbname>'), object_id('<TableName'), NULL, NULL , 'DETAILED')
Note that this is a resource intensive query so you typically do not want to run it during business hours.
Another option to monitor ghost cleanup is to use Xevents as outlined here:
<sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/15/an-xevent-a-day-15-of-31-tracking-ghost-cleanup.aspx>
The output of this Xevent will show us how the enqueuing is happening.
Is there a way to speed up the ghost cleanup process?
No. It is a background task. The best thing you can do is to monitor the ghost record count via the query and Xevent given above.
What are the known issues around ghost cleanup task?:
Make sure that you are using SQL version that includes the following FIX: ghost_record_count values keep increasing in SQL Server 2008 R2 or in SQL Server 2008
support.microsoft.com/kb/2622823
How to avoid issues around ghost cleanup task?
• Do not do huge deletes in place
• Delete data in smaller number of rows and allow ghost cleanup to clear out the deleted rows before repeating.
Depends on the SQL version the fix for ghost_record_count is in different CUs:
- SQL Server 2008 Service Pack 3 - Cumulative update package 4 for SQL Server 2008 Service Pack 3 and after
support.microsoft.com/kb/2673383
- SQL Server 2008 R2 - Cumulative update package 10 for SQL Server 2008 R2 and after
support.microsoft.com/kb/2591746
- SQL Server 2008 R2 Service Pack 1 - Cumulative Update package 4 for SQL Server 2008 R2 Service Pack 1
support.microsoft.com/kb/2633146
How to determine the version and edition of SQL Server and its components: