June 14, 2012 at 7:35 am
SPID 80 - Suspended - BlkBy 88 - DB MyDB - Truncate Table - 6/14/12 @ 8am
SPID 88 - Suspended - Cmd = Killed/Rollback - CPU TIME 28197641 -Disk I/O = 63109422 - 6/13/12 @ 2pm
Server Drive F: has 11 GB of 600 GB free
On F: MyDBlog.ldf = 581 GB
MyDB Recovery Model = SIMPLE ??
When I try and run...
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
I get the following message:
"Cannot shrink log file 2 (WebDatalog) because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Cannot shrink log file 2 (WebDatalog) because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator."
June 14, 2012 at 8:12 am
Well it looks like SPID 88 was a hung job that was doing a bulk insert. The job was stuck running for the past few days. I stopped the job in Activity Monitor and then tried to truncate again but recieved the same error msg.
June 14, 2012 at 8:17 am
The bulk insert has to roll back. If it did a lot of work, it may take a lot of time to roll back.
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
June 14, 2012 at 8:33 am
How can i check on the progress? SPID 88
June 14, 2012 at 8:35 am
kill 88 with STATUSONLY
Results in...
SPID 88: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
June 14, 2012 at 8:37 am
If it's stuck waiting for something outside of SQL (which that looks like), you may have to restart SQL.
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
June 14, 2012 at 8:52 am
MyDB is now "In Recovery" after stopping / restarting sql service.
2012-06-14 10:59:15.56 spid35s Recovery of database 'WebData' (6) is 0% complete (approximately 519461 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
June 14, 2012 at 9:39 am
Select * from sys.databases shows my database is in 'Recovering' state_desc. The error log shows that it will take 6 days to recover. Is there anything I can do to get this up and running sooner?
June 14, 2012 at 10:09 am
Nope.
You may want to check that there's no IO subsystem problems.
You sure the rollback clearly said 0 seconds remaining in the kill? If it was going to cause a long recovery, it should have read a non-zero number (or it's some other transaction that's causing the long recovery)
Edit: The initial estimate is usually very wrong. Give it a few minutes and check again for a more realistic estimate
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
June 14, 2012 at 11:34 am
I once had a transaction with 0% remaining on the rollback for 3 days. Ended up having to restore the database from a backup in order to resolve the problem. Never did find out how long it actually would have taken. That was for a transaction killed about 5 minutes after it was started.
Do you have backups you can recover from?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2012 at 12:48 pm
Yes but mine did not say 0% remaining. Mine said 0% COMPLETE after sitting for about 45 minutes. I've got MS on the line and I think we might try to do a Emergency Mode start up and start a new LDF file. Would that work in this case?
ALSO... I forgot to mention that since we could only keep 2 days of backups on the backup drive I set up a file delete job to delete backups more than a day old. Well the LDF file was on the same drive as where the backups were being taken so once the LDF file ate up all the drive space the backup jobs failed for the past two days. BUT the Backup file delete job continued to run and deleted our last good backup from 3-4 days ago! :w00t:
*** I did not set this server up it was dumped on me*** Please don't flame
I also noticed where all the other .MDF and .LDFs live. You'll never guess it.
C: !!
June 14, 2012 at 1:11 pm
Vertigo44 (6/14/2012)
Yes but mine did not say 0% remaining. Mine said 0% COMPLETE after sitting for about 45 minutes.
You said that it did: "Estimated time remaining: 0 seconds." Was that wrong? I would never have suggested a restart if I'd known the estimated time remaining did not read 0 seconds, it's only a sensible option when SQL estimated 0 seconds remaining (meaning there's actually nothing much to roll back)
I've got MS on the line and I think we might try to do a Emergency Mode start up and start a new LDF file. Would that work in this case?
99% chance it'll send the database suspect which will require an emergency mode repair that might work. Or might not.
Since you have no backups, I would strongly recommend waiting. Deleting the log file could result in the loss of the entire database.
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
June 14, 2012 at 1:17 pm
And these backup files aren't backed up to another location, file server or tape?
June 14, 2012 at 3:01 pm
Lynn Pettis (6/14/2012)
And these backup files aren't backed up to another location, file server or tape?
Well we have a backup from Saturday. I don't know if that is a good option. But it might be the only option at this point.
June 15, 2012 at 2:16 pm
Vertigo44 (6/14/2012)
Yes but mine did not say 0% remaining. Mine said 0% COMPLETE after sitting for about 45 minutes. I've got MS on the line and I think we might try to do a Emergency Mode start up and start a new LDF file. Would that work in this case?ALSO... I forgot to mention that since we could only keep 2 days of backups on the backup drive I set up a file delete job to delete backups more than a day old. Well the LDF file was on the same drive as where the backups were being taken so once the LDF file ate up all the drive space the backup jobs failed for the past two days. BUT the Backup file delete job continued to run and deleted our last good backup from 3-4 days ago! :w00t:
*** I did not set this server up it was dumped on me*** Please don't flame
I also noticed where all the other .MDF and .LDFs live. You'll never guess it.
C: !!
Your earlier post was:
Vertigo44 (6/14/2012)
kill 88 with STATUSONLYResults in...
SPID 88: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
The "estimated time remaining" of 0 was what I was refering to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply