Post reply

Database file showing 0KB!!! Urgent!

  • June 16, 2008 at 5:19 am

    #188008

    We have a problem with a database that was appearing in suspect status. Restarted sql but this didn't work and so detached the database to reattach it (the database file are on a SAN which crashed causing the db to go into suspect status).

    I tried to reattach the db but it said it was corrupt, restarted the server to refresh everything and the db file is now showing 0KB!! It used to be 6GB.

    What is going on here, urgent help needed!

    Thanks in advance

  • June 16, 2008 at 5:25 am

    #829128

    Hi

    Steps I would take to trouble-shoot:

    1) Check SQL Error log - there may be a entry stating that the log file drive went missing or similar.

    2) Do you have a backup? - how old?

    3) Was the DB on Full Recovery or Simple?

    Give us some details on this...

    Thanks

    Kevin

  • June 16, 2008 at 5:44 am

    #829132

    Hi, thanks for your quick response.

    When I detached the db it said there was an error while 'closing it cleanly'.

    On reattaching it, it said the physical name of the log file was incorrect when it wasn't so this indicated that the log file was corrupt.

    I tried a single file attachment in order to re-generate a new log file but it has problems doing this I think because the old log file was still present and so couldn't overwrite it. i tried moving the log file to another folder so I could try the single file attach again but wouldn't let me move the log file but returning the error 'file is corrupt or unreadable'

    I restarted the server to refresh it to find the mdf file size has gone to 0KB.

    As for backups; SQL Agent was not running (our customer has responsibility for this) and so no backup!!!

  • June 16, 2008 at 7:26 am

    #829197

    Hi

    Do you have any details of the existing .MDF file and .LDF file - do these still exist? or have these been overwritten?

    Does it seem like your original .MDF file is gone / missing and all that exists now is a 0KB file instead of the 6GB .MDF file?

    If this is the case - after you have ensured that you have searched the server for all .MDF files.

    You mentioned SAN - does the client have any Snaps, Clones or live copies of these disk drives?

    The last resort that I would attemp would be to search the drive for the "deleted" .MDF using software that can "search" for these deleted files that allows you to copy or restore these back to your system if they still exist on the disk but have been marked as deleted

    Let us know

    Thanks

    Kevin

  • June 16, 2008 at 1:47 pm

    #829462

    If you do find the deleted MDF you're going to have to hack it back into SQL Server as there's no way to get it to attach properly. See this blog post I just did on how to do that - taken from my session at TechEd last week.

    http://www.sqlskills.com/blogs/paul/2008/06/12/TechEdDemoUsingTheSQL2005DedicatedAdminConnectionToFixMsg8992CorruptSystemTables.aspx

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • June 17, 2008 at 6:10 am

    #829697

    Hi Paul,

    Thanks for this. There are two databases; the main db and an audit db. It is the main db data file that is showing as 0KB, however, we have the audit data file and log file which hopefully we'll be able to do something with. What is the best way of reattaching this without the same thing happening, I'm not sure from your blog what steps to take as it is talking about corrupted system tables rather than database files; sorry if I am misunderstanding.

    Thanks

  • June 17, 2008 at 6:45 am

    #829715

    Kevin (6/16/2008)

    Hi

    Do you have any details of the existing .MDF file and .LDF file - do these still exist? or have these been overwritten?

    Does it seem like your original .MDF file is gone / missing and all that exists now is a 0KB file instead of the 6GB .MDF file?

    If this is the case - after you have ensured that you have searched the server for all .MDF files.

    In response to Kevin's post:

    All we have for the main database is the data file showing 0KB. I don't know how this could have happened i.e. the data file losing all it's data after a restart! There is no ldf file; this did not re-appear after the restart.

    Thanks

  • June 17, 2008 at 10:50 am

    #829930

    Ah - oops - thought I'd blogged about re-attaching a suspect database. That'll be today or tomorrow.

    Your main db is gone completely and unrecoverable.

    For the audit databases, first thing is to make a copy of the files you have so they won't get lost completely if something goes wrong. What happens if you try to attach them?

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • June 18, 2008 at 9:38 am

    #830582

    Hi Paul,

    I managed to reattach the audit db using the 'hack' so we'll progress things from there.

    Thanks very much for your help.

Viewing 9 posts - 1 through 8 (of 8 total)

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