December 23, 2005 at 11:06 am
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
Backup your transaction log, this should purge the inactive area of your file. Look up transaction log truncation and BACKUP LOG in BOL.
December 23, 2005 at 11:24 am
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
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.
December 23, 2005 at 11:39 am
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
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.
December 23, 2005 at 11:46 am
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
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