August 17, 2014 at 7:53 pm
Hi All,
I created the maintenance plan for Rebuild/Reorganize Indexes everyday and I want to check if the transaction log has increased or not. How to do that ?
Thanks!!
August 18, 2014 at 12:47 am
From http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
SELECT
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
e.category_id = 2 AND --category 2 is database
e.trace_event_id = 93 --93=Log File Auto Grow
August 18, 2014 at 10:59 pm
Andrew G (8/18/2014)
From http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
SELECTloginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'TraceDB' AND
e.category_id = 2 AND --category 2 is database
e.trace_event_id = 93 --93=Log File Auto Grow
Thanks for your response ! Appreciate it !
I wonder what does this path ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0) refer to ??
August 18, 2014 at 11:21 pm
Sorry, that refers to the location of the Default Trace. Also the previous query has the database name set there.
Run the following to find the default trace and not limit it to a specific database. Note you will have multiple default trace files, so if you want to check older files, you'll need to plug that into the query.
DECLARE @DefaultTracePath varchar(3000)
SELECT @DefaultTracePath = CAST([value] AS varchar(3000))
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2
SELECT
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable(@DefaultTracePath,0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE e.category_id = 2 AND --category 2 is database
e.trace_event_id = 93 --93=Log File Auto Grow
August 18, 2014 at 11:59 pm
Andrew G (8/18/2014)
Sorry, that refers to the location of the Default Trace. Also the previous query has the database name set there.Run the following to find the default trace and not limit it to a specific database. Note you will have multiple default trace files, so if you want to check older files, you'll need to plug that into the query.
DECLARE @DefaultTracePath varchar(3000)
SELECT @DefaultTracePath = CAST([value] AS varchar(3000))
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2
SELECT
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
databasename,
objectName,
e.category_id,
cat.name,
textdata,
starttime,
endtime,
duration,
eventclass,
eventsubclass,
e.name as EventName
FROM ::fn_trace_gettable(@DefaultTracePath,0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE e.category_id = 2 AND --category 2 is database
e.trace_event_id = 93 --93=Log File Auto Grow
Thank you . I run this query on specific database but it give me zero record
August 19, 2014 at 12:49 am
Navigate to your default trace path on the server and look for older default trace files.
For example if the following query returns, "D:\MSSQL11.MSSQLSERVER\MSSQL\Log\log_31.trc", then goto the folder and check for older files like log_30.trc, log_27.trc and plug that file location into the query from my first post. You can step back through the default trace files as far back as they go. You can open the trace files in SQL Profiler as well.
SELECT CAST([value] AS varchar(3000))
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2
If you keep getting nothing back, then no log growth has occurred in the time that the default trace has been running.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply