Hello, my name is Curt. I'm a Senior Principal Engineer working with the GSE Team.
This video is dedicated to demonstrating how to shrink a Microsoft SQL Server database transaction log file. We'll review the different ways to shrink the transaction log, and what to do if the shrink log operation fails to run as expected.
So we have a couple of different ways to shrink the log file. We can go into SQL Server Management Studio. go to the properties of the database and do the shrink operation there and we're going to demonstrate that first off.
Then we also have the operation of doing the TSQL ShrinkFile command. The other thing that we want to discuss in this particular video is what happens if the shrink file operation fails to run as expected, and we're going to discuss options you'll want to take in that case Let's go ahead and get into Studio Management.
We're going to be working with the ‘AdventureWorks2017’ database and if we go to ‘Tasks’ on the database menu and come down to ‘Shrink’ we have two different options.
We could work at the database level dealing with all files within the database or dealing with specific files, and in this case we definitely want to go with specific files. Now by default you're going to see, if you're looking at our palette here, we have ‘Data’ that's showing up under the ‘File type’.
We're not going to do anything with the database file, instead we're looking to shrink the transaction log, so the transaction log is literally ‘Log’ under the ‘File type’. Now, we have some information about the database.
The database log is fairly small, this is a new database, and we see the size that's allocated to that transaction log, and we see free space. Free space can be important because without free space we cannot execute a shrink file operation on a database.
There's several different options. We're not going to go into a whole lot of detail here, but we have the option to release unused space which is basically truncating the transaction log. We could run this by just clicking OK and it would go ahead and do a shrink, or do an attempted shrink on that database. Instead of that we're going to go ahead and take a look at the TSQL option for shrinking the database.
I'm a bit lazy today so I'm going to go ahead and come up to ‘Script Action to Clipboard’ which is going to pull the transaction, the TSQL command that would have been run if we had clicked OK here.
I'm going to go ahead and click cancel, and I'm going to go ahead and paste in the TSQL command for doing a shrink on our AdventureWorks2017 database. Now, to go through the command once again, it's a shrink file command. We're dealing with the AdventureWorks2017 log, and we can specify how small we want that log to be.
In this case we're going to keep it to the default of zero. If you don't define anything here, or you leave the value as zero it will attempt to shrink the file to the original file size. We have the ‘truncateonly’ option which is giving up free space within the transaction log back to the OS.
So as we run that now we have a report of what it did and current size. So it went ahead and reduced it down to around a meg, which is great for us. A lot more additional disk space given back to us there. So, one of the things we also want to discuss in this video is what happens if we run shrink file and it fails to run successfully.
The number one reason why the shrink file operation would fail is because there's not enough free space within the database to execute the shrink file command. The first thing we'll we will want to do is to figure out the amount of free space within the file.
Now, we did see it through the GUI menu there we can also run this particular command against - now we have changed our focus here, we're working with the AdventureWorks2019 database instead. Running this particular command, the select command, it'll give us the amount of free space that is seen in that database.
Currently we're seeing three megs of free space within the database. There are two different options that we could use to try to reduce the database in a case where we've ran out of free space within that particular database. We can change the option, the recovery option, for the adventure Works 2019 database.
If we change the recovery option to ‘simple’ and we run the dbcc shrink file command it should reduce the file down to a size that is much smaller than the original size. Whenever we're putting our database into into simple mode the transaction log is dealing with active transactions and anything that's not active is being flushed out of the log.
We can run this particular commands. Most production databases will be in full recovery so going to simple allows us, or at least gives us a chance, to run the shrink file command successfully. So we're in simple mode, and once again our scenario is we were failing to run this successfully.
After going to simple mode we run the sync shrink file operation successfully, it gives us the information about the new file size, and the last thing we would want to do in that particular case is put it back into full recovery mode.
So at this point we've taken an unmanageable transactional log file in terms of using the shrink file command and actually brought it to a state where we're able to shrink that file and we put it back into a mode where it now can be backed up to ensure that we get the latest transaction information.
That concludes our demo of how to shrink a Microsoft SQL Server database transaction log.
Thank you for watching.
