Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Default trace - A Beginner's Guide Expand / Collapse
Author
Message
Posted Thursday, November 13, 2008 1:47 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058

So to verify/recap, the default action for SQL Server is to start a new trace file each time it's rebooted and it keeps at max 3 trc files. That sound about right?

It can matain 5 files at a time. If the server or sql restarts you will have 3 full files, 1 file partially full (because it was not full before SQL restarted) and 1 new file (empty).

You can absolutely move the trace data into tables using ::fn_trace_gettable. It will be no more difficult than inserting data from a table.




My blog: http://jahaines.blogspot.com
Post #602378
Posted Monday, December 8, 2008 3:50 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 9:03 AM
Points: 291, Visits: 476
Is there a system table or view that relates the ObjectType ID from the defualt trace to meaningful names?
We only get out numbers from this field. How are we supposed to tell waht sort of ObjectType is what?
For example, 8278 = View
But what system table contains this reference? (I had to find these references from MSDN!)

Apparanlty there is none.
http://www.sqlservercentral.com/Forums/Topic613481-149-1.aspx

But really- is this the case?
Post #615431
Posted Friday, February 13, 2009 1:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:11 AM
Points: 7,010, Visits: 8,461
My default trace crashed because .... disk is full

2009-02-12 15:44:20.880 spid116 Trace ID '1' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.


Is there a statement to just restart the default trace?
Or do I really need to script it from an existing file and start that one ?
Or do I really need to stop/start the SQLserver instance ?


I did find this in BOL: http://technet.microsoft.com/en-us/library/cc293615.aspx
but that doesn't give a simple solution.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #656340
Posted Friday, February 13, 2009 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 43,016, Visits: 36,174
Query sys.traces to see if the trace still exists (it should). Use sp_trace_setstatus to restart it


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #656350
Posted Friday, February 13, 2009 2:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:11 AM
Points: 7,010, Visits: 8,461
Actually, I query sys.traces to import the trace file to figure out file extends, ...

There is nomore info for trace id 1 !
No info at all because we are not tracing for other reasons either.

Then I searched SQLservers Errorlog and got the the errormessage stating the default trace ended because :

2009-02-12 15:44:20.880 spid116 Trace ID '1' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.

I don't know for sure about that time, but currently the disk (250GB) has 50GB free space.
I'm still trying to figure out what's been goin on, but for that server I'm just a "passer-by" who got called in to help out. So I'm in contact with the actual administrators to give me more input.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #656367
Posted Wednesday, January 27, 2010 2:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:30 PM
Points: 142, Visits: 560
Hi, disabling and enabling the default trace enabled option in sp_configure has restarted the stopped default trace in my server (I couldn't find out why it stopped by the way, no warning at error log)

sp_configure 'default trace enabled',0
GO
reconfigure with override
GO

and

sp_configure 'default trace enabled',0
GO
reconfigure with override
GO

Cheers
Post #854234
Posted Tuesday, June 15, 2010 6:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:57 AM
Points: 558, Visits: 1,481
Thanks for posting this article. Very cool stuff, which I will no doubt be referring to in the future.
Post #937435
Posted Tuesday, June 15, 2010 12:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:15 PM
Points: 2,278, Visits: 3,793
Excellent work Adam. This is really useful article for any DBA.

Mohammed Moinudheen
Post #937742
Posted Wednesday, June 16, 2010 12:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
Excellent work!

Thanks for sharing with us :)



Post #938472
Posted Thursday, June 17, 2010 9:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:24 AM
Points: 312, Visits: 1,107
Hello Everyone,
Just looking over the article again and thought I would make a stored procedure which automatically gets the path and log name used by the default trace. This way you can execute it on any SQL (2005/2008/R2) server and it will get the proper location and finally show you the details.

I also sorted by date with the most current at the top.

Here it is:


USE [TraceDB] -- change database name to where you keep your stored procs
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DSS_View_Default_Trace]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DSS_View_Default_Trace]
GO

USE [TraceDB]
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE PROC [dbo].[DSS_View_Default_Trace]
as

-- create a temp table to house the data
CREATE TABLE #DTraceLocal (DTraceName NVARCHAR(MAX))

-- inserting and converting data
INSERT INTO #DTraceLocal (DTraceName)
(SELECT CONVERT(NVARCHAR(MAX), [value]) FROM ::fn_trace_getinfo(0))

-- statement below shows actual path of the trace file
--SELECT * FROM #DTraceLocal WHERE [DTraceName] LIKE '%:\%'

-- pick out directory and name of the default trace file
DECLARE @tracelocal NVARCHAR(MAX)
SET @tracelocal = (SELECT * FROM #DTraceLocal WHERE [DTraceName] LIKE '%:\%')


-- showing data from within the default trace file
SELECT
loginname AS 'Login Name',
--spid,
hostname AS 'Host Name',
applicationname AS 'Application Name',
servername AS 'Server Name',
databasename AS 'Database Name',
objectName AS 'Object Name',
--e.category_id AS 'Category ID',
cat.name AS 'Category Name',
textdata AS 'Text Data',
starttime AS 'Start Time',
--eventclass AS 'EventClass',
eventsubclass AS '0=Begin,1=Commit',
e.name AS 'Event Name'
FROM ::fn_trace_gettable(@tracelocal,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
ORDER BY StartTime DESC

-- deleting temp table
DROP TABLE #DTraceLocal
GO



Post #939035
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse