Home > General, T-sql > set SQL error log retention period through T-SQL

set SQL error log retention period through T-SQL

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: