SQL Server Logs

Question

SQL Server Logs

gjwg

SSC-Addicted

Points: 444
More actions
February 28, 2005 at 9:11 am

#92465

Wondered if you could help...
We have a cluster SQL server and have noticed to our horror that the Current SQL Server Log has clocked up over 14Gb's. In comparison, the maximum Archive log is 5.7Gb.
The question we would like to raise is 'Is there anyway of controlling the size of the log file'?
It just takes an age to view this size of log...

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

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

You can periodically cycle the logs without rebooting. sp_cycle_errorlog or something like that will work. I had a server that never went down and we had the same issue, so we cycled them every week.

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

To cut directly to the chase:

use master

go

dbcc errorlog

go

checkpoint

go

It works well as a scheduled task.

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

gjwg SSC-Addicted Points: 444 More actions · Answer 3

Thanks rudy

Brilliant - I'll shall apply a scheduled task to all our SQL Servers on a period base

philcart SSC-Forever Points: 47795 More actions · Answer 4

We've got something along the lines of what Steve is suggesting. However, the stored proc that does this renames the log file so it doesn't get automatically removed.

--------------------
Colt 45 - the original point and click interface