How to shrink data or log files in SQL Server. What to do if a file is not shrinking, how to find the reason for not shrinking.
Connect to your SQL Server Instance
Click New Query to open a query window. Now first run the below query to find the name of the data or log file you want to shrink.
use DatabaseName --Replace DatabaseName with your database name go sp_helpfile go
Once the file is identified run the below query to shrink the file. This will shrink the file to 100MB
use DatabaseName go dbcc shrinkfile(DatabaseName_log, 100) --Replace DatabaseName_log with your data or log file name go
Note that shrinking a data file can cause fragmentation and is bad for queries. You might have reindexing scheduled on weekends which will fix the fragmentation. However you may face performance issues until the weekend. To avoid the issues try avoiding shrinking data files, or you may use truncateonly option. truncateonly releases free space at the file’s end, this way it will not cause fragmentation. However as it removes only free space at the end of a file, it may not clear much space that you may need. You can use truncateonly only transaction log file as well, it just removes inactive VLFs from the end of the log file.
use DatabaseName go dbcc shrinkfile(datafilename,truncateonly) go
Note that in case of transaction log files, you may not be able to shrink if there is an active transaction OR in case of a Full Recovery mode database, a transaction log backup is pending. To know the reason why its not clearing the space, run the below query.
use master go select log_reuse_wait_desc from sys.databases where name = 'DatabaseName' --Replace DatabaseName with your database go
If the reason (log_reuse_wait_desc) is an open transaction, find the open transaction using the below query and kill it, or ask the user/owner to stop the query execution.
use DatabaseName --Replace DatabaseName with your database go DBCC opentran go
If the reason (log_reuse_wait_desc) is pending log backup, run a log backup and try shrinking.
If the log is not shrinking because of Replication, you will have to check if replication is stuck, or in case of an orphan replication, remove the replication from the database using sp_removedbreplication.
In some cases you might need to shrink a log file to avoid outages. In those cases only, change the recovery mode of the database to simple (which will break the log backup chain), and shrink the log file. You can do that only if the database is not having LogShipping, Mirroring Or AlwaysOn enabled. Also if you follow this setup, after shrinking put the database back to full recovery mode and take a fresh full backup, which will restart the log backup chain.
