July 14, 2008 at 7:27 am
oberhardt is correct.
A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
July 14, 2008 at 8:08 am
I've had this happen before, when SQL was stopped to delete the log file you got as much of a commit as it possible, usually the log file has committed all transactions and if it hasn't there isn't much you can do. :ermm:
The fastest way to bring the database back online in this situation is:
1. Stop SQL and rename the mdf file
2. Restart SQL and delete suspect database (possibly have to restart SQL)
3. Rename the mdf to it's original name
4. Use sp_attach_single_file_db to bring
5. Do a full backup.
The next step is to take a baseball bat to the fingers of the sysadmin who did this so they will remember to call the DBA next time. 😀
July 14, 2008 at 9:14 am
I believe that we can detach the database and attach the database back without a ldf file. Sql Service will create a log file.
July 14, 2008 at 9:30 am
Bill Whitman (7/14/2008)
oberhardt is correct.A maintenance plan should be set up to truncate, shrink, and backup the transaction log -- nightly, if necessary.
Probably don't want to shrink the log unless you absolutely have to..
July 14, 2008 at 9:35 am
Hello,
They should not have been able to delete the LDF unless they stopped the service. First step should be to find out what the Tech did. I regularly use the detach/attach scenario to kill a LDF and start it at ) but we are a special case. On regular production data it is iffy.
Best wishes,
Barry
July 14, 2008 at 10:00 am
We had a similar situation with one of our clients. We are not responsible for their databases but we helped them out. Here is what happened:
It was a SQL Server 2000 SP4 envioronment.
- The database that got into similar problem was a production database but it was a staging database (data loss was ok kind of thing).
- The operating system level backup had failed previous night and then the next day we got a call saying the database was not responding.
- Upon checking the logs we noticed the following errors in logs:
"Fatal Error 9001 - log for DATABASE1 not available."
"LogWriter: Operating system error 33(The process cannot access the file because another process has locked a portion of the file.) encountered."
- The log writer error was related to the transaction log file which was huge (50GB +) in size (the client was not backing it up).
- The database was marked Suspect.
- Tried to remove the suspect flag by running sp_resetstatus but it did not work.
- After researching all the options we tried to detach and attach the database. But it did not work as we got the following error during "detach".
Server: Msg 947, Level 16, State 1, Line 1
Error while closing database 'DATBASE1' cleanly.
Since "detach" failed "attach" ended up giving the following error:
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DATABASE1'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'e:\db\database1_log.ldf' may be incorrect.
- Due to the problem with detach/attach we finally ended up doing the following which worked just fine:
1. Create a new db named the same as the inaccessible one
2. Put the new database in emergency mode
3. Stop SQL Server
4. Replace the newly created *.mdf file with the old good one
5. Rename/delete the newly created *.ldf file
6. Start SQL Server
7. Ran the undocumented DBCC REBUILD_LOG command
8. Ran data consistency check using DBCC CHECKDB
9. Put the database in multi user mode
Wondering why detach/attach did not work.
July 14, 2008 at 10:11 am
I too am very curious why the detach/attach method (removing the location parameter for th ldf) did not work.
Having done this many times for clients with non-maintained trans logs it's always worked. SQL always asks if you want to create a log file, say yes and you're done
???
Why didn't this work?
July 14, 2008 at 10:18 am
Correct, if you are shrinking down to smallest possible size -- not a good idea. But you can set your parameter to best-fit, so in case there is a runaway process you can maintain best-fit for normal operations.
July 14, 2008 at 11:16 am
HI!
What a great and funny article. I laughed and laughed. And not at you- with you.
The same thing could have just as well happend here.
You fixed it faster than I would have.
July 14, 2008 at 11:37 am
They stopped the sql service then deleted the log file.
Jimmy
"I'm still learning the things i thought i knew!"July 14, 2008 at 11:41 am
This was on 2005. I do not know about 2000
Jimmy
"I'm still learning the things i thought i knew!"July 14, 2008 at 11:41 am
Yes this is exactly what happened
Jimmy
"I'm still learning the things i thought i knew!"July 14, 2008 at 11:44 am
I do not recall if I tried that or not. I will give it a shot when i have some time and try to recreate the problem and attempt to resolve with this suggestion.
Jimmy
"I'm still learning the things i thought i knew!"July 14, 2008 at 11:44 am
Apparenty you missed the following >>
... and the SQL service was taken off line and then the LDF file was deleted all data should have been committed to the MDF.
<<
When the Service is not running the files are not protected. No detach was performed.
HTH -- Mark D Powell --
July 14, 2008 at 11:46 am
I did take some notes as I was performing the steps. I also fully documented it immediately after completing. The dbcc took about 1 hour on this small (~2gb) db.
Jimmy
"I'm still learning the things i thought i knew!"Viewing 15 posts - 16 through 30 (of 113 total)
You must be logged in to reply to this topic. Login to reply