Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

deletion of db taking long time Expand / Collapse
Author
Message
Posted Sunday, April 28, 2013 12:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, 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
Post #1447314
Posted Sunday, April 28, 2013 8:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 5,867, Visits: 12,950
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?


---------------------------------------------------------------------

Post #1447326
Posted Monday, April 29, 2013 12:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 1,660, Visits: 4,753
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.
Post #1447685
Posted Thursday, May 2, 2013 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:15 PM
Points: 2,966, Visits: 2,989
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
www.seavus.com
Post #1448848
Posted Friday, May 3, 2013 5:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:05 AM
Points: 11, Visits: 54
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
Post #1449141
Posted Friday, May 3, 2013 7:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 1,660, Visits: 4,753
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/
Post #1449201
Posted Friday, May 3, 2013 8:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 11, 2014 6:05 AM
Points: 11, Visits: 54
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
Post #1449232
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse