22 GB of log created in SQL Server

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

asked Mar 02 '11 at 11:09 PM in Default

ramesh 1
1.7k ● 46 ● 63 ● 68

(comments are locked)

4 answers:

You could also stop sql server, move the error log off the disk that is full and restart sql server. You can then investigate the reason for such a big errorlog file.

answered Mar 03 '11 at 12:06 AM

WilliamD
25.3k ● 16 ● 18 ● 41

(comments are locked)

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.

answered Mar 02 '11 at 11:30 PM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

(comments are locked)

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.

answered Mar 03 '11 at 12:47 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

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.

answered Mar 03 '11 at 01:44 AM

ThomasRushton ♦
29.1k ● 6 ● 9 ● 36

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.

Mar 03 '11 at 01:58 AM Fatherjack ♦♦

That's a good thought, @Fatherjack.

Mar 03 '11 at 02:22 AM ThomasRushton ♦

ah, sometimes you know, I get a flash of inspiration and BAM! awesome occurs!

Mar 03 '11 at 02:32 AM Fatherjack ♦♦

sp_cycle_errorlog did not work only answer left with me was RESTART hahah

Mar 07 '11 at 10:55 PM ramesh 1

Hmm. Was looking into dropping in the sp_cycle_agent_errorlog bit into my SQL Servers, and found that it's only in SQL Server 2005 onwards. sigh

Mar 08 '11 at 12:36 AM ThomasRushton ♦

(comments are locked)