Month: December 2019

No comments

SQL SERVER – Resolving database in SUSPECT mode

  By , ,

SQL Server runs in different modes, and can be in a specific state at a given time.

The modes are:

  • ONLINE
  • OFFLINE
  • RESTORING
  • RECOVERING
  • RECOVERY PENDING
  • SUSPECT
  • EMERGENCY

In this blog post, we will talk about how to deal with a database that is in SUSPECT mode.

Here are some of the reasons why SQL Server marks a Database as Suspect:

  • Database file corruption
  • Unavailable database files
  • Improper shutdown of SQL Server database Server
  • Failure to open the device where the data or the log file resides
  • SQL Server crash

How to Recover SQL Server from SUSPECT Mode

1.Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus 'YourDBName'; 
ALTER DATABASE YourDBName SET EMERGENCY

2. Perform a consistency check

DBCC CHECKDB YourDBName

3. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Take a complete backup of the database

5. Attempt the Database Repair allowing some data loss

DBCC CHECKDB ('YourDBName', REPAIR_ALLOW_DATA_LOSS)

6. Bring the database into the Multi-User mode

ALTER DATABASE YourDBName SET MULTI_USER

7. Refresh the database server and verify the connectivity of the database

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – Understanding ACID

  By ,

Concurrent operation has big implications for data integrity. In order to guarantee integrity, a relational database system must comply with the ACID principle. This is normally implemented through transactions.

In ACID, A stands for Atomicity, and that means when data is modified by some transaction, either all of its data modifications are performed or none of them are performed.

The C in ACID stands for Consistency. When a data modification transaction completes, all data must be in a consistent state. All constraints must be satisfied, and all internal structures must be correct.

I is all about Isolation. Modifications made by one transaction must be isolated from those made by other concurrent transactions. This also implies that if you redo the same operations with the same starting data, the results will always be the same.

The D in ACID stands for Durability. When a transaction is complete the results are stored permanently in the system and persist even if a system failure occurs. Now in order to get you set up, so that you can follow along with all the demos, let me introduce you to the tools I’ll be using.

Cheers!

One Comment

SQL SERVER – Deadlock on ALTER DATABASE to MULTI_USER mode

  By ,

I was trying to alter the database to MULTI_USER mode, but faced this error.

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.

Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

Basically, it says that you cannot kill a system process.

So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.

SET DEADLOCK_PRIORITY HIGH 
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter