Shulou(Shulou.com)06/01 Report--

one。 The content involved in this article (Contents) the content involved in this article (Contents) background (Contexts) basic knowledge (Rudimentary Knowledge) View default tracking information (Default Trace) supplementary notes (Addon) reference (References) II. Background (Contexts)

Consider this scenario: database tables, stored procedures often do not change, when these changes lead to BUG, many developers do not admit that they did it, then can we find out who did it?

SQL Server has Default Trace default tracking, database records information to log.trc files, you can view trace_event_id,46 to represent Create objects (Object:Created), 47 for Drop objects (Object:Deleted), 93 for automatic log file growth (Log File Auto Grow), 164for Alter objects (Object:Altered), and 20 for error logs (Audit Login Failed).

Although the relevant operation can be found in the above way, it has two disadvantages:

1) log.trc files are scrolling update files, all the data that may be deleted by the system, you can't find it for too long.

2) in some operations, you may have the benefit of hindsight, and you will only look for problems if there is a problem. We should take the initiative to monitor these DDL.

We can use DDL triggers to actively monitor the execution of DDL statements. When DDL is executed on the database, we save this information to the table and send the user's HostName and modified T-SQL to the specified email. This article will describe the use of Default Trace default tracking to solve the above problems, the way of DDL triggers can be referred to: SQL Server DDL trigger application and SQL Server database mail.

three。 Basic knowledge (Rudimentary Knowledge)

Default tracing is a new feature that first appeared in SQL Server 2005 and provides the ability to audit schema changes, such as table creation, stored procedure deletion, and so on. It runs by default, but you can enable and disable it through sp_configure.

The default trace log can be opened and viewed through SQL Server Profiler, or a table can be returned through Transact-SQL using fn_trace_gettable system function query, and the table data can be filtered and filtered.

What useful information can the default trace help us track? You can view the following:

1) use Default Trace to see who restored your database

2) which objects in the database are used by created / altered / deleted

3) find out the reasons for the rapid growth of log files

4) View and filter error messages such as Login failed for user 'sa'

four。 View default trace information (Default Trace)

Let's take a look at what the default trace records during our daily use of DDL:

(1) check whether Default Trace is enabled. If value is 1 in Figure1, default tracking is enabled. If value is 0, default tracking is disabled.

-- query whether Default Trace is enabled SELECT * FROM sys.configurations WHERE configuration_id = 1568

(Figure1:default trace enabled information)

(2) if the default tracking is off, you can turn it on and test it in the following ways:

-- enable Default Trace sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure' default trace enabled', 1; GO RECONFIGURE; GO-- Test whether EXEC sp_configure 'default trace enabled'; GO is enabled-- close Default Trace sp_configure' default trace enabled', 0; GO RECONFIGURE; GO sp_configure 'show advanced options', 0; GO RECONFIGURE; GO

(3) get the path of the log.trc rolling update file that is currently in use:

-- get the path of the current trace file SELECT * FROM:: fn_trace_getinfo (0)

(Figure2:log.trc file path)

The meaning represented by the option property value:

1:trace options, there are three values: 2 (scrolling file), 4, 8 (black box). Please refer to sp_trace_create

2:file name, or, more precisely, the path to the trace file

3:max file size, which sets the maximum scrolling file size. When this value is reached, a new scrolling file is created.

4:stop time, sets the time when trace stops

5: current status (0=stopped, 1=running)

In SQL Server2000, when using fn_trace series system stored procedures, the name of the stored procedure needs to be marked with "::". In SQL Server2000, the contents of the trace will be written to the file only when the trace is stopped (stop) and turned off (close).

(IV) Let's test how the default trace tracks the most commonly used DDL scripts. First create a test database TraceDB, and then create a test table Trace_log. Through the following script, the default trace records the contents of Figure3 and Figure4, and the EventName is Object:Created.

