October 3, 2011 at 7:28 am
Hi Experts,
I've another question for you. One our developer resized a varchar column for our biggest table 1.5Tb (several millions of records).
The job was supposed to be fast but it took so long that I had to take a drastic decision. I killed it.
I basically killed the spid after about 30 hours and as far as I understand the rollback time should take at least the same time.
The resize action was raised from the Enterprise Manager, so my question is:
How many chance do I have that the rollback will fail?
Right now in the sysprocesses it has the sleeping status and the lastwaittype keeps changing between IO_COMPLETION and PAGEIOLATCH_EX.
I didn't restart the server/service after the kill cause I read it wouldn't help. I didn't use the WITH STATUSONLY option during the kill but I guess I can use the physical_io column to calculate the progress. I know the number of IO operation that were done in the moment I killed the spid, so I assume it has to do the same number of operation in the other direction to complete the rollback. Is that correct?
Thank you
Carmine
October 3, 2011 at 7:36 am
Depends on how you guys edited the table in EM.
Far too often the code defaults to something like this :
begin tran
create table2 (new definition)
insert into table2 SELECT * from table1
drop old table
rename new table
commit
If you guys had run alter table dbo.x alter column a varchar(new size)
the change would have been done only in the system table and would have been over in less than 1 sec.
As for the rollback you're just stuck waiting for it. Restarting the server will just start over where it was.
Assuming you took a backup just before running that command you could always shut down the server, rename the files so that the rollback doesn't resume and then start the restore.
I would guess that it's less than 30 hours to run the restore, but obviously, only you know that for sure.
October 3, 2011 at 7:36 am
This will severely bloat your your transaction log. Do you have enough space on the disk where your transaction log is located? If not, it could bring your server down.
October 3, 2011 at 7:46 am
thank you for the quick reply.
So I saw the TSQL he used using the dbcc inputbuffer.
Unfortunatelly he created a second table and then insert.
About backup, he was suppose to run it after the success backup notification. He didn't wait for it and due to the load of the server I asked the Sys Adm to cancel the backup job assuming that it was going to release resources and speed up the job.
About the disk space, I recently moved all databases to a new disk array and I still have 1TB free. (this is another thread I opened on this forum)
Thanks
Carmine
October 3, 2011 at 7:59 am
"happy" waiting.
That's a good lesson learned for the "junior" dba?
And maybe another one for the managing team. Nothing is as simple as it seems and it's better to have a senior do things on prod after they've been proven on dev / QA.
October 3, 2011 at 8:30 am
Ninja's_RGR'us (10/3/2011)
"happy" waiting.That's a good lesson learned for the "junior" dba?
And maybe another one for the managing team. Nothing is as simple as it seems and it's better to have a senior do things on prod after they've been proven on dev / QA.
Happy waiting?
We had to stop half of the production. You should have seen my Site Leader face when I had to explain what happened.
About learning the lesson, there are so many people involved that I'm not sure something will change after.
Carmine
October 3, 2011 at 8:35 am
Happy was in quotation for a reason. I don't have anything else to offer at this point.
Maybe now is the time to have that little meeting.
October 3, 2011 at 8:37 am
A few more answers:
Chances the rollback will fail: virtually none. SQL ensures that it reserves space in the tran log to roll any transaction back.
Bloating of the log: Only due to other operations running, the transaction that's rolling back reserved all the log space it will need while it was processing.
Rollback takes at least as long as the roll forward did, usually longer. The waits you're seeing are IO-related, the IO subsystem can't keep up with the demands.
Do not restart SQL. The rollback will start over from scratch and with the database offline for the duration. Do not consider anything like deleting the transaction log, doing so will destroy the DB.
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
October 3, 2011 at 8:37 am
Ninja's_RGR'us (10/3/2011)
Depends on how you guys edited the table in EM.Far too often the code defaults to something like this :
begin tran
create table2 (new definition)
insert into table2 SELECT * from table1
drop old table
rename new table
commit
If you guys had run alter table dbo.x alter column a varchar(new size)
the change would have been done only in the system table and would have been over in less than 1 sec.
There's a reason why it's called Enterprise Mangler...;)
In our environment, all changes are applied via scripts, not the GUI. This allows us to eyeball check every change that's about to be made to Production.
October 3, 2011 at 8:42 am
If I were in the ops shoes, change management procedure / code reviews would be on top of my list in the currently hapenning meeting.
October 3, 2011 at 8:47 am
Ninja's_RGR'us (10/3/2011)
If I were in the ops shoes, change management procedure / code reviews would be on top of my list in the currently hapenning meeting.
And a QA environment with a representatively sized 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
October 3, 2011 at 8:54 am
About learning the lesson, there are so many people involved that I'm not sure something will change after.
All the more reasons to put it in place.
This is a super easy sale to make to any C<?>O.
You simply say. Here's what happened, why you're system is down and why you're losing X million / day because of it.
Here's what we need to put in place so it doesn't ever happen again and here's the budget I need in time & $.
From there, it's just a no brainer to do it your way ;-).
October 3, 2011 at 9:45 am
GilaMonster (10/3/2011)
Ninja's_RGR'us (10/3/2011)
If I were in the ops shoes, change management procedure / code reviews would be on top of my list in the currently hapenning meeting.And a QA environment with a representatively sized database.
Interesting side track. Assuming you can't have a 100% exact sized copy of the prod db? What are you requirement / tought process to decide on what size to use?
October 3, 2011 at 10:13 am
Ninja's_RGR'us (10/3/2011)
GilaMonster (10/3/2011)
Ninja's_RGR'us (10/3/2011)
If I were in the ops shoes, change management procedure / code reviews would be on top of my list in the currently hapenning meeting.And a QA environment with a representatively sized database.
Interesting side track. Assuming you can't have a 100% exact sized copy of the prod db? What are you requirement / tought process to decide on what size to use?
As close as possible to production size.
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
October 3, 2011 at 10:27 am
GilaMonster (10/3/2011)
Ninja's_RGR'us (10/3/2011)
GilaMonster (10/3/2011)
Ninja's_RGR'us (10/3/2011)
If I were in the ops shoes, change management procedure / code reviews would be on top of my list in the currently hapenning meeting.And a QA environment with a representatively sized database.
Interesting side track. Assuming you can't have a 100% exact sized copy of the prod db? What are you requirement / tought process to decide on what size to use?
As close as possible to production size.
Isn't interesting how ultimate truth is so obvious & simple :-D.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply