:::: MENU ::::

Tuesday, December 29, 2015

Memory is one of the important resources for any database management system and for any IT service in general and controlling this critical resource is one of any DBA major challenges.

Most of database management systems has a parameter or more to define the its share from the total amount of server installed memory, this maintain a hard limit to the DBMS that should not exceed and the rest should be used for the operating system itself “in case that there is no other software installed like web services for example and this–by default–the best practice for a production environment.”

This parameter is very useful for defining a hard limit for the memory the database case use and assign the rest for the OS operations in order to maintain the server performance but it is useless for measuring the amount of memory utilized, this is because the simple fact that this is a static value and in order to measure the memory and its peaks we need another figure to be considered which it the buffer pool utilization.

The buffer pool is the area inside SQL Server memory that used for hosting data and indexes pages for both reading and writing operations and represent about 99% of the total memory of SQL Server, by measuring the buffer pool consumption we can then measure the actual memory utilization for SQL Server instance.
 The below snapshot shows the difference between the SQL Server memory utilization “as a whole” and the buffer pool utilization.

















While the red line represent the memory utilization the blue one represents the buffer pool utilization, as we can see the total memory utilization is always the same because it is a static value, while the buffer pool utilization changes by the reading/writing operations activities on the system and that is why we find the utilization during some points reached the total assigned memory and sometimes else it decreased below.

 The next step will be how to collect the memory and buffer pool utilization figures and this will be explained deeply in the next post.

Sunday, December 13, 2015

This is one of the critical and bad situations that any DBA can face, to find yourself unable to start your SQL instance after disabling NT AUTHORITY login “for any reason”.

In my case it was disabled by the Network administrator guy by mistake and I did not realize that till restarting my SQL production instance for a planned maintenance operation!!

So, the situation is very critical that you find your production instance failed to be started with error message:
“Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled”.

The first positive point here is that the error message is clear enough and then we can start troubleshooting in the right track.

What we need to do in this situation is simply grant “sysadmin” permission for NT AUTHORITY system account again but the question is how can we connect to the instance while it is stopped?, the answer is to start it in single user mode and to do that we need to go through the following steps:

1- Open command prompt in administrator mode.

2- Navigate in SQL Server installation media till “Binn” folder.

3- Start SQL Server DB Engine service in single user mode using command “sqlservr.exe -s SQL2012 –m” while SQL2012 is the instance name, here is a complete example: 
“C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -s SQL2012 –m” 
while "–s  SQL2012" is the instance name and "–m" is the option for starting the instance in single user mode.

The next step then is to grant the NT AUTHORITY system user "sysadmin" permission and to do that we need to connect to the instance after starting it in single user mode via command prompt, the command to do that is as below:

SQL Authentication:
SQLCMD -S instance IP,port -U SQL account -P password
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
  
Windows Authentication:
SQLCMD -S instance IP,port –E
ALTER LOGIN [NT AUTHORITY\SYSTEM] ENABLE
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO

After that we can close the two opened command prompt and then restarting SQL Server instance in the normal mode.