SQL SERVER – A Quick Look at Logging and Ideas around Logging

This blog post is written in response to the T-SQL Tuesday post on Logging. When someone talks about logging, personally I get lots of ideas about it. I have seen logging as a very generic term. Let me ask you this question first before I continue writing about logging.

What is the first thing comes to your mind when you hear word “Logging”?

Now ask the same question to the guy standing next to you. I am pretty confident that you will get  a different answer from different people. I decided to do this activity and asked 5 SQL Server person the same question.

Question: What is the first thing comes to your mind when you hear the word “Logging”?

Strange enough I got a different answer every single time. Let me just list what answer I got from my friends. Let us go over them one by one.

Output Clause

The very first person replied output clause. Pretty interesting answer to start with. I see what exactly he was thinking. SQL Server 2005 has introduced a new OUTPUT clause. OUTPUT clause has access to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

Here are some references for Output Clause:

• OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE
• Reasons for Using Output Clause – Quiz
• Tips from the SQL Joes 2 Pros Development Series – Output Clause in Simple Examples

Error Logs

I was expecting someone to mention Error logs when it is about logging. The error log is the most looked place when there is any error either with the application or there is an error with the operating system. I have kept the policy to check my server’s error log every day. The reason is simple – enough time in my career I have figured out that when I am looking at error logs I find something which I was not expecting. There are cases, when I noticed errors in the error log and I fixed them before end user notices it. Other common practices I always tell my DBA friends to do is that when any error happens they should find relevant entries in the error logs and document the same. It is quite possible that they will see the same error in the error log  and able to fix the error based on the knowledge base which they have created. There can be many different kinds of error log files exists in SQL Server as well – 1) SQL Server Error Logs 2) Windows Event Log 3) SQL Server Agent Log 4) SQL Server Profile Log 5) SQL Server Setup Log etc.

Here are some references for Error Logs:

• Recycle Error Log – Create New Log file without Server Restart
• SQL Error Messages

Change Data Capture

I got surprised with this answer. I think more than the answer I was surprised by the person who had answered me this one. I always thought he was expert in HTML, JavaScript but I guess, one should never assume about others. Indeed one of the cool logging feature is Change Data Capture. Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made.

Here are some references for Change Data Capture:

• Introduction to Change Data Capture (CDC) in SQL Server 2008
• Tuning the Performance of Change Data Capture in SQL Server 2008
• Download Script of Change Data Capture (CDC)
• CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

Dynamic Management View (DMV)

I like this answer. If asked I would have not come up with DMV right away but in the spirit of the original question, I think DMV does log the data. DMV logs or stores or records the various data and activity on the SQL Server. Dynamic management views return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. One can get plethero of information from DMVs – High Availability Status, Query Executions Details, SQL Server Resources Status etc.

Here are some references for Dynamic Management View (DMV):

• SQL SERVER – Denali – DMV Enhancement – sys.dm_exec_query_stats – New Columns
• DMV – sys.dm_os_windows_info – Information about Operating System
• DMV – sys.dm_os_wait_stats Explanation – Wait Type – Day 3 of 28
• DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module
• Transaction Log Impact Detection Using DMV – dm_tran_database_transactions

Log Files

I almost flipped with this final answer from my friend. This should be probably the first answer. Yes, indeed log file logs the SQL Server activities. One can write infinite things about log file. SQL Server uses log file with the extension .ldf to manage transactions and maintain database integrity. Log file ensures that valid data is written out to database and system is in a consistent state. Log files are extremely useful in case of the database failures as with the help of full backup file database can be brought in the desired state (point in time recovery is also possible). SQL Server database has three recovery models – 1) Simple, 2) Full and 3) Bulk Logged. Each of the model uses the .ldf file for performing various activities. It is very important to take the backup of the log files (along with full backup) as one never knows when backup of the log file come into the action and save the day!

• How to Stop Growing Log File Too Big
• Reduce the Virtual Log Files (VLFs) from LDF file
• Log File Growing for Model Database – model Database Log File Grew Too Big
• master Database Log File Grew Too Big
• SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Can I just say I loved this month’s T-SQL Tuesday Question. It really provoked very interesting conversation around me.

Reference: Pinal Dave (https://blog.sqlauthority.com)