Shrinking log file in SQL Server has no change in file size

Question

I work for an organization and i have come across a problem where i cannot shrink the log file. The SQL server database is consuming 400 GB out of 600 GB hard disk drive. The log file size is around 350 GB.

I tried shrinking the log file but the size remains the same. The recovery model of my database is SIMPLE.

I have gone through online posts where there are suggestions to take backup of the log file and then try shrinking.But I do not have enough free space on my drive to save the log backup.(Note: I have only one drive in my system and I am not supposed to take backup on external drive ).

Will moving this entire DB to a different drive help ? Any solutions ?

Answer 1

You have an open transaction. Kill all connections then try again.

Answer 2

I tried shrinking the log file but the size remains the same. The recovery model of my database is SIMPLE.

I have gone through online posts where there are suggestions to take backup of the log file and then try shrinking.

If your db is in SIMPLE you cannot do log backups.

Instead, you should issue one or more times:

CHECKPOINT; DBCC SHRINKFILE...

This will help in case when log_reuse_wait_desc shows NOTHING/CHECKPOINT.

If it shows anything else you should eliminate the cause of what it's showing on

Answer 3

If your database is in SIMPLE mode, change the file size for the log to a smaller value, as low as 1 MB. The server will size it as small as it can.

However, be aware that the log file is sized to contain all active transactions. In your example, at one point in time you had 350GB worth of transactions in progress. Note that existing server activity can prevent it from shrinking to the desired size.

In simple mode, shrinking the log file is only worthwhile if you had a run-away transaction blow out the log. Otherwise, shrinking the log file doesn't solve anything and impacts performance because the server has to immediately grow the log file again for new transactions.

You can, and should, move the log file to a different drive than the database file.