May 15, 2009 at 4:01 pm
I didn't know that table exists.
command = DBCC, status = suspended, wait_type = LOGMGR, percent_complete = 0
This doesn't look like anything is happening, even though the query says 'running'.
I'm not sure where to look at the error logs but the SQL server logs don't show anything.
May 15, 2009 at 6:02 pm
run
select * from sys.dm_exec_requests
check the command column and find DBCC keyword and find the value of
percent_complete for that row.
that gives a approx percent value
May 15, 2009 at 10:02 pm
So for anyone who might read this in future, this was resolved successfully.
I opened a support call with Microsoft and Mani from Bangalore was brilliant.
The correct sequence of events to recover is below. The key was to only run checkdb NOT NOT with repair.
Thanks for all your advice.
alter database lng_app set emergency
dbcc checkdb('database')
DBCC TRACEON (3604)
ALTER DATABASE database REBUILD LOG
ON (NAME=Database_Log,FILENAME='E:\log\Database_log.Ldf')
dbcc traceoff
alter database database set multi_user
May 15, 2009 at 11:34 pm
Hi splatt,
Just a quick question - was your sever's "Recycle Bin" configured to delete files immediately? If not - you can probably still look in there to see if the .ldf file is there.
If you do not have, then try running sp_attach_db with the data file only. I am not sure if it works - if works, then fine - you will have the database upto the last checkpoint.
If that does not work, try using the CREATE DATABASE command, with the same database name, Specify the data file that you have as one of the files and use the FOR ATTACH_REBUILD_LOG option. I guess it works only when the database is 'cleanly shutdown" - as specified in BOL.
If all fails - find the last full backup and restore it. If you do find a log backup that was taken just before the disaster, you need to ensure you have the latest full backups before that plus all other log backups. So if you have a log backup say, made at 3:00 PM and your last full backup was made at 2:00 AM, you also need to find all the other log backups since the full backups before the 3:00 AM one.
Hope this helps.
May 15, 2009 at 11:36 pm
Sorry, I just saw the issue was resolved...:))
May 16, 2009 at 3:02 am
Just be aware that you may have lost data due to the log rebuild. Transactions that were committed, but whose changes were not hardened to disk when the log was deleted effectively will not have happened. Transactions that had completed and had some of their changes hardened to disk will have partially happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2009 at 5:53 am
Agreed about transactions being lost that have been written to log file but not to data files. Yes, you would lose them if the log was rebuilt without the transactions not being hardened in data files first.
However, transactions that are written to data files would be "atomic" - that means they will be complete chunks of transactions with corresponding beginning and ending in the log files. This is because the checkpoint process - the process that writes to data files from the log will start with a BEGIN TRANS in the log file and harden up to the corresponding COMMIT TRANS. So the data that would be in the data files would come from legitimate transactions. There can be no such instance where a transaction's partially changed data would end up in a data file.
May 16, 2009 at 9:37 am
ok - for anyone else reading the resolution - that is NOT the supported/documented approach and is only suggested when EMERGENCY mode repair does not work (as in this case). Don't call it the 'correct' resolution - the correct one is to use EMERGENCY mode repair, and only if it doesn't work, call Product Support and they'll walk you through the undocumented solution. Although in this case, they failed, because they didn't have you run checkdb/repair afterwards. Sigh.
Glad you got it resolved - now go run repair, and also figure out what transactional inconsistencies you have from rebuilding the log.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
May 16, 2009 at 10:27 am
Sadequl Hussain (5/16/2009)
This is because the checkpoint process - the process that writes to data files from the log will start with a BEGIN TRANS in the log file and harden up to the corresponding COMMIT TRANS.
Not true. Checkpoints write all dirty data pages to disk. This is why there's the need for an undo phase of restart-recovery, to undo transactions that had some of their changes hardened to disk but which never actually committed.
It's not trivial to demonstrate, but not impossible. Just needs access to a hex editor and for the SQL process to be abruptly terminated.
CREATE DATABASE TestingCheckpoint
GO
Use TestingCheckpoint
GO
CREATE TABLE Testing (
SomeColumn VARCHAR(200)
)
INSERT INTO Testing Values ('This is the before value')
go
begin transaction
update Testing set SomeColumn = 'This is the after value'
checkpoint
Note that the transaction has no commit. Now, to demonstrate that the changed value is in the data file, go to task manager, go to the processes tab and kill the SQL process. That's not a graceful shut down, it does not allow SQL time to rollback uncommitted transactions.
Once the SQL process has been killed, open the mdf file in a hex editor and do a test search. You will find that the string 'This is the after value' is present in the file. Hence the checkpoint did write data pages for an uncommitted transaction.
Now, if the log's intact, when I restart SQL it will notice that a change was written but had no matching commit in the log, and it will roll that back as part of the restart-recovery.
The rule for writing data is that the associated log records must be written before the data is hardened, but the transaction doesn't have to commit before the data can be written
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply