SQL SERVER default tracking app 4-Detect log file autogrow

SQL SERVER default tracking app 4-- automatic growth of detection log files

Users complain that the SQL Server database is running very slowly. This query, which is tracked by default, can identify the long run caused by the log file autogrow activity. Why is it so slow?

SELECT te.name as [Trace_events_name], T.databasename, T.ntdomainname, T.applicationname, T.loginname, T.Duration, T. StartTime, T.endtimefrom sys.fn_trace_gettable (CONVERT (VARCHAR), (SELECT TOP 1f.value from Sys.fn_trace_getinfo ( NULL) F WHERE f.property = 2), DEFAULT) Tjoin sys.trace_events TE on t.eventclass = Te.trace_event_idwhere Te.trace_even t_id = 93AND t.starttime between ' 2012-04-17 13:00:00.000 ' and ' 2012-04-17 15:00:00.000 '

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M00/53/EC/wKiom1Rz8QLye6BRAANNxHFwaBQ799.jpg "height=" 196 "border=" 0 "/>

Some factors cause log file autogrow to perform poorly, such as:

1) If a large transaction causes the log to grow, the SQL transaction needs to wait for the log autogrow to complete. The transaction needs to be written to the SQL Server transaction log file. In general, we will see a message in the SQL error log.

2) Auto-growth and auto-shrinking can cause conflicts on SQL Server databases.

3) Measure the physical fragmentation on the SQL transaction log file.

4) files are initialized immediately (Instant file initialization) only related to data files. will not benefit log file growth.

5) Minimize the size of the transaction. Regular commits help maintain smaller log files.

6) Follow up storage performance. Collects Perfmon performance counter information, for example: Logical Write bytes/sec and Logical Read bytes/sec.

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1582298

SQL SERVER default tracking app 4-Detect log file autogrow