SQL Server - Yikes! Can't rebuild log because database was not cleanly shut down

Author

Yikes! Can't rebuild log because database was not cleanly shut down

Jeff Turner

2006-03-25, 11:55 am

big problems, hoping someone can help. I'm trying to attach a database
after a server failure, and it won't let me attach it with the
transaction log I have OR w/o the transaction log and trying to get it
rebuilt.

I have 1 MDF and 3 NDFs. I created a new database with identical MDF
and NDF filenames, shut down SQL, copied my "good" database files over
top of them, and started SQL back up.

In Management Studio the database is there, but it won't let me do
anything -- it just tells me the database can't be opened "due to
inaccessible files or insufficient memory or disk space".

If I look in the SQL logs I find this: "The log cannot be rebuilt
because the database was not cleanly shut down."

does any have any pointers on how I can resolve this and get this thing
back online ?

the obvious answer of "restore from backup" aside... obviously if that
was an option I'd be there right now.

thanks!

2006-03-25, 11:55 am

Try this undocumented stuff provided by Kevin Sun[MS].

---
==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============

--
-oj

"Jeff Turner" <zigjst@gmail.com> wrote in message
news:1143225807.978705.216100@v46g2000cwv.googlegroups.com...
> big problems, hoping someone can help. I'm trying to attach a database
> after a server failure, and it won't let me attach it with the
> transaction log I have OR w/o the transaction log and trying to get it
> rebuilt.
>
> I have 1 MDF and 3 NDFs. I created a new database with identical MDF
> and NDF filenames, shut down SQL, copied my "good" database files over
> top of them, and started SQL back up.
>
> In Management Studio the database is there, but it won't let me do
> anything -- it just tells me the database can't be opened "due to
> inaccessible files or insufficient memory or disk space".
>
> If I look in the SQL logs I find this: "The log cannot be rebuilt
> because the database was not cleanly shut down."
>
> does any have any pointers on how I can resolve this and get this thing
> back online ?
>
> the obvious answer of "restore from backup" aside... obviously if that
> was an option I'd be there right now.
>
>
> thanks!
>

Tibor Karaszi

2006-03-25, 11:55 am

> Try this undocumented stuff provided by Kevin Sun[MS].

....just remember that SQL Server could have had roll-forward and rollback operations to perform in
the now missing log, so the database is potentially inconsistent state.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"oj" <nospam_ojngo@home.com> wrote in message news:%23Dz9Wb3TGHA.4132@TK2MSFTNGP11.phx.gbl...
> Try this undocumented stuff provided by Kevin Sun[MS].
>
>
> ---
> ==========
> 1. Back up the .mdf/.ndf files at first!!!
>
>
> 2. Change the database context to Master and allow updates to system tables:
>
>
> Use Master
> Go
> sp_configure 'allow updates', 1
> reconfigure with override
> Go
>
>
> 3. Set the database in Emergency (bypass recovery) mode:
>
>
> select * from sysdatabases where name = '<db_name>'
> -- note the value of the status column for later use in # 6
> begin tran
> update sysdatabases set status = 32768 where name = '<db_name>'
> -- Verify one row is updated before committing
> commit tran
>
>
> 4. Stop and restart SQL server.
>
>
> 5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
> suspected db.
> The syntax for DBCC REBUILD_LOG is as follows:
>
>
> DBCC rebuild_log('<db_name>','<log_filename>')
>
>
> where <db_name> is the name of the database and <log_filename> is
> the physical path to the new log file, not a logical file name. If you
> do not
> specify the full path, the new log is created in the Windows NT system
> root
> directory (by default, this is the Winnt\System32 directory).
>
>
> 6. Set the database in single-user mode and run DBCC CHECKDB to validate
> physical consistency:
>
>
> sp_dboption '<db_name>', 'single user', 'true'
> DBCC checkdb('<db_name>')
> Go
> begin tran
> update sysdatabases set status = <prior value> where name = '<db_name>'
> -- verify one row is updated before committing
> commit tran
> Go
>
>
> 7. Turn off the updates to system tables by using:
>
>
> sp_configure 'allow updates', 0
> reconfigure with override
> Go
> ============
>
>
> --
> -oj
>
>
> "Jeff Turner" <zigjst@gmail.com> wrote in message
> news:1143225807.978705.216100@v46g2000cwv.googlegroups.com...
>
>

Jeff Turner

2006-03-25, 11:55 am

thank you both for your replies...

I'm using SQL 2005 Standard, and when I try to update sysdatabases I
get:

"Ad hoc updates to system catalogs are not allowed."

does this solution apply to SQL 2005 ?

Tibor Karaszi

2006-03-25, 11:55 am

You cannot modify system tables in 2005, but you can use ALTER DATABASE to set emergency mode. I
don't know, though whether the other parts work on 2005.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

"Jeff Turner" <zigjst@gmail.com> wrote in message
news:1143229655.420077.267890@t31g2000cwb.googlegroups.com...
> thank you both for your replies...
>
> I'm using SQL 2005 Standard, and when I try to update sysdatabases I
> get:
>
> "Ad hoc updates to system catalogs are not allowed."
>
>
> does this solution apply to SQL 2005 ?
>