Log file is filling up

Question

Log file is filling up

Viewing 8 posts - 46 through 52 (of 52 total)

You must be logged in to reply to this topic. Login to reply

LearningDBA SSCertifiable Points: 6593 More actions · Answer 1

Steve Jones - SSC Editor (3/28/2014)
Trace is easy. Start Profiler, pick the options you want and then save as a trace script file. Then execute that on the server to run trace.

Is there any way to automate the process (I mean to run the trace at 10 PM every night)? I don't know when it will run again unless I have my laptop open at 10 PM every night.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

LearningDBA SSCertifiable Points: 6593 More actions · Answer 2

Steve Jones - SSC Editor (3/28/2014)
Trace is easy. Start Profiler, pick the options you want and then save as a trace script file. Then execute that on the server to run trace.

Actually, I just came across this detailed article on Server Side Trace written by Gail Shaw, which explains pretty much everything.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I wrote an article on server-side trace?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LearningDBA SSCertifiable Points: 6593 More actions · Answer 4

I think it was more of an article on performance.

"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

humbleDBA Hall of Fame Points: 3490 More actions · Answer 5

Steve Jones - SSC Editor (3/28/2014)
Trace is easy. Start Profiler, pick the options you want and then save as a trace script file. Then execute that on the server to run trace.

Whilst this sounds great, there are some big caveats with this. First off, the OP has said that the TLOG grows from 127MB to roughly 30GB within an hour. Secondly, running Profiler Traces can put great pressure on a server with a lot of work happening on it. It can also create a rather large trace file and that is then another issue, and finally, running the Profiler GUI trace is not guaranteed to capture all work; this is by design to try and lessen the detrimental impact of the trace and to ensure that the monitored server is not overwhelmed.

These are the one's I can think of off the top of my head. The fact is, if this is a production system you want to be very careful when running traces on busy systems and, IMHO, you should only run server-side traces. I have run heavy server-side traces of very busy systems generating 50GB of trace files in just 10 minutes with minimal impact to the monitored system, even though the the IT manager was very nervous becuase, previously, another DBA had run a Profiler GUI trace bringing the server and instance down.

Edit: corrected spelling mistakes :Whistling:

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Steve isn't suggesting running the traces via the GUI. He's suggesting using the GUI to create a server-side trace script and then running that. It's easier than trying to write one for yourself.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

humbleDBA Hall of Fame Points: 3490 More actions · Answer 7

GilaMonster (4/1/2014)
Steve isn't suggesting running the traces via the GUI. He's suggesting using the GUI to create a server-side trace script and then running that. It's easier than trying to write one for yourself.

Having re-read Steve's comment, yes, I think you're right Gail ...thanks for pointing out my mistake. Sorry Steve :blush:

Though I still hope I've put some pointers foward that may be useful, no? :ermm:

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 8

humbleDBA (4/1/2014)
GilaMonster (4/1/2014)
Steve isn't suggesting running the traces via the GUI. He's suggesting using the GUI to create a server-side trace script and then running that. It's easier than trying to write one for yourself.
Having re-read Steve's comment, yes, I think you're right Gail ...thanks for pointing out my mistake. Sorry Steve :blush:
Though I still hope I've put some pointers foward that may be useful, no? :ermm:

No apology needed, and good points. Running a trace doesn't necessarily need to be a long event. You might capture enough 10 minutes to pinpoint issues.