-- create a test database USE MASTER GO CREATE DATABASE TraceDB-- generate a DDL event USE TraceDB GO CREATE TABLE dbo.Trace_log (Id INT IDENTITY (1Magne1) not null, Sometext CHAR (3) null) by creating a table-- Script1: return the information of the Create operation just now-- =-- Author:-- Create date:-- Description:-- Blog:-- = DECLARE @ tracefile NVARCHAR (MAX) SET @ tracefile = (SELECT LEFT ([path]) LEN ([path])-CHARINDEX ('\', REVERSE ([path])) +'\ log.trc' FROM sys.traces WHERE [is_default] = 1) SELECT TOP 100 gt. [HostName], gt. [ServerName], gt. [DatabaseName], gt. [SPID], gt. [ObjectName], gt. [objecttype] [ObjectTypeID], sv. [subclass _ name] [ObjectType], e.[ category _ id] [CategoryID], c. [Name] [Category] Gt. [EventClass] [EventID], e. [Name] [EventName], gt. [LoginName], gt. [ApplicationName], gt. [StartTime], gt. [TextData] FROM fn_trace_gettable (@ tracefile DEFAULT) gt LEFT JOIN sys.trace_subclass_values sv ON gt. [eventclass] = sv.[ trace _ event_id] AND sv.[ subclass _ value] = gt. [objecttype] INNER JOIN sys.trace_events e ON gt. [eventclass] = e.[ trace _ event_id] INNER JOIN sys.trace_categories c ON e.[ category _ id] = c.[ category _ id] WHERE gt. [spid] > 50 AND-- spid up to 50 AND for the system to use gt. [DatabaseName] = 'TraceDB 'AND-filter gt by DatabaseName. [ObjectName] =' Trace_log' AND-filter by objectname. [category _ id] = 5 AND-- category 5 represents an object 8 for security. [trace _ event_id] = 46-- trace_event_id 46 indicates Create object (Object:Created), 47 indicates Drop object (Object:Deleted), 93 indicates automatic log file growth (Log File Auto Grow), 164indicates Alter object (Object:Altered), 20 indicates error log (Audit Login Failed) ORDER BY [StartTime] DESC

(information about the first half of the Figure3:Create event)

(information in the second half of the Figure4:Create event)

(5) then test the event tracking log generated by modifying the table. First, we artificially generate an event that modifies the table, add a column to the Trace_log table, and replace the e. [trace _ event_id] = 46 of the above Script1 script Where with e. [trace _ event_id] = 164. so that you can view the information of the Alter object, and the EventName is Object:Altered.

-- generate a DDL event USE TraceDB GO ALTER TABLE Trace_log ADD Col INT by modifying the table-- Script2: return the information of just Alter operation WHERE gt. [spid] > 50 AND-- those below 50y use gt for the system. [DatabaseName] = 'TraceDB' AND-- filter gt by DatabaseName. [ObjectName] =' Trace_log' AND-- filter e. [category _ id] = 5 AND-- category 5 represents the object by objectname Trace_event_id 46 indicates Create object (Object:Created), 47 indicates Drop object (Object:Deleted), 93 indicates Log File Auto Grow, 16 indicates Alter object (Object:Altered), 20 indicates error log (Audit Login Failed) ORDER BY [StartTime] DESC

(information about the first half of the Figure5:Alter event)

(information in the second half of the Figure6:Alter event)

(6) then test the event tracking log generated by modifying the table. First, we artificially generate an event to delete the table, and then replace e. [trace _ event_id] = 46 with e [trace _ event_id] = 47 in the above Script1 script Where, so that you can view the information of the Drop object, and the EventName is Object: Deleted.

-- generate a DDL event USE TraceDB GO DROP TABLE Trace_log by deleting the table

(information in the second half of the Figure7:Drop event)

five。 Supplementary note (Addon)

1. For log.trc files, it seems that only 5 files are retained. Where can I set them? The size of the file defaults to 20MB. Is there any place to set it? SQL Server maintains only 5 Trace files, up to a maximum of 20m. When SQL Server restarts or reaches its maximum value, a new file is generated and the oldest Trace file is deleted.

(Figure8:log*.trc file)

(Figure9:log*.trc settings)

An attempt to update the system table with the following SQL failed: exec sp_configure 'allow updates',1

This option still exists in sp_configure stored procedures, but its functionality is not available in SQL Server. Its settings do not work. Starting with SQL Server 2005, direct updates to system tables are not supported.

two。 Double-clicking the log.trc file will open it in SQL Server Profiler mode. Is it familiar to see here? It's just that we usually use Profiler to customize tracking events, while those saved in the Log folder are tracked by default.

3. In addition to using SQL Server Profiler custom tracing, you can also use the T-SQL of system stored procedures: sp_trace_create, sp_trace_setevent, etc., to create a trace. For more information, please refer to: SQL tracing introduction.

4. With regard to the parameters of the fn_trace_gettable system function, it is necessary to talk about it here. In order to see the impact of different parameters on reading the file, use the following SQL script to test and return COUNT (1) to see the difference of the read file.

1) start with the @ tracefile file, and then read a scrolling update file. 1 is the file itself.

2) start with the @ tracefile file and read 2 scrolling update files later

3) start with @ tracefile file. 0,-1, and default all indicate that all files are read later.

-- define the file path variable DECLARE @ tracefile NVARCHAR (MAX) SET @ tracefile = (SELECT LEFT ([path], LEN ([path])-CHARINDEX ('\', REVERSE ([path])) +'\ log.trc' FROM sys.traces WHERE [is_default] = 1)-- start with the @ tracefile file and then read a rolling update file. 1 for the file itself SELECT COUNT (1) FROM:: fn_trace_gettable (@ tracefile,1)-- start with @ tracefile file, then read two scrolling update files SELECT COUNT (1) FROM:: fn_trace_gettable (@ tracefile,2)-- start with @ tracefile file 0,-1 and default means to read all files SELECT COUNT (1) FROM:: fn_trace_gettable (@ tracefile,0) SELECT COUNT (1) FROM:: fn_trace_gettable (@ tracefile,-1) SELECT COUNT (1) FROM:: fn_trace_gettable (@ tracefile,default)

5. Default Trace cannot replace the function of DDL trigger (see: SQL Server uses DDL Trigger to prevent database modification). The default trace should be used as a monitor for SQL instances or to quickly get details of SQL problem events.

6. Default Trace does not track all events, it captures some key information, including auditing events,database events,error events,full text events,object creation,object deletion,object alteration.

7. Information about trace_event_id is described in Read Default Trace: If you are interested in what the default trace has been setup to capture you can run this (Note you cannot edit the default trace!).

-- Script5:trace_event SELECT * FROM fn_trace_geteventinfo (1) tg INNER JOIN sys.trace_events te ON tg. [eventid] = te.[ trace _ event_id] INNER JOIN sys.trace_columns tc ON tg. [columnid] = tc.[ trace _ column_id] WHERE te.name like'% login%'

(Figure10:trace_event_id information)

Another way to see the Event type is through: sp_trace_setevent.

8. About the Script1 script: the @ tracefile variable in the FROM fn_trace_gettable (@ tracefile, DEFAULT) gt represents the tracking log file path. You can also use the following way, but it's important to note that the following way returns the search for the currently used scrolling update file, while Script1's starts the search with the first file in the history scroll.

-- current rolling update file FROM sys.fn_trace_gettable (CONVERT (VARCHAR), (SELECT TOP 1 f. [value] FROM sys.fn_trace_getinfo (NULL) f WHERE f. Property = 2), DEFAULT) gt

9. How do I get which Event and column are tracked by a Trace?

-- get which Event and column DECLARE @ traceid INT SET @ traceid = 1 SELECT TCA.category_id,TCA.name AS category_name, TE.trace_event_id,TE.name AS trace_event_name, TCO.trace_column_id are tracked by a Trace TCO.name AS trace_column_name FROM fn_trace_geteventinfo (@ traceid) AS EI LEFT JOIN sys.trace_events AS TE ON EI.eventid = TE.trace_event_id LEFT JOIN sys.trace_categories AS TCA ON TE.category_id = TCA.category_id LEFT JOIN sys.trace_columns AS TCO ON EI.columnid = TCO.trace_column_id GO

(Figure11: some Trace information)

10. DBCC TRACEON (xxx); what does this tracking tag have to do with Default Trace?

six。 References (References)

SQL Server 2005-Default Trace (default trace)

Use Default Trace to see who restored your database?

The Default Trace

Default trace enabled (Option)

SQL SERVER tracking function

Some alternative applications of Trace

Read Default Trace

Fn_trace_gettable

Fn_trace_gettable (Transact-SQL)

Sp_trace_setevent

ObjectType Trace Event Column

Brief introduction to SQL tracking

How to use stored procedures to monitor traces in SQL Server 2005

Sp_trace_create (Transact-SQL)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.