Friday, 16 June 2017

Common SQL Server DBA Issues

SQL Server DBA Issues Handbook

In day to day activities we as DBAs receive lot of issues which can be resolved by following so simple steps. We will discuss some common SQL Server DBA Issues below-

Disk space full

Overview: Sufficient space in the disk is very crucial for all the databases to grow and perform transactions smoothly. Over the time, the database grows as a result in the growth of the data. Effective Capacity Planning should be done to overcome such challenges. Many a times log of the database grows due to some open transactions/log running queries and for such operations separate necessary actions should be taken to free up the disk space. Often storage gets full due to unwanted files on the disk which should be identified appropriately.

Actions to be taken:

1. Check for any unwanted files in the location for which the alert has been generated (e.g. C: ,D: etc.).
2. The majority of the issues related to disk space are because of log file growth. The growth typically occurs for one of two reasons:
  • The database is in FULL recovery and transaction log backups aren’t being taken for some reason
  • Someone runs a single, very large transaction which consumes all existing log space, forcing the file to grow large.
In either case Auto growth and pre-sizing (limitations on log growth) should be set accordingly.

Database is offline

Overview: Database being offline is one of the most common SQL DBA issues. In case any of the Database goes down or the status changes to OFFLINE then it is important for us to know the reason why the database has gone offline. SQL Server error log should be read thoroughly to know the reason.

Action to be taken:

Please check the SQL server error log to know the details of the database activities before the database went offline. You can navigate to run-> eventvwr->Windows Logs
Connect to SSMS-> sp_readerrorlog
Once the reason is known below steps can be performed to bring back the DB online-
Open Management Studio and in the "Connect to Server" dialog box enter your connection parameters
Click the "Options >>" button and navigate to the "Connections Properties" page
In the "Connect to database" dialog box type the word master.
Open up a New Query window and paste the following T-SQL code into it:
USE [master]
Database can be brought online using GUI as well. To do so you need to perform the below steps-
Open SSMS-

SQL DBA issues -Database is Offline

You will be able to see the database status for all the databases in Object Explorer. Once you identify the Offline Database, right click on the same and you’ll have to select Tasks-> “Bring Online” as shown in the figure below-

DBA ISsues -How to bring SQL DB Online

Upon clicking the Bring Online option your Database will come online and users can get connected to it. You will see the below screen after the database comes online successfully.

SQL DBA issues DB Online

SQL Server Job has failed

Overview: SQL Server agent jobs or SQL Server jobs are predefined tasks performed in SQL Server at scheduled time. Any change in permissions or job related objects may cause a job to be failed.

Action to be taken:

Get Details about specific failed job
Job named TestJob has been failed for some reason. Now we have to further investigate several aspects about its failure. For this purpose

Go to Jobs folder->Right click on failed job->Click on View History

Troubleshoot SQL Server Job failure
It will present previous history of this specific failed jobs. By further drilling any record of failure, you may find further details and the reason of job failure.

Sql Server Job is failing

Database connectivity issues

Overview: Many times, clients are unable to connect to the Servers or the front-end applications which in turn get connected to the database server. The problem could be due to various reasons but one of the major issues is authentication issue.

Actions to be taken:

   Authentication Method

To inspect the authentication mode of your SQL Server:
1.  Open SQL Server Management Studio. Then, right-click on the server and select Properties in the popup menu.
2.  In the Server Properties dialog box, select Security.
common SQL DBA issues
3.  Make sure Server Authentication is set to SQL Server and Windows Authentication Method.
Common SQL DBA Issues

User is unable to connect to the database due to inappropriate permissions

Overview: Users are unable to connect to the database. Problem could be either access rights have been modified or it’s a new user.

Actions to be taken: Get details about the User's Login and check the error. The error could be due to incorrect passwords, Modification/Revocation of access rights.

Login Failed for user

Backup/Restore is failing

Overview: The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

Actions to be taken: Please read the below error codes and action should be taken as per the error. Please read the article which I have published here(please click for the link) for some backup related scripts. You can know the granular level details which are specific to backup and restore in sql sever by going through the article.

SQL Server Backup and Restore Alerts
Backup Success
Database backed up: Database: %1, creation date(time): %2(%3), pages dumped: %4!d!, first LSN: %5, last LSN: %6, number of dump devices: %9!d!, device information: (%10).
Backup Failure
%1: Backup device '%2' failed to %3. Operating system error = %4.
Backup Failure
%1: %2 failure on backup device '%3'. Operating system error %4.
Backup Failure
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
Backup Failure
Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
Backup Failure
BACKUP DATABASE cannot be used on a database opened in emergency mode.
Backup Failure
Cannot open backup device '%ls'. Device error or device off-line. See the SQL Server error log for more details.
Restore Success
Database restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
Restore Success
Log restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
Restore Success
Database file restored: Database: %1, creation date(time): %2(%3), file list: (%4), number of dump devices: %6!d!, device information: (%7).
Restore Failure
File '%ls' cannot be restored over the existing '%ls'. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files.
Restore Failure
The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
Restore Failure
Database '%.*ls' (database ID %d). The RESTORE statement could not access file '%ls'. Error was '%ls'.
Restore Failure
Database '%.*ls' (database ID %d) was marked for standby or read-only use, but has been modified. The RESTORE LOG statement cannot be performed.
Restore Failure
Database in use. The system administrator must have exclusive use of the database to restore the log.

SQL Database recovery model needs to be changed.

Overview: A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time. The model database sets the default recovery model of new databases.

Action to be taken:

Set recovery model using Management Studio
1. Right click on database name and select Properties.
2. Go to the Options page.
3. Under Recovery model select "Simple" or “Full” or “Bulk-Logged”
4.     Click "OK" to save.

Common SQL DBA issues day to day

So We have seen that the most common looking SQL Server DBA issues can be tackled very easily by following the above techniques.

Monday, 12 June 2017

Use SQL Server Temporary Drive for Temp Database

Configure SQL Server to use Azure VM Temporary drive to store Tempdb database

By default, SQL Server uses C drive to store its Tempdb Database. Both its data file and lof file goes in C Drive only. Tempdb database grows heavily in certain scenarios like when database backup is happening, log shipping is active on a server, index rebuilding(if sort in tempdb is on) is getting performed , some bulk operations are going on in the background etc.
This article demonstrates the process to configure SQL Server to use Azure VM Temporary drive to store Tempdb database. Throughout this document we will be using D as a temporary drive.
Our configuration process includes-

  • Modify PowerShell script
  • Execute Alter database commands on SQL Server
  • Configure the Start Mode of SQL Server and SQL Agent startup as Manual.
  • Create a PowerShell script to recreate the folder in D:\ if needed and start SQL server
  • Create a schedule task at system startup to execute the PowerShell script.

Before proceeding for the movement you need to identify following information

1.        SQL Server and SQL server Agent Service Names
2.        Name of user under which above services are running.
3.        Azure VM Temporary drive should have enough space available to hold Tempdb database

1.        Prerequisites
·         PowerShell script
############## Script for Azure Recovery Services Monitoring ##############
$SQLService="sql_server_service_name" #### Ex -  SQL Server (MSSQLSERVER)
$SQLAgentService="sql_server_agent_service_name" #### Ex - SQL Server Agent (MSSQLSERVER)
if (!(test-path -path $tempfolder)) {
New-Item -ItemType directory -Path $tempfolder
$Acl = Get-Acl $tempfolder
$permission = 'user_name','FullControl','ContainerInherit, ObjectInherit', 'None', 'Allow'   #### Ex - 'NT AUTHORITY\NetworkService'
$Ar = New-Object $permission
Set-Acl $tempfolder $Acl
Start-Service $SQLService
Start-Service $SQLAgentService

2.   Steps to configure
2.1. Modify the above created PowerShell script-
Modify information marked in yellow as per requirement. To highlight, you need to modify

a.    SQL Server Service Name (sql_server_service_name)
b.   SQL Server Agent Service Name (sql_server_agent_service_name)
c.    User Name for above services (user_name)
2.2. Execute Alter database commands on SQL Server -
Using SSMS connect to your SQL Server instance. Execute the following T-SQL commands to change location of the TempDB files:
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\SQLTEMP\templog.ldf')
2.3.    Configure the Start Mode of SQL Server and SQL Agent startup as Manual-
Using Configuration Manager set the Start Mode of SQL Server and SQL Agent as Manual.

Temporary drive in AZURE

2.4. Create a PowerShell script to recreate the folder in D:\ if needed and start SQL server
Copy and paste above PowerShell script and save it as a PowerShell file in the “C:\myfolder” drive (OS drive), for example as “C:\myfolder\SQL-startup.ps1”.
2.5. Create a schedule task at system startup to execute the PowerShell script
Using Task Scheduler create a Basic Task that executes when the computer starts and executes the script in the PowerShell file. For this, specify:
Program/script:                PowerShell
Arguments:                  –file ‘C:\myfolder\SQL-startup.ps1’
schedule task at system startup to execute the PowerShell script

With this, every time your Server gets rebooted, a new folder in D Drive will be created (if in case somehow it does not get created itself) for your temp database files. This will enable smooth operations of your SQL Server system as well.

So we have seen that we can Configure SQL Server to us Temporary drive to store Tempdb database operations and make an ample use of Azure's Temporary Drive