Resolve SQL Server Database Stuck in Suspect Mode Problem Easily

Summary: Many times SQL Database administrators encounters SQL server database stuck in suspect mode issue. Due to inaccessibility of the database objects, all the DBA’s try to bring the database online as soon as possible because. So this problem tackling blog will discuss the steps by step process to resolve SQL Database Marked as Suspect issue.

Suspect Mode in SQL Server database means the recovery process has started but not finished successfully. The user will not be able to connect to the database nor the user can recover it. All the database objects will be inaccessible. This issue will clearly reflect the risk of data loss.

“Urgent Please! When I woke up in the morning and found my company SQL database is marked as suspect. I do not have the backup and I want to bring the database online as soon as possible. Please help me to resolve this problem.”

Why SQL Server Database Stuck in Suspect Mode? Know the Reasons

Before Proceeding to solution part we have to discuss various causes for this problem. Here are the reason responsible for SQL Database Marked as Suspect mode.

1. Improper or forcefully shutdown of the SQL Server.
2. In case if the system failed to open the device where the data or the log file resides.
3. Due to lack of disk space may be the reason for this problem.
4. In case of SQL Server crash issue, or if the database files are used by the third party backup software.
5. If SQL database cannot complete roll forward or rollback operation.

Note: The user can also read the another post to Restore SQL Server database from MDF file. 

Solve SQL Server Database Stuck in Suspect Mode Problem Manually

Follow the steps below to resolve this problem manually

1. First the user has to Switch the database to Emergency Mode. For this Launch the SQL Server Management studio and then connect to your database. After that select the New Query option.

Execute This Command

EXEC sp_resetstatus 'your database name';
 ALTER DATABASE db_name SET EMERGENCY

This will turn of the suspect flag and switch off to Emergency Mode.

2. Now Perform the Consistency check
DBCC CHECKDB (‘Your databasename’)


3. Bring the database into Single User Mode
ALTER DATABASE your database name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Backup your database because the next command can cause data loss situation.

5. Now Execute the following command
DBCC CHECKDB ('Your database name', REPAIR_ALLOW_DATA_LOSS)

6. After this Change the database into Multi User Mode.
ALTER DATABASE database_name SET MULTI_USER

Finally the user to refresh the SQL serve database and check the connectivity issues.

Note: The user can try the above steps to resolve SQL server database stuck in suspect mode. But in this problem there is a high risk of data loss. So in that case the user can try SQL database recovery tool to resolve this issue. SQL database Recovery Tool is capable to repair the database from suspect mode. The user can easily Recover SQL Database objects such as SQL tables, stored procedure, functions, triggers, indexes. Also this application shows the preview of deleted SQL table records in red color.

Final Verdict

In this article, we have discussed the problem SQL server database stuck in suspect mode. We have provided you the reason for this problem. The user can try the above methods to resolve this issue. But in case if you are facing any problem while performing the manual methods. Then I suggest you take the help of SQL database recovery tool to resolve this issue.

Decrypt SQL Server Stored Procedure via Different Methods

SQL Server user makes the frequent use of stored procedures in the database in order to maintain the integrity and consistency of the data. Simply, a block of SQL query is embedded into the stored procedure, which can be called at any time in the program. Since, these procedures are associated in the encrypted form with the database application, one finds it quite difficult to view the code behind a particular stored procedure. Only the permitted users are allowed to view the source code behind the stored procedure. More importantly, the developers who wish to update the already included stored procedures in an application, finds it much necessary to decrypt encrypted SQL server stored procedure so that they can easily work on the code and may update the behavior of the procedure. Keeping in mind the need of decryption, we have attempted to provides way to decrypt SQL Server stored procedure using different methods.

Manual Approach to Decrypt SQL Server Stored Procedure

Any stored procedure can be decrypted by using the DAC(Dedicated Administrator Connection) with SQL Server.

To connect to DAC: The DAC can be connected to SQL Server Management Studio by prefixing ADMIN to the server name in query editor.

Once a connection is established with DAC, the decryption can be performed using the following steps:

1. Using DAC connection, fetch the encrypted values of the stored procedure
2. Obtain the encrypted value for the blank procedure having ‘-’ character in its definition
3. Obtain the plain text blank procedure statement in unencrypted form
4. Now, XOR the results of step 2 and step 3 to get the final decrypted value of the procedure

Consider the following script that allows to decrypt encrypted stored procedure named ‘dbo.MyDecryption’ using DAC. The scripts needs to be executed carefully, otherwise if any of the parameter is missing, it will generate an error.

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS
BEGIN
PRINT 'This is the decrypted text preview..'
END
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted =(
SELECT imageval
FROM sys.sysobjvalues
WHERE OBJECT_NAME(objid) = 'MyDecryption’)
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.MyDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = (
SELECT imageval
FROM sys.sysobjvalues
WHERE OBJECT_NAME(objid) = 'MyDecryption’)

SET @procedureHeader = N'CREATE PROCEDURE dbo.MysDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
SET @decryptedChar =
NCHAR(
UNICODE(SUBSTRING(
@encrypted, @cnt, 1)) ^
UNICODE(SUBSTRING(
@procedureHeader, @cnt, 1)) ^
UNICODE(SUBSTRING(
@blankEncrypted, @cnt, 1))
)
SET @decryptedMessage = @decryptedMessage + @decryptedChar
SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

Decrypt Encrypted SQL Server Stored Procedure using Third Party Tool

Various third party tools for decrypting SQL Server database objects are available in the industry, which cater the demand to easily decrypt the encrypted database objects. One such efficient tool is SQL Decryptor. The tool carries out decryption in the following easy steps:

1. Enter all database credentials such as server name, login mode, user credentials(name & password) to connect to server

2. On the preview page, choose the required stored procedure and preview the decrypted script in the text box.

3. Under the export options, choose to export the decrypted file to SQL Server directly or export as SQL Compatible Script.

So, the users can make the use of automated decryption tool to decrypt the stored procedures without running any complicated queries.

Conclusion

Considering user’s requirement to decrypt various database objects like the stored procedures, we have tried to explain the method to decrypt encrypted SQL Server stored procedure using manual methods and third party software. Users are advised to run the SQL script carefully to completely decrypt the stored procedure. However, to get rid of the complicated queries the user can also go for the third party SQL Decryptor software. It can efficiently decrypt the database objects and the decrypted script can also be exported using the tool.

Resolve Error “SQL Database is in Use” Using Manual Steps

Problem while Working On Database:

While working on SQL Server users perform various actions such as Rename, Detach, Restore, and Drop on SQL Server. Due to which an error occurs “SQL Database is in use” with different error numbers while performing such actions on SQL database.

How to Tackle this Problem ?

Some scenarios are discussed below, which helps users to resolve the occurrence of an error while executing various actions on SQL database.

Scenario 1: While Restoring the Database

When the user tries to restore the database and other users are accessing the same database at that time. Then, they face SQL error message 3101, which is due to SQL database is in use. There are some solutions, which helps users to remove the error occurrence.

Exclusive Access

User can get the exclusive access by dropping all the other connections to restore the database. There is another way to check the connections usage of the database, which user needs to restore, i.e. sp_who2 and SSMS.

Using KILL Command

After getting exclusive access, user can use KILL command that helps to kill every connection that is using the database. Before this, user must be aware that which connections are killed as the rollback issue may be required.

ALTER DATABASE

There is another option, in which user can put the database in single user mode after that they can restore. It also rollback depends on the option user use, but it will all do connections at once.

Scenario 2: Renaming the Database

While renaming the database user receives SQL error 5030. This error occurs when the database is in Multi User mode where various users are utilizing the database at same time. To resolve this issue, user first need to set the database to single user mode and then rename the database and after that set it to Multi user mode again. User can follow the steps given below to perform the process.

• Firstly set the database to single user mode,
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• Now rename the database
ALTER database AdventureWorks MODIFY NAME = NewAdventureWorks
• Set database to Multiuser mode
ALTER DATABASE AdventureWorks SET MULTI_USER WITH ROLLBACK IMMEDIATE

Scenario 3: While Dropping the Database

When the user tries to drop a database when users are connected to SQL Server database, then they will receive SQL error message 3702. User can follow the mentioned steps to remove the error occurrence by getting the exclusive excess to drop database in Server.

1. Connect to SSMS and expand the database node.
2. Right click on the databases that are required to drop.
3. Choose the delete option from drop down list to view the delete object.
4. Now choose the checkbox “close existing connections” to drop existing connections before dropping the SQL Server database.
5. Select option “Delete backup and restore history information for database,” user will be able to remove the database backup and restore history that is stored in database.

Using TSQL Query

User can execute the TSQL code to drop the database in SQL Server by using TSQL Query as mentioned below:

EXEC msdb.dbo.sp_delete_database_bakuphistory @database_name = N‘AdventureWorks’
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Drop DATABASE [AdventureWorks]
GO

Scenario 4: Detaching the Database

When the users detach the database, they face SQL Server error 3703. This error is due to the reason that multi users are using the same database at the time. To resolve this, user can kill the respective accessing DB processes; sometimes it does not work. For this, user needs to force DB delete/detach by ending the existing connections by leaving the DB in single user mode.

Select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d name like ‘%mydb%’
go
kill 53
go

Users need to fix the DB offline with ‘SET OFFLINE WITH ROLLBACK IMMEDIATE’ setting so that the DB will be turn offline immediately and after that detach DB will work correctly. When the user needs to attach the DB they can make the DB online.

ALTER DATABASE  SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE [Database Name] SET ONLINE

Conclusion

With the help of the discussed solution, user can easily resolve an error “SQL Database is in Use” to perform various actions such as restore, rename, detach, and drop on the SQL server database.

Property Owner is not available for Database SSMS error

When you try to launch the database mirroring GUI or some other database property window in SSMS you get this error:

Cannot show requested dialog.

Additional information:
  Cannot show requested dialog.(SqlMgmt)
    Property Owner is not available for Database'[XXXX]'. This property may not exist for this
    object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

 

I saw this issue during our DR (Disaster Recovery) exercise. We were using the SSMS GUI and we successfully failed over one database from the principle server to the mirror server, but when we did a failback via the GUI we got this error.Visit below link to troubleshoot this issue:

Property Owner is not available for Database SSMS error

Visit my author profile to read my other articles: Manvendra Deo Singh

SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule

Last month I was working on a SQL Server 2005 to SQL Server 2008 R2 upgrade.  Unfortunately, we were not able to complete our upgrade because of a failing a upgrade rule named "Security Group SID (Security Identifier)".  The SQL Server Upgrade Setup Wizard did not enable the 'Next' button in order to proceed with the upgrade until this failure was resolve.  In this tip, I will describe the reason for this failure and how I fixed the issue in order to have a successful upgrade. Read my article published on MSSQL Tips through below link:-

SQL Server 2008 R2 Upgrade Failure due to Security Group upgrade rule 

 

Visit my author profile to read my other articles. Manvendra Deo Singh