Archive

Archive for April 18, 2013

set SQL error log retention period through T-SQL

April 18, 2013 Leave a comment

we all may need to tweak the SQL error log retention period on a single instance or list of SQL server VIA CMS (central management server)

below 2 commands are quite useful for achieving the same

CentralManagementServer

READ the current setting of sql error log retention 

/* USE [master] GO EXEC xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’ GO */

you may receive below error on few server .. you may want to  go ahead with the update command and come back to see the results of xp_instance_regread

possible error message for some server ..

RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’ servername(username): Msg 22001, Level 1, State 1

set  the LOG retention sql error to 20 days 

/* USE [master] GO EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’NumErrorLogs’, REG_DWORD, 20 GO */

above command will set the retention to 20 days and you may rerun EXEC xp_instance_regread to confirm the same setting

Advertisements