Search This Blog

Shrinking a Log File on a Test or Development Server

SQL Server:Shrinking All dbs Transaction Log (Transaction Log Shrinking) use script

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.
1
2
3
4
5
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.
1
2
3
4
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.
Method 2:


Unusually Large Log


The log file appears to be quite large compared to the size of the database file.
This can happen if your log file grew too large and has not yet been shrunk.
This can also happen if full backups are not running regularly.
Suggestions:
Check the frequency of your backups.

Steps to shrink the DBHealthHistory Database if it gets too large.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBHealthHistory SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBHealthHistory_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DBHealthHistory SET RECOVERY FULL;
GOa
-- Be sure to do a full backup, then kick off transaction log backups
-- check the size of the files.
SELECT size / 128.0 as sizeMB, name
FROM sys.database_files;

SQL Server:Shrinking All dbs Transaction Log (Transaction Log Shrinking) use script