SQL Server Logs

Question

Post reply

SQL Server Logs

diogenes1331

Default port

Points: 1420
More actions
December 7, 2005 at 6:50 am

#111105

SQL Server 2000 SP3a.

Lately when I am trying to look at the most recent SQL Server jobs via Enteprise Manager it actually takes several minutes before it allows me to see the Logs. I have checked for blocked process in the sysproccess table and I see no blockages.

Any suggestions on what else I should check?

Thanks for your time.

Viewing 6 posts - 1 through 5 (of 5 total)

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

ramses2nd Hall of Fame Points: 3720 More actions · Answer 1

Are you trying to view the actual logs under Management...SQL Server Logs? If so these files can get very large and thus take a long time to open. If you look to the far left of the log name you can see the size of the file and should be able to get an idea if the log is overly large.

If you are actually looking at the jobs and their job history, you may want to look at the settings for how much historical data is kept. you can do this by right clicking SQL Server Agent, choosing properties, and selecting the Job System Tab. Here you can see if the job history is limited and if so, how many records are stored for the individual jobs and overall log. If these numbers are high then it will impact how long it takes to gather the data for viewing.

diogenes1331 Default port Points: 1420 More actions · Answer 2

diogenes1331

Default port

Points: 1420

December 8, 2005 at 7:30 am

#608408

Thank you very much for the answer.

Yes, I am trying to look at the SQL Logs under Management. The files maybe too big and at the moment the server is being hit quite severely.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Recycling SQL Server errorlogs to keep them a managable size may help. The basic command is DBCC ERRORLOG. There are scripts posted that check the size and roll it over based on that. I just find it easier to schedule a JOB once a week (on my busiest servers the size rarely gets to 500 Mb). If you do this (roll the errorlogs) you'll probably want to increase the number of errorlogs that SQL Server keeps around. This is done in EM by right click on SQL Error Logs and selecting properties. Then you just modify the default value of 6 to whatever you like (I keep 20).

Another method is just using plain old notepad to view the active log. I suggest doing it on the server itself so as to remove the network latency/data transfer time from the equation. Don't try WordPad, it says the file is locked.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."