November 13, 2008 at 1:47 pm
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.
December 8, 2008 at 3:50 am
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?
February 13, 2009 at 1:37 am
My default trace crashed because .... disk is full
2009-02-12 15:44:20.880spid116Trace 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
Learn to play, play to learn !
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[/url]
- 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
February 13, 2009 at 2:00 am
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
February 13, 2009 at 2:28 am
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.880spid116Trace 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
Learn to play, play to learn !
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[/url]
- 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
January 27, 2010 at 2:12 am
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
June 15, 2010 at 6:49 am
Thanks for posting this article. Very cool stuff, which I will no doubt be referring to in the future.
June 15, 2010 at 12:11 pm
Excellent work Adam. This is really useful article for any DBA.
M&M
June 16, 2010 at 12:12 pm
Excellent work!
Thanks for sharing with us 🙂
Rudy
June 17, 2010 at 9:58 am
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
Rudy
August 23, 2010 at 10:37 am
Great article, thanks!
I'm not seeing the actual objectName for anything though and tried it with both SQL 2005 SP2 and SQL 2008 SP2 CTP.
Thoughts on why this data is null? I'm using the scripts per your article examples.
Thanks!
Lori
August 23, 2010 at 10:45 am
You have to be in the right database for object_name() to return the right name for a given object_id. For example, an object_id for a table or procedure in adventure works will return null if you execute it against master.
August 24, 2010 at 9:02 am
OK, I just re-ran the test per the script verifying I'm in the right DB and the ObjectName is still Null. Per the default trace it should be capturing this data right? What else can I try?
Thanks!
Lori
August 24, 2010 at 12:44 pm
As per http://msdn.microsoft.com/en-us/library/ms175848.aspx
Displaying Object Names When Viewing Traces
--------------------------------------------------------------------------------
If you wish to display the name of an object rather than the object identifier (Object ID), you must capture the Server Name and Database ID data columns along with the Object Name data column.
Disclaimer: I haven't actually tried this. 🙂
August 24, 2010 at 1:02 pm
Lori,
The script in the article does use the objectname counter to return the object, if all of your objects are returning null, you may have a filter where objectname is null. I had a filter for null objectname names in the beginning of the article to highlight database modifications. You have to make sure you filter where objectname is not null or something specific.
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply