Identify what traces are running
-
I have a SQL Server 2008 instance with a Database containing ONE table with the following structure:
CREATE TABLE [dbo].[Reads Writes](
[RowNumber] [int] IDENTITY(0,1) NOT NULL,
[EventClass] [int] NULL,
[ApplicationName] [nvarchar](128) NULL,
[ClientProcessID] [int] NULL,
[DatabaseID] [int] NULL,
[DatabaseName] [nvarchar](128) NULL,
[EventSequence] [bigint] NULL,
[HostName] [nvarchar](128) NULL,
[IsSystem] [int] NULL,
[LoginName] [nvarchar](128) NULL,
[LoginSid] [image] NULL,
[NTDomainName] [nvarchar](128) NULL,
[NTUserName] [nvarchar](128) NULL,
[RequestID] [int] NULL,
[SPID] [int] NULL,
[ServerName] [nvarchar](128) NULL,
[SessionLoginName] [nvarchar](128) NULL,
[StartTime] [datetime] NULL,
[TextData] [ntext] NULL,
[TransactionID] [bigint] NULL,
[XactSequence] [bigint] NULL,
[CPU] [int] NULL,
[Duration] [bigint] NULL,
[EndTime] [datetime] NULL,
[IntegerData] [int] NULL,
[IntegerData2] [int] NULL,
[LineNumber] [int] NULL,
[NestLevel] [int] NULL,
[Offset] [int] NULL,
[Reads] [bigint] NULL,
[RowCounts] [bigint] NULL,
[Writes] [bigint] NULL,
[BinaryData] [image] NULL,
[Error] [int] NULL,
[ObjectName] [nvarchar](128) NULL,
PRIMARY KEY CLUSTERED
(
[RowNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Judging by the column names in the table, I suspect someone started a trace that is writing to this table (the DB is getting huge at 18GB).
How do I identify and stop this trace??
-
first, select * from sys.traces will show you all currently running traces.
you'd typically see one trace, the default trace.
If you see a trace with a NULL path, it's being performed by an application, like SQL Profiler , spotlight for SQL server, or another app.
id status path max_size stop_time max_files is_rowset
1 1 Z:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_201.trc 20 NULL 5 0
2 1 NULL NULL NULL NULL 1
to stop or drop a trace, you need to know the trace id.
Replace the word TRACEID with 2 for example, to stop the trace #2
--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
Before you drop the trace, you might want to script it out to see what the hell it was doing;
i've a nice script here that will do that...
it's an easy call
EXEC sp_ScriptAnyTrace 2
for example, and gives you a very readable documented script as a result.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Select * from Sys.traces only shows the default trace : (
1 1 F:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_201.trc 20 NULL
Any other way I can identify what is populating this table with what appears to be trace data??
-
Jpotucek (12/3/2014)
Select * from Sys.traces only shows the default trace : (1 1 F:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_201.trc 20 NULL
Any other way I can identify what is populating this table with what appears to be trace data??
Is it continuously being loaded? It could be a job or process that is archiving the data from the default trace to it. Then there is the possibility that someone is occasionally running profiler and and sending to the table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thanks. some of the entries are recent.. some are very old. I think I'll truncate and see if it continues to populate. Thank you for your Support : )
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply