Rebuild/Repair corrupt LDF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Rebuild/Repair corrupt LDF

Author

Topic

jackyy
Starting Member

4 Posts

Posted - 2008-11-19 : 16:47:53

New to the forum and found this in the SQL 2000 forum. Tried the steps and received this message:

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Any thoughts?

The original thread is here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39820

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-19 : 21:01:45

The statements you actually ran rather than just the message would be nice.

Did you run reconfigure after changing the allow updates and before doing your system table update?
And did you run it as a separate batch?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jackyy
Starting Member

4 Posts

Posted - 2008-11-19 : 21:06:24

Statements re in the link at the bottom of my post. I can post them in here if you want but I thought it might be useful to see where I got the info from also.

Here is what I was trying to do from the original thread...now 4 yrs old I just realized!

Posted - 09/13/2004 : 16:27:41 Show Profile Reply with Quote
Solution:
LISTING 1: Undocumented DBCC Command REBUILD_LOG
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN

UPDATE master..sysdatabases
SET status = status | 32768
WHERE name = 'MyDatabase'

IF @@ROWCOUNT = 1
BEGIN
COMMIT TRAN
RAISERROR('emergency mode set', 0, 1)
END
ELSE
BEGIN
ROLLBACK
RAISERROR('unable to set emergency mode', 16, 1)
END

GO

EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- Restart SQL Server at this point.

DBCC REBUILD_LOG('MyDatabase','C:\MyDatabase.ldf')

/*Perform physical and logical integrity checks at this point.
Bcp data out if your integrity checks demonstrate that problems exist.
*/

ALTER DATABASE MyDatabase SET MULTI_USER
GO

-- Set database options and recovery model as desired.
GO

---

Incidentally, I realize that "detatching" a database should be utilized before one should ever hope to be able to reliably follow with "attach db", but these were the cirumstances that I was given: Two files, mdf and ldf, and the ldf is corrupt so all attach methods were failing.

The above REBUILD_LOG works WONDERFULLY, although there are obviously data integrity caveats. It has, however, successfully taken the database out of 'suspect' and replaced the .ldf file nicely in this instance.

jackyy
Starting Member

4 Posts

Posted - 2008-11-24 : 15:31:40

Anyone have any suggestions of where to start on this or am I just screwed?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-25 : 11:24:58

Is the original advice in the previous forum not helpful - i.e restore from backups - i.e the *.BAK and *.TRN files?

celestia
Starting Member

2 Posts

Posted - 2008-11-26 : 04:45:46

The answer is in the error: Ad hoc updates to system catalogs are not allowed.

In SS2K ad hoc changes to system tables weren't supported, in SS2K5 they are not allowed. As darkdusty said, there are documented (ie supported) ways of doing what you want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 05:18:41

You can attach the database with recreating the LDF with

sp_attach_single_file

or, it you are using sql server 2005 and later, ALTER DATABASE ...

E 12°55'05.63"
N 56°04'39.26"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-27 : 06:24:14

In 2005 there's now a documented way to set a database into emergency mode and the hack updating sysdatabases is no longer needed.

ALTER DATABASE ... SET EMERGENCY

What's the situation of the DB, what have you tried?

--
Gail Shaw
SQL Server MVP

yogeshbhoyar
Starting Member

1 Post

Posted - 2009-02-04 : 04:42:54

I am working on SQL Server 2005 and our database log file (ldf) was deleted so i am tring to recover the ldf file and i was tried all these methods to recover missing LDF file but it does not execute properly when i execute the statement

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

it displays following message

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

please give me some idea to recover ldf file
thanks!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 09:16:22

quote:
Originally posted by yogeshbhoyar

I am working on SQL Server 2005 and our database log file (ldf) was deleted so i am tring to recover the ldf file and i was tried all these methods to recover missing LDF file but it does not execute properly when i execute the statement

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

it displays following message

Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

please give me some idea to recover ldf file
thanks!!

Have you tried attaching mdf file with sp_attach_single_file?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-02-04 : 11:49:37

You can't change the system tables in SQL 2005.

Can you expand a bit on what happened? How did the log get deleted? Do you have a backup?

--
Gail Shaw
SQL Server MVP

sourav_tech
Starting Member

1 Post

Posted - 2009-07-12 : 07:03:02

Hello There

Can you suggest me the steps needed to follow up Rebuild the corrupt LDF file in SQL Server 2005\2008??

The dbcc rebuild_log() command doesn't work in SQL 2005\08!

Regards
Sourav

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-07-12 : 11:15:38

Restore from backup if you have one.
If you don't (why not), try setting the DB into emergency mode and doing a repair.

ALTER DATABASE <DB name> SET EMERGENCY
DBCC CHECKDB ('<DB name>',REPAIR_ALLOW_DATA_LOSS)

--
Gail Shaw
SQL Server MVP

dekj
Starting Member

4 Posts

Posted - 2011-02-01 : 19:17:44

I found a way to recreate the log file. I tried many things, and eventually found myself with the database, which I will call POSProd throughout this example, offline and with no way to bring it online. Here is the step-by-step procedure I eventually followed.

1) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

2) Rename out the old MDF (and NDF) files. Copy them as well if you have space, so if you eventually corrupt the original files, you will still have something to work with. My files were over 2 TB long, and this took 6 hours

3) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

4) In SQL Server Management Studio, right-click the POSProd database, and delete it.

5) Right-click Databases, and create a new POSProd database, with the same files as the old one. For me, this included the MDF file, the LDF file, and the NDF file.

6) Stop the SQL Server Management Studio. At a command prompt, enter

net stop "SQL Server (MSSQLSERVER)"

this ended up stopping the SQL Server and the SQL Server Agent.

7) Delete the new MDF (and NDF) files, but leave the LDF log file.

8) Rename the old MDF (and NDF) files back to their original names

9) Restart the SQL Server and SQL Server Agent. . At a command prompt, enter

net start "SQL Server (MSSQLSERVER)"
net start "SQL Server Agent (MSSQLSERVER)"

This restarted these two services, which had been stopped previously.

10) At this point, you will have a POSProd database pointing to the correct database files, but also to an LDF log file that is still not attached to them. It seems that they are all related with GUIDs or something, and the LDF GUID is still incorrect, so you will still not be able to act on the database. DON’T DO ANYTHING ELSE WITH THE DATABASE EXCEPT WHAT I HAVE DONE BELOW. I don’t know what effect that will have on the database.

11) Open a new query in SQL Server. Alter the POSProd database to emergency mode

alter database POSProd set emergency

12) Set the database to single user mode. Without this, the checkdb command won’t run.

ALTER DATABASE [POSProd] SET SINGLE_USER WITH NO_WAIT

13) Run dbcc checkdb

dbcc checkdb ('POSProd',repair_allow_data_loss)

Once again, because the database was about 2 TB, this ran for about 10 hours. Its listing showed many errors, but also noted that the LDF file was recreated for the original POSProd files. This looked very interesting.

14) Alter the database back to multi user mode

ALTER DATABASE [POSProd] SET MULTI_USER WITH NO_WAIT

15) Set the database online.

alter database POSProd set online

16) Now you still do not have a good database, because SQL Server still knows about the old database. So detach the database by right-clicking the POSProd database, and selecting Tasks / Detach.

17) Attach the database back by right-clicking Databases and selecting Attach. Browse to the MDF file and press OK.

Voila!! The database (in my case) was completely back. All the tables, every row of data.

Good luck if you need this, and remember, DO NOT EVERY DELETE THE LOG FILE. It is as important to SQL Server as the database files, even if (as in my case) there are no transaction boundaries involved in writing to the DB.

dekj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources