Solved

Quick way to Check if Database is in Single User or Suspect Mode

Posted on 2009-05-15
9
956 Views
Last Modified: 2012-05-07
Hello:

I have a task where I need to check 30+ servers, each having multiple databases to see if any of the databases are in Single User Mode or Suspect Mode.  I'd like to automate this with some sort of a script or batch file.  These are MS SQL 2000 databases.

Any ideas?

Thanks,

Dan
0
Comment
Question by:ddotson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24395414
SELECT *
FROM master..sysdatabases
WHERE State = 4 --  will list out the databases in suspect mode
or user_access  = 1 --single user mode
0
 

Author Comment

by:ddotson
ID: 24395432
Aha!

I've seen where there is the ability to do this from the command line.

How would I do that?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24395460
u have to use OSQL command in the commad propmt
http://technet.microsoft.com/en-us/library/aa213090.aspx
 
again u have to use the same query in the osql
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ddotson
ID: 24395858
State doesn't seem to be a valid column.
0
 

Author Comment

by:ddotson
ID: 24395872
neither does user_access.
0
 

Author Comment

by:ddotson
ID: 24395917
I put one database in single user mode and he "status" column changed....
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24396530
I thought u are using sql sever 2005/2008,
in sql server 2000, u have to use 'Status' column

From BOL:
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
 
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24396646
0
 

Author Comment

by:ddotson
ID: 24399200
This worked better than I expected.  Here's my final code (runs from a batch file):

C:\Program Files\Microsoft SQL Server\90\Tools\binn\sqlcmd" -Q "SELECT CONVERT(char(10),GETDATE(), 101) as Date, LEFT(Name, 6) AS [Database], status AS StatusCode, CASE status when 0 Then 'OK' when 4096 Then 'Single User Mode' Else 'Unknown' END AS Status FROM sysdatabases WHERE name LIKE 'DB%'" -o c:\databasecheck.txt


Here's the SQL formatted properly:

SELECT CONVERT(char(10),GETDATE(), 101) as Date, LEFT(Name, 6) AS [Database], status AS StatusCode,
CASE status
   when 0 Then 'OK'
   when 4096 Then 'Single User Mode'
   Else 'Unknown' END AS Status
FROM sysdatabases
WHERE name LIKE 'databaseName%'

Outputs to a text file like this:
Date       Database StatusCode  Status          
---------- -------- ----------- ----------------
05/15/2009 DB0               0 OK         
05/15/2009 DB1               0 OK         
05/15/2009 DB2            4096 Single User Mode         
05/15/2009 DB3               0 OK
05/15/2009 DB5               8 Unknown         
05/15/2009 DB6               8 Unknown         
 
(6 rows affected)

Open in new window

0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question