Solved

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

Posted on 2009-05-15
9
918 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
need assistance with a VBscript 3 42
SQL Get Store Procedure Column Name As Row 5 41
SQL- GROUP BY 4 25
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

726 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