Running DBCC Shrink commands is quite a controversial issue across the SQL Server community. In this article, we will review details about this command and provide a brief overview of its use and also warn you about the risks of running this command. As DBAs, a number of databases were handed over to from other teams or vendors, and it is not always we get to manage the databases which we created. As DBAs, whenever we are involved in migrations or new projects, we need to ensure that we carefully plan a smooth transition of the database to production and regular use. It is at this stage that we need to factor in the size of the database. Can you imagine, you set up a database application without considering the growth forecast for the first year or so. How about you create a SQL Server database with size so small that it needs to grow every other day raising capacity disk alerts in the middle of the night? It may sound silly, but in reality, the truth is this happens, and this sometimes may not be in your control.
Few points to consider for the Proactive DBA
Before taking over support of the production databases, be sure to check with your predecessor what the forecasts are in terms of database growth. Is the initial size of the databases that you will manage sized adequately? Don’t worry too much if the current size of the database is way bigger than the data it currently has. Remember, you don’t want those disk capacity calls at 3:00 am in the morning when you could have totally avoided it by having a correctly sized database. It is a general trend for database administrators across the industry to sacrifice their lives late at night on mundane issues which should have well not occurred in the first place. Also, along with the database size, keep in mind the disk capacity of the server. You don’t want the disk size to be way too small than what a database can accommodate. These are all simple things that come handy at the time of planning. However, as you know, it is not always a database professional who is installing or configuring databases in the first place. The important point to note is to get the basics right with your database in terms of sizing and you may not need to run this command ever. However, as always, as a DBA, there are times when things may not be within your control and during this time you can use DBCC Shrink file commands for efficient use.
When can I use DBCC ShrinkFile?
You just received a disk space alert right in the middle of your sleep and you have strict SLA’s to meet. The priority level is a P2 and the SLA may breach very soon. And you realize expanding the disk is not going to happen any time soon, well, in that case, keep your DBCC ShrinkFile commands handy so that you can use them to reclaim the space. As the name suggests, it shrinks the file of the data or log file of the database. But before you start off with running the DBCC ShrinkFile commands, try to check why the database file is growing in the first place.
- Did the file grow due to some long running transaction?
- Is there any kind of blocking on the server?
- Is there any major application release happening that you have not been informed about? (This happens most of the time)
- Is there any kind of database replication or mirroring issue causing database growth?
It is important to get answers to these questions as quickly as possible. Generally, there is one answer to all of these questions and it is a great free tool called sp_whoisactive. There are no words to describe the enormous use of this tool and I have used it on multiple occasions to fix numerous production related issues. You can download the latest code from this link: http://whoisactive.com/ . It is easy and simple to use and returns the output in no time. If you are seasoned DBA, you will already have this at your disposal.
DBCC ShrinkFile with examples
The syntax for the DBCC ShrinkFile is simple and straight forward, refer to this example below.
use YOURDATABASE go DBCC Shrinkfile(FileName,1024)
The above example shrinks FileName belonging to YOURDATABASE to 1024 MB. You can perform the same operation using SQL Server Management Studio (SSMS). Right-click the database, go to Tasks, select Shrink, and then Files.
Once you click Files, you will get this window.
Here, you have the option to select the file type: Data, Log or Filestream Data and perform the “Shrink action” as required. It is easier to use the DBCC command itself for shrinking purposes.
Using DBCC ShrinkFile with additional options
You can run the DBCC ShrinkFile command with additional options – emptyfile, notruncate or truncateonly.
Emptyfile
You can use the emptyfile command like below.
use YOURDATABASE go dbcc shrinkfile(FileName,emptyfile)
This will help to move the data to other files within the same file group. Once done, you will be able to delete a database file if it is no longer required. However, there are few things to note with this emptyfile option as you wouldn’t be able to do much to empty the contents of the primary data file with file ID 1. In order to get the file ID number, run this script.
select file_id, name,physical_name from sys.database_files
Here, in this example, the filename is “mo” and file_id is 1. When you try emptying the file mo which has file_id 1, you will encounter this error message.
This is because there is system information within the original file, which cannot be emptied. But, if you try the same command on the other data file “mo2data”, the empty file command will succeed.
Notruncate
As the name suggests, there is no space released back to the OS. This is more like an internal operation within the file where the pages get redistributed within the file itself without changing the overall size of the database file. Because of this, there is a huge possibility for fragmentation to be introduced. See the example below.
-- Example only Use YourDatabase go DBCC SHRINKFILE (filename,notruncate); GO
Truncateonly
As the name suggests, free space will be released back to the OS from the end of the file. This is by far the safest operation you can run using DBCC ShrinkFile. See the example below.
-- Example only Use YourDatabase go DBCC SHRINKFILE (filename,truncateonly); GO
What to do when DBCC ShrinkFile on the transaction log is not working as expected? Refer to this safe method to fix the issue
There are times when this command may not work as expected. Assuming you have a situation where you are trying to shrink the log file of a database and it does not seem to work. Below are few of the steps you may take to understand why the shrink is not working as expected. You will notice that the shrink file will show as succeeded, however, there is no reduction in the size of the log file. In that case, run this command to check a few things about the log file usage.
select name,log_reuse_wait_desc,* from sys.databases
From the screenshot, you can see that the log_reuse_wait_desc column is waiting on log backup.
Here, you can see that the log backup for the database needs to be performed before you can really shrink the log file. If this is on a production database, try to perform the log backup on another drive where there is space available. For performing the log backup, use the sample command below.
backup log DBName to disk='C:\Program Files\Microsoft SQL Server\MSSQL15.A1\MSSQL\Backup\DBName.trn' with init,stats,compression
After you run the backup command, run the below command again to see the status of the log_reuse_wait_desc column.
select name,log_reuse_wait_desc,* from sys.databases
From the screenshot, you can see that the status for the log_reuse_wait_desc column has changed to “Nothing”.
Here, you can see that the status for the “log_reuse_wait_desc” column has changed to “Nothing”. In your case, it may still show as “LOG_BACKUP”. Continue to perform the log backups for the database until the status changes to “Nothing”. The reason you may still see the “LOG_BACKUP” status even after performing transaction log backups is because no VLFs may have been cleared after you ran the transaction log backup. VLF stands for Virtual log files and is part of the internal architecture of the transaction log. Transaction log files are made up of these VLFs. You can get information about the VLFs by running this command.
select * from sys.dm_db_log_info(5)
Here 5 represents the database_id. The screenshot of the output is shown. The number of rows returned represents the actual number of Virtual log files (VLFs) in the database. You can check the column “vlf_status” to check the status of the virtual log file. Value 2 means it is active. With this command, you can check the internal flags within the transaction log file to understand why the transaction log is not getting freed even after performing log backups.
Previously, the command that was used was DBCC LOGINFO which provided similar information.
What to do when DBCC ShrinkFile on the transaction log is not working as expected? Something you can perform on non-prod environment. However, not recommended on production environment
You would have come across on multiple websites people recommending to change the recovery model to a simple one and then run a shrink file in order to release space back to the OS. Keep in mind that changing the recovery model to a simple one will affect the recovery of your database as you wouldn’t be able to recover to a specific point in time. This again depends on your business SLA. You can change the recovery model using T-SQL (below) or using the GUI.
alter database DB_NAME set recovery simple
Using the GUI, change the recovery model as shown. Right-click the database, go to “Properties”, click “Options”. Under “Options”, select the “Recovery Model”.
You will notice that just changing the recovery model to Simple will not release the space back to the OS. You would need to explicitly run the DBCC ShrinkFile command to shrink the file and reclaim the space. See the sample script below. This command will shrink your FileName to 1024 MB.
use YOURDATABASE go DBCC Shrinkfile(FileName,1024)
Auto Shrink database option
You will notice that there is an option known as “Auto Shrink” within the database properties. Just right-click the database to view the database property. Under the options section, you will see this option as shown. From the model database setting, you can see that the “Auto Shrink” option is disabled by default. So, whenever any new database is created, this option is in the disabled state too. There may be some cases where database professionals may unknowingly leave this option enabled without being aware of the negative consequences of leaving it on.
Run this command to check the status of this option for the databases on the server.
select name,is_auto_shrink_on,* from sys.databases
You will see this output.
If by chance, you see that it is enabled, you can disable it either by using the GUI or you can run the below command against the database.
ALTER DATABASE YOUR_DATABASE set AUTO_SHRINK OFF
Other maintenance suggestions
Refer to these few additional tips to avoid the issue of database growth, due to which you need to run DBCC ShrinkFile commands.
- Ensure the recovery model of your databases is aligned with business SLAs. If your business does not require a point-in-time recovery for test databases, just leave them in a Simple recovery model. I have seen multiple occasions where the recovery model of the databases was complete when things are fine with recovery using the latest full backup
- Ensure proper monitoring in place, especially with database growth. You should be alerted when the log file utilization reaches 85 %. This will give you some time to solve the space issue
- Ensure regular log backups are made if the database is in the Full recovery model and you should be alerted if any of the log backups fail
- Ensure there is sufficient space on the database drives in order to avoid space shortage issues
- For databases that can be archived, develop some archive strategies so that you can move older data to another database to create reports and make that database read-only. This will give you more control in terms of database sizing
- Ensure to regularly perform integrity checks on your database using DBCC CheckDB. For more information, refer to this article: https://codingsight.com/dbcc-checkdb-overview/
Conclusion
- From this article, you got a good understanding of using DBCC ShrinkFile command
- You learned about the risks of running the DBCC ShrinkFile commands
- You learned the different options we can provide using the DBCC ShrinkFile commands
- You saw the options we can try if the transaction log is not shrinking using the DBCC ShrinkFile commands
- You learned about the default auto shrink setting within the database property
- You also learned other database maintenance suggestions in order to keep your databases healthy
- Finally, make sure to be ready in any case for those OFF days which may not be within your control
