November 6, 2012 at 7:45 pm
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.
November 6, 2012 at 8:53 pm
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 EMERGENCYALTER 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?
November 7, 2012 at 12:00 am
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 EMERGENCYALTER 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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2012 at 7:47 am
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".
November 7, 2012 at 9:20 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2012 at 9:47 am
Read it.
Thanks for the response, Gail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply