Showing posts with label Database Offline. Show all posts
Showing posts with label Database Offline. Show all posts

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
                                “OR”
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]
GO
ALTER DATABASE [database_name] SET ONLINE
GO
Database can be brought online using GUI as well. To do so you need to perform the below steps-
Open SSMS-
Run->SSMS->Enter

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
ID
Category
Error
Severity
Description
1
Backup Success
18264
10
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).
2
Backup Failure
18204
16
%1: Backup device '%2' failed to %3. Operating system error = %4.
3
Backup Failure
18210
16
%1: %2 failure on backup device '%3'. Operating system error %4.
4
Backup Failure
3009
16
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.
5
Backup Failure
3017
16
Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
6
Backup Failure
3033
16
BACKUP DATABASE cannot be used on a database opened in emergency mode.
7
Backup Failure
3201
16
Cannot open backup device '%ls'. Device error or device off-line. See the SQL Server error log for more details.
8
Restore Success
18267
10
Database restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
9
Restore Success
18268
10
Log restored: Database: %1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!, device information: (%8).
10
Restore Success
18269
10
Database file restored: Database: %1, creation date(time): %2(%3), file list: (%4), number of dump devices: %6!d!, device information: (%7).
11
Restore Failure
3142
16
File '%ls' cannot be restored over the existing '%ls'. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files.
12
Restore Failure
3145
16
The STOPAT option is not supported for RESTORE DATABASE. You can use the STOPAT option with RESTORE LOG.
13
Restore Failure
3441
21
Database '%.*ls' (database ID %d). The RESTORE statement could not access file '%ls'. Error was '%ls'.
14
Restore Failure
3443
21
Database '%.*ls' (database ID %d) was marked for standby or read-only use, but has been modified. The RESTORE LOG statement cannot be performed.
15
Restore Failure
4301
16
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.