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

LinkedIn as your Stepping Stone for Opportunities with Internationally-Oriented Organisations

 As some of you may have read already, Andy Warren‘s series on LinkedIn (part 1, 2, 3) and networking has sparked my interest by first listing down what I would like to share about this brilliant business networking utility.  And then proceeding to read his – from first glance, he’s done a great job at listing all the contact utility functionality,

The prerequisites, and why I’d like to add to Andy’s recommendations (really hoping I didn’t trump a part four Master WarrenJ)  for being able to really take advantage of this tool would be completing one’s profile to the 100% level and obtaining as many recommendations as possible.  Currently, I am at twelve, so I believe establishing credibility by means of online references is a significant prerequisite to mastering LinkedIn’s networking potential – because if you recommend someone online, they are taking a leap of faith in you, it’s something they are willing to state in front of the entire world basically.

You’ll be pleasantly surprised also, that if you describe the way you work exactly (e.g. personally, I described following Brad McGehee’s Exceptional DBA guide), or your preferred methodology, it will allow you to bring in qualified clients that have had the chance to filter out obvious signs or attributes from other profiles, such as stagnation, lack of recommendations, or territorialism, that can be undesirable (some of those may also depend on how long their profile has existed, so no hard/fast rule, each situation could be different).  LordAlex, my Flash Guru mate here, loves to describe it as a method to make a pillar of the all-important (in this net generation) Online Persona.

Further, it should be treated as a longer than usual Curriculum Vitae (or Resumé in N.A.) but in accordance to the format obviously, because perhaps if you place details in the wrong portion of your profile, an opportunity could easily be missed.  I love the way a mate here in Montreal (Martin Arvisais) describes it as a great place ‘pour vendre ta salade’ (cute local way of saying to sell your stuff). 

Another good reason to do it is, to be quite forthright, showing how you can contribute to your professional community – as Andy Mentioned in Part 2, just after ramping up your contacts within this tool.  There are several SQL Server related groups in LinkedIn, my recent contributions through the LinkedIn groups are part of the reason why Canada’s MVP Lead approached me over the past week for a nomination (also, thanks to a referral from SQLServerToolBox.com ‘s Scott Stauffer, and frequent speaker, a SQL DBA based in Vancouver) – therefore, what more motivation could one implore to Link themselves In.

 

SQL Server 2005/8 Database Compression Presentation in Burlington, Vermont - April 15th

Yesterday, I drove the Smart down to Burlington/Colchester, Vermont to meet up with MVP Roman Rehak and speak to the local user group about SQL Server Row, Page and VarDecimal compression, originally touched during this post.

The final version of the presentation is here.

I had much more performance gains on the SAN before (vardecimal), so for those of you with SSDs, perhaps the test scripts may not really show a big difference for the SELECT times...however, at least major disk space at least will be gained. We started with a table of 260MB and ended up dropping its size down to 80MB.

Here are the essential parts fo the script for you to test out compression on your own databases:

-- all SQL Server internal compression is done at the table level
-- testing started on a table that was 260MB, with a check on the storage used each time
-- I/O , according to the Actual Execution Plan for a normal SELECT started at 24.17 (no compression)

--first round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)  -- table became 180MB

--second round of compression
ALTER TABLE schema.TableName REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE)  -- table now down to 80MB
-- I/O cost, according to the Actual Execution Plan for a SELECT down to 7.62 (just under a third)

-- after all your compression work is done, run a single console command
DBCC SHRINKDATABASE(name,0) 
– replacing the zero with the amount you want to leave free

 

Activity Monitor in SQL Server 2008: an Embedded Optimisation Gizmo for the Thrifty DBA

As promised back in December ‘08, after having spoken briefly at SQL teach here in Montreal thanks to MVP Paul Neilsen (SQLserverBible.com), here is an introduction to one of our favorite new Management Studio Enhancements: Activity Monitor.  To use AM, right click on a SQL Server 2005/8 instance in the object explorer and take a look, it’ll inspire you to take care of what’s bogging down your database engine and motivate you for some good ol’ Spring cleaning.

