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.

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.

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:

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:

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:

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:

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.

Shrinking a Log File on a Test or Development Server

Related Videos

Leave a Reply Cancel reply