Disaster Recovery Scenarios with Hewlett Packard's Data Protector

Let's say it is a late Monday morning, because something always comes up before you are required at your vocation, and by means of unfortunate circumstances, upon arrival you are faced with a clustered server that has provided you with a beautiful stack dump thanks to an e-mail Alert sent from SQL Response (not a pitch, just a real world example of what I rely on).  The Alert indicates to you that virtual memory at the time was, let's say, three times the physical RAM made available to the specific instance in question - ouch (ah, the morning couldn't have started off easier, eh?).  Then you've logged into the cluster and noticed several databases in Suspect Mode, okay, bring it on then, this is going to be a fun one :)

Since restoring local backups is preferable and that you've tried to copy them from your network backup location and you're still stuffed (as in the Scotch phrase), then, another solution would be to try and reattach the databases (since the files were there still, but access denied error prevented me from using them, of course) – because for whatever reason, the cluster has flipped your instance onto another node and the databases are not available (!) folder issues, not configured right (arghhh). That didn't work…so now, a direct restore from tape ends up as the only choice.  This is where a your Disaster Recovery Solution should kick in, as setup, in this case, with Hewlett Packard's Data Protector. It's a decent tool that can help out to recover the databases from tape - as long as you are thoroughly familiar with how to use it (as Brent Ozar has explained in this great post, please make sure you run through restore drills...so that the real disaster recovery event itself is not a disaster either).

 

A prerequisite for work with this tape backup management tool is to ensure that the licensed client tools are installed on the server in question: namely the Disk, Media/Support, User Interface and MS SQL Integration. If you are to use Hewlett Packard's Data Protector tool frequently, which is a pretty straight-forward, create a SQL backup group (chose your in-house nomenclature) in your HP Data Protector Manager client tool (current version installed A.06.10). Within the business day, those who are managing your tape solution should be able to get to you the specific tape in question (if not already loaded) – this is assuming that you do not have the ideal Direct Attached Storage to Tape solution to that specific server, and your HP DP is centralised across your SQL Server Infrastructure (the gateway, or Cell server, as they describe in their techie defs.). Locating the offending server in the backups group, all you have to do is click on the instance, and then click on the database to be restored and there you go (next, finish), problem goes away…unless you have to restore onto a database with a different name - now that's the only real pesky issue (from the point of view of a lazy point-and-click DBA).

What I would consider the worst case scenario with respect to depending on HP Data Protector would be when you must restore a database overtop of another database that holds a different name, and resides on another server entirely. HP Data Protector will give you a replace error from the DP Manager tool if you try this, and there is no easy work-around via this GUI tool (as you may also be used to doing with the SSMS GUI, or by code, here), even when you use the same statement you see in the logs. Thankfully, there is a command line solution – see below for full details, quick example next.

Let's say you are doing a database restore, where you need to restore overtop of another database with the backup from one holding different names, please observe this restore command example:

Omni -mssql -barhost SERVERNAME -base DBNAME my_session_id -asbase NEWDBNAME -file logical_datafilename Drive:\datafilelocation.mdf -file logicalLogfile_name Drive:\logfilelocation.ldf -replace

Here below are more details on how to restore using the Command Line Client Utility of HP data protector, taken directly from page 53 of their own documentation

( C:\Program Files\OmniBack\Docs\IntegrationMS.pdf ):

Restoring using the Data Protector CLI

From the Data_Protector_home\bin directory, run:

omnir -mssql -barhost ClientName [-destination ClientName]

[-instance SourceInstanceName] [-destinstance

DestinationInstanceName] {—base DBName [—session SessionID]

[MSSQL_OPTIONS]... | —base DBName —datafile

GroupName/DataFileName —session SessionID

[DATAFILE_OPTIONS]...} MSSQL_OPTIONS

-asbase NewDBName {-file LogicalFileName1 PhysicalFileName1

[-file LogicalFileName2 PhysicalFileName2]...} -replace -nochain -recovery {rec | norec}

-standby File

DATAFILE_OPTIONS -replace -nochain -recovery {rec | norec}

Provide the Session_ID of the backup session. For object copies, do not use the

copy session ID, but the object backup ID (equals the object backup session ID).

Integration guide for Microsoft applications 53

For description of the CLI options, see the omnir man page or the HP Data Protector

command line interface reference.

Examples

