SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default trace - A Beginner's Guide


Default trace - A Beginner's Guide

Author
Message
Adam Haines
Adam Haines
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3170 Visits: 3135

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
Rin Sitah
Rin Sitah
Old Hand
Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)Old Hand (399 reputation)

Group: General Forum Members
Points: 399 Visits: 548
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?
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12073 Visits: 8924
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86805 Visits: 45254
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, MVP, M.Sc (Comp Sci)
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


ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12073 Visits: 8924
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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- 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 :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
sporoy
sporoy
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 623
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
Chris Houghton
Chris Houghton
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 1695
Thanks for posting this article. Very cool stuff, which I will no doubt be referring to in the future.
M&M
M&M
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3529 Visits: 3906
Excellent work Adam. This is really useful article for any DBA.

M&M
Rudy Panigas
Rudy Panigas
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1311
Excellent work!

Thanks for sharing with us Smile



Rudy Panigas
Rudy Panigas
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 1311
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search