Salvage The Tail End Of The Log

In the event of disaster, the first thing that any experienced DBA will do, is try to back-up the tail-end of the log, in an attempt to salvage DML operations that have been made since the last log backup, but what if the server is inaccessible?
Well, the fix is "dirty" but effective. You basically need to "fudge" the log onto another server, in order to perform the backup.

First, on a second instance or server, create a shell database with the same name as the original. In this case...

CREATE DATABASE FudgeLog

...Now, set this database to be offline...

ALTER DATABASE FudgeLog SET OFFLINE

...Next, delete the data files and the log file that exists for the database on the second server. Now, Copy the log file from the original server, to the folder that the new database's file were in (before you deleted them).

If you now try to bring the database back on-line, you will receieve an error (obviously, considering you have no data files and an incorrect log file!), but you will be able to perform a tail-log backup, with the following syntax...

BACKUP LOG FudgeLog TO DISK = 'D:\SQLServerDownAndDirty\FudgeLog\FudgeLog_Log.bck' WITH INIT, NO_TRUNCATE

...You can now restore this backup at the end of your log chain, on your original server. You will loose no data, keep your job, and sleep happily at night! ;-)

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Nested Transaction? No such thing!

My post about transaction isolation levels has prompted me to have a good old rant about one part of the T-SQL syntax that drives me mad is the ability to create nested transactions, despite the fact that no such thing exists in SQL! Don't believe me? Check this out...

CREATE TABLE NestedTransactions
(Col1 int)

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     COMMIT TRAN --Inner Transaction
ROLLBACK --Outer Transaction

SELECT * FROM NestedTransactions

...So in the script above, we have committed the inner transaction, so even though we rollback the outer transaction, the NestedTransaction table should still include the value 2 from the inner transaction, right? Wrong...

...Ok, so what about if we try this script...

BEGIN TRAN --Outer Transaction
     INSERT INTO NestedTransactions
     VALUES(1)
     BEGIN TRAN --Inner Transaction
          INSERT INTO NestedTransactions
          VALUES(2)
     ROLLBACK --Inner Transaction
COMMIT --Outer Transaction

SELECT * FROM NestedTransactions

...This time, we have rolled back the inner transaction, but committed the outer transaction, so the NestedTransactions table should have one value, of 1, right? Wrong...

...And check out the message I received...

(1 row(s) affected)
(1 row(s) affected)
Msg 3902, Level 16, State 1, Line 9
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(0 row(s) affected)
...As you can see, when it rolled back the transaction, it rolled back everything, so when I tried to commit the outer transaction, there was nothing to commit.

The bottom line, is that nested transactions do not exist!

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom

Understanding Transactional Anomolies & Isolation Levels

This isn't going to be a particularly technical post, but I have seen some confusion lately about transactional anomalies, and the isolation levels that protect against them, so I first wanted to cover what the three main anomalies are, and then talk about how isolation levels protect against them.

The worst anomaly is a Dirty Read. This is where a transaction reads data that is subsequently rolled back by another transaction, and hence, never exists in the database, as in the example below. This should never be allowed if business decisions are going to be made, based on this data.


DIRTY READ

Transaction 1	Transaction 2
BEGIN TRAN
INSERT INTO myTbl(Col1,Col2) VALUES (1,2)
	BEGIN TRAN
	SELECT Col1, Col2 FROM myTbl WHERE Col1 = 1 AND Col2 = 2
	COMMIT TRAN
ROLLBACK

The next anomaly is a non-repeatable read. This is where, within the context of one transaction, the same query is issue twice, but the second time it runs the results are different, due to an UPDATE statement in a different transaction, as in the example below. This can cause an issue if you are performing complex calculations such as compound interest.

NON REPEATABLE READ

Transaction 1	Transaction 2
BEGIN TRAN
SELECT Col1, Col2 FROM myTbl WHERE Col1 = 1 AND Col2 = 2
	BEGIN TRAN
	UPDATE myTbl SET Col1 = 2 WHERE Col1 = 1 AND Col2 = 2
	COMMIT TRAN
SELECT Col1, Col2 FROM myTbl WHERE Col1 = 1 AND Col2 = 2
COMMIT TRAN

A phantom read is similar to a non-repeatable read, but relates to the other transaction inserting or deleting data, as opposed to updating it, such as in the example below. This will cause problems if you are performing very complex calculations, such as actuarial calculations.

PHANTOM READ

Transaction 1	Transaction 2
BEGIN TRAN
SELECT Col1, Col2 FROM myTbl WHERE Col1 = 1 AND Col2 = 2
	BEGIN TRAN
	INSERT INTO myTbl(Col1,Col2) VALUES (1,2)
	COMMIT TRAN
SELECT Col1, Col2 FROM myTbl WHERE Col1 = 1 AND Col2 = 2
COMMIT TRAN

So how can we protect against these anomalies? Well, the main tool we have at our disposal is transaction isolation levels. Traditionally, SQL Server has supported the following isolation levels:

Read Uncommitteded

Read Committed (DEFAULT)

Repeatable Read

Serializable

But since SQL Server 2005, the following isolation levels have also been provided:

Read Committed Snapshot (Becomes DEFAULT if turned on)

Snapshot

Read Uncommitted does not protect against any of the aforementioned anomalies, and works by not taking out any locks for read operations. It is known as an "optimistic" concurrency level.

Read Committed will protect against dirty reads, but will allow the other anomalies to occur. This level is the default, and works by taking out a lock for read operations, but then immediately releasing it. This is also an "optimistic" concurrency level.

Repeatable Read is a "pessimistic" concurrency level, and is so called because it will stop all anomalies except for Phantom Reads, but works by taking out locks for read operations and holding them for the duration of the transaction.

Snapshot is also a "pessimistic" concurrency level, and works by locking either an index range (if possible) or the entire table.

It goes without saying that if you choose either of the two pessimistic isolation levels, then you will encounter higher levels of contention, meaning more waits and worse...deadlocks!

So to get around this issue, Microsoft introduced Snapshot and Read Committed Snapshot. These isolation levels offer the same level of protection as Serializable and Read Committed respectively, but without taking out any locks!

So that's fantastic isn't it? Lets just use Snapshot for everything right? Wrong! Like everything, there is always a trade-off, and in this case, it relates to how these two isolation levels work. They both work by taking advantage of TempDB called the Version Store.

If you use Snapshot isolation, then at the beginning of every transaction, SQL takes a "snapshot" of all of the required data and puts it in the version store, and satisfies the query(s) from there. This has tow issues. Firstly, you are generating a lot more I/O, so you must ensure that TempDB is properly sized and scaled, and that the I/O sub-system is man enough to handle the extra workload. The second issue is that this could potentially lead to "lost updates" which are not supported by SQL Server. If they were, this isolation level would be aptly called Chaos. This means that if you update the data in the version store, and in the base table at the same time, your snapshot transaction will throw an error and be rolled back.

Read Committed Snapshot also uses the version store, but this time it takes the snapshot at the beginning of every statement within the transaction, rather than at the beginning of the transaction itself. Worse than that, if you turn Read Committed Snapshot on, you are turning off Read Committed, which means that Read Committed Snapshot becomes the default isolation level, and is used for any transactions where an isolation level is not explicitly stated...Watch your server die!

Quite often, if you need a pessimistic isolation level, you just need it for part of a transaction, or for one or two specific tables. If this is the case, rather than using a transaction isolation level, you can use query hints, which give you much more granular control. More information about query hints and how to use them is available in books online.

I hope this cleared things up for anybody who was not familiar with this topic!

Find my book, Pro SQL Server Administration on Amazon -

America


United Kingdom