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 DBCC ERRORLOG ALSO NO USE
Do you have any idea why the ERRORLOG is growing that big? Are you auditing logins to the ERRORLOG? There's also a case when you use SqlDependency object for handling query notifications in a .NET app that might cause the ERRORLOG to grow heavily. **[
http://support.microsoft.com/kb/958006]** You say that sp\_cycle\_errorlog is not working. Is it giving any error messages, or simply not doing anything? If you have totally run out of disk space due to the big ERRORLOG file, you might have to delete/move/compress some files to gain enough space to create a new ERRORLOG file. :
can you read the log file to see what is in there? 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.
How long has the server taken to generate 22GB of ErrorLog? 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`.