To restore the database RONA running on the SQL Server ALMA to the same

destination, run:

omnir -msssql -barhost ALMA -base RONA

To restore the data file DATAFILE_01 in the file group FILEGROUP_02 of the

database RONA running on the SQL Server ALMA to the same destination, run:

omnir -MSSQL -barhost ALMA -base RONA —datafile

FILEGROUP_02/DATAFILE_01 —session 2008/10/17-3

Restoring to another SQL Server instance or/and another SQL Server

Prerequisites

• Both SQL Servers must have the same local settings (code page and sort order).

This information is displayed in the session monitor for each backup.

• The target SQL Server must be configured and reside in the same Data Protector

cell as the original SQL Server.

Hope this helps in deciding what disaster recovery solution you chose to go with.

I'd like to hear from my readers of which is their favorite if you would be so kind.

P.S. Make sure to have client tools installed on ALL nodes in your cluster, right click on the client and Check Installation to be sure...then run a single small database backup (live drill to tape basically) to verify that it is all okay.

Querying the Procedural Cache on Canada Day (July 1st)

The goal of this post is to understand the procedure cache and execution plans to ensure we use fewer resources and that queries run better.  Better means higher throughput, more concurrency and fewer resources – as described by MVP Joe Webb during SQLTeach in Vancouver just last month.

 

In the last column on the right produced by querying the procedural cache (see below), it provides a graphical text-based, or XML-based representation for the data retrieval methods chosen by the Query Optimiser. In understanding the execution plan, we read it from right to left, playing close attention to the relative execution costs (if high) for specific statements that are displayed: such as physical operation, logical operation, i/o cost, cpu cost, rows, and row size.  Zooming in and out are functionalities available too, as well as the properties window (which I usually have hidden...and forget about).

There may be a thousand ways to resolve a query, or to the find one that’s good enough to return the results in the most efficient way – this is what the query optimiser does. Uses a cost-based algorithm which estimates the best way, it then passes onto the part of SQL Server that processes the query and there it may change the plan, or re-estimate what the execution plan would be.  For example, as part of your stored proc., you are doing recurrent updates, and then you have changed up to 50% of your rows, and thus the table stats have changed consecutively. Erstwhile, the plan that created originally has changed due to the statistics. Maybe now there is a better index to use or foundation has changed to the point where it needs to recompile. In reality, it’s best to make your decisions on the Actual plan, and not the estimated plan (in SSMS click Query, Include Actual Execution Plan, or to see the full before/after story compare with Query, Display Estimated Plan). You will want to watch out for clustered index scans, since they are synonymous with a Table scan, and that is to be avoided or indicates there is a problem with the query.  If you see a table scan, change the heap to a clustered index, or add an index to the specific column necessary - clustered index seeks are a good sign optimisation wise.  If the tables are of an insignificant size however, don't bother splitting hairs. 

As mentioned by Brad McGehee back in December 2008 at SQL teach in Montreal, an interesting way to do trail and error is to make different versions of the query and let it give you estimated/actual plans for all the batches and to compare each iteration of the result set.  Furthermore, to quickly read the execution plans findings, the tooltips are a lot better and give way more details in 2008 (which even tells you now, correlating with an index-related DMV, if an index needs to be created).

 following query allows you to view all the recent queries (top 50 in this case) that have run on your instance, as well as their graphical plans, which are stored in XML (have to double-check this, but I'm pretty sure the tooltips work in the XML too).

SELECT TOP 50

DB_Name(qp.dbid) as [Database] , qp.number , qt.text as [queryText],qs.total_logical_reads as [Reads],

 SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,

    ((CASE statement_end_offset 

        WHEN -1 THEN DATALENGTH(qt.text)

        ELSE qs.statement_end_offset END 

            - qs.statement_start_offset)/2) + 1) as [StatementText]

,qp.query_plan

FROM sys.dm_exec_query_stats as qs 

      CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY 

      qs.total_logical_reads DESC

 Happy Canada Day! :)




