Truncate Log Files and Recover Space

We have had many issues on our database server with space. The databases are growing faster than we can purchase space and there is also a lot of issues with people wanting to store backups locally for quick restores.

There is a really cool(free) tool that helps you analyze which file and directories are taking up all your space. It's called WinDirStat and you can download it here: http://sourceforge.net/projects/windirstat/

It will help you find where all your space is going and if there are any obscure files laying around (temp files) that no one uses, but are taking Gigs of space.

Using this tool, I was able to find that TempDB was taking over 94GB and it's log file was 20GB. We just use a Simple backup scheme and don't really need to recover databases with transaction files. We just restore the previous day's information.

I also noticed a few other database logs had grown large. Now, I know this tool doesn't take the place of a proper database maintenance plan, but we don't have any good DBAs. So, this will have to do.

   1:  USE DatabaseName

   2:  GO

   3:  BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

   4:  DBCC SHRINKFILE(<TransactionLogName>, 1) --LOGICAL NAME FOUND IN PROPERTIES

   5:  GO