Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9434
  • Last Modified:

Stop SQL 2005 Trace

I am attempting to kill a rouge Trace in SQL 2005 without success!

I have attempted:
exec sp_trace_setstatus 1,0

With the result of:
You do not have permission to run 'SP_TRACE_SETSTATUS'

I have tried executing this as an admin on the server and as "SA" with the same results.
0
MarcGraff
Asked:
MarcGraff
  • 6
  • 5
1 Solution
 
MrRobotCommented:
hi MarcGraff,

sounds like the default trace. take a look at this one,

http://www.mssqltips.com/tip.asp?tip=1111

0
 
MrRobotCommented:
also, take a look at the activity monitor in the management section in management studio, and disable any active connections related with the running trace.
0
 
MarcGraffAuthor Commented:
I believe there are 2 traces running.


Results of SELECT * FROM fn_trace_getinfo(default);
1      1      6
1      2      \\?\E:\SQL_Data\MSSQL.1\MSSQL\DATA\audittrace20071009212539.trc
1      3      200
1      4      NULL
1      5      1
2      1      2
2      2      E:\SQL_Data\MSSQL.1\MSSQL\LOG\log_28.trc
2      3      20
2      4      NULL
2      5      1

When I run:
exec sp_trace_setstatus 2, 0

I get:
Msg 19070, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
The default trace cannot be stopped or modified. Use SP_CONFIGURE to turn it off.

But when I run:
exec sp_trace_setstatus 1, 0

I get:
Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 1
You do not have permission to run 'SP_TRACE_SETSTATUS'.

The trace with the ID of 1 is what I need to stop. Any suggestions?

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
MarcGraffAuthor Commented:
In the Job Activity Monitor the only things listed is the DB backups.
0
 
MrRobotCommented:
have you checked the activity monitor to see if there is an active connection requested the trace?
0
 
MarcGraffAuthor Commented:
Under Management Activity Monitor I do not see anything related to a Trace.
0
 
MarcGraffAuthor Commented:
When I changed my options to show system processes I now see one item with a COMMAND of TRACE QUEUE TASK but I cannot kill a system process.
0
 
MrRobotCommented:
that's the default trace task and is ok. also the error message is strange somewhat, though it may be a wrong error message, which is usual in sql server 2005.

there may be something wrong with your login and permissions, wrong mappings or something. someone needs the alter trace permission to change a trace status, except sa. you can't give rights to yourself, so try to create a new user, run the command "grant alter trace to myUser", login with that user and try to stop the trace again.

you can also check to see if you can run a trace with your current login.
0
 
MarcGraffAuthor Commented:
I have ended the default trace with:
execute sp_configure 'show advanced options', 1
reconfigure with override

execute sp_configure 'default trace enabled', 0
reconfigure with override

execute sp_configure 'show advanced options', 0
reconfigure with override

Now the results of SELECT * FROM fn_trace_getinfo(default);
1      1      6
1      2      \\?\E:\SQL_Data\MSSQL.1\MSSQL\DATA\audittrace20071009212539.trc
1      3      200
1      4      NULL
1      5      1

I am now working on creating a new trace and then distroying it.
0
 
MarcGraffAuthor Commented:
I have successfully created and destroyed a new trace. This should eliminate the permissions possibility correct?

Any other thoughts?
0
 
MrRobotCommented:
ah.. audittrace.. haven't noticed it the previous time you pasted. it's the c2 audit mode, you can turn it off using options again, it's off by default, so somebody must have turned it on before.

C2 auditing records information that goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements. The audit information contains the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user's application, and Server process id of the user's connection.

Audit logs are stored in the Program Files\Microsoft SQL Server\Data\ folder as AuditTrace_yyyymmddhhmmss.trc, where the second part of the name indicates date and time when the log file was created. Size of a log is limited to 200MB, but new ones are generated automatically whenever the old one is full as long as there is available disk space. Otherwise, shutdown of SQL Server is initiated. Ensure that you have sufficient space on your hard drive, as the volume of recorded information is significant. In emergency situations, where no space can be immediately freed for new log files, you can restart SQL Server with the -f flag, which will disregard auditing settings.

To enable C2 auditing, use sp_configure stored procedure with the 'c2 audit mode' parameter. Assigning it a value of 1 enables auditing, 0 reverts it back to the default. Since this is an advanced option, you will need to turn on the "show advanced options" setting. In addition, changing this setting requires a restart of the SQL Server. Permissions to perform these steps are limited to members of sysadmin fixed server role. The following sequence of T-SQL commands can be executed from the SQL Query Analyzer in order to enable C2 auditing (you will need to restart the SQL Server afterwards):

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE

EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE

since it is already running, you probably have the advanced options on, so you may just try to set "c2 audit mode" off and restart.

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now