Blocking

Question

Blocking

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 1

Everyone is correct. I would like you to take a rest but you can really do something here to make the rollback faster. Lessen the load on your server. Try to see if resource deficiency is there. Rest is ur luck.

-LK

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Elliott W (8/31/2009)
If you stop and start SQL I can almost guarantee your database will be corrupted.

Not at all. If you stop SQL and restart it, it will still roll the transaction back during the restart-recovery.

Providing the transaction log is intact, SQL can handle unexpected shutdowns and restarts without causing any damage to the data file. That, in fact, is the primary reason for the transaction log.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Elliott W (9/1/2009)
This was especially true in 2000, almost without fail if I killed a process and it was in the middle of a rollback and then I killed the server it would trash the DB.

If that happens, I would take a long, hard look at the IO subsystem, especially at the disk write cache and make sure that it's properly battery-backed and that the batteries are working.

A non-battery backed write cache and a hard server shutdown can indeed cause corruption, but that's not SQL Server's fault.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

GilaMonster (9/1/2009)
If that happens, I would take a long, hard look at the IO subsystem, especially at the disk write cache and make sure that it's properly battery-backed and that the batteries are working.
A non-battery backed write cache and a hard server shutdown can indeed cause corruption, but that's not SQL Server's fault.

It happened on several different servers real and virtual and rarely involved a hard server down, sometimes a hard SQL down but rarely a server. Also, I'm not trying to blame anything. I'm just saying that it is a REALLY bad idea to down a server during the rollback and that it is better to take your lumps and wait..

CEWII

rajdba Ten Centuries Points: 1362 More actions · Answer 5

Hi,

Please consider this scenario, if you have a PRDO box and some ADHOC query which was not properly tested in DEV/UAT was resource expensive and blocking others and your last resort was to KILL that SPID after approval. You KILLED it. After that it was not ROLLING BACK for hours and holding lot of resources.

What you think we should do?

Thanks,

Raj

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

rajdba (9/1/2009)
What you think we should do?

Wait or restart SQL. Those are your only options. Note that if you restart SQL and do not have 2005 Enterprise edition, the entire database will be unavailable while the transaction rolls back.

Usually waiting is the better option.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rajdba Ten Centuries Points: 1362 More actions · Answer 7

Gail Shaw

You are right, waiting is better than recycling.

I think we can control all these by restricting ADHOC and untested queries on PROD.

I have faced the problem in DEV /UAT and at that time recycling the SQL Server is fast then that of waiting and never happened the Database got corrupted. It always had roll backed automatically.

I wish and pray it never happens with me on PROD.

I am anxious to know what happened with Sourav’s BOX.

Thanks

Raj

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Hello Raj

Our Sr. DBA team did SQL Cluster Failover to rectify the issue!!

Thanks.

Richard M. SSCertifiable Points: 7401 More actions · Answer 9

.... a cluster failover is not more than a shutdown on one side and restart on the other one, which involves the rollback anyway when the DB is started up.....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

rajdba Ten Centuries Points: 1362 More actions · Answer 10

Sourav,

Good to hear that!!!

Any Data corruption or unwanted problem after failover?

Thanks

Raj

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

Sourav,

You will need to let the rollback complete - it is going to happen one way or another. If there is a massive impact on the server due to the rollback, try to mitigate it otherwise.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events