February 12, 2008 at 12:13 pm
I tried to increase the error log value from 6 to great than 99. I did this by adding a registry entry however it does not seem to work. I tested this by stopping the server and restarting it, forcing it to hopefully add an additional error log to the 6 that were already there, but nothing happened. How can I verify that the registry addition took effect, as Enterprise Manager won't give me the correct error log value.
February 12, 2008 at 12:47 pm
Cycle the error log:
EXEC sp_cycle_errorlog;
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 12, 2008 at 1:07 pm
I did and got this message:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
it doesn't give me the info I need....
February 12, 2008 at 1:10 pm
Why on earth would you need more than 99 error logs? Actually, why would you need more than ten?
The thing I suggested would just show you if you had more than 6 log files available.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 12, 2008 at 1:20 pm
I guess it didn't show me that, that means my registry change did not take effect...now I need to figure out somehow why it didn't.....yeah tell me about it. It wasn't my idea that there needs to be more than 99!! I won't even tell you how many they want me to set it to...it is ridiculous!
February 12, 2008 at 1:23 pm
Check the logs. If you had six, you should have seven now. Cycling the error logs simply stops the logging in one log and starts it in the next, new log.
It sure sounds like you need to push back on that requirement. I'd be hard pressed to understand why they need to keep that many logs around. Even if they did, why not simply back up the inactive logs to some other location?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 12, 2008 at 1:27 pm
You don't need to hack the registry to do this in SQL2005.
Open up SSMS, management, right-click on SQL Server Logs, pick Configure. Set it there.
You CANNOT set it to more than 99. That's a hard limit (per BOL).
The cycle isn't going to show you anything, but if it did work you'd be able to look under SQL Server logs and see Current + 1-x where x is anywhere between 6 and 99.
And yes - since you're in SSMS, unless you hit "refresh", you might not see that you have a new log #.
http://msdn2.microsoft.com/en-us/library/ms177285.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 1:28 pm
I am in the process of convincing them of this (backing up old ones) but they want to consult MS, so I said in the meanwhile I'd do my research. The backing up of old log files could be automated, right?
Ok, so I looked at the logs and there are 7, current and then the ones number 1 through 6...I guess it worked, right?
February 12, 2008 at 1:29 pm
If they want to pay 250$ for microsoft to tell them what the documentation already states, that's certainly their prerogative. Otherwise - show them the article above.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 1:29 pm
SSCrazy,
You do have to do it in the registry if you want more than 99...can't do it in Enterprise Mgr.
February 12, 2008 at 1:37 pm
And having just tried it - the minute it cycles, the number>100 resets to 99. So - you're still stuck.
That's from within SSMS. If you're talking 2000 then you might be right, but you're on the wrong forum.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 1:42 pm
I did run EXEC sp_cycle_errorlog; again so that it would force a new error log but it still shows the active one and then logs numbered 1 through 6 (total of 7).
February 12, 2008 at 1:44 pm
I'm on SQL Server 2005, the solution for greater than 99 is to edit the registry (MS solution)...I'm not understanding your comment reg. recycling and it set back to 99...how when where? Some process would have modified the registry entry for that to happen, right?
February 12, 2008 at 1:56 pm
From what I can tell - it completely overlooks it. Perhaps once you touch the setting in SSMS, etc... I've got it set @ 250 in the registry: cycle the thing 150 times. Result - 100 logs. Cycle another 150 times. Still 100 logs. Undo everything done in SSMS, and keep the registry setting. Cycle again 150 times. Still 100 logs.
Again - it may be because I change the count in the UI first.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 12, 2008 at 2:10 pm
yeah, i can confirm that it is ignoring the registry stuff. Ok - hopefully MS will have an answer to all of this.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply