A few days back I got a call close to midnight where a client’s client was facing an issue. They had recently suffered a power failure and the Sysadmin while bringing the database server online deleted the ldf file for the database by mistake. I can only assume he thought log file meant error logs or something. But unlike most examples we see where you are advised to use
One of the three below options
In this case we couldn’t since there were active transactions running on the server when the power failure happened and as a result the database mdf file was not consistent. The error we kept getting was
| The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. |
Which made sense, now the problem was how we get the database back online, naturally they didn’t have any backups. A quick google search took me to this awesome article. If you are a DBA I strongly recommend you take 10 minutes out to read the article properly since it can save you a world of pain someday.
The basic steps are outlined below
- Create another database with the same name.
- Take the database offline with rollback immediate –this is a very important step.
alter database corrupt set offline with rollback immediate
- Copy past the corrupt mdf file over to the location where the offline database mdf and ldf files are present and run the alter database modify file command shown below.
- When you try to bring the database online you will get an error but that’s OK.
alter database corrupt set online
- Fix the corrupt database by running DBCC checkDB and repair_allow_data_loss, this will clear out the inconsistent data.
And with this the database is up and running.
Please Consider Subscribing