BTW - Alternate methods to produce execution plans are by using: Profiler, 2005 performance dashboard and SQL 2008`s data collector.

How to Avoid Database Restore Issues by a Simple Script Edit - A DB Restore Work-around

During the process of applying changes to a production database - let's call it Change Management (pick your ITIL or COBIT method accordingly), we should always go through a thorough testing process on an exact copy and environment too, if possible, of the production database itself.  Straight to the point, there is a simple method to avoid Error 3154: The backup set holds a backup of a database other than the existing database message or, in other words, the non-matching backup set restore failure that I have been stumped on occasionly, until I remember to edit the script as below.



Here's a typical Restore script run in SQL Server 2008/5 - which will fail because the media set is not matching:

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' FROM DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10  -- replace is because you are restoring over

First create an empty database with the desired Test nomenclature for your respective environment.  Notice, that if we take out both the File=  headers for the restore (struck out below), we will be able to successfully restore the backup file even if the database is from an entirely different server.

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' FROM DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10

Now, in another scenario, if we are doing this on databases being used in production, and require a quick restore, there will possibly be a log file (tail of the log file error) problem that can be mitigated by first doing a log backup prior to full restore, switching the database to single user mode, then restoring over top of the existing database (no replace needed below since it's on the same server, same DB) with the specific restore point, finally followed by a switch back to Multi User mode.

GO

declare @backupSetId as int

select @backupSetId = position from msdb..backupset where database_name=N'MyDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDatabase')

if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDatabase'' not found.', 16, 1) end

RESTORE VERIFYONLY FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

GO

ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATERESTORE DATABASE [MyDatabase] FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.bak' WITH FILE = 1, NOUNLOAD, STATS = 10

GO

ALTER DATABASE [MyDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE



If you are simply starting up the restore on a new server for the very first time or overwriting the same database again, then you will only need a typical restore:

RESTORE DATABASE [RestoreDirectlyDBfromBackup] FROM 
DISK = N'DriveName:\SQLBackups\BackupFile.BAK'
-- N'\\NetworkServerExample\SQLBackups\DBNAME_db_2009date.BAK'
WITH  FILE = 1,  MOVE N'DatabaseName_data' TO N'DriveName:\SQLData\DBName.mdf',  MOVE N'DatabaseName_log' -- NB. if you have multiple data files you will need to do a MOVE X To FileLocation for each one of themTO N'DriveName:\SQLLogs\DBName.ldf',  NOUNLOAD,  STATS = 10



Happy Restoring :)

TRY/CATCH - The Great Way to Handle Exceptions in SQL 2005/8

This is based on the original post in 'Franglais'.

The goal of this post is to explain how to handle errors thanks to a useful T-SQL functionality, available since SQL 2005 onwards, that developers are already used to – and similar to how exceptions are handled in the Visual Studio environment.

If you are used to using @@error raiserror in previous versions of SQL Server, then the probably the best thing for you to use now is TRY/CATCH. Not to be used everywhere, just when you have to run multiple inserts/updates that are critical and that you anticipate errors for that code. For just a single insert/update within a proc, then just use begin / end and do not go crazy with it everywhere.

To use Try/Catch, you'll group your first block of sometimes iffy exception code within the Begin Try/End Try. The second block will be your Begin Catch/End Catch.

Begin Try
Block of T-SQL code that can be an issue
End Try
--when there is a problem with the above (as soon as there is an error):
Begin Catch --
Bloc du code T-SQL -- useful sysfuntions are available, VS programmers like this...
-- Error_Number/Severity/State/Procedure/Line/Message() here use these functions

-- to throw back info to the application as necessary
End Catch

The typical method of control is the Begin Transaction/Commit, but you can also take advantage of Try/Catch block. An exception will not automatically cause the transaction to rollback - this is a common myth...things will keep running afterwards and a commit will happen with the part of code you really needed to run before. One can also use XACT_ABORT whenever you are not using TRY/CATCH and are dealing with transactions (thanks to Adam Machanic MVP for clarifying this in his book Expert SQL Server 2005 Development page 62-72). In other words, use only one of the two at the same time: either set XACT_ABORT ON or your Try/Catch block.

Nota Bene: Compilation errors or syntax are not handled in the Try/Catch block, so make sure to sort those out before using this functionality. It's used to handle transactions that are prone to exceptions. If you have logical errors, then TRY/CATCH is not your saviour.

References:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p1.aspx TRY/CATCH Helps to Resolve Deadlocks
http://www.databasejournal.com/features/mssql/article.php/3587891
http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/

The Pro-Active DBA during the Economic Tsunami

As none of us are immune to the current financial crisis, and your development projects have probably come to a halt all around your respective work environments, I propose a list of pro-active steps to reduce long-term costs associated with the management of your SQL Server database systems below. Normally, I was just on holiday now and tried to get off the grid, but the apartment we rented in Rome, just off of Piazza Pasquino and in between Campo di Fiori and Piazza Navona, has no shortage of vibrant night-life/dining/drinking parties going on just one floor below 'till two in the morning!

Right, back to the crisis :) which I just evaded for a few weeks:
1. Archive by means of purging data from the largest, worst performing and space hogging database tables you possibly can. This will involve significant co-ordination with developers and application users of course, so by no means a quick step in reducing costs - in other words do not pull out the battle axe and truncate all! Seek to satisfy the lowest common denominator needs (within reason) and prepare a COBIT style change management (or ITIL equivalent) to ensure no steps have been left out. Follow up your 'great' purge with data file optimisation (indexes off to another disk if available) and then a one-off database shrink to seal the deal.
2. Mitigate the Data Explosion and Compress your largest tables in SQL 2005 Enterprise post SP2 , if your table has a Decimal datatype – or in SQL 2008 Enterprise or Developer, use Row and Page Compression.
3. For instances on 2005/8 Digg with activity monitor to narrow down what is hogging the system resources. Check the Procedural Cache and watch out for tables scans, etc., and optimise stored procedures by the use of temp tables when there are many joins involved.
4. Certify yourself, or upgrade your certification to maintain your competitive edge and for simple self-enrichment within the profession.
5. Run through Disaster Recovery Scenarios to ensure business continuity for your employer or client. Practice restoring onto your DRP environment with the appropriate restore scripts.
6. If you do not know your environment like the back of your hand yet, update server configuration documentation with various tools and create visual infrastructure documents (e.g. in Visio).
7. Kick up on the networking with LinkedIn and join groups from institutions you’ve been through, whether they be academic or professional.
8. Start Blogging, answer questions in groups and read up on the best professional magazines or books in the industry. Reach out to like-minded bloggers and support them.
9. When you’ve done all this…go on a holiday, you’ll probably deserve it by then, as for Italy, I can thoroughly recommend it J!

The scale of the columns still standing at the Senate in the Forum is impressive as you can see below.

Hunting Down Useless Indexes

Hopefully, by the time we have our annual family vacation in May, the Cherry Blossoms will be out – until then I’ll just dig into some very, very heavily updated Indexes that are causing Quest’s Spotlight, at my latest contract with Transcontinental, to flash warnings for excessive disk activity. Of course, if we were in Utopia, there would be loads of extra disks to isolate the indexes to, however Sir Thomas More’s head was lopped off by Henry VIII and that Utopian dream died rather quickly. Now back to reality…

Prerequisite References - Index Related Dynamic Management Views (SQL 2005/8): sys.dm_db_missing_index_details , sys.dm_db_index_usage_stats

Nobody wants to have indexes that are updated for no reason, so when you see elevated numbers for user_updates, let’s say in the millions, which will need obvious action, while at the same time user_lookups (if clustered idx) and user_scans/seeks are at zero AND the table in question is huge, then you might want to drop those indexes on the table with the goal of gaining system resources. Bonus, you might even have happier users receiving their data quicker too!

Example: I have a table with its respective Clustered Index, and there are five non-clustered indexes based on top of that Clustered Index – since they are dependent. If two of the non-clustered indexes (mostly likely due to the use of a code generator) have no user_scans, nor user_seeks (or only a negligible amount) the other two non-clustered indexes have fair amount of user_scans, so I have decided to Include them in the clustered index if they have a significant number of user_seeks or drop them completely. Testing is the best way, so using profiler I shall see what the results are once applying realistic workloads – one thing for sure, is that there will not be so much updating going on for no reason. There are queries that allow you to determine the useless indexes very quickly, such as MVP Paul Nielsen's Duplicate Indexes post, and the useless index list can be huge, especially if the application generated its objects from an ORM, so we want to focus on the largest tables first as a guide through the over-indexation mess.

Thankfully, with the help of the great details within the DMVs, we have no excuse not to analyse problematic indexes.

Further reading from some great sources:

http://www.sqlservercentral.com/articles/Indexing/64134/ Missing Indexes SQL 2005/8


http://msdn.microsoft.com/en-us/magazine/cc135978.aspx Ian Stirk’s Optimising Application Performance by Index analysis


http://www.codersrevolution.com/index.cfm/2009/2/14/SQL-Server-Performance-How-Are-My-Indexes-Being-Using

Digging into Transaction Log Files

(1linerForward) I originally wrote this in French while working at LaCaisse.com - http://dbhive.blogspot.com/2008/07/fichiers-journaux-de-transactions.html   

Why do you care, as a DBA, or a Developer for that matter, about what is happening in the Transaction Log? Lets rollback a little and see... The raison d’être of a transaction log file is to write all the necessary information we need to recuperate any and all activity happening against the database.  Every SQL Server database must have at least one log file. Here, have a brief look what it looks like inside the log file itself:

SELECT * FROM  ::fn_dblog(DEFAULT, DEFAULT) AS l  -- more examples, and details, below

 During the Spring of 2008, for an auditing project, our requirement was be able to read the active log files, as well as those that were archived.  The archived logs gave us the option, with the help of third party tools (or thanks to in part the query above), to effectively interrogate the log file as if it were one mega table. The way that Lumigent Log Explorer's documentation describes it: 'to assist you in solving or recovering from problems that may occur in a typical database system' - gets the heart of what could be a potential point-in-time restore (please use Full recovery model if this is your requirement).   


My view is that you may end up with a mystery transaction at one point that no logic can explain, and thus your database integrity is in question - not a place where any DBA wants to be naturally. Empowering yourself to dig into the log file and resolve these types of mysteries is the main point of this post, because you will be able to find out what the values were before/after a change to the database, and who/what application has committed the change, whereas before one typically disregards anomalies (at least I was most of the time before).  This gives us motivation to ensure that log files are archived, since we're following Erasmus' proverb to 'leave no stone unturned,' with respect to resolving such mysteries.

Given that you can query the log file (you will see transactions as BEGIN_XACT , COMMIT_XACT, please see full reference list below), it is therefore possible to raise alerts for undesirable activity, e.g. someone executing data definition language in production. Combined with Database Mail and SQL Server Agent this can be automated too, or in the case of Lumigent Log Explorer, one can configure an alert for each DDL/DML command, which is perhaps useful - to filter out problems in development.  The approach of monitoring objects during manipulation or creation will allow you to take control of an environment progressively and proactively. Here is how to query the log file or some typical unwanted incidents:

USE [master]
GO
ALTER DATABASE [DBname] SET RECOVERY SIMPLE WITH NO_WAIT
GO  -- if you leave something in Simple, the rows after checkpoint 
-- will be recycled, therefore I suggest FULL or at least Bulk_logged
USE [master]
GO
ALTER DATABASE [DBname] SET RECOVERY FULL WITH NO_WAIT
GO
-- if you need to clean up the space quickly for testing
USE [dbname]
GO
DBCC SHRINKFILE (N'DBname, 0, TRUNCATEONLY)
GO
-- truncate a table or perform undesireable activity, etc.
SELECT Operation, Context, [Transaction ID], [Begin Time], [End Time], AllocUnitName, [Description],

[UID], [Server UID], SPID, [Transaction Name], [Number of Locks], [Lock Information]

, * -- shows the rest of the columns, I put the most interesting first

FROM ::fn_dblog(DEFAULT, DEFAULT) AS l

where operation='mark_ddl' -- this will show rows where there is data definition language

-- operation='LOP_MODIFY_ROW' or operation='INSERT_ROWS' or operation='DELETE_ROWS'

-- operation='LOP_BEGIN_XACT' -- means beginning of a transaction

-- operation='LOP_COMMIT_XACT' -- means the end of a transaction

order by [Current LSN] asc


-- for the above Mark_DDL you can create a job step that checks your critical 
-- databases for undesireable activity and if there is an existence of a DDL change (use IF EXISTS with the above)

declare @myfromname varchar(150)

declare @alladdresses varchar(max)

declare @myrecipients varchar(150)

declare @mycurrentaddress varchar(max)

declare @SubjectLocal varchar(200)

declare @databasename varchar(100)

set @databasename=(select top 1 name from sysfiles)

BEGIN-- Name of current sender

SET @myfromname = N'Message regarding Log file activity on ' + @@servername

-- Get e-mail adresses of operators

BEGIN

SET @alladdresses = N''

DECLARE MAILResults_CURSOR CURSOR FORWARD_ONLY READ_ONLY FOR 

SELECT email_address FROM msdb.dbo.sysoperators 

where email_address IS NOT NULL

OPEN MAILResults_CURSOR

FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients

WHILE @@FETCH_STATUS = 0

BEGIN

SET @mycurrentaddress = @myrecipients + CHAR(59)

SET @alladdresses = @alladdresses + @mycurrentaddress

FETCH NEXT FROM MAILResults_CURSOR INTO @myrecipients

END

CLOSE MAILResults_CURSOR

DEALLOCATE MAILResults_CURSOR

IF @alladdresses <> N'' 

BEGIN

SET @SubjectLocal = 'Log file undesireable activity in the ' + @databasename + ' DB on ' + @@servername

EXEC msdb.dbo.sp_send_dbmail

@profile_name = NULL

,@recipients = @alladdresses

,@copy_recipients = NULL

,@blind_copy_recipients = NULL

,@subject = @SubjectLocal

,@body = 'Please verify the log to find out what happened in '+ @databasename +' using select * FROM ::fn_dblog(DEFAULT, DEFAULT) AS l'

,@body_format = 'TEXT'

,@importance = 'High'

,@sensitivity = 'Normal'

END

END 

END 

**Posting ADDY: for a great similar story on this (added in June 2009, after my talk in Vancouver where I performed a similar demo also) Paul S. Randal takes a similar step here

Internally to the SQL Server Log File you'll have an operation code that is captured by the log record. Here are the most common ones taken from a combination of Microsoft and Lumigent Log Explorer's help files.

§    ABORT_XACT Indicates that a transaction was aborted and rolled back.

§    BEGIN_CKPT A checkpoint has begun.

§    BEGIN_XACT Indicates the start of a transaction.

§    BUF_WRITE Writing to Buffer.

§    COMMIT_XACT Indicates that a transaction has committed.

§    CREATE_INDEX Creating an index.

§    DELETE_ROWS Rows were deleted from a table.

§    DELETE_SPLIT A page split has occurred. Rows have moved physically.

§    DELTA_SYSIND  SYSINDEXES table has been modified.

§    DROP_INDEX Dropping an index.

§    END_CKPT Checkpoint has finished.

§    EXPUNGE_ROWS row physically expunged from a page, now free for new rows.

§    FILE_HDR_MODIF  SQL Server has grown a database file.

§    FORGET_XACT Shows that a 2-phase commit transaction was rolled back.

§    FORMAT_PAGE  Write a header of a newly allocated database page.

§    INSERT_ROWS  Insert a row into a user or system table.

§    MARK_DDL Data Definition Language change - table schema was modified.

§    MARK_SAVEPOINT designate that an application has issued a 'SAVE TRANSACTION' command.

§    MODIFY_COLUMNS  Designates that a row was modified as the result of an Update command.

§    MODIFY_HEADER  A new data page created and has initialized the header of that page.

§    MODIFY_ROW  Row modification as a result of an Update command.

§    PREP_XACT Transaction is in a 2-phase commit protocol.

§    SET_BITS Designates that the DBMS modified space allocation bits as the result of allocating a new extent.

§    SET_FREE_SPACE  Designates that a previously allocated extent has been returned to the free pool.

§    SET_MASK_BITS

§    SORT_BEGIN A sort begins with index creation. - SORT_END end of the sorting while creating an index.

§    SORT_EXTENT Sorting extents as part of building an index.

§    UNDO_DELETE_SPLIT The page split process has been dumped.

§    XACT_CKPT during the Checkpoint, open transactions were detected.

For more info: http://www.sqlservercentral.com/articles/Design+and+Theory/63350/ -- SSC's intro to Log Files http://msdn.microsoft.com/en-us/library/ms189085.aspx - Log behavior under different recovery models http://www.youtube.com/watch?v=ZM44LUuA6hc - video how transaction logs work overview (simplified) http://www.youtube.com/watch?v=nyz0AYCwhtM&feature=related - transactions

http://www.sans.org/reading_room/whitepapers/application/forensic_analysis_of_a_sql_server_2005_database_server_1906?show=1906.php&cat=application page 22

P.S. The above is to give a thorough understanding of Log Files, and is neither developed as a replacement to SQL Server Management Studio's Schema Changes History, nor Paul Nielsen's AutoAudit