Shrinking a Log File on a Test or Development Server

On my computer I run SQL Server 2012, and I use a number of test or development databases. I don’t run backups on these because I don’t care if they get destroyed since they are easy to replace. Recently I upgraded to a SSD, and now my C:\ drive which I am running SQL Server from is really fast, and SQL Server in the development environment is also very fast. The only drawback is the size of the SSD, instead of a 2TB disk, I am now limited to 120GB of fast disk, and I have a second disk with 2 TB.  I ended up with the 120GB drive because I was more focused on performance rather than the overall size.

Yes, this disk is really fast, but suddenly I am need to be more aware of what is using up space because of the limited size. I use the Adventure works for a lot of test scripts, and queries that I might be blogging about, and I noticed that my AdventureWorks log file was 1.5GB or about 1% of my entire disk space, and that is about all waste, on this particular server.

My first thought was to just move the databases over to my slower non-SSD drive, but I want things as fast as possible, so that is not an option.

WARNING: This advice is intended for a development or test database where it doesn’t matter if the data file is lost.  This could be done in production once it was practiced in a test or development environment.  Just be sure you know what you are doing before running it on a production system.

First lets take a look at the size of the AdventureWorks log file.

AdventureWorksLogSize

Its been a while since I set up this database, and I don’t remember how it was originally configured for backups, but first lets set the Recovery Model to be simple, meaning that we don’t need transaction log backups. The Recovery model can be set from the Database Properties dialog on the Options page.

Simple

Next we can check to see how much space is being used, and how much space is available in the AdventureWorks2012_log file. To do that we just run the following query in the AdventureWorks database.

USE AdventureWorks2012;
GO
SELECT (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int))/128.0 AS AvailableMB,
 name
FROM sys.database_files;

Which when run produces the following output:
waste_space

Showing that in the log file there is 1.5GB of available space that could be reclaimed. Now on to reclaim it. To reclaim the space we are going to use DBCC SHRINKFILE on the log. When running this be sure to run it only on the log. If DBCC SHRINKFILE is run against the data file, it usually ends up fragmenting all of the indexes and decreasing performance while the space is reclaimed.


USE AdventureWorks2012;
GO
DBCC SHRINKFILE (AdventureWorks2012_Log);
GO

Producing the following output:

shrinkfile_results

After the shrink the size is 63 data pages, 56 used. To see the actual numbers in MB, just run the original query from above to see the following results:

nowast_space

From here we see that the available space on the log file has been cleared up, and if we look at the file sizes on disk, we see the following:

filesystem_after_shrink

Rather than 1.5GB the file is now 504KB or about half a megabyte.

Overall the plan behind this was to free up space on my SSD, and that is what we have done here by shrinking the log file.

Over 400 downloads of the database health reports.

Today, the Database Health Reports had its 400th download.  I would like to say thank you to everyone who is using the product, and an extra thank you to those who have provided me with feedback.

Here is a history of the Database Health Report project, http://databasehealth.stevestedman.com/about.

My Goal:  I would like to provide the Database Health Reports project for free to as many people as I possibly can for as long as I can.  One day I am sure that it will require more of a support team, and more developers, but as long as I can do this for FREE with just me working on it I will continue to do that.

Download

If you haven’t tried out the Database Health Reports project you can get it from the FREE download page.

I am currently working on Beta 6. Currently Beta 5 is out in the wild and going well.

Thanks!!!

Thanks everyone for using the Database Health Reports project!

-Steve Stedman

Database Health Reports – Beta 5 Released Today.

The Database Health Reports application provided free of charge to the SQL Server community. Enjoy!

Today I released Beta 5 of the Database Health Reports.

Beta 5 is mostly bug fixes and performance improvements. There was one new feature added, the Big Clustered Indexes report.

The Big Clustered Indexes report shows clustered indexes that may be larger than optimal.

BigClusteredIndexes

Give it a try with the Free download of Database Health Reports Beta 5.