SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


deletion of db taking long time


deletion of db taking long time

Author
Message
Krishna1
Krishna1
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 556
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
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10444 Visits: 13687
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?

---------------------------------------------------------------------
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12411 Visits: 10677
Krishna1 (4/28/2013)
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

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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5930 Visits: 5084
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
C_O
C_O
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 61
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
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12411 Visits: 10677
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
http://blogs.msdn.com/b/psssql/archive/2008/09/12/sql-server-2000-2005-2008-recovery-rollback-taking-longer-than-expected.aspx

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".
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1b3dbf8d-252f-43c4-80d6-d5724fe912b4/


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
C_O
C_O
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 61
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 ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search