Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

REBUILDING LOG Expand / Collapse
Author
Message
Posted Thursday, September 7, 2006 2:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 6:04 AM
Points: 305, Visits: 381

Hi,

I deleted LOG file from test server, thinking that I'll be able to rebuilt it, the way I did it number of times on SQL 2000, but DBCC REBUILD_LOG is not working in 2005 (giving syntax error).

After looking around found ATTACH_REBUILD_LOG  option with CREATE DATABASE , but its not working for me, gives operating system error 2, Could someone help me in recreating log in 2005.

Cheers

 

Deepak Khattar

 




Post #306817
Posted Thursday, September 7, 2006 8:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 27, 2009 2:59 PM
Points: 342, Visits: 68

See BOL for sp_attach_single_file_db

Also See if you can detach the DB and then try and attach but do not specify trans log when you attach it just specify the mdf and the path, a new trans log will be created with a default size in the default location. 




Post #306945
Posted Thursday, September 7, 2006 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:02 PM
Points: 344, Visits: 297

Vik,

sp_attach_single_file_db will work if database was cleanly shutdown i.e. it was detached.

As per BOL
===========
Remarks

When sp_attach_single_file_db attaches the database to the server, it builds a new log file and performs additional cleanup work to remove replication from the newly attached database.

Used sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.

===========

OS error 2 = The system cannot find the file specified.

Few questions:

what is the syntax you are using?

As per BOL (SQL 2005)

FOR ATTACH_REBUILD_LOG requires the following:

  • A clean shutdown of the database.

  • All data files (MDF and NDF) must be available.

 

HTH

Post #306955
Posted Thursday, September 7, 2006 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 27, 2009 2:59 PM
Points: 342, Visits: 68

HTH,

You are correct - if the DB was not shutdown cleanly - it will not work.  I guess the only way is:

1. Rename database.mdf to old_database.mdf

2. Create New Database with same name as old  database

3. Change DB properties to "Allow modifications to be made directly to the  system catalogs"

4. Run this Query
 update sysdatabases
 set status = 32768
 where name='database_name'

5. Stop/Start SQL Server
Database will be in Emergency Mode
Now DTS all the data out of this DB




Post #306974
Posted Sunday, September 10, 2006 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 8:33 AM
Points: 162, Visits: 362

A system admin in a company I know of did exactly the  same, "to reclaim disk space". His backup routine didn't include transaction log backups. To make matters worse, a restore was impossible, because the tape drive had been out of order for a couple of weeks.

I said, 'Yes - I'll see what I can do', and dashed off to my keyboard to search the net. Eventually, I found some possible solutions. All relied on being able to modify the system tables, as some have mentioned above.  I didn't want to try the procedure without checking with Microsoft, so for a few houndred dollars they provided me with a procedure practically similar to the ones on the net:


Microsofts disclaimer on DBCC REBUILD_LOG:

The Command is an undocumented and unsupported command that can be used in emergency situations where the database is offline because of some catastrophic problem with the transaction log. It is designed to destroy the entire contents of the current transaction log, rebuild a new one, and allow the user to bring the database back online. However, this may result in more data lost than if the last known, good backup was restored. As such, if you choose to use the Command with or without the assistance of Microsoft PSS, Microsoft strongly recommends you backup all of the database and transaction log files associated with the database for the Command as a method to recover from problems where the Command is being considered.

If this Command is successful and the database is brought back online, the database may be in a physically and logically inconsistent state. The ACID properties of Atomicity and Consistency are no longer guaranteed. The DBCC CHECKDB command can be used to determine what physical problems may exist. However, there is no method to determine what logical inconsistencies exist within the database aside from the user’s own manual inspection of the database tables. This means that there is no method to determine if the database pages that exist within the database reflect the correct committed state of the database when it was last taken offline.
 

Take a backup first!

1. Create a new DB with the same name as the original DB.
2. Stop SQL Server.
3. Replace the old MDF atop of the newly created one.
4. Start SQL Server, DB should be marked as suspect. Note that the status under master..sysdatabases of the DB is 1073741840.
5.Change the database context to Master and allow updates to system tables:
                
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

6.              
begin tran
-- Putting" a database in emergency mode
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
-- If you run DBCC REBUILD_LOG without setting the database in Emergency mode, the command does not work.
-- You do not receive an error, but the log is not rebuilt either.
 

7.              
-- 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 of the
-- new log file, not a logical file name. If you do not specify the full path, the new log is created in
-- the same directory as the primary database file (.mdf). Therefore, if the log file already exists in that
-- path, use a different name for the new log. You cannot move the log or delete the log because SQL
-- Server is using the log).

8.
begin tran
update sysdatabases set status = 0 where name = '<db_name>'
-- verify one row is updated before committing
commit tran

9.
RESTORE DATABASE <dbname> WITH RECOVERY

Good luck!
This procedure actually worked, the sysadmin didn't loose his job, and the company is thriving.




Post #307476
Posted Monday, September 11, 2006 8:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, October 20, 2014 6:02 PM
Points: 344, Visits: 297

Nice post with all steps. but....

DBCC REBUILD_LOG doesnot work with SQL 2005.

 

Post #307810
Posted Tuesday, September 12, 2006 1:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 8:33 AM
Points: 162, Visits: 362

Eh - blush... How could I ignore that? They deliberately made it useless and replaced it with other, unsupported command(s).
Bastards.

Anyway, hope it can be of use to desperate SQL 2000-users without proper backup routines...

 




Post #307830
Posted Friday, July 16, 2010 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 13, 2010 1:10 PM
Points: 2, Visits: 46
Its replaced with Alter database for attach_rebuild_log and for attach_force_rebuild_log. Second one is not documented.

use second one at own risk..
Post #954149
Posted Friday, July 16, 2010 2:54 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Please note: 4 year old thread.


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 #954191
Posted Friday, July 16, 2010 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 13, 2010 1:10 PM
Points: 2, Visits: 46
np :). I felt that thread was incomplete.
Post #954260
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse