July 6, 2011 at 1:20 pm
So this should happen only if sql was shut down during such a long running uncommitted transaction , and that too only because it performs a rollback of the said transaction.
Am i correct ?
PS:Please be patient with me am not as smart as I sound sometimes 😀
Thanks Gail saw your post just after i posted mine
July 6, 2011 at 1:39 pm
Yup.
Tested it. Open transaction with a couple million rows changed. Controlled shutdown (from within SQL ran the SHUTDOWN command). SQL shut down fairly fast, the transaction was rolled back after the restart.
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
July 7, 2011 at 2:58 am
July 7, 2011 at 8:45 am
Shut down SQL Services first through config manager or command. Better to not risk database corruption in killing the service by outside means.
Also, you are rebuilding parts of system databases, and the TempDB database in full. Check the created date on TempDB [select create_date from sys.databases where name = 'tempdb']. It will be from that last time you restarted your services. SQL server reinitialized everything that was in cache to a clean slate, as well as deals with uncommited transactions as mentioned above.
I've seen power failures were the backup battery didn't give enough time for a proper shutdown. The server needed ANOTHER restart to bring up the services correctly. Don't pull the plug, or push the power button. I would always want to have some control in the shutdown, personally.
-Me
July 7, 2011 at 8:50 am
matt.newman (7/7/2011)
Shut down SQL Services first through config manager or command. Better to not risk database corruption in killing the service by outside means.
That doesn't risk database corruption.
Dodgy IO subsystems cause corruption. Non-battery backed disk write caches (or incorrectly configured ones) can cause corruption. Just stopping SQL from somewhere other than config manager won't.
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
July 7, 2011 at 8:59 am
Killing all power by pulling a plug in the middle of a read, with other failed hardware components... no problems as far as you see it?
Sudden power loss on a system that relies on power and it leaves the door open as far as I have seen things over time. There may be a lot of recovery items at software level, but your server may or may not have some underlaying hardware issue that a sudden power loss will bring to the surface.
If you're fine pulling the plug, or the computer is locked and nothing else can be done is one thing, but a controlled shutdown is always (in my opinion) much beter than pulling a plug.
July 7, 2011 at 9:08 am
matt.newman (7/7/2011)
Killing all power by pulling a plug in the middle of a read, with other failed hardware components... no problems as far as you see it?
That's not what I said. I said "Just stopping SQL from somewhere other than config manager won't <cause database corruption>."
If you've got failed hardware, well, see my comments in the same post about what does cause database corruption.
Shutting down windows without first killing SQL will not cause database corruption (unless you've got failed hardware)
Stopping SQL from somewhere other than config manager won't cause corruption
Killing the SQL process (from task manager) won't cause corruption.
Hard server shutdowns (power failures) won't cause corruption (unless you've got failed hardware or incorrectly configured disk write caches.)
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
July 7, 2011 at 9:12 am
That's about what I thought, just being clear. Perhaps my initial post should have stated something more explicitly. My main remark on "outside means" was that the suggestion of pulling a plug or hard shutdown was something I would avoid. I would recommend shutdown by "proper" means, it possible, for reassurance needs. If you did shut down by non-proper means, Miscrosoft would not really be able to do anything for support if you did come accross an issue.
July 7, 2011 at 9:49 am
matt.newman (7/7/2011)
My main remark on "outside means" was that the suggestion of pulling a plug or hard shutdown was something I would avoid.
Sure. That's just common sense with computers. But even with hard shutdown, short of damaged drives/cache problems, SQL will still come back clean. That's the durability and consistency requirements of ACID.
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
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply