Solved

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

Posted on 2009-05-15
9
893 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
  • 5
  • 4
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:ddotson
Comment Utility
State doesn't seem to be a valid column.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ddotson
Comment Utility
neither does user_access.
0
 

Author Comment

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

Expert Comment

by:Aneesh Retnakaran
Comment Utility
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
Comment Utility
0
 

Author Comment

by:ddotson
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now