× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to Audit SQL Server with the Default Trace

In this article we will talk about the auditing feature of SQL Server’s evasion outline, how the outline is implemented and what it contains. After that, we will see how to use information as of the outline sleeve for auditing and problem searching

About the default trace:

SQL Server's evasion trace was introduced in SQL 2005 to provide information for presentation and auditing information obtainable from side to side by the Management Studio article Browser. For example, the plans modify times gone by account is occupied from evasion trace facts. You will be able to enter this description by right-clicking a database in Object analyzer, selecting intelligence from the option then highlighting plan modify the past.

The evasion outline can be disabled by altering the pattern alternative evasion outline enabled to nothing. But so as to be the merely manage your contain in surplus of it. I might not set in or remove actions or information columns to the evasion outline as you can do it in a usual outline.

The evasion outline is collected of five twenty megabyte outline files that are accessed in a surrounding robin manner, at what time the primary file is filled, the outline move to that file in the succession. When the fifth file is filled, the first file is erasing and a new-fangled file is fashioned.

The outline files are positioned in the monitor subdirectory of SQL Server’s mount address list. If you install to the evasion address list, the corridor will seem to be a bit like this:

D:\ Microsoft SQL Server\MSSQL8.0.MSSQLSERVER\MSSQL\Log\log_xx.trc

By means of the evasion outline for auditing and Problem Analysis:

Still you cannot alter the outline to detain diverse information, you are intelligent to question the outline file and identify just the information you wish to perceive, filter out the rest. This is ended probably by the meaning fn_trace_gettble. Here it allows you to question one or further outline files as if they were a solitary tuple. The purpose can doubt both vigorous and motionless outline files.

The fn_trace_gettble takes two parameters, the trail to the outline file and a numeral worth on behalf of the numeral of overturn files. When you are questioning the evasion outline, the figure of files is forever 5.

The outline does not amass the name of the proceedings monitor, merely the occasion id. In order to get with no trouble legible production you require joining fn_trace_gettable to sys.trce_events to obtain the name of the juncture. Here is an example function describe that will revisit the evasion outline information as of my example of SQL Server 2008. You will have to regulate the trail and the draw file name to set of clothes your surroundings:

Listing 1: Sample showing execution plan for select

select e.nme, q.*
from fn_trce_gettble( 'C:\Microsoft SQL Server\MSSQL8.0.MSSQLSERVER\MSSQL\Log\log_715.trc' , 5 ) as q
join sys.trce_events f on e.trce_event_id = q.EventClass

This query precedes all the rows in the sketch round with the entire characteristic in series. This is not typically sensible because of the integer of statistics come back. You can riddle consequences with a WHERE phrase.

You can boundary the data return by specifying only the column you want to perceive. If you want, you will be able to occasionally decide on statistics into an undying counter to fashion old times of the measures of awareness to you.

Selecting Events:

Depending on your exact supplies, your power is intelligent to employ the actions in the substance collection and the safety Audit collection of proceedings. Even though login information can be helpful on some occasion, it is from time to time too luxurious. Some application generates cents of logins per subsequent. Audit Login breakdown proceedings are usually far less recurrent and beginning a safety audit viewpoint that are of additional attention than winning logins.

Audit diagram entity right of entry also produce a lot of row, but it is very helpful for sanctuary auditing. If it is probable to comprise of moving presentation, I would do so. This occasion fires each time authorization is exercised on a file object. For example, it will document whilst an explicit user accessed the praise certificate tuple. It will make out the type of right entry for example select, update, delete and its motivation bestow you the textbook of the set of laws that was executed. It will in addition tell you if the effort to right of entry the entity was victorious. This will considerably decrease the weight of the outline puts on the member of staff serving at table.

Putting it all together:

A victorious auditing guide standard is collected of two basics. The first is to incarcerate the essential information. The subsequent constituent is to appreciate what the data income and change it to helpful order.

Of the two, the next is additional hard. It is complex in this container by the information that dissimilar proceedings in a draw use the information columns in a different way. Every occasion populates information only keen on a separation of the obtainable columns of the outline. So, to doubt the information astutely we have to be acquainted by means of which columns are pertinent for a given occurrence and what the information in them earnings.

How it is done:

I wonder why no User Interface is prearranged to allow this selection. On the other hand, Microsoft strength contains blond rationale for responsibility. Close by is a number of solemn implication if you allow this alternative.

At any rate it will be able to complete via my preferred instrument, SQL Query Analyzer. Earlier you allow this alternative to associate of sysadmin collection. Before attempting to set the 'c2 audit mode' pattern alternative, you have to allow the 'show advanced options' pattern alternative. This is performed by means of the next authority:

Listing 2: Sample showing execution plan for master

USE master
EXEC sp_configure 'show advanced option', '1'
To enable the feature, set 'c2 audit mode' to 1 using the following command:
sp_configure 'c2 audit mode', 1

To make active the AUDIT analyzer, de-comment the suitable appearance.

Allow at slightest one audit. Doubt recorder. Example logg.xml case has a recorder so as to enable cataloguing for all sister topics.

Listing 3: Sample showing execution plan for group

  Review group arrangement  
	<recorder name="audit.query" additivity="true">
		<level value="info"/> 
		<appender-ref ref="AUDIT"/>

Subsequent to surroundings the worth, you have to stop and resume the member of staff serving at table for C2 review mode result. Now when you do an Insert, Select any other declaration, it will log the occasion in the draw files. These records are inherent in the \mssql\data information depository for non-payment instances of SQL Server 2000, or the \mssql$instancename\data information bank for named instances of SQL Server 2000 as AuditTrace_yyyymmddhhmmss.trc, anywhere the subsequent part of the name indicates date and time when the plot file was shaped. For example, some file names on my system are review_2356147852.trc and review_23561445236. The dimension of a plot is imperfect to 300MB, other than original outline archive are transformed at any time when older one is filled. Also a new one is generated on the blackout of SQL Server with a different set up.

Single obsession to be conscious of is that SQL Server will end if it cannot write plot entries, i.e. you plot so much that you lope out of disk liberty. In tragedy situation, where no freedom can be right away freed for new log records, you can resume SQL Server with the -f standard, which will discount auditing settings.

Characteristic of review system:

  • Arrange the audit at the tuple stage, column stage
  • Permit the client to alter the events (insert/update/delete)
  • Permit the client to immobilize and can make possible the review as and at what time desirable.
  • Permit the client to trail the change from end to end the rendezvous and login name.


The main restriction of the auditing is that it reduces the presentation of the SQL Server. This happens owing to reduction every accomplishment to the dossier. Moment constraint is the unbreakable disk breathing space.

These auditing annals breed fast, which will decrease the disk break. According to the C2, if it is not able to mark to the outline file, SQL Server spirit is blackout.


Stipulation your server to preserve a meticulous audit pursues, then the C2 review alternative with high-quality option. On the other hand, your strength has to believe few belongings before configuring it. Hope you have understood the concepts behind audit trace. Enjoy reading.

See also

Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login