First, let me start off by mentioning that even if you do not have SQL 2008 instances, it is worth it to install SQL 2008 Management Studio (SSMS is the client tool acronym to manage your SQL Server infrastructure) just to have this critical problem resolution feature known as Activity Monitor.  SSMS has backwards compatibility built-in; thus you can take advantage of the dynamic management views already existing in SQL 2005 while connecting from SQL 2008 SSMS’ Activity Monitor (AM).  To view the equivalent information used in the AM before, for example, I was loading information directly from the dynamic management views in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SSMS as AM and provides sortable columns, which enables exceptionally swift pin-pointing of problematic operations.

Ever since the RTM of SQL 2008 was released last summer (unless perhaps, you had beta versions) you can view real-time critical SQL Server performance details and even sort by the worst performing queries, whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, the application it is running from, the number of logical reads…you get the picture, practically everything you need to fix SQL Server tribulations – even giving the option to right click on a line in Recent Expensive Queries to get down to optimising the offending code right away!  There are four panes with graphs for each, plus collapsable details, so you can even view/filter processes, resource waits and disk activity, as well as my favorite Recent Expensive Queries.  By hovering over any of the columns within the respective information panes, one can also see which dynamic management view was used to provide the systems management information; for further investigation and perhaps even set up alerts for when thresholds are met.

If you cannot update your instances to SQL 2008 for a while, which would not be surprising considering the economic tsunami hitting the world (and now a Pandemic!), then at least you can use the updated client tools to enjoy this eye-opening and cost-effective updated feature – in my opinion a critical step in remediation.

On that note, Happy Easter / Passover to all my readers J

Dot Net Usergroup in Montreal Presentation notes - Grouping Sets in SQL Server 2008

On March 11th, 2009, I gave a talk about Grouping Sets in SQL 2008 thanks in part to the original SQL Server Central front-page blog post on the subject and Rushabh Mehta's inability to be in two places at once:) 

As promised to those of you who were at the meeting (and for anyone else of course), here are the presentation notes as well as example scripts.

Speaking is very motivating to a very receptive crowd (as it was that night), and there are two more talks I have lined up:

1) in April:  SQL Server Data Compression in Burlington, Vermont, thanks to Roman Rehak's invite. Date to be posted as soon as I find his e-mail with the schedule.

2) June Will be joining Mario Cardinal, Jean-Yves Roy, and Erik Renaud at Vancouver’s International Developer Conference - Discussion on why Developers should care about Transaction Log Files

Merci à tous qui ont prit le temps dans cette énorme crise économique de m’écouter. A bientôt.

All the Aggregates you crave with Grouping Sets in SQL Server 2008

As reporting requirements increase, it seems that aggregate functions have thankfully risen to the occasion concurrently. To maintain its competitive edge as Staples' best Canadian vendor, BaldGorilla, where I’m currently consulting, has been able to fulfill the most demanding deadlines thanks to the query results produced from Grouping Sets. This is a new facet of the typical Group By clauses most database administrators have become accustomed to, prior to this version of SQL Server.

Straight to the point, all one has to remember is to include the grouped columns in brackets after the Group By Grouping Sets ((SelectCol1),(SelectCol2),..) clause to fully enjoy what limited cube and rollup functionalities we have seen in previous versions of this database management system. Of course, Grouping Sets are not a replacement for denormalising these data and creating cubes in a true data warehouse, however went it comes to satisfying requirements under tight project deadlines for each iteration(s) of reporting deliverables for paramount decision support systems, this functionality is, without a question, your overall rollup value blessing.
Actually, you might feel overwhelmed with all the extra lines of grouped sum values, therefore I have been cutting the result sets up into several tables as to not root confusion for the client – or my fellow developers and I :). These temporary table slices (in the end dumped into actual regularly pre-populated tables), are in actual fact partitions of the Grouping Sets, which have made it easy for our designers to create many intelligent decision support graphs. These summative data tables can combined with yet another improved SQL 2008 application component that we have gotten quite used over the past five years - Reporting Services. The analysis fashioned thanks to Grouping Sets, is limited business intelligence without the prerequisite of building a (usually quite dear) fully fledged online analytical processing system.

References:

http://www.sqlservercentral.com/articles/SQL+Server+2008/65539/
http://www.databasejournal.com/features/mssql/article.php/3790436/Grouping-with-SQL-Server-2008.htm -- explains the different result sets depending on how you use the brackets
http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
http://weblogs.sqlteam.com/derekc/archive/2008/01/31/60478.aspx

OMG, I was asked to speak at SQLteach.com - by Paul Neilsen

Mister/Master of SQL Server himself, author of many versions of the SQL Server Bible (SQLserverBible.com) was talking to us today at SQLteach here in Montreal. One of his top ten favorite new features in SQL 2008 are the Management Studio enhancements - which lead me to open my yap a little too much about Activity Monitor (right click on a server in object explorer to select AM), so much so that he invited me to come up and give an explanation of why I like it so much (I'll follow up on that in a future post). Basically, I explained how you can view real-time critical SQL Server performance details and even sort by worst-performing queries. Then, for the worst performing queries, right-clicking gives you the option to view the actual offending code - so that you can get to optimising right away! Furthermore, the Activity Monitor really takes advantage of all the dynamic management view data - to think only months ago in SQL 2005 production I had all this loaded on several sheets in Excel dynamically to understand what really was going on...now all built-in. I would suggest upgrading to SQL 2008 for just this practically.


Thanks again to Jean-René Roy for organising a great week-long event - never had a chance to meet so many MVPs, such as Brad McGehee and Paul Nielsen, in the photo to the right with local DBAs Paolo de Rosa and Pollus Brodeur. Also had lunch/dinner/beers with Adam Machanic, Scott Stauffer, Roman Rehak, Itzik Ben-Gan all in one week! Actually, to be honest, it felt like a DBA therapy session at times :)

SQL 2008 Row and Page compression – or SQL 2005 post SP2 vardecimal conversion

One of the great new features in SQL 2008 is Row and/or Page Compression. Plus, still good news for those of you on SQL 2005 in production who might be there for a long while: there’s a decent feature you can take advantage of too that is very expensive to do in SQL 2008. Why? Well, Compression in SQL 2008 requires the Developer or Entreprise edition to be installed, so if you want to benefit from this mega space saver (like 1.3TB down to 650GB as I have seen, plus queries running at as little as 40% the time they took before) you will be happy with VARDECIMAL conversion in SQL 2005 (SP2 required though). Both have stored procedures that you can run to estimate how much space you will save: In 2008 it's sp_estimate_data_compression_savings -- which randomly takes rows and gives you quite an accurate estimation of space saved/gained.
Set your statstics and io on just for details of the page reads while doing the comparison before and after. See Brad's [McGehee] Compression examples here.

In SQL 2005, from sp2 onwards, you can do this:
exec sys.sp_db_vardecimal_storage_format ''DatabaseName'', ''ON''
exec sp_tableoption ''dbo.BigAssTableLoadedWithDecimals'', ''vardecimal storage format'', 1
DBCC SHRINKDATABASE(DatabaseName,0)
-- 0 can be replaced with the amount of space you want to leave free for expansion, normally if you have huge decimal types in tables with millions or rows, you'll see a huge difference in size of the db after the shrink.
References: http://msdn.microsoft.com/en-us/library/bb508963.aspx

Please take care while you are doing your big table conversions and make sure that you have enough space for the whole table to be added onto the MDF file and relatively heavy load on the LDF [log] data file. What you can do is switch to bulk mode (set recovery level) while you are doing the compression, then re-enable Full/Bulk or whatever recovery mode you were using before.
Basically what you are doing is clamping down on the wasted space per line with respect to Row level compression in SQL 2008 (characters even get dictionary compression, and also prefix compression), which is what SQL 2005 SP2 calls vardecimal (limited to that column type only however). Obviously, the 2008 higher-end editions have upped the cost for you to have this functionality, but it goes with the territory – a large organisation has major space to gain usually, and don’t forget that it’s not only the space in the database you are gaining, it’s all the cumulative space in backups over tie you'll save, plus and all the increased time (performance) for execution of queries - one could argue simply that by taking the huge number of times a query costs in CPU time, multiplied by its frequency of execution to get the CPU hours saved. Hey boss, were's my bonus, I just saved the company n,000 hours of CPU execution tme:) Remember that clients will get their data twice in less than half the time!

