InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:44:05 GMT20http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]sibir1us (5/10/2011)[/b][hr][quote][b]YSLGuru (5/10/2011)[/b][hr][quote][b]sibir1us (5/9/2011)[/b][hr]Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq[/quote]Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its really annoying to be required to sign up for yet one more sites list just to read/review something the author wants to actually share with others.[/quote]It is worth it, trust me. :)[/quote]It very well maybe but the point is you don't know until you've gone thru the sign up process. Its one thing to sign up at a site you plan to visit regularly like this one but if you have to register just to review/read something advertised on another site I think thats asking too much in this day and age. Then again maybe most foilks like collecting site registrations like some collect stamps and its just me who prefers not to have to register every time. I did get a post from what I assume is the site owner saying the registration was to discourage those who only wanted to review the default trace file and that they did not have time to spam the emails of those who registered. Not sure where the user read anything about being accused of spamming however its their site so they can put in place what ever restrictions they want.Tue, 10 May 2011 11:43:14 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]YSLGuru (5/10/2011)[/b][hr][quote][b]sibir1us (5/9/2011)[/b][hr]Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq[/quote]Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its relaly annoying to be required to sign up for yet one more sites list just to read/review something teh author wants to actualy share with others.[/quote]It is worth it, trust me. :)Tue, 10 May 2011 11:28:41 GMTsibir1ushttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]sibir1us (5/9/2011)[/b][hr]Here is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faq[/quote]Too bad you have to sign up for yet one more site in order to review/use the item detailed in the post. Maybe its just me but its relaly annoying to be required to sign up for yet one more sites list just to read/review something teh author wants to actualy share with others.Tue, 10 May 2011 10:54:16 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxHere is a nice set of reports on the Default trace: http://sqlconcept.com/default-trace-audit-documentation-and-faqMon, 09 May 2011 22:49:26 GMTsibir1ushttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxHere is a more detailed article about the Default trace: [url=http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/]http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/[/url]Tue, 15 Mar 2011 07:34:35 GMTsibir1ushttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]gaffar786 (12/18/2010)[/b][hr]Trace ID '3' was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem. i am getting this error on SQL Server, There is default error message enabled on SQL.How to specifiy the roll over option on default trace.if i open any default trace files, i am unable to view any roll option on SQL Server profiles studio.Any idea would be appreciated.[/quote]You can't modify the default trace beyond stopping it using sp_configure. As ALZDBA mentioned the default trace is usually trace id 1 in sys.traces, but a better way to tell is to check the is_default column for the trace. I've never seen the Default Trace anything but 1, but if you have common criteria or C2 auditing enabled it might get a different id.Mon, 20 Dec 2010 08:09:53 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxDefault trace typicaly has trace id 1.Check sys.traces to figure out what kind of trace that is, what file it tries to write to, and check if you can correct that problemSun, 19 Dec 2010 03:39:40 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxTrace ID '3' was stopped because of an error. Cause: Stopping the trace because the current trace file is full and the rollover option is not specified.. Restart the trace after correcting the problem. i am getting this error on SQL Server, There is default error message enabled on SQL.How to specifiy the roll over option on default trace.if i open any default trace files, i am unable to view any roll option on SQL Server profiles studio.Any idea would be appreciated.Sat, 18 Dec 2010 16:56:28 GMTgaffar786http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxThanks for the tip, Jack.Thu, 02 Dec 2010 22:11:47 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]Jeff Moden (12/2/2010)[/b][hr]I recently had cause to revisit this fine article... well done, Adam. Great for neophytes and alumni alike.As a side bar, here's the code I ended up using which could actually be turned into an iTVF...[code="sql"] SELECT trc.LoginName, -- trc.LoginSID, trc.SPID, trc.IsSystem, trc.HostName, trc.ApplicationName, trc.ServerName, trc.DatabaseName, trc.ObjectName, evt.Category_ID, CategoryName = cat.name, trc.TextData, trc.StartTime, trc.EventClass, trc.EventSubClass, EventName = evt.name FROM FN_TRACE_GETTABLE( ( SELECT CAST([Value] AS NVARCHAR(4000)) --Drive, path, and filename of trace file FROM FN_TRACE_GETINFO(0) --0 = Current Server WHERE [Property] = 2 --2 = Trace File Path ) ,0) trc --0 = Latest default trace file INNER JOIN sys.Trace_Events evt ON eventclass = trace_event_id INNER JOIN sys.Trace_Categories cat ON evt.category_id = cat.category_id WHERE trc.DatabaseName = 'somedatabasenamehere' AND trc.ObjectName = 'somesqlserverobjectnamehere';[/code]Heh... and, no... I don't code for things to work on case sensitive servers. :-P[/quote]Jeff,I prefer to use sys.traces over fn_trace_getinfo and then you can do an cross apply on fn_Trace_gettable. Like this:[code="sql"]sys.traces T CROSS APPLY sys.fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) <> 0 THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc' ELSE T.[path] End, T.max_files) TRC[/code]Thu, 02 Dec 2010 13:17:26 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]peter-970097 (9/21/2010)[/b][hr]Helpful article Adam - thank you.How can I use this to trace Queries and Sp activity?PeterMelbourne, Australia[/quote]I don't believe you can. The default trace wasn't setup to detect such things. You'll have to setup your own trace for such things.Thu, 02 Dec 2010 13:00:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxI recently had cause to revisit this fine article... well done, Adam. Great for neophytes and alumni alike.As a side bar, here's the code I ended up using which could actually be turned into an iTVF...[code="sql"] SELECT trc.LoginName, -- trc.LoginSID, trc.SPID, trc.IsSystem, trc.HostName, trc.ApplicationName, trc.ServerName, trc.DatabaseName, trc.ObjectName, evt.Category_ID, CategoryName = cat.name, trc.TextData, trc.StartTime, trc.EventClass, trc.EventSubClass, EventName = evt.name FROM FN_TRACE_GETTABLE( ( SELECT CAST([Value] AS NVARCHAR(4000)) --Drive, path, and filename of trace file FROM FN_TRACE_GETINFO(0) --0 = Current Server WHERE [Property] = 2 --2 = Trace File Path ) ,0) trc --0 = Latest default trace file INNER JOIN sys.Trace_Events evt ON eventclass = trace_event_id INNER JOIN sys.Trace_Categories cat ON evt.category_id = cat.category_id WHERE trc.DatabaseName = 'somedatabasenamehere' AND trc.ObjectName = 'somesqlserverobjectnamehere';[/code]Heh... and, no... I don't code for things to work on case sensitive servers. :-PThu, 02 Dec 2010 12:52:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxHelpful article Adam - thank you.How can I use this to trace Queries and Sp activity?PeterMelbourne, AustraliaTue, 21 Sep 2010 21:23:23 GMTpeter-970097http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxThis is incredibly cool! I just queried the trace to find the cause of a mysterious nightly error I've been seeing for months! Turns out its from a SQL Agent Job that isn't even scheduled to run (so of course I wasn't looking at it). Thanks for this very helpful article. It's great to have another tool in my DBA bag of tricks :-)SusanFri, 03 Sep 2010 13:39:06 GMTSusan Van Eyckhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxThat did the trick! Duh! I should have looked closer to the where clause.Thank you,LoriWed, 25 Aug 2010 06:47:38 GMTlorisj33http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxLori,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.Tue, 24 Aug 2010 13:02:29 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxAs per http://msdn.microsoft.com/en-us/library/ms175848.aspxDisplaying 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. :)Tue, 24 Aug 2010 12:44:36 GMTMarcia Qhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxOK, 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!LoriTue, 24 Aug 2010 09:02:33 GMTlorisj33http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxYou 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.Mon, 23 Aug 2010 10:45:39 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxGreat 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!LoriMon, 23 Aug 2010 10:37:57 GMTlorisj33http://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxHello 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 procsGOIF 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]GOUSE [TraceDB]GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOCREATE PROC [dbo].[DSS_View_Default_Trace]as-- create a temp table to house the dataCREATE TABLE #DTraceLocal (DTraceName NVARCHAR(MAX))-- inserting and converting dataINSERT 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 fileDECLARE @tracelocal NVARCHAR(MAX)SET @tracelocal = (SELECT * FROM #DTraceLocal WHERE [DTraceName] LIKE '%:\%') -- showing data from within the default trace fileSELECT 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 tableDROP TABLE #DTraceLocalGOThu, 17 Jun 2010 09:58:15 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxExcellent work! Thanks for sharing with us :)Wed, 16 Jun 2010 12:12:18 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxExcellent work Adam. This is really useful article for any DBA.Tue, 15 Jun 2010 12:11:30 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxThanks for posting this article. Very cool stuff, which I will no doubt be referring to in the future.Tue, 15 Jun 2010 06:49:08 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxHi, disabling and enabling the [b]default trace enabled[/b] 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',0GOreconfigure with overrideGOandsp_configure 'default trace enabled',0GOreconfigure with overrideGOCheersWed, 27 Jan 2010 02:12:21 GMTsporoyhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxActually, 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 : [i]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] 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.Fri, 13 Feb 2009 02:28:54 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxQuery sys.traces to see if the trace still exists (it should). Use sp_trace_setstatus to restart itFri, 13 Feb 2009 02:00:11 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxMy default trace crashed because .... disk is full [quote]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. [/quote][b]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 ?[/b]I did find this in BOL: [url]http://technet.microsoft.com/en-us/library/cc293615.aspx [/url]but that doesn't give a simple solution.Fri, 13 Feb 2009 01:37:47 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxIs 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 = ViewBut what system table contains this reference? (I had to find these references from MSDN!)Apparanlty there is none.[url]http://www.sqlservercentral.com/Forums/Topic613481-149-1.aspx[/url]But really- is this the case?Mon, 08 Dec 2008 03:50:56 GMTRin Sitahhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote]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? [/quote]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.Thu, 13 Nov 2008 13:47:49 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]Adam Haines (11/11/2008)[/b][hr]Sounds like you have a very active server. The default trace will only maintain 5 trace files at a time. I believe your issue is that you can only fit a few days worth of events into 5 trace files. It should also be noted that every time SQL restarts a new trace file is created. This behavior can nip you in the butt because introducing a new file will release the eldest file. At which point you will have 3 complete trace files, 1 partially complete trace file and 1 new/empty trace file.If you wanted to maintain complete history, you can have a sql job that inserts the trace data into a local table daily. At which point you have history of all captured events. Another option would be to setup DDL triggers to handle server/database level DDL events.[/quote]We do have a very busy box. It services 300-400 users via third party wen based accountig application. Our DB is around 120GB so we are getting up there in over all size.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? If yes then I like your idea about capturing the data. We do reboot every server each night simply because our third party software has far less issues with daily re-boots. So we can with fare ease, schedule a dump of the trace data each night.ThanksThu, 13 Nov 2008 13:37:54 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]Simon Villiers (11/12/2008)[/b][hr]The sys.traces view has a "max_files" item with a value of 5.Is it possible for this value to be changed to allow more/less trace files?S.[/quote]Hi Simon, I was in training with a Microsoft SQL Engineer today; and asked him that exact same question and asked him about altering the default trace events. He said you *might* be able to do it, but if you do it will not be supported by Microsoft. Now he didn't make it clear to me was he referring to only SQL Server or just Default Trace. Suggestion he gave was, some people here have given is to backup the files on regular intervals. As those files total 100MB only, about 20MB each file. So if you server has alot of activity have a script copy the files every x hours, and make sure when you are about to restart SQL Server you stop the SQL Server and copy the files. Because restarting the SQL Server forces it to make a new trace file in default trace. Thanks.- Mohit.Wed, 12 Nov 2008 17:02:40 GMTMohit K. Guptahttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxThe sys.traces view has a "max_files" item with a value of 5.Is it possible for this value to be changed to allow more/less trace files?S.Wed, 12 Nov 2008 16:49:44 GMTFalhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxSounds like you have a very active server. The default trace will only maintain 5 trace files at a time. I believe your issue is that you can only fit a few days worth of events into 5 trace files. It should also be noted that every time SQL restarts a new trace file is created. This behavior can nip you in the butt because introducing a new file will release the eldest file. At which point you will have 3 complete trace files, 1 partially complete trace file and 1 new/empty trace file.If you wanted to maintain complete history, you can have a sql job that inserts the trace data into a local table daily. At which point you have history of all captured events. Another option would be to setup DDL triggers to handle server/database level DDL events.Tue, 11 Nov 2008 22:17:30 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxI have tried listing each trc file in the query and still noth before a few days back. ThanksTue, 11 Nov 2008 20:19:10 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]YSLGuru (11/11/2008)[/b][hr]Adam,Oustanding article; gives what users really want! Interesting question though, any thoughts as to why one may have Default trace data going back only a few days even though the option for enabling/disabling the Default trace has never been executed since SQL 2005 first went on line for the user a year ago? I've been with the company for less then a year and am still figuring out whare everything is so many things like the cool stuff have to take a back seat. I checked and the Default Trace option was not disabled but for some reason the oldest trace info for it is only a few days back. Any thoughts?Thanks[/quote]Are you using the earliest file in the folder path where the trace logs are created? The function I used in the article to query the trace does a rollup of all files from the initial one to current. For example, say you have log.trace, log1.trc, log2.trc, and log3.trc. If you specify log3.trc you only get the data that is contained in log3.trc, which may or may not have a lot of data. It depends on how active your server is. If you were to choose log2.trc, you will get the contents of log2.trc and log3.trc. Now if you choose log.trc you get the contents of all the trace files up to the current trace file. This is the rollup feature I was speaking of.Tue, 11 Nov 2008 17:38:29 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxAdam,Oustanding article; gives what users really want! Interesting question though, any thoughts as to why one may have Default trace data going back only a few days even though the option for enabling/disabling the Default trace has never been executed since SQL 2005 first went on line for the user a year ago? I've been with the company for less then a year and am still figuring out whare everything is so many things like the cool stuff have to take a back seat. I checked and the Default Trace option was not disabled but for some reason the oldest trace info for it is only a few days back. Any thoughts?ThanksTue, 11 Nov 2008 17:14:06 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]Marcia Q (11/11/2008)[/b][hr]Thanks for posting this article - I didn't know this trace was out there.Does anyone have any suggestions as to why my default trace doesn't show up? I'm a sysadmin...When I run: SELECT * FROM sys.configurations WHERE configuration_id = 1568I get this:configuration_id name value minimum maximum value_in_use description . is_dynamic is_advanced--------------------------------------------------------------------------------1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1But when I SELECT * FROM ::fn_trace_getinfo(0) OR select * from sys.traces I get no results.I see some old trace files named log_625.trc, log-626.trc, log_627.trc, log_628.trc but they're from a two-day time period back in October.I'm confused...again... [/quote]I would try sp_reconfigure to disable it and then use sp_configure to enable it back.Tue, 11 Nov 2008 16:59:51 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspx[quote][b]Brian Strickland (11/11/2008)[/b][hr]Forgive me for being unclear, but our servers don't have a log.trc file. There are a number of other trc files with naming conventions like log_xxx.trc. On one of our servers, the latest trace file is from 10/30/2008 and is named log_211.trc.[/quote]Create some objects and look for them in the 211 trace file. This way you can verify if the trace is working or not. If not, disable then enable it.Tue, 11 Nov 2008 16:59:19 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic600430-1208-1.aspxForgive me for being unclear, but our servers don't have a log.trc file. There are a number of other trc files with naming conventions like log_xxx.trc. On one of our servers, the latest trace file is from 10/30/2008 and is named log_211.trc.Tue, 11 Nov 2008 16:53:46 GMTBrian Strickland-453869