SQL Server Database Health-Check

SQL Server Database Health-CheckAre you sure you can restore your database with the help of backups with minimal loss? Do you restore your data regularly? Probably many SQL Server users don’t even think of it until it strikes them. So ask yourself yet another question – What should I do about my backup to make sure I can restore it with minimal loss should a disaster occur? To put it shortly – You need to check the health of your SQL Server database regularly. Read on to find out how it’s done.

SQL Server Database Health-Check

It’s common knowledge that the best way of keeping your database safe and sound is to make scheduled backups regularly. Of course, it is necessary to work out the best backup plan that will cover all your needs. But before that you have to consider RPO (Recovery Point Objective) and RTO (Recovery Time Objective).

Recovery Point Objective (RPO)

In other words – how much data can you afford to lose? For example, your database backup plan is Full backup every 24 hours, differential every four hours and transaction log every 15 minutes, in such case if failure occurs the time for which data will be lost will not exceed 15 minutes. To be confident in your backup plan your database should be in the full recovery mode and your transaction log backups should occur every “<=n” minutes. (“n” – is the maximum amount of data loss that you can sustain).

Recovery Time Objective (RTO)

RTO – if database disaster occurs, how much time are you able to spend restoring a database to its working condition? This will determine whether you just keep your backups on a drive ready for access or actively keep a warm standby using log shipping.

Restore Your Backups

Suppose you are familiar with RPO and RTO, but beside this, you need to do regular SQL Server database backup health-check. Testing your database backups is an extremely important point which should not be ignored. This implies that from time to time you need to restore your database with the help of backups to another server or another database. Restoring database periodically is useful not only for practice but also to check the validity of backups.

Please note, if you haven’t yet tried restoring your database backup it means that you don’t have a backup!

RESTORE VERIFYONLY and CHECKSUM options

As an additional SQL Server database backup health-check, you can make all backups with CHECKSUM option. The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. Use the following T-SQL Command to perform a backup with CHECKSUM:

BACKUP DATABASE Adventureworks TO DISK = 'Adventureworks.bak' WITH CHECKSUM;

You can verify a backup with the VERIFYONLY  option. It does not restore the backup, it only verifies it:

RESTORE VERIFYONLY FROM DISK = 'Adventureworks.bak'

Conclusion

So, your backup plan and RESTORE VERIFYONLY & CHECKSUM options are just additional tools that can help you to perform SQL Server database health-check, but the best way to make sure you will be able to restore your database with minimal loss is to restore all necessary backups to another server or database.

File Backups

SQL Server allows to make backups and restore not only the entire database but also a single filegroup or even a separate file.  This is called a file backup. This type of backup contains all data from one or more files or filegroups.

File backups are often used to increase the recovery process speed by restoring only damaged files, without restoring the rest of the database, if necessary. Assume there is a database that contains several files. Each file is stored on a separate disk and suddenly one disk fails. In this case the recovery process will not take more time because there is no need to restore the entire database, it will be enough to restore only the file from the failed disk.

Another use case is when some filegroups in your database are updated more frequently than others. In this case you can backup them separately using a file backup.

One of the biggest disadvantages of using file backup is that such backups are difficult to manage. It takes more time to manage every file backup separately. Also, if failure occurs and the backup of the damaged file is lost the entire database becomes unrecoverable.

Let’s explore how does full file backup work under different recovery models. Assume there is a database “Adventureworks” which contains two filegroups “Group1” and “Group2”, each filegroup includes two files “File1” and “File2” for filegroup “Group1” and “File3” and “File4” for “Group2”.

Case for Simple Recovery Model

To make sure that the database can be restored, under the simple recovery model,  all read-write files must be backed up together. It’s quite simple to accomplish this as there is no need to backup all read-write files one by one, you just need to use the READ_WRITE_FILEGROUPS option (known as a partial backup):

BACKUP DATABASE Adventureworks READ_WRITE_FILEGROUPS, 
   FILEGROUP = 'Group1', 
   FILEGROUP = 'Group2' 
TO DISK = 'Groups.bck'

Full file backup (simple recovery model)

Case for Full Recovery Model

If the database runs under the full recovery model you should backup the transaction log, regardless of the file backup strategy you chose. All transaction log backups beginning with the first file backup and a complete set of  full file backups equal to a full database backup. Please check the picture bellow to understand how it works:

BACKUP LOG Adventureworks TO DISK = 'log_13_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group2' TO DISK = 'Group2.bck' 
BACKUP LOG Adventureworks TO DISK = 'log_14_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group1' TO DISK = 'Group1.bck'
BACKUP LOG Adventureworks TO DISK = 'log_15_00.bak' 
BACKUP DATABASE Adventureworks FILEGROUP = 'Group3' TO DISK = 'Group3.bck'

Full file backup (full recovery model)

Note though, that using this backup strategy leads to a complex database restoration process.

If any file is offline then the whole filegroup containing this file is offline too and cannot be backed up.

SQL Server backups only one file at a time. Of course, it is possible to backup multiple files in one operation, but should you need to restore only one file, the restore operation will take more time (to find the file, the whole backup will be read).

Case for Differential File Backup

SQL Server allows to make not only full file or filegroup backups but also a differential file backup. Creating a full file backup makes it possible to apply a differential file backup. If the database runs under the simple recovery model the differential file backup can be applied only to read-only filegroups. As far as the full recovery model is concerned, a differential file backup can be applied to any filegroup which has been previously backed up. Differential file backups reduce time to restore database because less transaction log backups have to be restored. It is better to use differential file backups under the following circumstances:

  • There is a need to backup some files more frequently than other
  • Some files are really large and changes in these files occur infrequently

Making a differential file backup is quite simple, just add “WITH DIFFERENTIAL” option:

BACKUP DATABASE Adventureworks 
   FILE = 'File2',
   FILE = 'File3' 
TO DISK = 'Groups_diff.bck' WITH DIFFERENTIAL

Differential File Backup

How to Create a File Backup with SQL Server Management Studio

Here is how you can create a file backup using SSMS:

  1. Right click on the database you want to backup
  2. Select “Tasks”, then “Back up…”
  3. Choose “Files and Filegroups” and in the new window check in the files or file groups you want to backup
  4. Add a backup destination
  5. Click “OK”File Backup

Point-in-time recovery

Point-in-time recovery allows to restore a database into a state it was in any point of time. This type of recovery is applicable only to databases that run under the full or bulk-logged recovery model. If the database runs under the bulk-logged recovery model and transaction log backup contains bulk-logged changes, the recovery to a point-in-time is not possible.

To understand how point-in-time recovery works let’s review the use case with a sample database (under the full recovery model) where a full backup is made every 24 hours and a differential database backup that is created every six hours, and transaction log backups are created every hour. Please see the picture below:Point-in-time recovery
Assume some crucial data were deleted at 13:30 and the last transaction log backup was made at 14:00. The transaction log backup from 14:00 gives us the opportunity to restore the database to its state at 13:29:59. In this case, only 30 minutes of updates will be lost.

Below we will consider two ways of restoring a database to a point-in-time:

Using Transact-SQL

To recover a database to a point-in-time it is necessary to start from restoring the full database backup using the following syntax:

RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE

After the full backup is restored it is time to restore the last differential backup. It our case – the last differential backup was made at 12:00. Differential backup can be recovered with the help of this syntax:

RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY

This differential backup includes all changes that were made to the database since the last full backup.

And the last step is to apply all transaction log backups that were made after the differential backup. Please apply all transaction log backups in the same sequence in which they were created. It is crucial to remember to specify the time to which database should be restored with the help of STOPAT option when you restore the last log backup (in our case the database should be restored as of 13:29:59). Please also note that all backups, except the last one, are restored with NORECOVERY option, while the last one should be restored with RECOVERY option recovering the database into its working state:

RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY 
RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY

Using SSMS (SQL Server Management Studio)

  1. Right click on the database that should be restored from the list, select “Tasks” – “Restore” – “Database…”Restore Step1
  2. In the window that appeared press “Timeline” button to set up the time for database restorationRestore Database
  3. Check in “Specific date and time” to set up the time for database restoration (13:29:59), press “OK” to save changes, and press “OK” button in the previous window:Restore to a point-in-time Step3
  4. When the restoration process is completed, the following message will appear on the screen:Restore to a point-in-time Step4

Violation of … constraint ‘…’. Cannot insert duplicate key in object ‘…’ (Msg 2627)

SQL Server’s error “Violation of … constraint ‘…’. Cannot insert duplicate key in object ‘…’” can appear when a user tries to insert a non-unique value into a table’s unique column such as the primary key. Look at the following syntax:

CREATE TABLE Table_1
(ID INT NOT NULL PRIMARY KEY, 
 NAME VARCHAR(10) NOT NULL, 
 AGE date);

The user created the new table Table_1, and started to fill it adding a row:

INSERT INTO Table_1 (id, name, age) VALUES (1, 'John','30');

then the next row:

INSERT INTO Table_1 (id, name, age) VALUES (1, 'John','30');

After execution of this command the following error appeared:

Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__Table_1__3214EC27204DF2A8'. Cannot insert duplicate key in object 'dbo.Table_1'. The duplicate key value is (2).

This message says that the user tries to insert duplicate values into a unique column. To avoid this error just select an unique value, for example, 2:

INSERT INTO Table_1 (id, name, age) VALUES (2, 'John','30');

Invalid filegroup ‘…’ specified (Msg 1921)

Invalid filegroup ‘…’ specified.” error can appear in the case when the name of Filegroup where the new Table_1 will be stored is invalid. Look at the syntax bellow:

CREATE TABLE Table_1
(Column1 int primary key, Column2 char(8))
ON [SecondaryFilegroup];

In this case, the user tried to store the new Table_1 into SecondaryFilegroup and received the following error message from SQL Server:

Msg 1921, Level 16, State 1, Line 1
Invalid filegroup 'SecondaryFilegroup' specified.

It means that such filegroup does not exist. The user has to create such filegroup or use another existing one to store the Table_1.

CREATE DATABASE failed. Some file names listed could not be created (Msg 1802)

The error “CREATE DATABASE failed. Some file names listed could not be created. Check related errors.” can occur when the user tries to create a database with file names specified. To understand when this error can appear and how to avoid it, let’s consider the following syntax:

CREATE DATABASE Adventureworks 
ON PRIMARY
( NAME = Adventureworks_Data,
  FILENAME = 'Adventureworks_Data.mdf',
  SIZE = 0,
  MAXSIZE = 30,
  FILEGROWTH = 10% )
LOG ON
( NAME = Adventureworks_Log,
  FILENAME = 'Adventureworks_Log.ldf',
  SIZE = 3MB,
  MAXSIZE = 20MB,
  FILEGROWTH = 3MB );
GO

In this case, the SQL Server will send the following errors:

Msg 5174, Level 16, State 1, Line 2
Each file size must be greater than or equal to 512 KB.
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The reason why this error appeared is that one of the file parameters is incorrect. In this case, the user set up the size of the data file to zero. Msg 5174 gives a hint that Each file size must be greater than or equal to 512 KB.

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205)

“Transaction was deadlocked” error occurs when two or more sessions are waiting to get a lock on a resource which has already locked by another session in the same blocking chain.  As a result, none of the sessions can be completed and SQL Server has to intervene to solve this problem. It gets rid of the deadlock by automatically choosing one of the sessions as a victim and kills it allowing the other session to continue. In such case, the client receives the following error message:

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

and the killed session is rolled back. As a rule, the victim is the session that requires the least amount of overhead to rollback.

Why SQL Server Deadlocks Happen?

To understand how “Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction” error happens let’s consider a very simple example.

Let’s create two tables “t1” and “t2” containing only one integer column:

CREATE TABLE t1 (id int)
CREATE TABLE t2 (id int)

and fill them with some data:

INSERT INTO t1 (id) 
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
GO
INSERT INTO t2 (id)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

Now, suppose we started a transaction that deletes rows with id=2 from t1:

BEGIN TRAN
DELETE FROM t1 WHERE id = 2

Then, assume that the other transaction is going to delete the same rows from both tables:

BEGIN TRAN
DELETE FROM t2 WHERE id = 2
DELETE FROM t1 WHERE id = 2

It needs to be waiting for the first transaction to complete and release table t1.

But, assume that the first transaction now deletes the same row from the second table:

DELETE FROM t2 WHERE id = 2

After executing this statement you should receive the following error message:

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

It is caused by a situation when the first transaction is waiting for the second one (to release t2) while the second transaction is also waiting the first (to release t1) one in the same time.

How to Analyze Deadlock Graphs

A deadlock graph is a block of information showing what resources and sessions are involved in a deadlock. It helps to understand why the deadlock happened.

Before SQL Servers 2008, in order to capture this information you had to set up a server-side tracing or enable trace flags and then wait while the deadlock occurs. Starting from SQL Server 2008 everything is much easier. You can retrieve a deadlock graphs retrospectively from the extended events “system_health” session. To do this, go to “Management” > “Extended Events” > “Sessions” > “system_health” > “package0.event_file” and click “View Target Data…”

 

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

 

Thousands of events will be shown in the opened window. There you can find deadlock reports which marked as “xml_deadlock_report”. Let’s choose one we’ve just simulated

SQL Server Deadlock Graph

and look at its deadlock graph (in form of XML) details consisting of resources and processes sections.

Resources section displays the lists with all the resources which were involved in the deadlock:Resources section SQL Server

It shows what processes were fighting over and what types of locks they were causing. It has two or more entries. Each entry has a description of the resource followed by the lists of the processes that held a lock or requested a lock on that resource. Locks in that section mainly will relate to a key, RID, a page or a table.

After the resources section let’s turn to the processes section to find out what those processes were doing.

Processes section displays the details of all the processes which were involved in the deadlockprocess-list SQL Server.png

This section contains entries about the threads involved in the deadlock and provides such crucial information like host names, login names, isolation level, times, session settings and so on. But the most valuable information is the isolation level under which each query was running and the details about statement caused the deadlock.

How to Choose a Deadlock Victim

If you can’t avoid deadlocks, there is an option to specify which process should die when a deadlock occurs. SQL Server chooses a deadlock victim based on two factors: DEADLOCK_PRIORITY set for each session and the amount of work which SQL Server has to do in order to roll back the transaction.

The DEADLOCK_PRIORITY option can be set by a user to HIGH, NORMAL, LOW or to an integer value from -10 to 10. By default, DEADLOCK_PRIORITY is set to NORMAL (0). Use the following syntax to set deadlock priority:

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar } <numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

For example, a session with NORMAL deadlock priority will be chosen as a deadlock victim if it involved in a deadlock chain with other sessions having deadlock priority set to HIGH or integer value greater than 0. And it will survive if the other sessions have LOW deadlock priority or its integer value less than zero.

LOW is equal to -5, NORMAL is the same as o, HIGH equals to 5. In other words, run the following commands to set a deadlock priority to NORMAL:

SET DEADLOCK_PRIORITY NORMAL;
GO

or

SET DEADLOCK_PRIORITY 0; 
GO

To check the deadlock priority of the session you can use the following query:

SELECT session_id, DEADLOCK_PRIORITY FROM sys.dm_exec_sessions WHERE SESSION_ID = @@SPID

How to Avoid Deadlocks in SQL Server

As a developer, you need to design database modules in a way that minimizes risks of deadlocks. Here are some useful tips on how to do that:

Make sure the applications access all shared objects in the same order

Consider the following two applications (bad practice): 

 APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Update Part Table 2. Update Supplier Table 
3. Update Supplier Table  3. Update Part Table
4. Commit Transaction 4. Commit Transaction

These two applications may deadlock frequently. If both are about to execute step 3, they may each end up blocked by the other, because they both need access to an object that the other application locked in step 2 and was not to release it till the end of the transaction.

Please see the following correction of the above example, changing the order of the statements (good practice):

APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Update Supplier Table  2. Update Supplier Table 
3. Update Part Table 3. Update Part Table
4. Commit Transaction 4. Commit Transaction

It is a very good idea to define some programming policy that defines the order in which database objects and resources have to be accessed by the applications (also it is a good policy to release locks in the opposite order to that in which the applications locked them).

Keep transactions short and simple

Please consider the previous example:
the applications have two statements in a transaction (bad example)

 APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Update Supplier 2. Update Part
5. Update Part 5. Update Supplier
6. Commit Transaction  6. Commit Transaction 

Please consider the following changes in the above example  (good example):

 APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Update Supplier 2. Update Part
3. Commit Transaction 3. Commit Transaction
4. Begin Transaction 4. Begin Transaction
5. Update Part 5. Update Supplier
6. Commit Transaction  6. Commit Transaction 

In this case you have everywhere one update at a time, thе transactions are very short, and there will be no deadlocks here at all.

Make sure the applications use the minimum necessary transaction isolation level.

The lower isolation level, the lower possibility of deadlocks (and the higher possibility of violation of data integrity, although).

For example, when the lowest isolation level possible (READ UNCOMMITTED) is used, there are no deadlocks at all. Although in this case you have to take special care of data integrity,
as READ UNCOMMITTED isolation level allows a transaction to read a table before another transaction finishes writing to the table (i.e. before the writing commits), and in this situation some data can be read before an update finishes, so you have to be careful about reading possibly outdated or inconsistent data.

Use “manual” lock/unlock possibilities to lock/unlock objects by yourself, not leaving it to the system, i.e. not using high level transaction isolation levels

Consider again the above two applications (bad practice):

 APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Update Part Table 2. Update Supplier Table 
3. Update Supplier Table  3. Update Part Table
4. Commit Transaction 4. Commit Transaction

Here the situation is prone to deadlocks, but if all of the ‘update’ statements are wrapped up with special lock/unlock procedures, then there will be no deadlocks.

Consider the applications changed this way (good practice).

 APPLICATION 1  APPLICATION 2
Begin Transaction Begin Transaction
‘Manual Lock’ of Part Manual Lock’ of Supplier
Update Part Update Supplier
‘Manual Release’ of Part   Manual Release’ of Supplier  
Manual Lock’ of Supplier Manual Lock’ of Part
Update Supplier Update Part
Manual Release’ of Supplier Manual Release’ of Part
Commit Transaction Commit Transaction

To implement ‘Manual Lock’ use  sp_getapplock procedure.
For ‘Manual Release’ use
sp_releaseapplock procedure.
In the example above there will be no deadlocks (and no ‘lost updates’ etc) even using the lowest transaction isolation level, as each transaction do not ask for access to another object before releasing the previous one.

In case Repeatable read or Serializable isolation levels are required, and two applications use the same database object very frequently, use UPDLOCK hint

Consider the following two transactions (bad practice):

  APPLICATION 1   APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Read Part table
(S-lock #1 is set by the Read)
2. Read Part  table
(S-lock #2 is set by the Read)
3. Change structure of Part table 
(waiting for S-lock #2 release)
3. Change structure of Part table 
(waiting for S-lock #1 release)
4. Commit Transaction 4. Commit Transaction

Here are the two transactions just checking some data up before changing them
(e.g. counting a number of records of a table before inserting a new record in it).

On step 2 both transactions apply S-lock to the same database object and then on step 3 they both wait for release of the S-locks for changing something in the structure of the object (e.g. inserting a row to the table).

Please see the changes in the two transactions (good practice):

  APPLICATION 1  APPLICATION 2
1. Begin Transaction 1. Begin Transaction
2. Select Part table
with UPDLOCK optimizer hint 
2. Select Part  table
with 
UPDLOCK optimizer hint 
3. Access Part table
3. Access Part table
4. Commit Transaction 4. Commit Transaction

The advice is to start all relevant transactions with SELECT statement with UPDLOCK optimizer hint for making the two applications to deal with the same database object one after another in turn:

SELECT * FROM Part WITH (UPDLOCK)

Other advices on how to avoid deadlocks in SQL Server

There are also other general advices related to the matter, such as

  • have normalized database design,
  • use bound connections and sessions,
  • reduce lock time (e.g. don’t allow users input during transactions),
  • avoid cursors,
  • use row versioning-based Isolation levels,
  • use ROWLOCK optimizer hint,

etc.

 

Database ‘Adventureworks’ cannot be opened. It is in the middle of a restore (Msg 927)

To understand how to avoid “Database ‘Adventureworks’ cannot be opened. It is in the middle of a restore” error let’s consider the following example:

Assume, in the database some crucial data were deleted, and the restore operation was started

RESTORE DATABASE Adventureworks FROM DISK = 'Adventureworks_full.bak' WITH NORECOVERY, REPLACE 

Note, that even if  the full backup was restored the database still is in the NORECOVERY state and there is no way to use it. But if you try to run this database the SQL Server will send this error message:

Msg 927, Level 14, State 2, Line 3
Database 'Adventureworks' cannot be opened. It is in the middle of a restore.

Here are some ways how to avoid this error.

  • The first one is to restore only full backup WITH RECOVERY option:
RESTORE DATABASE Adventureworks FROM DISK = 'Adventureworks_full.bak' WITH RECOVERY
  • The other way is applicable when you need to restore several backups, such as differential and transaction log. Doing this you need to restore the last backup WITH RECOVERY option in order to restore the database to its working state.

Database ‘Adventureworks’ does not exist (Msg 911)

Database ‘Adventureworks’ does not exist. Make sure that the name is entered correctly” can occur if there is a mistake in the name of the database. Assume that there is a need to run database “Adventureworks”. The next syntax was used to issue the database:

USE Advantureworks

and the following error was received:

Msg 911, Level 16, State 1, Line 1
Database 'Advantureworks ' does not exist. Make sure that the name is entered correctly.

If the database name is read carefully, to notice the error will not take many efforts. In this case, it is necessary to change letter “a” to “e”:

USE Adventureworks

Unknown object type ‘…’ used in a CREATE, DROP, or ALTER statement (Msg 343)

Let’s consider the error “Unknown object type ‘…’ used in a CREATE, DROP, or ALTER statement” on the following example:

There is a need to add one more column “Column4” in the table “Table_1” which located in the database “Adventureworks”. To execute this operation was used next syntax

ALTER Table_1 
ADD Column4 INT;

But column was not added and from the SQL Server was received the following message:

Msg 343, Level 15, State 1, Line 3
Unknown object type 'Table_1' used in a CREATE, DROP, or ALTER statement.

This error occurs because SQL Server does not understand what to ALTER. In this case, it is necessary just to add TABLE after ALTER

ALTER TABLE Table_1 
ADD Column4 INT;