You could do this in the temp database also, rebuild using the temp while adding the compression to the specific object during creation, but make sure your temp is as big as the object you are rebuilding – such as the index+table data size, with a good fifty percent on top of that to be sure you’re not going to run out for other applications.
The Data Copmression Wizard in SQL Server Management studio demystifies much of this, if you are already lost. Mind you, it’s not very efficient if you have to do this on a whole bunch of objects, in that case you would be better off using T-SQL. Sort your tables by the largest first (data pages should be in the thousands), and evaluate the space savings. If you are in the millions of data pages, use this compression – as mentioned before even in SQL 2005 post Service Pack 2 build you can take advantage of row compression by means of VARDECIMAL (but not page compression sadly).
Another condition you should be aware of, If you are input/output bound on your system, meaning that you are waiting on your disks, then you can benefit from compression – Brad McGehee has stated that he prefers Row compression for OLTP environments (today at SQLTeach.com) – and if the e-DBA guru's mentioning it, then it’s really worth looking into.
If you are using tables with multiple non-clustered indexes, only compress those indexes that are used occasionally. Heavily used indexes are to be avoided therefore – so, as always, to be sure TEST, TEST, TEST....on several servers, your test environment, your disaster recovery environment, your dev., and even your laptop (still enjoying disk performance for databases on Dell’s XPS Samsung SSD – holding back on a 32GB SSD express card for logs/swap/temp in the New Year). Do it on your development environment just to save space on large tables –b/c Dev environments are usually starved for data, and then just watch what happens over the next while....check and see if queries are taking forever to run, and let a few of the developers know – maybe they’ll see something you missed too. SQL DBAs - get out there and compress!

How Certification can help you stay current with technology, as well as continuing your education

If you'd like to keep up with your profession as a SQL DBA, I thoroughly recommend certification. It has certainly given me an edge with respect to several mandates over the past years, and even at the least, a decent boost of confidence with respect to being able to quickly process through complex database infrastructure problems.
Unfortunately, just having a University degree is not enough these days to be competitive in the job market, you have to get loads of experience as well as keep up post–graduate studies in what my father likes to describe as 'waves'. Reading up on new methods to maintain the fast pace of change, especially in the database world, is great, but putting yourself under pressure to pass an exam takes the integration of that knowledge to the next step. You'd be surprised how much one can accomplish. This year, with some fatigue of course, I have taken the equivalent of five certification exams (due to a retake of the MCDBA upgrade exam) and wow; there are so many things I've picked up by studying for them. It will take me months to go through and apply what I've learned while preparing for the tests. I'm the kind of DBA who keeps perhaps too many notes, as a consequence of this studying.
I'd like to diverge to draw a centuries old parallel here: One of the main points of a school founded in the middle of the nineteenth century, the Working Men's College (of London) - was to encourage education for life. In fact the college has been serving local people and employers for over 150 years. Therefore, for the enrichment of the community with respect to professional development, certification is important since these are individuals who are seeking to obtain skills and qualifications that enhance their career prospects.Many of those who study and obtain certification are consultants that pay out of their own companies' money for classes, since reinvesting in your resources shouldn't be ignored (QC govt. here actually encourages 1% global budget for education). Others who take courses receive direct support from a sponsoring employer who sees that one of the best ways to improve their firm’s productivity and customer satisfaction, is by having better skilled, qualified and motivated employees. Therefore, continuing education in this way, is a win-win for both sides.Many reasons for certification are the same as that of the Working Men's College since they are:
-Relevant to the skills needed by local employers
-Easy to access, so that learning can take place at the employer’s premises or at the college’s classroom.
-Flexible, so that learning can fit around the needs and schedules of business. An individual can simply take the exams or courses when things are quiet - often different from semester system schedule.
-Effective in producing, in the shortest possible time, skilled and qualified staff.

P.S. Keep up those practice tests (if you are on your way to certification), since once you're cool with the material, test prep. is your best way to feel relaxed on the big day of your exam.