Stop SQL Server transaction log (.LDF) files from growing indefinitely

SQL Server .LDF files are growing and growing and growing... Symptoms


You notice that in your SQL databases directory the .LDF files are growing permanently.


Solution


Set the recovery mode of your SQL Server databases to ‘simple’.


Step-by-step instructions


  1. Perform a full-backup of your SQL Server databases.
    Note: This is very important since switching from the full or bulk-logged recovery model to the simple recovery model breaks the backup log chain. Therefore, it is strongly recommend to back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log. [Source]
  2. Switch recovery mode of SQL databases to SIMPLE.
    (See also: What is simple recovery mode?)

    Important Note:
  3. “The Simple recovery model lets you restore the database to the point from which it was last backed up. However, this recovery model does not enable you to restore the database to the point of failure or to a particular time.”  [Source]


    For each database...


    ... set recovery mode to SIMPLE.

  4. Shrink the transaction log (.LDF) files.


    Don't forget to Shrink...

    ... the log files after changing the recovery mode. 
  5. Perform a full-backup of your SQL Server databases.

 


 


 


 


 


 


Optionally you can use a script for the steps described above:



SqlScript


See and possibly unlock files checked out by other team members

Ed Hintz points out that this is easily done with TFS Power Tools and wrote about it in 2007.

  1. In the Source Control Explorer, right click on a parent folder that contains the pending change and choose, “Find in Source Control”.
  2. Choose "Status…".

    Find in Source Control command from TFS power tools
  3. Press the Find button. (optionally you can type in a user and\or wildcards if you want to narrow the search)

    Optionally filter for user and/or wildcards
  4. You will be presented a “Find in Source Control” window.
  5. Select the pending change and press the undo button OR right click Undo…

    Can easily undo changes of other users
  6. Done.

Customizing the folders and file extensions automatically excluded

When adding items to source control a list of excluded folders as well as file extensions is shown. You can customize those defaults in the registry.

image

Matthew Mitrik has the details:

If the add operation is successfully completed, the list will be saved to the registry.

Order of finding masks to filter:

  1. HKCU\Software\Microsoft\VisualStudio\[Version]\TeamFoundation\SourceControl\AddOptions\ExcludeMasks
  2. HKLM\Software\Microsoft\VisualStudio\[Version]\TeamFoundation\SourceControl\AddOptions\ExcludeMasks

(Thanks to Rich and Matt)

Recommendations for Virus Scanning on TFS computers

AV software can slow down your Team Foundation Server installation Anti-virus software can badly impact the performance of your Team Foundation Server. Therefore make sure the following folders are excluded from scanning for viruses:

  • TFS Version Control Cache Folder
  • Team System Web Access Cache Folder
  • SQL database folders
  • SQL backup folders

Additionally Microsoft published virus scanning recommendations for:

(Thanks to Wes to pointing me at those three articles.)