Shrink SQL Log Files
If you’re a Windows Administrator and have Microsoft SQL Servers in your environment–and you’d be a rare bird if you didn’t–then you’ve encountered this problem. Backups are missed for awhile, massive restore is done, something happens and your transaction log file grows way too big. It’s not uncommon to find it being much larger then the actual database! If you’re like me you would RDP to the server, check out the data drive and see that the log file itself was out of size. You’d then hit Google to find out how to do a shrink file, then have open SQL Management Studio and try to figure out the logical name of the log file, then run the query. Usually about 20 minutes of lookups, all so you can run a SQL query that typically takes a few seconds to complete. Time to address this using PowerShell.
Invoke-SQLQuery
Before going any further, I want to let you know that I heavily leverage my Invoke-SQLQuery function to accomplish this. I’ve actually built a module at work that includes this function so I can run it at any time–and the Shrink SQL Log function is now in the same module!
Specifications
So let’s outline what we want this script to accomplish:
- Point the script at a SQL server
- List the databases, database and log sizes. We’ll also need the last backup date because you can’t really shrink the log until you have a good backup
- Graphically display this information, letting the user select which databases they want to shrink (Out-GridView)
- Shrink the database
- Report the before and after information
Once you break it down you quickly realize that this is a pretty simple script, but we will need to display all that information, which should be stored in SQL tables somewhere. How hard could it be? Well, as it turns out, pretty hard. I’m sure I’ve mentioned this before, but I’m not a SQL DBA and I don’t even play one on TV and while I’m much better at writing queries then I was a few months ago I’m FAR FAR from an expert. So I broke out my Google-fu to discover how to display databases, their file and log file sizes.
There’s a lot of code out there, but I finally found what I was looking for over at StackOverflow from Alex Aza:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with fs | |
| as | |
| ( | |
| select database_id, type, size * 8.0 / 1024 size | |
| from sys.master_files | |
| ) | |
| select | |
| name, | |
| (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, | |
| (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB | |
| from sys.databases db |
Now I have the database sizes, I needed to include the last backup too. I found the SQL code to do it from Pinal Dave at SQLAuthority.com. I have to be honest here. I tried for a couple of hours to get these two mashed together and have one query to rule them all. I was not successful at this and ended up running both queries and then manually bringing them together in PowerShell. After I got that working, I finished the whole script with all the functionality I wanted working. But I was bothered by the 2 query system and I later came back to it and did eventually come up with a single query that pulled all the information on one fell sweep! Here’s Pinal’s original code:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT sdb.Name AS DatabaseName, | |
| COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpTime | |
| FROM sys.sysdatabases sdb | |
| LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name | |
| GROUP BY sdb.Name |
And here’s the final query I came up:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH fs | |
| AS | |
| ( | |
| SELECT database_id, name, type, size * 8.0 / 1024 AS size | |
| FROM sys.master_files | |
| ) | |
| SELECT | |
| db.name AS Name, | |
| db.database_id AS ID, | |
| CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 0 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS FileSizeMB, | |
| CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 1 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS LogSizeMB, | |
| (SELECT MAX(bus.backup_finish_date) FROM msdb.dbo.backupset AS bus JOIN fs ON bus.database_name = fs.name) AS LastBackup | |
| FROM sys.databases AS db |
I like to keep my SQL commands all upper case because it’s a great way of differentiating between SQL command and field/database names. I also added CAST and ROUND in there to format the data just the way I wanted it. Ultimately the data is being displayed to the user, and going out to 4 decimal places just isn’t user friendly so I took it down to 2 decimal places (surprisingly difficult in SQL).
Next comes 2 lines of code, which in essence is the very core of this script:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| $DBs = Invoke-SQLQuery -Instance $Instance -Database Master -MultiSubnetFailover -Query $DBsQuery | Select ID,Name,FileSizeMB,LogSizeMB,LastBackup | |
| #Now display | |
| $Selected = @($DBs | Out-GridView -Title "Databases on $Instance – Select databases/logs you wish to shrink" -OutputMode Multiple) |
First one runs the query above and returns all the information that I need. I need to the database_id because at this point I still don’t know the logical name of the transaction log file, and I will have to discover that after the user has selected the databases they want to shrink. And once I know that I can start a loop on the Selected databases and begin the shrinkage process. Yes, I said it.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ForEach ($Select in $Selected) | |
| { | |
| $Type = [int](-not $Database) | |
| $Name = (Invoke-SQLQuery -Instance $Instance -Database Master -MultiSubnetFailover -Query "Select name From sys.master_files Where database_id = '$($Select.ID)' And type = $Type").Name | |
| If ($Name) | |
| { | |
| Write-Verbose "$(Get-Date): Shrinking $($Select.Name) database/log file: $Name" | |
| $Result = Invoke-SQLQuery -Instance $Instance -Database $Select.Name -MultiSubnetFailover -Query "DBCC SHRINKFILE($Name,1)" |
This is the point where I mention a little scope creep in the project. I’ve been talking about how this script is all about shrinking the log file, and that’s even what I ended up calling the script, but I realized it didn’t have to be just that. On very rare occasions you’ll want to shrink the database file too. Now, you could just go into SSMS and do that from the GUI and considering the times you’re going to be called to do this, doing it via he GUI is perfectly fine. But I’ve already done about 95% of the work so why not include it in this script? That’s where $Type comes in. In SQL the type of the database is 0 if it’s the database and 1 if it’s the transaction log. But if I specify a $Database switch, it’s going to be 1 when I want to do the Database and 0 when I want to do the transaction logs. Might as well just use this switch as my key, so I flip the boolean value to match how SQL defines type and then cast the boolean as an integer which then turns it from $true/$false to 1/0. Now the $Type variable will match the type field in SQL and all I have to do is include it in the query to get the logical name ($Name = … above) and I’ve got the proper file. I then use another Invoke-SQLQuery call and use the DBCC SHRINKFILE command to drop the file size to the smallest limit that SQL will allow.
After that I run another file size query–pretty much Alex’s code–with a WHERE clause to limit it to just the database I’m working with. From there I can build the before/after output:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| If ($Result) | |
| { | |
| $BackupByDBQuery = @" | |
| WITH fs | |
| AS | |
| ( | |
| SELECT database_id, type, size * 8.0 / 1024 AS size | |
| FROM sys.master_files | |
| ) | |
| SELECT | |
| db.name, | |
| db.database_id, | |
| CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 0 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS FileSizeMB, | |
| CAST(ROUND((SELECT SUM(size) FROM fs WHERE type = 1 AND fs.database_id = db.database_id),2) AS DECIMAL(12,2)) AS LogSizeMB | |
| FROM sys.databases as db | |
| WHERE name = '$($Select.Name)' | |
| "@ | |
| $NewSizes = Invoke-SQLQuery -Instance $Instance -Database Master -MultiSubnetFailover -Query $BackupByDBQuery | |
| $Select | Add-Member -MemberType NoteProperty -Name NewFileSizeMB -Value ($NewSizes.FileSizeMB) | |
| $Select | Add-Member -MemberType NoteProperty -Name NewLogSizeMB -Value ($NewSizes.LogSizeMB) | |
| Write-Output $Select |
My original object $Select is still valid and shows the databases that were selected for shrinkage, so I just use Add-Member to add to that object and add the after information so you can see how the log file has shrunk. And that’s it. The rest of the script is comment-based help and error trapping.
Download Shrink-SQLLogFile.ps1 here
You may have noticed I used a non-standard verb here. Typically I avoid this as the verbs approved by Microsoft are usually fine, but in this case it just wasn’t. There’s a Expand verb, but no Shrink?! And Shrink is just going to be too well known, especially to SQL DBA’s that I just felt no choice in using it. So if you ever load it into a module, you’ll need to use the -DisableNameChecking switch to suppress the warning.
4 Comments »
Leave a reply to Announcing the PS.SQL Module « The Surly Admin Cancel reply
Me
LinkedIn
The Surly Admin 
Thanks for sharing this script with us you can also shrink the transaction log file using SQL Server Management Studio. Must see form here: http://www.sqlserverlogexplorer.com/shrink-transaction-file/
Hi John, you’re absolutely right you can definitely do it using the GUI. Of course, the script runs a LOT faster and is a lot easier to deal with, plus you can shrink multiple logs at the same time. Of course, the reality is Shrink doesn’t work that often and you’ll often have to run full backups and log backups just to truncate the log enough so you CAN shrink it. Could script that too, I guess but have to draw the line somewhere!
[…] Shrink-SQLLogFile: point the script at a SQL server, receive a list of databases and select the ones you want to attempt to shrink the log for. Yes, I know Shrink is not an approved verb and I’ve been resisting renaming it for the longest time because Shrink just makes sense. But I gave in and the next version of PS.SQL will rename the function to Resize-SQLLogFile. […]
Good postt