I just ran into a problem where we were not able to create new tables in the database.  The CREATE TABLE command would simply hang.  After examining the log files, we saw the following error:

“Autogrow of file ‘<database_name>_log’ in database ‘<database_name>’ was cancelled by user or timed out after … ”

I examined the log files and found that the log file for the database had grown to over 50Gb.

My first attempt to truncate the log file was to use the “Shrink file” option from within Microsoft SQL Server Management Studio.  To get there, right-click on the database name, select “Tasks” -> “Shrink” -> “Files”.  In the dialog box that appears, change the file type from “Data” to “Log”.  I tried several time to shrink the log file this way.  No luck.  I was not able to reduce the size of the log size to less than its allocated 50Gb.

The solution that my co-worker and I found was to use some SQL statements to reduce the log file size.  The SQL we used was as follows:

The <TransactionLogName> parameter in the commands above is the logical name of the log file as defined in the database, not the name of the physical file on disk.  To find the logical name of the log file to use as the <TarnsactionLogName> parameters in the command above, do the following:

In SQL Server Management Studio, right-click on the database name and select “Properties”.  In the dialog box, select the “Files” page from the left navigation pane.  In the database files view, look at the “Logical name” column to get the logical name of your log file.  Make sure you use the correct name or you could unexpectedly find yourself without data in your database.

Once I ran the above SQL commands, the log file was reduced to its initial size.  In my case, the file went from 50Gb to 10Mb.  That’s 3 orders of magnitude smaller.

Hope this helps you reduce the size of a bloated log file in your SQL Server database.

Follow

Related posts: