January 29, 2015 at 12:05 am
hi,
in following link
https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx
in SNAPSHOT para
"Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data."
1) please tel me how it handels snapshot transactions in database recovery ?
2) In how many cases database goes in recover and how often?
3) This is from the above link please tel me if have under stood correctlly
"When the snapshot isolation level is enabled, if you are deleting a row on a heap (a table without clustered index) and the transaction log fills before the log record for ghost row is persisted, the database is taken offline. If this occurs, the database will automatically restart, undergo a full recovery, and come online."
my question is by saying "When the snaphot isolation level is enabled" that means when "ALLOW_SNAPSHOT_ISOLATION " Command is used to enable snapshot in database not the setting of snapshot isolation in any query so that one can start transaction?
yours sincerly
January 29, 2015 at 1:44 am
rajemessage 14195 (1/29/2015)
1) please tel me how it handels snapshot transactions in database recovery ?
Same as any other other isolation level, uncommitted transactions are rolled back, committed transactions are re-done
2) In how many cases database goes in recover and how often?
Other than the log full scenario which you described, when SQL is restarted or the database is taken offline and brought back online
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply