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


more ▼

asked Mar 02, 2011 at 11:09 PM in Default

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Mar 03, 2011 at 12:06 AM

avatar image

26.2k 18 38 48

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 02, 2011 at 11:30 PM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 03, 2011 at 12:47 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 03, 2011 at 01:44 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

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, 2011 at 01:58 AM Fatherjack ♦♦

That's a good thought, @Fatherjack.

Mar 03, 2011 at 02:22 AM ThomasRushton ♦♦

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

Mar 03, 2011 at 02:32 AM Fatherjack ♦♦

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

Mar 07, 2011 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, 2011 at 12:36 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 02, 2011 at 11:09 PM

Seen: 2661 times

Last Updated: Mar 02, 2011 at 11:09 PM

Copyright 2018 Redgate Software. Privacy Policy