REBUILDING LOG

  • 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

     

  • 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. 

  • 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

  • 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

  • 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.

  • Nice post with all steps. but....

    DBCC REBUILD_LOG doesnot work with SQL 2005.

     

  • 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...

     

  • 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..

  • Please note: 4 year old thread.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • np :). I felt that thread was incomplete.

  • You would probably be better served by starting a new thread rather than add to a 4 year old thread.

  • Gourav Das (7/16/2010)


    np :). I felt that thread was incomplete.

    I don't care if its 40 years old. nwb's post about 'attach_force_rebuild_log' lead to a final solution that saved my !@#.

    I had a 1tb database that had just imported millions of records over 36 hours before crashing with a created log file of 160gigs in that day and a half. I didn't have 2-3 days to allow this to 'recover' (data loss not a problem in this case [importing of data can be replicated at failed records] I just could not afford the lost time)

    DBCC TraceOn(1807)

    CREATE DATABASE myCoolDB

    ON (Filename = N'\\my\cooletwork\path\myCoolDB.mdf')

    FOR ATTACH_FORCE_REBUILD_LOG

    DBCC TraceOff(1807)

  • Do note that even that can and will fail. It can also result in a DB that's structurally inconsistent (not just transactionally), one that's got metadata corruption, allocation corruption or other sorts of really unpleasant and very hard to fix problems.

    The log is not something that can be deleted without concern.

    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

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply