June 17, 2009 at 8:13 am
SQL Server 2005
SBS 2003 R2
A database vendor installed and configured SQL Server 2005 on this server to host their database. I have been getting error messages that teh C:\ drive is low on space. A bit of investigation reveealed that there are over 4,000 files, named like this:
master.mdf..2009.02.12.10.34.57_562.trc
This is choking my system volume, and a bit more of this will result in a dead server. The database vendor isn't responding as quickly as I'd like, and I want to save this server. I am not the sharpest knife in the drawer when it comes to SQL Server, and I have learned that there is nothing you can do with it unless you have a LOT of knowledge - Google will teach you enough to get into trouble, but not nearly enough to get out of it.
I did learn that the SQL Profiler isn't on the server (anywhere I can find it anyway), but there is a program (3rd party, I suspect) called AnjLab.SQLProfiler. When I open it (hoping to find a trace configuration I can study and perhaps change the retention time) there is nothing.
Anyway, the question is simply this: can I delete some of those old trace files without blowing up SQL Server, the master.mdb or my cleint's business?
June 17, 2009 at 8:38 am
What do you get if you run this query in Management Studio on that server?
select * from fn_trace_getinfo(0)
That will give you data on active traces.
- 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
June 17, 2009 at 8:47 am
sp_stop_trace as well to shut it down.
I'd look if this is in the startup folder, or perhaps its' a service. You can disable it and then figure out if you need it later.
June 17, 2009 at 9:02 am
Thanks for the quick response. Here is the output from your query:
1,1,2
1,2,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_30.trc
1,3,20
1,4,NULL
1,5,1
2,1,2
2,2,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf..2009.02.12.10.32.51.trc
2,3,5
2,4,NULL
2,5,0
3,1,2
3,2,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf..2009.02.12.10.34.57_5197.trc
3,3,5
3,4,NULL
3,5,1
The file referred to in traceid 2 (the first column) is a single 1K file - apparently that trace was only run the one time. The file referred to as 3,2,C:\Program Files\...\master.mdf..2009.02.12.10.34.57_5197.trc is the current file in a list of about 4600 files, each 5,120kb in size - a total of over 22GB.
Another contributor has suggested I can stop the trace, using a sp_stop_trace command.
I can't believe all this old data is required, and I am happy to delete about 3,000 of these, as long as I dont render the whole thing useless by doing so.
June 17, 2009 at 9:08 am
I don't see anything in the startup folder. When you say "maybe it's a service" are you referring to a Windows service (services.msc) or is this also a SQL Server Mgmt Studio term?
Since I don't know the purpose of the trace, I am not sure if stopping it is wise. However, if this isn't required for everything to run (maybe something someone enabled for troubleshooting and forgot to turn it off), then I will definitely disable it.
Hopefully, however, I can find out where it has been set up, so I can be sure it won't start again when the server is rebooted next time.
Thanks for your response - it's appreciated.
June 17, 2009 at 9:36 am
Well, for now I have moved about half of those trace files over to the data volume - there's lots of space there. However, if running this trace isn't required for the health of the server (e.g. accidentally left on by a prior tech), I would like to disable it.
sp_stop_trace isn't in the SQL Books Online (sp_stop_job is the closest I can find), nor can I find its syntax using good old Google.
Where could I find the sytax for it?
June 17, 2009 at 10:05 am
Sorry, sp_trace_setstatus
http://msdn.microsoft.com/en-us/library/ms176034(SQL.90).aspx
Something must be starting it.
June 17, 2009 at 12:00 pm
Trace ID 3 is the problem, as you found. It's set to not "roll over files", so it's going to just keep adding them forever.
You can stop the trace, using sp_trace_setstatus, but if it's set to restart automatically when the server starts up, it'll start again.
First, take a look at sp_trace_create in BOL. You'll need to understand what you're looking at. The key is the input parameter "@filecount".
Second, run this:
select * from master.sys.sql_modules where definition like '%sp_trace_create%';
You're looking for a proc that starts a trace when the server starts up. Check out sp_procoption in BOL for details on how that can be set up. If you don't find one, then something else may be starting it, or it may not be auto-starting at all.
What you're trying to do is find what's starting the trace, so you can stop it and then re-create it with the @filecount option set to something that allows you to keep a useful amount of data, but which will clean up after itself so it doesn't clog up the hard drive.
Does that help?
- 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
June 17, 2009 at 12:11 pm
Yes, it helps a lot, thanks...
What I did was user the sp_trace_setstatus command to stop it (I didn't delete it). I then moved all those trace files over to the other volume, where there is lots of space, in case someone wanted to look at it. I'm thinking that someone set the trace up to troubleshoot something, and forgot to disable it.
You're right, however - this may start again when the server is rebooted. I'm going to monitor this for a while, maybe even reboot the server at night and see what happens, before I look into it further.
I sure do appreciate the assistance: your voluntary help this morning helped me to avert a crisis that could have been pretty severe. The database vendor, on the other hand, in spite of having been paid handsomely for their work putting all this in, hasn't responded to a message I left before posting here...
I am always amazed at how incredibly complicated SQL server is. As I mentioned, it's a lot easier to learn how to get into trouble with this product than it is to figure out how to get out of it.
Thanks again to both of you. You've been very helpful.
June 17, 2009 at 12:33 pm
You're welcome. Glad we could help.
You don't need to reboot. Just restart the SQL service. You can do that from Management Studio, and it's a lot faster than rebooting.
- 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
July 14, 2011 at 3:41 pm
Just wondering how this all turned out... Today for myself this exact issue happened for me as well. It turned out I tracked it down to an internal employee who had went ahead and download a trace program, not knowing what he was doing he ran the trace and it did the exact same thing as what was described in the above posts... 5MB *.trc files....
Just my 2 cents hopefully no one comes across this problem but this person will not be doing that again....:exclamation:
November 18, 2014 at 8:15 am
Same issue here. What are some tools/commands I can use to see what's starting the trace everytime the service/server restarts? I looked in the SQL Server -> SQL Server Agent ->Jobs list, but didn't find anything there. Where else can I look?
Thanks!
November 18, 2014 at 8:28 am
Stored procedures marked to run on startup:
SELECT [name] FROM master.dbo.sysobjects WHERE type = 'P' AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 18, 2014 at 9:07 am
Awesome fast response, thank you! Unfortunately that command returns 0 results for me. Any other ways of checking? I poked around the query a little, tried removing things, and got a lot of results when removing the "type" and "OBEJCTPROPERTY" clauses, but when I add the OBJECTPROPERTY(...) clause, the results become 0. Since I'm not entirely sure what the OBJECTPROPERTY function does (until I google it right now), are there other ways of querying what stored procedures start at startup?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply