SQL server, SQLServerPedia Syndication

Log file disk full and sp_add_log_file_recover_suspect_db.

We all know that the log file is stored on disk and can be set to auto grow without limits. There may be times when your log file goes unmonitored and fills up your drive. When this has happened in the past, I used to have a few files (like the dead weights in the air balloons) that I used to delete to free up a few hundred megs of space. Then I would proceed with a shrink of the log file. But what if a scenario came up with no more free space left thereby preventing any SQL action like a shrink?

I came across this SP: sp_add_log_file_recover_suspect_db. If you have another disk with some free space on it, you can use this SP to add another file to your log file filegroup, and now the DB will be out of suspect mode, and the DB will be able to continue writing to the log file. Meanwhile, find the reason for the log file growth, and see if it can be controlled. Once you have the extra room, the DB is made available and you can work on fixing the log size issue.


Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s