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

SQL SERVER - A Quick Look at Logging and Ideas around Logging TSQL2sDay

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.

SQL SERVER - A Quick Look at Logging and Ideas around Logging logkind1

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:

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:

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:

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):

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!

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)

Previous Post
SQL SERVER – Developer Training Resources and Summary Roundup
Next Post
SQL SERVER – Video – Beginning Performance Tuning with SQL Server Execution Plan

Related Posts

No results found.

7 Comments. Leave new

  • Very Interesting Information. Thank u sir.

  • Hi Pinaldev,

    In the above article your discussion is started on What is Logging

    But u got reply.. DMVs, OUTPUT clause, Errorlog etc… i m not understanding please clear the confusion..

  • Very good article sir. I would like to know what is the difference when we view the log file using DBCC log(dbname) and through log viewer.

    Both show different content.The first one shows quite abstract content and the other shows something which i can atleast understand

  • Manish Patel
    July 10, 2012 1:08 am

    How to turn on client side logging? We are using SQLoleDb library to connect to server and wanted to log at client for better understanding how much time has been spent between client and server. We have tried client side statistics, but doesn’t give us consistent result.

    Appreciate your response.

  • Hello All,
    We have enabled Tripwire Database monitoring on SQL 2008 server.Everyday I find tables created under Model Database,but our DBA confirmed that they dont touch model DB at all,but similiar tables names are already under actual Financial DB for example,but no other creation or deletion of tables are there.
    Any clue?

  • Hi to all
    Can any one tell me why i only see the backup event in sql server error log
    there is nothing else i can see
    is it possible that some one restrict me to that


Leave a Reply