| Downloads |
|---|
| 19652.zip |
Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.
Viewing Transaction Logs
Viewing SQL Server transaction logs can help you track down data problems and help you better understand SQL Server's internal architecture. Several third-party tools let you view SQL Server 6.5 transaction logs, but you can also use a SELECT statement to view the logs. In SQL Server 2000 and SQL Server 7.0, which don't have transaction log files, you can use an undocumented command to view a log of transactions.
In SQL Server 6.5, every database has a syslogs system table, which contains the transaction log that SQL Server uses to recover the database and to roll forward transactions. Syslogs has two fields: xactid, the transaction ID, and op, the update-operation number. So, you can use the following SELECT statement to view the transaction log:
SELECT xactid AS TRAN_ID,
op AS LOG_RECORD
FROM syslogs
The following list describes some of the op values you might see in the results:
- op = 0—Begin transaction
- op = 4—Insert row
- op = 5—Delete row
- op = 9—Modify row
- op = 30—End transaction
Neither SQL Server 2000 nor SQL Server 7.0 has a syslogs system table; the database transaction log is now an OS file. So, running the previous SELECT statement returns an error. But you can still view the transaction log by running the following undocumented command:
DBCC log ( \{dbid|dbname\},
\[, type=\{-1|0|1|2|3|4\}\] )
The command accepts either the database ID (dbid) or database name (dbname) of the database whose transactions you want to view. The command also accepts a type parameter, whose value specifies the amount and type of transaction information you want to see. The default type value is 0, which gives you minimal information (operation, context, transaction ID). A value of 1 gives you more information, including flags, tags, and row length; 2 gives you detailed information, including object and index names and page and slot IDs; 3 gives you full information about each operation; 4 gives you full information about each operation as well as a hexadecimal dump of the current transaction log's row; and -1 gives you everything that 4 does—plus a hexadecimal dump of the checkpoint's beginning statement and the database version. Note that to view transaction information for SQL Server 2000 or SQL Server 7.0's Master database, you can simply run
DBCC log (master)
Cascading Outer Joins
Usually when you want to perform an outer join between two tables, you can use either the old join syntax (a *= b) or the ANSI-standard syntax (a LEFT OUTER JOIN b). However, one situation requires the ANSI syntax. If you want to left-join a to b, then left-join the resulting set to c, the old syntax simply won't work. To cascade outer joins, you must use the ANSI approach, as the code example in Listing 1 shows. This example uses the authors table from the Pubs database. The ANSI-standard code first asks to see the names of publishers that are in the same city as an author. Then for each publisher listed, the code asks to see the ZIP code of any store that has the same name as the publisher. Listing 1 also shows that trying to use the old join syntax to cascade the join results in an error.
Although the ANSI syntax is required to cascade outer joins, the ANSI join syntax is also preferable to the old syntax in most cases because it's much more readable, separating JOIN conditions from WHERE conditions. And more readable means easier to maintain.
