Post reply

Purging SQL Server logs

  • December 23, 2005 at 11:06 am

    #364491

    Hi,

    My current SQL Server log is huge due to an error that happened on a Friday night at 22:37 and kept logging until Monday when I got in.  It was a database that could not allocate space for an object (I still don't have all the alerts setup to notify me by page for these kinds of errors).  So now my current SQL Server log is so huge that it takes approx 10 mins just to open it.  Can I purge this log or remove the hundreds and hundres of lines that the error produced without bouncing the service to create a new current log?

    Thanks!

    Isabelle

    Thanks!
    Bea Isabelle

  • December 23, 2005 at 11:11 am

    #611347

    Backup your transaction log, this should purge the inactive area of your file.  Look up transaction log truncation and BACKUP LOG in BOL.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 23, 2005 at 11:24 am

    #611348

    Hi,

    I am new to SQL so I think I'm a little confused with your reply. What transaction log are you referring to?  I am familiar with backing up transaction logs for databases to free up space, but I don't have a specific database that has an issue with that.  I am talking about the SQL Server logs that I check every day for errors or warnings through Enterprise Manager.  It says in BOL that they are located in Program Files\Microsoft SQL Server\MSSQL\logs\Errorlog but I don't see one in our directory.  Are we talking about the same thing?  Sorry if I'm not clarifying the terms correctly.

    Thanks!

    Isabelle

    Thanks!
    Bea Isabelle

  • December 23, 2005 at 11:38 am

    #611349

    My bad.  We are talking about two different logs.  You are referring to the SQL Server error logs.  Look into sp_cycle_errorlog in BOL.  This is what you want.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 23, 2005 at 11:39 am

    #611350

    Hi,

    I found my Errorlog file and tried to modify it, but it doesn't take the changes.  I also saw in BOL that I can use sp_cycle_errorlog to archive the current log and create a new current one without having to restart the SQL Server.  Is that my only option?  It's not really that big of a deal to have the archived log as big as it is, just checking to see what my options are.

    Thanks,

    Isabelle

    Thanks!
    Bea Isabelle

  • December 23, 2005 at 11:43 am

    #611352

    I believe using sp_cycle_errorlog is your only option.  Windows will not let you open and remove the entries as long as SQL Server has a hold of the file.  After you use sp_cycle_errorlog, you should be able to open the file in a text editor and remove as much of it as you wish.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • December 23, 2005 at 11:46 am

    #611353

    Great!  Thanks for the info.

    Isabelle

    Thanks!
    Bea Isabelle

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy