Lets talk about a very common but complex/tricky problem when your production SQL database starts having performance issues. Some of these are related to huge database size both mdf (data) and ldf (log).
I have recently been part of problem where one of our dataabase had mdf file which was about 400Gb and ldf (Log file) was about 200GB. Now it was getting difficult to manage the backups, ability to quickly restore in case of need and all other associated problems.
The business was keen to find a workable solution as soon as possible. And after a few attempts I was able to get the database size reduced to ~200GB. Obviously there was no magic and I was able to free up a large ammount of data by deleting/archiving records from an Audit table which gets hit by record inserts for every user action in the application.
But interstingly SQL does not free up the space automatically after you delete data/records from the database. It still occupies the same disk space on the file system. But I just deleted large amount of data from my database and I know there is free space in the database. So I thought I’ll document the steps that I went through in a blog post for a ready ref. to anyone having a similar problem statement.
There are couple of ways to achive your desired outcome. But as always there are certain limitations and drawbacks of each option that you chosse.
Shrinking the database – Options
Shrink LDF File
select * from sys.database_files
SELECT GETDATE();
use MyProdDatabase
SELECT GETDATE();
backup log MyProdDatabase TO DISK = ‘C:\MyProdDatabase\MyProdDatabase.trn’
SELECT GETDATE();
–First param is file_id for log file which is 2 (Validate it from the first query at the top, sys.database_files)
–Second parameter is the final size (in MB).
DBCC SHRINKFILE(2, 500)
SELECT GETDATE();
*DBCC SQLPERF(LOGSPACE)
SELECT GETDATE();
*DBCC LOGINFO
SELECT GETDATE();
There is an AutoShrink option available in SQL. It looked like it is the right approach to all my problems. But hold it. The world is not that simple and easy. The devil is in the detail. But let’s talk about Auto Shrink first and why it might not be a good solution in this case.
Shrinking is a resource intensive activity and uses high CPU, Input/Output, and leads to high logging activity along with sometimes blocking of other operations. Also the automatic SHRINk takes away the control from the user in the sense that it runs in the background and you have little control over it to monitor and start stop at a desired time. You may ideally be looking to run it under your supervision and monitor it’s progress.
DBCC SHRINK / DBCC SHRINKFILE
(This option can be used from the Query editor or also using the SQL Server Management Sudio. )
I’ll attach some screenshots for ref. but to be honest when i used the SQL Server Management Sudio, i did not get the desired results. So not certain if that works. The Queries worked perfectly fine though.