Transaction Log Fills Up the Drive….Oh No!

SQL Server Best Practices
SQL Server Best Practices
Maybe your users cannot access your database, or maybe your preferred monitoring software is reporting limited or non-existent disk space on your log drive for a particular SQL Server. Maybe you get a call from a Subject Matter Expert telling you their application is wigging out, or maybe you get a call from a user or even better, from your helpdesk. We have all gotten that phone call or e-mail at one point or another. A transaction log has gone awry and wreaked havoc on your limited drive allocated to the log files. What next?

Do we know the database? We could look at the drive and find the culprit. However, we will need the logical name of the log file in order to shrink it, remember it isn’t always the same as the filename. Run the following in SQL Server Management Studio (SSMS) in order to get the log file name:

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

At this point, we can try to shrink the database by issuing the following command:

DBCC SHRINKFILE('<DB log file name>', 0)

If this does not shrink the file, then run the following command to see the log_reuse_wait_desc column (way over to the right):

SELECT * FROM sys.databases WHERE name = '<DB Name>'

The following values of the log_reuse_wait_desc are the most common reasons that the shrink operation has failed:

Continue reading on SQLGator.com.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *