May 11, 2012 at 9:31 am
I want to look through about 30 trace files from this past week. I can open each one, then "Save as Trace Table" to a SQL table to run queries against each of the 30. But is there a better way ? Perhaps merge them as trace files, or open them all and save to the same SQL table so I can query just 1 table ?
How do you handle groups of trace files when working with them ?
May 11, 2012 at 10:03 am
homebrew01 (5/11/2012)
I want to look through about 30 trace files from this past week. I can open each one, then "Save as Trace Table" to a SQL table to run queries against each of the 30. But is there a better way ? Perhaps merge them as trace files, or open them all and save to the same SQL table so I can query just 1 table ?How do you handle groups of trace files when working with them ?
create a table with the schema definition we have in trace file then save all the trace file one by one "as table " then insert everything in table created on first step. u need to have another column which should have a unique value to differentiate all traces data
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 11, 2012 at 10:08 am
Query them from SQL, and/or insert into table from there.
SELECT <columns desired> FROM fn_trace_gettable('<trace file name>', 1)
Saving one by one from the GUI is just painfully slow
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
May 11, 2012 at 10:11 am
if it helps, i usually create this temp table, and then insert multiples into it via the script below:
i don't keep track of the specific file the way Bhuvnesh suggests, but you could add another column to do that:
CREATE TABLE [dbo].[#TMP] (
[NAME] NVARCHAR(128) NULL,
[TEXTDATA] NTEXT NULL,
[BINARYDATA] IMAGE NULL,
[DATABASEID] INT NULL,
[TRANSACTIONID] BIGINT NULL,
[LINENUMBER] INT NULL,
[NTUSERNAME] NVARCHAR(256) NULL,
[NTDOMAINNAME] NVARCHAR(256) NULL,
[HOSTNAME] NVARCHAR(256) NULL,
[CLIENTPROCESSID] INT NULL,
[APPLICATIONNAME] NVARCHAR(256) NULL,
[LOGINNAME] NVARCHAR(256) NULL,
[SPID] INT NULL,
[DURATION] BIGINT NULL,
[STARTTIME] DATETIME NULL,
[ENDTIME] DATETIME NULL,
[READS] BIGINT NULL,
[WRITES] BIGINT NULL,
[CPU] INT NULL,
[PERMISSIONS] BIGINT NULL,
[SEVERITY] INT NULL,
[EVENTSUBCLASS] INT NULL,
[OBJECTID] INT NULL,
[SUCCESS] INT NULL,
[INDEXID] INT NULL,
[INTEGERDATA] INT NULL,
[SERVERNAME] NVARCHAR(256) NULL,
[EVENTCLASS] INT NULL,
[OBJECTTYPE] INT NULL,
[NESTLEVEL] INT NULL,
[STATE] INT NULL,
[ERROR] INT NULL,
[MODE] INT NULL,
[HANDLE] INT NULL,
[OBJECTNAME] NVARCHAR(256) NULL,
[DATABASENAME] NVARCHAR(256) NULL,
[FILENAME] NVARCHAR(256) NULL,
[OWNERNAME] NVARCHAR(256) NULL,
[ROLENAME] NVARCHAR(256) NULL,
[TARGETUSERNAME] NVARCHAR(256) NULL,
[DBUSERNAME] NVARCHAR(256) NULL,
[LOGINSID] IMAGE NULL,
[TARGETLOGINNAME] NVARCHAR(256) NULL,
[TARGETLOGINSID] IMAGE NULL,
[COLUMNPERMISSIONS] INT NULL,
[LINKEDSERVERNAME] NVARCHAR(256) NULL,
[PROVIDERNAME] NVARCHAR(256) NULL,
[METHODNAME] NVARCHAR(256) NULL,
[ROWCOUNTS] BIGINT NULL,
[REQUESTID] INT NULL,
[XACTSEQUENCE] BIGINT NULL,
[EVENTSEQUENCE] BIGINT NULL,
[BIGINTDATA1] BIGINT NULL,
[BIGINTDATA2] BIGINT NULL,
[GUID] UNIQUEIDENTIFIER NULL,
[INTEGERDATA2] INT NULL,
[OBJECTID2] BIGINT NULL,
[TYPE] INT NULL,
[OWNERID] INT NULL,
[PARENTNAME] NVARCHAR(256) NULL,
[ISSYSTEM] INT NULL,
[OFFSET] INT NULL,
[SOURCEDATABASEID] INT NULL,
[SQLHANDLE] IMAGE NULL,
[SESSIONLOGINNAME] NVARCHAR(256) NULL,
[PLANHANDLE] IMAGE NULL,
[GROUPID] INT NULL)
and my typical code to insert:
INSERT INTO #tmp
SELECT TE.name,
T.*
FROM ::fn_trace_gettable('c:\Data\MyTrace4.trc', default) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
Lowell
May 11, 2012 at 12:52 pm
Thanks Lowell !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply