While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.
Following are main database status: (Reference: BOL Database Status)
ONLINE
Database is available for access.
OFFLINE
Database is unavailable.
RESTORING
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.
RECOVERING
Database is being recovered.
RECOVERY PENDING
SQL Server has encountered a resource-related error during recovery.
SUSPECT
At least the primary filegroup is suspect and may be damaged.
EMERGENCY
User has changed the database and set the status to EMERGENCY.
Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.
1) Using T-SQL (My Recommendation)
Example:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks'
GO
ResultSet:
DatabaseStatus_DATABASEPROPERTYEX
——————————————————
ONLINE
DatabaseStatus_sysDatabase
——————————————————
ONLINE
2) Using SQL Server Management Studio
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL Database Status, BOL sys.databases, BOL DATABASEPROPERTYEX
30 Comments. Leave new
great tip. thanks. helped me solve a problem.
What is the database Status?
Please Explain about database status
Regards
Vasagar G
Database status refers to the state the database is in.Like, Normal r suspect mode, recovering mode.
hi,
I need information about the last updated database by whom and how many users logon a database
sp_who2 gives the results abt the active transactions under a server and under sql logs u can get the info u need
Hi
I’m bit confuse on Recovery_Pending and Suspect Status
SQL Server 2005 SP3
I followed this Sceanrio:
Create a new database suppose name “Test”
Stop the SQL Server
rename the exixting ../DATA/test.df file to …/DATA/test.sav
Again Restart the SQL Server
And runthe following Query
SELECT DATABASEPROPERTYEX (Test,’status’)
OUTPUT : SUSPECT
BUT When I Run:
select state, state_desc, is_in_standby from sys.databases where database_id = db_id(‘Test’)
OUTPUT
State 3, State_desc RECOVERY_PENDING, is_in_standy 0
I found there is descripancy in the result
State of Suspect is 4 in sys.databases but it shown 3
Would you please clarify this.
Thanks in advance
does this work for linkservers? I’m getting NULL values on some status. but when I run it locally, it only produces ONLINE database status. Please advice. Thanks
How does this work with database names like [test test]?
hey, where are you?
answer me!
why does DATABASEPROPERTYEX return status ‘online’ while in management studio it is called normal?
Dear Sir,
We are using Microsoft Dynamics with SQL server 2005
today I stop all new connection request and transaction from Axapta and start
shrinking log file through below mentioned command
DBCC SHRINKFILE
(LOG FILE NAME, 50) where the actual size is 67GB.
simultaneously I am getting the error in AXAPTA that
” sql error description: [Microsoft][ODBC SQL Server driver][SQL SERVER] The transaction log for database “AxDynamics” is full. to find out why space in th elog cannot be reuse, see the log_reuse_wait_desc coloum in sys.databases.
I was too much afraid. that what happens.
but however what I do that cahnge the log file location and delete the existing one.
after that problem solved.
and secondly now our database size is near to 80 GB and tooooooooooooo much slow response when query for any report.
kindly refer my any third party software and guide me about the sys.database table[that where is its location and how to solve out these type of issues.]
I ALSO WANT TO SPEED UP MY DATABASE QUERY SPEED
please tell me what is the status of databse during the backup???
how do i reset the db status from suspect to emrgency (32768)
where do i need to change the state to 32768, i mean which table,quite dificult find the table…
Is there anyway to find how much restoration is pending for a database in Restoring Mode?
This information helps for projecting the time for completion.
Thanks in Advance,
Pavan Nayakanti
I dont think there is a way of knowing it. But based on the size you can guess it from the previous experience
Hi red your tips for sql server it s simply supper..
hi Dave ,
i have logshipping database which around 700gb . recently logshipping job was failed due to insufficient space then i made some space for log file and then restarted the job but it failed . then i thought it might require restart and restarted but after restarting database showing
“RESTROING” mode very long time . i checked sql server log
2011-04-30 10:34:30.94 spid20s The database ‘xxx’ is marked RESTORING and is in a state that does not allow recovery to be run.
2011-04-30 10:34:30.94 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2011-04-30 10:34:30.94 spid5s Recovery is complete. This is an informational message only. No user action is required.
2011-04-30 10:34:31.21 spid51 Configuration option ‘Agent XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.
2011-04-30 10:34:31.54 spid51 Using ‘xpsqlbot.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_qv’. This is an informational message only; no user action is required.
2011-04-30 10:34:31.77 spid51 Using ‘xpstar90.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_instance_regread’. This is an informational message only; no user action is required.
2011-04-30 10:34:31.88 spid51 Using ‘xplog70.dll’ version ‘2005.90.3042’ to execute extended stored procedure ‘xp_msver’. This is an informational message only; no user action is required.
2011-04-30 10:34:48.93 spid52 Configuration option ‘Agent XPs’ changed from 1 to 0. Run the RECONFIGURE
after this no log showing and database still in restoring mdoe
Restore full database then start log shipping
how to check backup of the database status like % of complete backup in sql server. Can you give me t-sql statement for this.
select * from sys.dm_exec_requests
Hi,
I have 3 servers. In all the server my database is showing 0 as status . What does status 0 means.
Does 0 means its online??
In all the 3 serveres my model database status value is ‘1073741840’
If i use the databasepropertyex funtion it shows me the database is online
Please can you help me in this
Thanks,
H
How to check the procedures status ( Active/Invalid) in my Database?
Im My database(schema) having more than 50 procedures , before runing script I want to check their proc status ?Please help me on this .
can anyone create the dynamic Script to check the status of all databases at one time on any Server.
You can use a while loop that runs for each database or use undocumented stored procedure ms_spforeachdb
can you please give me the details of this store procedure how you creat this store procedure.