SQL Server Performance

Lose log file

Discussion in 'General DBA Questions' started by vietcave, Sep 20, 2005.

  1. vietcave New Member

    Hi everyone,

    How can I use a database whose log file is lost?

    Thanks
  2. ghemant Moderator

    Hi,<br />dont sure but using sp_attach_single_file_db you can do it, it will make a clean and new log file for you while attaching your db.<br />BOL says what should be taken care while using this option :<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Remarks<br />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.<br /><br />Use sp_attach_single_file_db only on databases that were previously detached from the server using an explicit sp_detach_db operation.<br /><br />Use sp_attach_single_file_db only on databases that have a single log file. Do not use this stored procedure on databases that have multiple log files.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  3. dineshasanka Moderator

  4. vietcave New Member

    No, my hard disk containing the log file is formated.
  5. ghemant Moderator

    hi,
    Log file is formated ?! what does it mean ? is your log file get currepted ?!

    Regards



    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  6. FrankKalis Moderator

    Another way might be to use dbcc rebuild_log as described here:http://www.windowsitpro.com/SQLServer/Article/ArticleID/8636/8636.html
    Make sure you read closely through the explanations at the end of the chapter "Shrinking an Active Log File in SQL Server 7.0".
    However, the question is still valid. How have you lost the t-log file? Or is this a hypothetical scenario question? Anyway, that's a good example where a sound backup and disaster recovery plan is essential.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. vietcave New Member

    I reinstall my Windows. I did not notice my log file is in hard disk D. I formated D before installing Windows. So I lost my log file.

    Regards
  8. FrankKalis Moderator

    Oops, when I started typing there wasn't your explanation how this has happened. So, someone did a low-level formatting of the HD? Might want to visit him with a big sledgehammer? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  9. FrankKalis Moderator

    Well, in this case am I guessing right that you didn't properly take the database offline?
    Chances are more than good that this action corrupted your database, so that you are ot able to recover. You have a recent backup, haven't you?


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  10. ghemant Moderator

    Hi,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vietcave</i><br /><br />I reinstall my Windows. I did not notice my log file is in hard disk D. I formated D before installing Windows. So I lost my log file.<br /><br />Regards<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />hi,<br /><br />may give a try to suggested sp_attach_single_db or<br /><br />create a new database with same name and physical structure e.g. the location of .mdf and .ldf files and physical and logical name. then de-attach the db , stop sql service and then replace copy your existing db's .mdf file with newly created db's (with same name) .mdf file. <br />this is just suggestion might get succeed. <br /><br />Any suggestions on this Frank / Satya, is this possible !<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  11. vietcave New Member

    My backup also is in hard disk D.

    I wish I had it.
  12. ghemant Moderator

    Hi,
    To recover lost / formated data you might try with Stellar Phonix Tool tooo , have you tried my suggested way ! is it working ?!

    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  13. FrankKalis Moderator

    I haven't tried this myself yet. However, what I have once tried was to copy and burn an online database on CD, Well, I got it on CD, but I wasn't able to bring it back online again. I even looked at the files with a hex editor, but didn't succeed. But it's worth a try. Especially when you don't have a recent backup.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  14. dineshasanka Moderator

  15. vietcave New Member

    Exactly, sp_attach_single_file doesn't work well. I have an error 1813.

    I created a new database that is the same name as my old database. And then I replace these data files. But it still doesn't work. It seems data file and log file have a contraint about time.
  16. ghemant Moderator

    quote:Originally posted by vietcave

    Exactly, sp_attach_single_file doesn't work well. I have an error 1813.

    I created a new database that is the same name as my old database. And then I replace these data files. But it still doesn't work. It seems data file and log file have a contraint about time.
    is it that you have more then one data file ?! and have you created new db with same name and same no of files ?!

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  17. vietcave New Member

    No, my database has one data file.

    I create a new database whose name is the same as my old database. And then I shutdown my SQL Server. I delete the data file of this new database (but don't delete log file) and copy the data file of my old database to replace. It doesn't work...
  18. ghemant Moderator

    Hi,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by vietcave</i><br /><br />No, my database has one data file.<br /><br />I create a new database whose name is the same as my old database. And then I shutdown my SQL Server. I delete the data file of this new database (but don't delete log file) and copy the data file of my old database to replace. It doesn't work...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /> <br />please re-do again , don't delete newly created .mdf file and copy on it which will overwrite existing new .mdf with old .mdf file. <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Don't delete old .mdf<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  19. vietcave New Member

    What a pity!

    I still can not access it.

    I check properties by right clicking my old and new data file. And I find out that their times are different. Maybe it is the reason that prevents me from recovering my database.

    Thank you very much
  20. satya Moderator

    Try the following process, which I've documented and followed in critical cases at my end <i><br /><br />REBUILD LOG and recover a suspect database:<br /><br />1. Back up the .mdf/.ndf and .ldf files.<br /><br />2. Change the database context to Master and allow updates to system tables:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> Use Master<br /> Go<br /> sp_configure 'allow updates', 1<br /> reconfigure with override<br /> Go<br /></font id="code"></pre id="code"><br />3. Set the database in Emergency (bypass recovery) mode:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> select * from sysdatabases where name = '&lt;db_name&gt;'<br /> -- note the value of the status column for later use<br /> begin tran<br /> update sysdatabases set status = 32768 where name = '&lt;db_name&gt;'<br /> -- Verify one row is updated before committing<br /> commit tran<br /></font id="code"></pre id="code"><br />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.<br /><br />4. Stop and restart SQL server.<br /><br />5. The syntax for DBCC REBUILD_LOG is as follows:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> DBCC REBUILD_LOG('&lt;db_name&gt;','&lt;log_filename&gt;')</font id="code"></pre id="code"><br /><br />If a log file with the same name as specified in DBCC REBUILD_LOG already exists in that directory, then the following message occurs:<br /><br /> Server: Msg 5025, Level 16, State 1, Line 1<br /> The file 'C:MSSQL7Data&lt;log_filename&gt;' already exists. <br /><br />You will need to specify a different filename or rename or delete the existing one.<br /><br />Rebuild the log with this code:<br /><pre id="code"><font face="courier" size="2" id="code"><br /> DBCC TRACEON (3604)<br /> DBCC REBUILD_LOG('&lt;db_name&gt;','&lt;log_filename&gt;')<br /> Go<br /></font id="code"></pre id="code"><br /> If the command is successful, the following message appears:<br /><br /> Warning: The log for database '&lt;db_name&gt;' has been rebuilt.<br /> Transactional consistency has been lost. DBCC CHECKDB should be run to<br /> validate physical consistency. Database options will have to be reset, <br /> and extra log files may need to be deleted.<br /><br />You must reset the status using sp_dboption or through Enterprise Manager.<br /><br />6. Set the database in single-user mode and run DBCC CHECKDB to validate<br /> physical consistency:<br /><br /> sp_dboption '&lt;db_name&gt;', 'single user', 'true'<br /> DBCC CHECKDB('&lt;db_name&gt;')<br /> Go<br /> begin tran<br /> update sysdatabases set status = &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rior value&gt; where name = '&lt;db_name&gt;'<br /> -- verify one row is updated before committing<br /> commit tran<br /> Go<br /><br />7. Turn off the updates to system tables by using:<br /><br /> sp_configure 'allow updates', 0<br /> reconfigure with override<br /> Go<br /><br /></i><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  21. ghemant Moderator

    Hi,
    have you replace / overwrite the .mdf file ! and still want let you access the db !
    have you tried using STELLAR PHONIX to recover lost/deleted/formatted data !



    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami
  22. ghemant Moderator

    Hi Vietcave,
    have you tried what suggested by Satya ?! i am very interested in result! are you able to recover your database !

    Regards


    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami

Share This Page