July 13, 2008 at 7:17 am
Comments posted to this topic are about the item A Story of the Deleted Transaction Log
Jimmy
"I'm still learning the things i thought i knew!"July 13, 2008 at 11:36 pm
I've had a lot of experience with corrupt transaction logs. We have a client where they put out machines with faulty raid controllers. If the machine lost power, HDD corruption ensued.
Fortunately it was with SQL 2000, where you can actually rebuild a transactionlog (an undocumented stored proc).
Eg, for a corrupt MSDB transaction log, put the DB in emergency mode and:
DBCC rebuild_log('msdb','D:\MSSQL\Data\msdblog.ldf')
This even works with replicated DBs (without dropping replication).
July 14, 2008 at 12:37 am
I am just wondering how the transaction log has been deleted during SQL server being running. The SQL server locks the data and log files and they cannot be moved or deleted.
July 14, 2008 at 1:04 am
wolfen (7/14/2008)
I am just wondering how the transaction log has been deleted during SQL server being running. The SQL server locks the data and log files and they cannot be moved or deleted.
Quite easy. Detach the database - this commits all outstanding transactions in the transaction log and then takes the database offline. It is not visible in your database engine afterwards.
The way James described this incident it was very likely the saving grace of the technician.
Until the day I developed a script to run my backup and truncate the backup log I ran into this issue myself. Normal way was to detach the database, delete the log file and attach the database again. SQL Server didn't find the log transaction file so it recreated it empty.
July 14, 2008 at 1:06 am
I have not come accross these sort of excited recovery scenario, and learn a lot from the experience shared from the article and responses.
In the past, I also come across the DB log growing too large. I found that the fastest way to shrink the DB log is like that :
- Stop the DB
- Detach the DB with sp_detach_db
- Delete / Rename the LDF file
- Attach the DB with sp_attach_single_file_db with the MDF file left.
Can sp_attach_single_file_db to be used in the mentioned scenario ?
July 14, 2008 at 1:10 am
I think James hasn't told us everything yet. 😀
What I find curious is that the technician was able to detach the database (otherwise he wouldn't have been able to delete the transaction log) but didn't know about the attach routine.
July 14, 2008 at 1:25 am
Knut Boehnert (7/14/2008)
I think James hasn't told us everything yet. 😀What I find curious is that the technician was able to detach the database (otherwise he wouldn't have been able to delete the transaction log) but didn't know about the attach routine.
That's what I meant in my previous post.
July 14, 2008 at 2:15 am
I would urge anyone who encounters a similar issue to read Paul Randal's blog (http://sqlskills.com/blogs/paul/) as the likelyhood is that the database will be in an inconsistent state. We had similar issues recently, on a system we didn't manage for the customer but were happy to try and help out with. Paul's blog was our first port-of-call, just to refresh ourselves on the steps we should take. Unfortunately, the database was not recoverable without data loss and unknown inconsistency, which wasn't acceptable to the customer, but not so important to call in data recovery experts. We had explained the issue of likely data inconsistency to the customer and they were able to act accordingly. BTW James, not saying that you were wrong here as you probably had no choice, but if the consistency of the database is critical because, say, its a banking system, then using data recovery experts may be the only way left to you in trying to get the deleted file back, in an effort to prevent unknown data inconsistency.
The DBCC CHECKDB ...REPAIR_ALLOW_DATA_LOSS... is the absolute last thing you should do (http://www.sqlskills.com/blogs/paul/2007/09/15/CorruptionLastResortsThatPeopleTryFirst.aspx), when all-esle has failed, and you should do your best to understand the consequences of this action on the database.
July 14, 2008 at 2:16 am
I've had a few issues with rogue transaction logs as well (on MSDE at any rate). Even on autoshrink I've had them grow to over 10GB, and didn't even notice it.
They are pretty easy to deal with though - truncate them (WITH TRUNCATE ONLY) then shrink.
A lot of installations we have scheduled maintenance jobs that do this, and defrag indexes periodically.
July 14, 2008 at 4:15 am
Wow! That sure was an interesting problem.... Glad it worked ok, I'd probably be up nights though worrying over that DB and if anything was lost....
July 14, 2008 at 5:04 am
Couldn't it be possible that the technician just stopped the SQL service, deleted the file (the biggest one) and then restarted the service ? (it's not the first time I've seen this). That would also make sense since no data seemed to be lost...
July 14, 2008 at 6:38 am
Why wouldn't you simply detach the database, then attach it back with the single file option? Sorry, I'm in a hurry this morning, but I'm almost certain I've moved databases that way without a transaction log.
Student of SQL and Golf, Master of Neither
July 14, 2008 at 6:41 am
Awesome article. Fortunately enough, I have not encountered such an issue but good to know the steps needed.
Do these steps ring true for 2005 & 2000?
-B
July 14, 2008 at 6:51 am
Very interesting.
As an experienced DBA...were you jotting notes along the way as you stepped (er, fumbled) your way through this fix?
How long did the entire scenario take to get the DB back up and running again?
What non-BOL resources did you use to help solve the issue?
I'm alway interested in these additional details because I often see impatience with staff waiting for a fix to complete...they wonder if it is working properly and want to cancel and try something else. Very often, this impatience leads to more/worse problems than the original problem itself.
July 14, 2008 at 7:06 am
There are two ways to recover a single data file DB in SQL 2005.
1. Create DB for attach ...
CREATE DATABASE database_name
ON [ ,...n ]
FOR { ATTACH [ WITH ]
| ATTACH_REBUILD_LOG }
2. If the log was lost during SQL server is down, once you start the sql server, a new log file will be generated automaticly.
Viewing 15 posts - 1 through 15 (of 113 total)
You must be logged in to reply to this topic. Login to reply