22 GB of log created in SQL Server

0

hi team,
i use sql server 2005 tobay i can see sudden disk space issue, and found that 22 GB of error log is created, and now i had execute sp_cycle_errorlog to generate new log but not working,
please suggest me a way other than Restart

If you cannot cycle the error log then I would guess the drive is completely full. If that is the case then I would look to move/compress other data to give you a few MB free to cycle the error log and then you can remove/compress/investigate the huge log.

If you're not a fan of stopping/restarting your SQL Server (or, more likely, not allowed to...), then you might want to consider setting up a scheduled task to call sp_cycle_errorlog regularly, say once a week. That way you get a bit more more control over when you can archive off the old error logs. There are even blog posts out there that show you how to apply a bit more intelligence, and only cycle the error logs if they are above a certain size.

You also might need to think about cycling the Agent's log too - the SP to run here is sp_cycle_agent_errorlog.

personally I cycle mine every night. I then have a handy dandy way of finding things in the log that happened on 'such and such a date'. Also anything older than x days gets deleted so I know it wont get out of hand.