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.
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
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
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
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)
Very Interesting Information. Thank u sir.
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..
Those are various kinds of logging only. It is about information logging.
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
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.
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.
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