?
Solved

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

Posted on 2009-05-15
9
Medium Priority
?
998 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

766 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