December 2, 2009 at 3:40 pm
SS2005, Service Pack 2 is installed.
I started a Profiler trace that I cannot stop. When I created the trace, I did not enter a stop by date/time, just closed Profiler, thinking I could view the trace later & stop it.
I am logged in as the "sa" user. This user was renamed to "sysdba" because of company requirements, which I mention on the off-chance it is relevant. I verified I have "sysadmin" server role assigned.
Output from this command: select * from sys.traces
11\\?\<drive>:\<file path>\audittrace20091120162811.trc200NULL001102128204341248NULL2009-11-20 16:28:11.9002009-12-02 16:00:07.86074827NULL
Output from this command: select * from ::fn_trace_getinfo(NULL)
116
12\\?\<drive>:\<file path>\audittrace20091120162811.trc
13200
14NULL
151
When I run this command:
exec sp_trace_setstatus @traceid = 1, @status = 0
This error results:
Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 1
You do not have permission to run 'SP_TRACE_SETSTATUS'.
I had an Administrator log onto the server, then open SSMS using Windows-authentication, to run grant execute on SP_TRACE_SETSTATUS to the 'sa' user, now named 'sysdba'. This is the error that resulted:
Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'sysdba', because it does not exist or you do not have permission.
Stopping all SQL Server services didn't stop the trace. Restarting the server didn't either. Web-searching has failed me, no one else seems to have this same problem. I'm not able to get Microsoft support. Suggestions?
December 2, 2009 at 3:52 pm
Also, I had the Administrator try to alter the trace status, using the "exec sp_trace_setstatus" command, and the Adminstrator got the same "you don't have permission" error message. This was done while logged in via Windows authentication.
December 3, 2009 at 7:04 am
Um, restarting the server would definately stop the trace unless you put it into a boot script that fires when SQL Server starts up.
Plus, that trace, that's the black box trace. It's supposed to be there. By default it only keeps 20mb for each file and only five files. It provides a way to see what's been happening on your server over a short period of time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2009 at 8:05 am
Thanks for the reply, Grant. I thought the same thing (restarting would stop the trace), which is why I did the restart. It didn't. I found that if the SQL Server services are running, attempts to delete the trace file via Explorer failed, says the file is in use by another process. If I stop the services, I can delete the file. Which then reappears when the services are restarted.
I don't know what a black box trace is, but I question that it should be there for these reasons: I don't have a similar trace running on any other SQL Server databases, C2 is disabled, and I found the problem because I found 20+ of the files after the Thanksgiving holiday (which means it's not keeping just the five). I reran the "select * from ::fn_trace_getinfo(null)" command to view all log files, these were the results:
116
12\\?\<drive>:\<path>\audittrace20091120162811.trc
13200
14NULL
151
212
22<drive>:\<path>\system\MSSQL.2\MSSQL\LOG\log_46.trc
2320
24NULL
251
I believe the second trace, log_46, is the normal system logging.
December 3, 2009 at 8:10 am
The second trace is the SQL 2005 default. Trace ID 1 in your case is not the default.
This means that there's something restarting the trace when SQL Server service is re-started. Look in the master database for a stored procedure that starts it. If not there, look for a script being run when SQL is started up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 3, 2009 at 8:20 am
If restarting the service doesn't clear the trace, then you've got something running within SQLAgent or as a startup script that refires it. If SQL Server is shut down, so are each and every trace. Has to be, no options there.
First thing I'd do is search the server to see if you have procedures or agent jobs that are starting traces. Then, Disable the job or check the procedures to see if they're set to run automatically when the server restarts, sp_procoption, and disable them. If you can't find anything like that, then it's possible that a client machine is firing off the command.
So in that regard, I'd restart the server and then check the default trace to see what user, service or app is creating the other trace.
It does sound like the sysdba login is not properly mapped to the sysadmin role. See if you can map a different login to that role and then try using that to kill the trace or fix the sysdba login.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 3, 2009 at 4:02 pm
Thanks to all who replied, problem is resolved! In my description, I said C2 logging was not enabled. Turns out it was. As far as the permission problem (inability to execute sp_trace_setstatus), I figure the database is operating as expected, so I'm not pursuing that further.
Please accept my apologies for wasting your time. And my appreciation for the suggestions. I learned today.
December 4, 2009 at 6:48 am
That makes sense.
No appology needed. Sometimes it takes a second look to find things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 8:06 am
angela.morten (12/3/2009)
Thanks to all who replied, problem is resolved! In my description, I said C2 logging was not enabled. Turns out it was. As far as the permission problem (inability to execute sp_trace_setstatus), I figure the database is operating as expected, so I'm not pursuing that further.Please accept my apologies for wasting your time. And my appreciation for the suggestions. I learned today.
As Gus said. That's not an issue. I had read the thread yesterday and didn't have anything to add, but then I re-looked at your original post today and copied the result from sys.traces into SSMS under my own query from sys.traces to get the column headers and noticed 2 things that help determine the trace type:
1. is_rowset = 0. That means it is most likely (99%) not a trace created by the Profiler GUI, but a server-side trace.
2. is_shutdown = 1. Normally will mean it is either a C2 or Common Criteria audit trace. You can create a trace that will shutdown the server if it can't write to the file, but it is unlikely.
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
December 4, 2009 at 9:11 am
Jack, thanks for the additional information.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply