April 28, 2013 at 12:46 am
Dear All
I was doing bcpin with 20 million records. I stoped it in between. Then tries to delete the table form the DB. It took long time hence stoped this also.
Now trying to drop db this is also taking time.
Can anybody help. I wnat to create new db and start fresh.
Its my development DB.
Regards
April 28, 2013 at 8:32 am
did you drop the database via the gui? If so did you untick the delete database history radio button, not doing so can cause the drop database to take a long time.
A drop table or truncate table should be pretty quick, did you check for blocking?
---------------------------------------------------------------------
April 29, 2013 at 12:08 pm
Krishna1 (4/28/2013)
Dear AllI was doing bcpin with 20 million records. I stoped it in between. Then tries to delete the table form the DB. It took long time hence stoped this also.
Now trying to drop db this is also taking time.
Can anybody help. I wnat to create new db and start fresh.
Its my development DB.
Regards
You said you first attempted to delete 20 million records, but then "stopped it in between". I'm guessing that first transaction is still in a rollback state and is blocking your attempts to drop the table or database.
Run SP_WHO2 and look for any sessions with a command status = 'KILLED/ROLLBACK'. If that's the case, then you have no other choice but to wait for the transaction to rollback, which will take as long or often times even longer than the duration of the original work.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 2, 2013 at 9:05 am
Hi,
Do you have a snapshot created for that database? If yes, then you must drop it first.
You can also check if you have any opened sessions to the database you want to drop. Kill them too.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
May 3, 2013 at 5:33 am
Hi,
I am pretty sure if you stop and start the service, it stops the rollback operation, after that, you can drop the table removing the keep history button, since you are not on production it should not be a problem, unless there are other devs working on your dev environment, then you need to make sure everyone is fine with you restarting the service?
Kind regards
May 3, 2013 at 7:40 am
chrisolivierrsa (5/3/2013)
Hi,I am pretty sure if you stop and start the service, it stops the rollback operation, after that, you can drop the table removing the keep history button, since you are not on production it should not be a problem, unless there are other devs working on your dev environment, then you need to make sure everyone is fine with you restarting the service?
Kind regards
Simply restarting the service will not get around the problem of a long running or stuck rollback. Once initiated a rollback must complete, if you want your database back intact and available for normal use.
Recovery/Rollback Taking Longer Than Expected
There is a way to stop the service and then restart using a switch that will tell SQL Server not to go into recover transactions. However, that will leave any database with pending transactions in an inconsistent state, it's status in SSMS will show as 'RECOVERING' or 'SUSPECT', and it will be inaccessible.
Using the SQL Server Service Startup Options
http://msdn.microsoft.com/en-us/library/ms188396(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.105).aspx
Database States
http://msdn.microsoft.com/en-us/library/ms190442.aspx
At that point, you can drop the database, and then restore from backup. If you dig around on Google, you may find other options available, but all of them will involve some loss of data if you choose to keep the database without going to backup. I wish Microsoft could engineer a way to optimize the database recovery process.
To prevent this scenario from occuring again in the future, consider implementing a method where you break million row updates, inserts, deletes into "chunks".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 3, 2013 at 8:25 am
Thanks for clearing that up,
I must say, I have never lost a db by stopping and starting the service (which I would not try on a prod env, but on dev it's a different story), after sometimes waiting many hours for a rollback to happen, I would probably be faster to restore a backup if anything went wrong with the stop/start than it would waiting, but I guess it's better to be safe than sorry 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply