x

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
more ▼

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

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(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][1]

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.

[1]: http://support.microsoft.com/kb/958006
more ▼

answered Mar 02, 2011 at 11:30 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(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

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x738
x87
x57
x13
x1

asked: Mar 02, 2011 at 11:09 PM

Seen: 2345 times

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