Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1224
  • Last Modified:

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

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
ddotson
Asked:
ddotson
  • 5
  • 4
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT *
FROM master..sysdatabases
WHERE State = 4 --  will list out the databases in suspect mode
or user_access  = 1 --single user mode
0
 
ddotsonAuthor Commented:
Aha!

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

How would I do that?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ddotsonAuthor Commented:
State doesn't seem to be a valid column.
0
 
ddotsonAuthor Commented:
neither does user_access.
0
 
ddotsonAuthor Commented:
I put one database in single user mode and he "status" column changed....
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
ddotsonAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now