Database Log File Full - Emergency Work-Around Script

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » Database Log File Full - Emergency...

Add to briefcase

Database Log File Full - Emergency Work-Around Script

Expand / Collapse

Author

Message

Posted Tuesday, November 06, 2012 7:45 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 PM
Points: 254, Visits: 1,032

I'm doing my best to recreate a script I read written by Gila Monster (Gail Shaw) when she was recently assisting a DBA who had a database in "suspect" status because he had a database log file's hard drive completely full.

I couldn't find the script in forum posts so I did some research, added a few lines of my own based upon my research, and tested the following script which worked as expected. It created a second log file in another location (which would buy some time to fix the root problem of runaway log file growth).

I'd like at least a second opinion. Did I leave anything out? Is the sequence of commands optimum?

ALTER DATABASE <dbname> SET EMERGENCY
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE <dbname> SET SINGLE_USER
ALTER DATABASE <dbname> ADD LOG FILE (NAME = <newlogicalfilename>, FILENAME = '<newwindowsfilepath>.<newlogicalfilename>.ldf', SIZE = NNN GB)
ALTER DATABASE <dbname> SET MULTI_USER
ALTER DATABASE <dbname> SET ONLINE
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC ON

I collect scripts like this for emergencies.

Post #1381770

Posted Tuesday, November 06, 2012 8:53 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 06, 2013 10:38 AM
Points: 316, Visits: 1,488

Lee Crain (11/6/2012)

I'm doing my best to recreate a script I read written by Gila Monster (Gail Shaw) when she was recently assisting a DBA who had a database in "suspect" status because he had a database log file's hard drive completely full.

I couldn't find the script in forum posts so I did some research, added a few lines of my own based upon my research, and tested the following script which worked as expected. It created a second log file in another location (which would buy some time to fix the root problem of runaway log file growth).

I'd like at least a second opinion. Did I leave anything out? Is the sequence of commands optimum?

ALTER DATABASE <dbname> SET EMERGENCY
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE <dbname> SET SINGLE_USER
ALTER DATABASE <dbname> ADD LOG FILE (NAME = <newlogicalfilename>, FILENAME = '<newwindowsfilepath>.<newlogicalfilename>.ldf', SIZE = NNN GB)
ALTER DATABASE <dbname> SET MULTI_USER
ALTER DATABASE <dbname> SET ONLINE
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC ON

I collect scripts like this for emergencies.

No. Why would you set the db into emergency mode just because of excessive log growth?

What scenario are you expecting this script to resolve?

Post #1381780

Posted Wednesday, November 07, 2012 12:00 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 38,072, Visits: 30,366

Lee Crain (11/6/2012)

I'm doing my best to recreate a script I read written by Gila Monster (Gail Shaw) when she was recently assisting a DBA who had a database in "suspect" status because he had a database log file's hard drive completely full.

A database will not go suspect because the log is full. If the log fill up, the DSB goes read only, that's all.

I'd like at least a second opinion. Did I leave anything out? Is the sequence of commands optimum?

ALTER DATABASE <dbname> SET EMERGENCY
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
ALTER DATABASE <dbname> SET SINGLE_USER
ALTER DATABASE <dbname> ADD LOG FILE (NAME = <newlogicalfilename>, FILENAME = '<newwindowsfilepath>.<newlogicalfilename>.ldf', SIZE = NNN GB)
ALTER DATABASE <dbname> SET MULTI_USER
ALTER DATABASE <dbname> SET ONLINE
ALTER DATABASE <dbname> SET AUTO_UPDATE_STATISTICS_ASYNC ON

I collect scripts like this for emergencies.

Um, no!
I certainly did not ever write anything like that (if I did, I need to hand that MCM back).

Emergency mode is the absolute last resort for a suspect or recovery pending DB. Once in emergency mode one has to run checkDB with the repair allow data loss option on said suspect DB (no other repair option is valid) to get back online (and the repair may fail). Hence why it's the last resort when there's no good backup.
A full log will not send a DB suspect, so adding a log file is pointless

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1381804

Posted Wednesday, November 07, 2012 7:47 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 PM
Points: 254, Visits: 1,032

Well, that's why I posted this, for corrections.

The scenario I thought I remembered was:
1. Log file full, database in read-only, suspect mode,
2. Disk full, no room for the log file to grow.

If the log file and its disk drive were both full, would the database be marked "suspect"? If not, how would SQL Server respond to this situation?

And what would be the correct DBA response to this situation? I've already read the warnings about setting the database recovery model from "Full" to "Simple".

Post #1381988

Posted Wednesday, November 07, 2012 9:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 38,072, Visits: 30,366

Lee Crain (11/7/2012)

Log file full, database in read-only, suspect mode,

A full log does not send a database suspect.

If the log file and its disk drive were both full, would the database be marked "suspect"? If not, how would SQL Server respond to this situation?/

As I said earlier, no. The DB goes read only. That is all. Suspect is when a rollback or crash recovery fails due to corruption.

And what would be the correct DBA response to this situation? I've already read the warnings about setting the database recovery model from "Full" to "Simple".

Go read my article 'Why is my transaction log full?'
Worst case, add a log file and then sort the problem out once the DB is working again.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1382051

Posted Wednesday, November 07, 2012 9:47 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 PM
Points: 254, Visits: 1,032

Read it.

Thanks for the response, Gail.

Post #1382062

« Prev Topic | Next Topic »

Add to briefcase

Permissions

Expand / Collapse