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

How to protect My Stored Procedure Code.

When deploying applications to a client's server(s) or to a shared SQL Server, there is often a concern that other people might peek at your business logic. Since often the code in a stored procedure can be proprietary, it is understandable that we might want to protect our T-SQL work. Use "WITH ENCRYPTION" option to protect your SQL code as shown below.

CREATE PROCEDURE dbo.Manvendra

WITH ENCRYPTION

AS

BEGIN

SELECT 'SQL statements'

END

 

Now when you will try to run sp_helptext to see the code, below error will appear.

"The text for object 'Manvendra' is encrypted"

How to find out how much CPU a SQL Server process is really using

Have you ever think about kpid in SQL Server when you look into sysprocesses system table.Its very useful coloumn when you are dealing with CPU pressure.When you look into the database server you see CPU utilization is very high and the SQL Server process is consuming most of the CPU. You launch SSMS and run sp_who2 and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.
At the server level you can only see the overall SQL Server process, but within SQL Server you can see each individual query that is running. Is there a way to tell how much CPU each SQL Server process is consuming? In this article I explain how this can be done.
Find the below tip to get step by step process to identify a particular sql server process which is responsible for CPU pressure.

Tip: How to find out how much CPU a SQL Server process is really using

Enable Powershell Remoting on SQL Server Instances

Logging on to each SQL Server instance for daily DBA Administrative tasks can be quite lengthy. Are there any options available in PowerShell to reduce the effort and complexity of managing a SQL Server environment? Yes It is..Find this MSSQLTips for step by step process to enable Powershell Remoting and access SQL Server Instances remotly.

Tip:- Enable Powershell Remoting on SQL Server Instances

ASYNC_IO_COMPLETION Wait type

Normally this wait type can be seen in backup and restore transactions.and whenever you will see this wait type your backup/restore process will be in suspended state most of the time because the process is waiting to get IO resource to proceed its operation and it will wait till certain time period then moved in suspended state. In that case your process will take more than its usual time to complete or most of the time it will hung or will showing in executing state for unknown time duration.

This wait type occurs when a task is waiting for asynchronous I/Os to finish. This wait type is normally seen with few other wait types like BACKUPBUFFER,BUCKIO etc. This is clear indication of DISK IO issue.You can also get the Average disk queue length or current disk queue length value at the same time when you are getting this wait type.Compare both counters and if these counters have high value then you should look into your storage subsystem. Identify disk bottlenecks, by using Perfmon Counters, Profiler, sys.dm_io_virtual_file_stats and SHOWPLAN.

Any of the following reduces these waits:

1.      Adding additional IO bandwidth.

2.      Balancing IO across other drives.

3.      Reducing IO with appropriate indexing.

4.     Check for bad query plans.

5.     Check for memory pressure

 We can also corelate this wait type between Memory pressure and Disk IO subsystem issues.

BACKUPBUFFER Wait Type

Today I was working on an issue in which backup job was showing in executing state since last 2 days.Initially my observation was either it was hung in middle of its process or it was processing very slow.Here i decided to see the lock wait type for this process.when i gather this info i see the wait type was BACKUPBUFFER wait type.

BACKUPBUFFER Wait Type:-This wait type Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount. This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.This wait type can be seen with one more wait type i.e. ASYNC_IO_COMPLETION. 

These both wait type indicates that the issue is with storage/disk subsystem.So solution is to either ask your storage team to look in this or change your disk/storage subsystem and run your backup on a healthy and fast storage subsystem.

Apple iPod touch 32 GB (4th Generation) NEWEST MODEL

RESOURCE_SEMAPHORE Wait Type

RESOURCE_SEMAPHORE waits occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

High waits on RESOURCE_SEMAPHORE usually result in poor response times for all database users, and need to be addressed.

It is also useful to correlate high waits on RESOURCE_SEMAPHORE with the Memory Grants Pending and Memory Grants Outstanding SQL Memory Mgr performance counters. Higher values for these counters indicate a definite memory problem especially a non-zero value for Memory Grants Pending.

The root cause of this type of memory problem is when memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory. The solution would be to tune the offending queries, or manage their workload so that they are executed at less busy times.

You can also get another article about sql server Wait type here:Sql server Wait Type

What is SQL Server Wait Type? or How to get wait type info in sql server?

In sql server 2005 and later version we can get this info from various DMVs.One of the most useful DMV is sys.dm_qs_wait_stats.This DMV gives you the detail report of all wait types and you can look into those wait types which are causing issue or waiting from lot much time.You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.Following columns will come in output when you run this DMV:

• wait_type :- Name of the wait type.
  waiting_tasks_count:-Number of waits on this wait type. This counter is incremented at the start of each wait.
• wait_time_ms:-Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
• max_wait_time_ms:-  Maximum wait time on this wait type.
• signal_wait_time_ms :-Difference between the time that the waiting thread was signaled and when it started running.

In general there are three categories of waits that could affect any given request:

• Resource waits are caused by a particular resource, perhaps a specific lock that is unavailable when the requested is submitted. Resource waits are the ones you should focus on for troubleshooting the large majority of performance issues.
• External waits occur when SQL Server worker thread is waiting on an external process, such as extended stored procedure to be completed. External wait does not necessarily mean that the connection is idle; rather it might mean that SQL Server is executing an external code which it cannot control. Finally the queue waits occur if a worker thread is idle and is waiting for work to be assigned to it.
• Queue waits normally apply to internal background tasks, such as ghost cleanup, which physically removes records that have been previously deleted. Normally you don't have to worry about any performance degradation due to queue waits.

sys.dm_os_wait_stats shows the time for waits that have completed. This dynamic management view does not show current waits.If you want to see current wait type then you should use another system table sys.sysprocesses.Run below cmd:

Select * from sys.sysprocesses

The output describes you the wait time and wait type for each process id. So here you can get which process is pending since how much time and which type of wait is that.Once you have the process id then you can run below cmd to get the codes behind that SP id:

DBCC inputbuffer(SP ID)

 You can also compare DMV (sys.dm_os_wait_stats)output to this sysprocess table output and you can better analyze the exact issue.

A SQL Server worker thread is not considered to be waiting if any of the following is true:

    *      A resource becomes available.
    *      A queue is nonempty.
    *      An external process finishes.

Although the thread is no longer waiting, the thread does not have to start running immediately. This is because such a thread is first put on the queue of runnable workers and must wait for a quantum to run on the scheduler.

Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout.

The contents of this dynamic management view can be reset by running the following command:
Copy

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

This command resets all counters to 0.

How to know db version or build no from a backup file? or How to get the exact version on which master db was backed up?

Generally we don't need this info for user db but when you have to recover or restore master db then you should have exact db version or build no else you cannot recover your db like i am giving you a scenario suppose you instance is corrupted now you are not able to access your sql server instance and even you don't have exact db version like 9.00.**** In this case you need exact build no else you can not recover your sql server Instance.To get this info you need a master db backup file.You can run below cmd to get this info:

Restore headeronly from disk='D:\MSSQL\Backup\master.bak'

Now output will show you lot much info like user name,LSNs,device type,server name etc but you should look into below three coloumns  to get exact version no of sql server.As per this output the version on which master db was backed up is 10.50.1600.Now you got the exact build so install sql server till this build no and restore master db easily.

SoftwareVersionMajor  SoftwareVersionMinor  SoftwareVersionBuild
10                                  50                                   1600




You can get another article regarding how to restore master db in this blog as well.Kindly find the link:Restore master db

How to recover or repair a suspect database in sql server

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc.

You can run below cmd to get about all errors due to whihc db went in suspect mode:

DBCC CHECKDB ([DBNAME]) WITH NO_INFOMSGS, ALL_ERRORMSGS

Once you have enough info about errors then your next step should be Repair.If error is showing datafiles missing then check your datafile drive connectivity from storage side if its missing then you dont need to do anythig just contact someone from storage or one who manages storage in your env and ask them to check data file drive is healthy or not.

And if there is another issue then you can repair your database from below cmds:

SP_RESETSTATUS [DBNAME]
go
ALTER DATABASE [DBNAME] SET EMERGENCY
go
ALTER DATABASE [DBNAME] SET SINGLE_USER;
go
DBCC CHECKDB ([DBNAME], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
go
ALTER DATABASE [DBNAME] SET Multi_USER;

Put your db name in place of [DBNAME] in above script.

Note:-REPAIR_ALLOW_DATA_LOSS is a one way operation i.e. once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database before executing above mentioned queries.

"Error 14258: Cannot perform this operation while SQL ServerAgent is starting. Try again later."

I saw this error today while i was diagnosing my log shipping issue in which primary and standby servers was not in sync.i checked log but i didn't find anything even Sql agent was running and all jobs was showing as successful.

Then i tried to bounce Agent service on primary node then i got below error:

"Error 14258: Cannot perform this operation while SQL Server Agent is starting. Try again later."

To fix this issue, I tried stopping and then restarting just the Agent
service and it won't start, reporting the same error then my guess was that SQL Server was running in lightweight pooling mode. I set it back to the default of
thread mode.

sp_configure 'allow updates', 1
go
reconfigure with override
go
sp_configure 'lightweight pooling', 0
go
reconfigure with override


And after this change it worked for me..

How to disable Auto Commit in SQL Server

You can turn auto commit OFF by setting implicit_transactions ON:

SET IMPLICIT_TRANSACTIONS ON

When the setting is ON, it returns to implicit transaction mode. In implicit transaction mode, every change you make starts a transactions which you have to commit manually.
And when you need to enable it just run above cmd with OFF clause.

SET IMPLICIT_TRANSACTIONS OFF

autocommit is the default for Sql Server 2000 and up.

Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Whenever you get this issue make sure your security context is correctly supplied.In that case you have to use domian user acount in sp_addlinkedserver stored procedure whihc has access rights to Active Directory.The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login: domain\user; With password: password.Find below dummy example.

Step1:-EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'

Output : Success

Step2: EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='Domainname\username', @rmtpassword='#Security@SWIM#'

Also,Make sure your SQL Server Service is running under the account which has access right to Active Directory.

How to get, when your db was restored last time?

Suppose you have to findout when you db was restored last time and by whihc user.To get this info you have to look into a system table 'restorehistory' whihc exist in system database msdb.Run below cmds to get this info:

use msdb
go
select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date]
from restorehistory
where Destination_database_name like ('qa%')

Once you will run this you will get below output.

To find out no of traces running on your db instance

Suppose you want to find out how many traces are running on your instance?
No idea???here is a solution to find out no of traces, also you can stop them by below script.

To Find out the number of traces running use the below query

SELECT * FROM fn_trace_getinfo(default);
GO
You will get below output:

You can find out the no of traces through trace ID coloumn/count.Now suppose you have to stop them then run below script:

DECLARE @TraceID int
SET @TraceID = 2
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

Replace the trace id value with one which has to be stopped.

Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix/Solution/Workaround:

Follow below steps:-
1. Move mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)
3. Stop the SQL Server service.
4. Overwrite new mdf and ndf files with the original ones.
5. Start SQL Server.
6. Run this script (Set the @DB variable to the name of your database before running):
Declare @DB sysname;
set @DB = ‘DBName’;
– Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
– Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
or
– Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
– Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

If you are not able to connect after single user then run below cmd in one go.

Alter database dbname set single_user with roll back immediate
go
Run your DBCC checkdb command
go
Alter database dbname set multi_user

I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.