Best Solutions to Repair Suspect Database in MS SQL Server

To repair Suspect database error in MS SQL 2005, 2008, 2012, 2014, 2016 and 2017 read this article to recover SQL Server database from Suspect mode.

What is Suspect Database Error?

Microsoft SQL Server serves as a storehouse for data that is needed for different software applications on a single system or across a network. Sometimes a user may face a situation where a database in the SQL Server instance currently under running state is marked as Suspect. This condition will lead to a failure in creating a connection with the database. Such a database which is tagged as Suspect can be accessed by removing the causes for this problem. The forthcoming article will discuss the causes for this problem to occur and the possible solutions to repair suspect database from SQL Server 2005, 2008, & 2012.

The error message shown to user is somewhat similar to the one given below

Starting up database ‘abc_d’.
Error: 9003, Severity: 17, State: 5.

The log scan number (189623:15:2) passed to log scan in database ‘abc_d’ is not valid.

The above-mentioned error shows that there is some corruption of the log file(.ldf) and does not adhere to the data file(.mdf). Other error messages can also be shown depending upon the cause due to which the database has been categorized as Suspect.

User queries help in getting a better idea about the problem and to recover SQL database.

“I am currently using SQL Server 2008 edition on my Windows 7 system and there are many applications of mine that are dependant on it. Recently I have been facing difficulties in connecting my applications to some of my Databases and they are being shown as Suspect. Kindly guide me to recover SQL database from Suspect mode.”

SQL Server Database Shown as Suspect: Possible Reasons

There are a number of reasons why SQL Server marks a particular Database as Suspect.
Some of these reasons are mentioned as follows

  • Corruption present in the SQL Server Database
  • Insufficient space available to SQL server to repair suspect database during startup.
  • The files of Database are in use by OS or other Backup software
  • Sudden SQL Server Shutdown, Hardware or Power Failure

There exist a number of techniques for solving the issue of Suspect SQL database and broadly they can be classified as Manual and Automated. The upcoming section will try and explain a Manual technique for achieving this goal.

Quick Tip: If you want to skip the tedious & complex manual approach then it is highly advised to use an automatic tool such as SQL Recovery to efficiently recover SQL database from Suspect mode

Repair Suspect Database Manually

Implement the steps given below to recover Suspect SQL Server Database and to access important data.

1. Bring Database in Emergency Mode using the following code

USE master
GO

ALTER DATABASE abc_d SET EMERGENCY
GO

2. Execute DBCC CHECKDB command.
This will scan and check the physical and logical integrity of objects present in the marked database

DBCC CHECKDB (abc_d )
GO

3. Bring the Database in Single Mode using the below-mentioned query

ALTER DATABASE abc_d SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

4. After the Database is in Single Mode, execute the following code to repair suspect database

DBCC CHECKDB (abc_d, REPAIR_ALLOW_DATA_LOSS)
GO

5. Lastly, restore Multiple access to the database by executing the query

ALTER DATABASE abc_d SET MULTI_USER
GO

Repair Suspect Database by Manual Method: Shortcomings

  • Manual solution consumes a lot of the users’ time for SQL server suspect database repair
  • High risk of losing data forever during manual approach to recover SQL database from suspect mode
  • A Large number of queries to recover SQL database make it complex for non-technical users.
Conclusion

The Suspect database problem is a common issue faced by users of SQL Server database and it has various causes. There is a set of TSQL commands to overcome this error but it poses many issues during implementation. It is better to use automated tools designed specifically for this purpose to repair suspect database.

Automatically link databases to Red Gate SQL Source Control

Automatically link databases to Red Gate SQL Source Control

For the ones that have many databases to keep under source control, it can be really useful to speed up the link-to-source-control process. The only way we have now is to use the GUI provided by Red Gate SQL Source Control. Actually, there’s a github project called SOCAutoLinkDatabases by Matthew Flat, a Red Gate engineer, but, unfortunately it works only on a shared database model (centralised) in TFS. Let’s see how to manage the link using Working Folder (which is also good for many SCM) and dedicated database model (distributed).

Continue reading

SQL Server Latest Updates (Jan. 2016)

Directly from the SQL Server Release Service blog, here the latest updates for SQL Server 2012 SP2 and SP3:

Cumulative Update #1 for SQL Server 2012 SP3

Cumulative Update #10 for SQL Server 2012 SP2

Additionally, Microsoft announced the release of TLS (Transport Layer Security) 1.2 support for SQL Server 2008, 2008 R2, 2012 and 2014. Read more here.

Stay Tuned! 🙂

How to manage SQL Server security with SQL Source Control

How to manage SQL Server security with SQL Source Control

One of the most common issue you can find when source controlling the database is about the security. How to manage the users and the related permissions?

If you use to apply permission to users and to assign users to the database, this can be a problem, especially when you are in the deployment phase (or else when getting latest versions from the source control). Let’s see these two scenarios:

Continue reading

A 2015 full of DLM

After SQL Saturday Pordenone, I’ll keep speaking about DLM (aka ALM on databases) during the following events:

PASS Italian Virtual Chapter, April 14. I’ll demonstrate the SQL source control usage on SQL Server database
PASS SQL Saturday Torino, May 23, I’ve proposed two sessions (source control and unit testing on database)
.Net Campus a Roma, May 30, I’ll speak about continuous integration with SQL Server (source control, unit testing, deploy).
There’s a lot of work to do. But I’m thinking now about two or three new sessions. I hope to finish them in the last months of the year, and I hope to meet you in one of  these events, at least online.
Stay tuned!

 

Accessing SQL Server data with Simple.Data

Accessing SQL Server data with Simple.Data

Recently Michael suggested me a lightweight framework for accessing and manipulating data with SQL Server. I’ve tried it and I’ve found some misunderstanding on the official documentation. The framework I’m speaking for is Simple.Data, a very user-friendly, simple-to-install, quick-to-apply, open and lightweight framework.

After some tests I’ve started to write down an article, that should be taken like a reference due to missing information on official documentation.

You can find it here (in italian).

Hope this helps!

Stay Tuned! 🙂

Interview with Kris Wenzel on EssentialSQL.com

Interview with Kris Wenzel on EssentialSQL.com

EssentialSQL.com is a very useful resource for learning SQL Server.

As in the website homepage: “Now it is time to learn SQL in simple English.”
Kris would like to reach the following goals:
  • Get started in an easy to follow step-by-step manner.
  • Use reader’s time wisely (focusing on what is important to learn to get the most value from your time).
  • Answer the questions.
It’s an important contribute to the SQL Server community. Kris explains here why he’s started to write EssentialSQL.com.
I’ve found this interview very interesting. We’ve spoken about my favourite topics, like Source control on database and database unit testing. This is my job, everyday.
We’ve spoken also about worst practices I saw in my past experiences and then he asked me to give some suggestion to people who start to explore SQL Server.
Thus, if you have a couple of minutes, you can read my interview here.
Stay tuned! 🙂