locked
Transaction log file deleted RRS feed

  • Question

  • Hi everyone,

    I am a Dynamics Nav Developer and I have some questions about recovering a database with the transaction log file deleted (On Sql 2005). One of the "IT" guys thought that it was too big and deleted it (aprox.136GB). And the database was detached from the master db.
    So, after a few hours of reading/searching i've made a backup of the ndf and mdf file and renamed them on the actual location. I created a new database with the same name and locations of the files (data and log files). I turned sql off, replaced the new files with the old one (containing data), and started sql. With this, i could see the database instance, but it couldnt be accessed. So i turned the database to emergency mode and single user and started the DBCC CHECKDB. After 14 hours it's still running, but i cant see any resource consumption (processor, memory, space on hdd). In the books online, i have found that i can see the server requests in sys.dm_exec_requests. I see some processes started (in background) at the same time as i runned the DBCC CHECKDB, but i cant see the real command running and it's progress.
    From this point i dont know what to do next . Wait (maybe a few days, db has 17.5GB) and see if anything it's happening or stop this process and rethink the approach?
    And no, they don't have any backups (neither sql or nav).

    Thanks a lot,
    Thursday, June 4, 2009 8:57 AM

All replies

  • So if i understand correctly, the database had been detached and then the ldf file has been deleted.

    With the original database file try attaching it back to the server, specifying the data files only. This should create a new log file for you although you will obviously lose any data that was in the deleted log file. This is unavoidable.

    HTH!


    every day is a school day
    Thursday, June 4, 2009 10:22 AM
  • Hi richbrownesq,

    I tried this way but i get  an error saying that the "log file could not be rebuilt because the database was not cleanly shut down".

    Thanks
    Thursday, June 4, 2009 10:56 AM
  • Restore from your most recent backup i s definitely the best thing you can do now. SQL Server had modifications in the LDF file, which it need at startup in order to provide a consistent database (what we call "recovery" - happens for each database every time SQL Server starts). SQL Server will never provide us with an inconsistent database and if you lack the ldf file, then there's not much SQL Server can do! Note that it isn't only the user data that can be inconsistent without recovery from the ldf file, it is also SQL Server's internal structures - i.e. physical corruption in the database. So, hoefully I have convinced you to restore from a backup, take that loss and make sure this doesn't happen again. Google for "Paul Randal" and other suitable words and you will find more about this.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 4, 2009 11:04 AM
  • Hi Tibork,

    Unfortunately they don't have any backups.
    Thursday, June 4, 2009 11:05 AM
  • Oh no! How can this happen? I can only assume that this is some test database or similar which can be re-created, then? Else, why would a production installation run without backups?
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 4, 2009 11:07 AM
  • Perhaps I should add that you could open a case with MS Support and see if they can assist you in getting in to the database. You would have access to a database which is potentially both logically and physically inconsistent, meaning you will salvage whatever data can be salvaged by ysing TSQL queries to copy data into some healthy database. Then you would go through data and look for inconsistencies (things like foreign key constraints, half-baked transactions and things like that). Not fun, which is why restore or re-create from external data is preferred.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, June 4, 2009 11:12 AM