Sp_Cycle_ErrorLog not working
-
Hi,
I have a problem with sp_cycle_errorlog not cycling the error logs properly (and they are currently approx 1.2Gb!). When I run it (viewing the files through explorer):
ERRORLOG.1 becomes ERRORLOG.2
ERRORLOG.2 becomes ERRORLOG.3
....etc
but ERRORLOG doesn't become ERRORLOG.1!
I was wondering if it is a permissions issue, but can't see one as SQLServer & SQLServerAgent both are running under an account that is a local admin.
I've tried DBCC ERRORLOG as well and that gives me the same result, tried googling as well but hasn't given me any leads, has anyone got any ideas?
SQL2K SP3 + MS03-31, Windows 2000 Adv. SP4
Thanks
Dan
-
Using this sqlagent job, it works fine over here. This job is scheduled on all our servers one a week.
As you can see I encountered a problem using the sp, but using dbcc it works fine via sqlagent !
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'DBA_Switch_SQLServer_Errorlogfile') > 0
PRINT N'The job "DBA_Switch_SQLServer_Errorlogfile" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DBA_Switch_SQLServer_Errorlogfile', @owner_login_name = N'sa', @description = N'Wekelijks nieuwe logs beginnen omdat deze soms zeer groot worden.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'SwitchSQLServerErrorlogfile', @command = N'-- switch sqlserver logfiles (errorlogs)
-- EXEC sp_cycle_errorlog (does not do the correct thing via SQLAgent)
DBCC errorlog', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Wekelijks', @enabled = 1, @freq_type = 8, @active_start_date = 20030214, @active_start_time = 0, @freq_interval = 2, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Nope, still have same problem using sp_cycle_errorlog or dbcc errorlog run as SQLAgent job or as sp.
Does anyone know what permission dbcc errrorlog needs at an OS level? Or what registry permissions it needs?
Cheers
Dan
-
May seem a bit strange, but what happens if you make a sqlagent-job and have it execute the sp/dbcc using OSQL with sa-login ?
e.g.
osql -Usa -Pdontleaveitblank -S yourserver -d master -Q "exec Sp_Cycle_ErrorLog " -o "C:\OSQL_Sp_Cycle_ErrorLog.txt"
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Still the same situation
Any other ideas?
Cheers
Dan
-
There has been a bug in sql7 where this osql using sa was a workaround.
now I'm out of ammo
...... 
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What account does the SQL services run under? Do the have permissions on the ...\MSSQL\LOG directory? Are you able to stop the SQL services and manually rename the file.
Maybe it's just a size thing. I've never come across a log file that is that big. I usually cycle out the logs automatically once they reach 500k. I find that if they're any bigger it can be a real pain to locate stuff quickly.
--------------------
Colt 45 - the original point and click interface
-
Another server here has the same problem, but was rebooted at the weekend. Its log file is now 10mb, but the problem is still there so don't think it is a file size issue.
SQLServer runs under a domain acc. with local admin privaleges. The directory with the errorlog file in it has full control given to the account that SQLServer runs under. If I log onto the server as the account that SQLServer runs under I can copy the errorlog file, and rename the copy as errorlog.1 - so I don't think it can be file system permissions. I can't stop the services & rename the file as both boxes are prod.
Does anyone have any detailed info into how dbcc errorlog works? Maybe any registry settings it looks at, that kind of thing?
This is turning into a right PITA!
Cheers
Dan
-
I have the same problem and could not fine a solution yet.
-
anyone know a fix for this? i've got a 4G error log that wont recycle 🙁
-
what does executing xp_enumerrorlogs in master return ?
-
So exec Sp_Cycle_ErrorLog or dbcc errrorlog do not work using Query analyser.
Did you also perform a checkpoint using Query analyser ?
If none of the above work, I guess you're stuck with at stop and restart of the sqlserver instance.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
it returns the archive number, date, log file size
archive number = 0
date = about 1 min ago
log file size = 4G
-
nope, neither exec Sp_Cycle_ErrorLog or dbcc errorlog works before or after a checkpoint. cant restart the service as its prod. damn!!!!!!
-
xp_enumerrorlogs returns just the one row ? there should be a row for each errorlog file. Is the max number of errorlogs set to 1 somehow ?
(it's meant to be between 6 and 99).
sounds a bit of a strange problem ..
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply