Disaster Averted?
From: MPF (abcd_at_senditon.com)
Date: 01/15/05
- Next message: andrew: "RE: check sql licence"
- Previous message: Andrew J. Kelly: "Re: Source of EM and QA"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 14 Jan 2005 23:32:57 -0500
Thanks in advance for any insight.
Some process, unknown at this point, flooded a transaction log to the point
of filling up the drive it was on. I managed to free up enough space on the
drive to do a full backup, which truncated the log, but of course didn't
shrink the .ldf file. This was all fine and good, but the available space in
the .mdf file was 0 bytes according to EM. Obviously it tried to auto-grow,
but was unable to because of low/no disk space. Following the backup, I
created a table in the affected database and was able to create a table,
insert a couple of rows and delete the table. I thought all was well, but
just for kicks and grins, I stopped and restarted the SQL Server services.
Much to my amazement, the database was now Suspect!
Assuming I had a good backup (which was transferred to tape), I attempted to
sp_detach_db the database, and it could not detach "cleanly" because it was
Suspect. Fair enough, but the database was removed from
master..sysdatabases. Hmm, I thought, there goes any hope of support from
PSS.
Following the failed sp_detach_db, I tried to sp_attach_single_file_db
(since the server apparently dropped it), but the file was obviously
corrupt, assumably because of the 0 bytes available on the .mdf file
mentioned earlier, and would not attach.
After restoring the backup (thankfully it was good), and performing
sp_detach_db, deleting the 20GB+ transaction log, and reattaching via
sp_attach_single_file_db we were able to recover? the database. I ran a
quick script in a while loop to push about 1MB of data into a new table and
try to force the .mdf file to grow, which it did. Subsequent backup/restore
operations were successful, but I explained the potential for data loss
given the drastic measures needed for this recovery.
The questions..
1. Why was the database "operational" until the service was restarted?
2. Why was the database removed from sysdatabases when the error message
said it couldn't detach cleanly?
3. A maintenance plan was in place as of 01/11/05, and we had a "good"
backup that night. Subsequent backups, starting on 01/12/05 failed (I don't
manage this server...) and nobody noticed. I selected the option to perform
DBCC CheckDB as part of the maintenance plan, could this have caused the
T-Log to bloat like it did? I know DBCC CheckDB uses TempDB, but does it use
the T-Log of the database as well?
4. Folowing the restore of the backup with the bloated T-Log, how much, if
any, data loss might be expected? I didn't see anything in the logs with
regards to transactions being rolled forward/backward, so I'm a bit
concerned about that. Didn't have users available to test, just curious.
Thanks again,
Morgan
- Next message: andrew: "RE: check sql licence"
- Previous message: Andrew J. Kelly: "Re: Source of EM and QA"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
