How to increase the number of SQL Server logs

In this article I would like to demonstrate how we can increase the number of logs in SQL Server for the engine. The SQL Server error log contains events and errors related to SQL Server engine and services. You can use this error log to troubleshoot problems related to SQL Server. The default value for the maximum number of log files is 6. However we can increase that. The maximum number of logs can be up to 99.

Let us now see how we can increase the number of SQL Server logs via SSMS:
Step 1: Connect to the SQL Server where you want to perform this
Step 2: Expand the management folder
Step 3: Right Click on SQL Server Logs
Step 4: Click on Configure
Step 5: When you click on configure the ‘Configure SQL Server Error Logs’  box opens. Change the Maximum number of error logs to the desired number.
Step 6: Click on OK.
We can also run the below query to attain the same:

1 2 3 4 5 6 7

USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 14 GO

Related articles

How to Recycle SQL Server Agent Error Logs

The SQL Server Agent Error Log is a log maintained by SQL Server to record all error messages related to SQL server agent. This record is maintained since the last time the log was initialized or the agent was restarted.
In a highly available OLTP environment the SQL Server runs for a long span of time without restarts. This may cause the logs to to grow by a huge margin and it might be difficult to open the log while troubleshooting an issue.

To counter this situation we can do the following:

1) Run the following SP on a regular basis:

1 2 3 4

USE msdb go EXEC dbo.sp_cycle_agent_errorlog go

This will re-initialize the current log and start a new log with the current date time stamp

2) We can also use the ssms to accomplish the same:
a) Right click on the Error logs:

b) Click on OK

3) The third way is to create a job and invoke the mentioned stored procedure in step 1 and schedule it on weekly basis.

Implementing user-defined Server Roles in SQL Server 2012

In SQL Server 2012 you can now create an user-defined server role and configure server level permissions for it. In previous versions this was not possible. If we had to delegate someone with administrative tasks we had no choice but to assign more rights and access than required. With SQL Server 2012, user-defined server roles can be created and configured with specific permissions for specific set of DBA’s.

Let us understand with an example how we can create an user defined server role.
Step 1: Right click on Server roles and select ‘New Server Role‘
Step 2-> As the dialog box opens, type in a server role name -> set the owner to a preferred login. In our case we would choose sa.
Step 3 -> Choose an option\s from Securables window. In our case we chose Servers. Under servers you will find the name of the server. Select the option. Below in the permissions window select the following as shown in the snapshot.
Step 4 -> Click on OK. You will find the new server role under the server roles in SSMS
Step 5 -> Now let us add a login to this new role. Right click on the ServerRole1 -> Click on Properties -> On the members tab click on Add
Step 6 -> Add a login that you want a to give membership to this role. Click on OK.
So now you have successfully given a particular login few administrative rights that is required rather than granting it a privilege like sysadmin.However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables. Below is the script for the entire action we did.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

USE [master] GO CREATE SERVER ROLE [ServerRole1] AUTHORIZATION [sa] GO use [master] GO GRANT ALTER SERVER STATE TO [ServerRole1] GO use [master] GO GRANT ALTER TRACE TO [ServerRole1] GO use [master] GO GRANT CONNECT SQL TO [ServerRole1] GO ALTER SERVER ROLE [ServerRole1] ADD MEMBER [testdb] GO