Finding the status of your SQL Server Database with a script

Finding the status of your SQL Server Database with a script

I have been in a position where I wanted to know the status of my databases at regular intervals and I would want this information in a script. Well here is a script that I have used in the past that once placed in a job, I could monitor if the database changes its state then of this information, it could send me an email.
It is handy to track to see if your database goes into a funny state during the day.

select name as DBNAME,getdate() as RUNDATE, STATUS, STATUS2,
CASE WHEN (STATUS & 1) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [AUTOCLOSE],
CASE WHEN (STATUS & 4) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [SELECT_INTO_BULKCOPY],
CASE WHEN (STATUS & 8) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [TRUNC_LOG_ON_CHKPT],
CASE WHEN (STATUS & 16) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [TORN_PAGE_DETECTION],
CASE WHEN (STATUS & 32) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [LOADING],
CASE WHEN (STATUS & 64) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [PRE_RECOVERY],
CASE WHEN (STATUS & 128) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [RECOVERING],
CASE WHEN (STATUS & 256) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [NOT_RECOVERED],
CASE WHEN (STATUS & 512) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [OFFLINE],
CASE WHEN (STATUS & 1024) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [READ_ONLY],
CASE WHEN (STATUS & 2048) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [DBO_USE_ONLY],
CASE WHEN (STATUS & 4096) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [SINGLE_USER],
CASE WHEN (STATUS & 32768) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [EMERGENCY_MODE],
CASE WHEN (STATUS & 4194304) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [AUTOSHRINK],
CASE WHEN (STATUS &1073741824) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [CLEANLY_SHUTDOWN],
CASE WHEN (STATUS2 & 16384) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_null_default], –This one
CASE WHEN (STATUS2 & 65536) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [concat_null_yields_null],
CASE WHEN (STATUS2 & 131072) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [recursive_triggers], — This one
CASE WHEN (STATUS2 & 1048576) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [default_to_local_cursor], –This one
CASE WHEN (STATUS2 & 8388608) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [quoted_identifier],
CASE WHEN (STATUS2 & 33554432) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [cursor_close_on_commit],
CASE WHEN (STATUS2 & 67108864) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_nulls],
CASE WHEN (STATUS2 & 268435456) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [ANSI_warnings],
CASE WHEN (STATUS2 & 536870912) = 0 THEN ‘FALSE’ ELSE ‘TRUE’ END AS [full_text_enabled]
from master.dbo.sysdatabases

If you found this script useful, just leave a comment below

SQL Server Information from a script

SQL Server Information from a script.

SQL Server has a lot of useful information that you need to administrate or develop solutions. Here is a great SQL Script to find out information about your SQL Server Instance and operating system.

-- Get selected server properties 
SELECT SERVERPROPERTY('MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName],  
SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], 
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], 
SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY('ProductLevel') AS [ProductLevel], 
SERVERPROPERTY('ProductVersion') AS [ProductVersion], SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('Collation') AS [Collation], SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled], 
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled], SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported];

-- Windows information 
SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);

-- Get configuration values for instance
SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name OPTION (RECOMPILE);

-- SQL Server Services information 
SELECT servicename, process_id, startup_type_desc, status_desc, 
last_startup_time, service_account, is_clustered, cluster_nodename, [filename]
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);


-- Get processor description from Windows Registry
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';


-- Volume info for all LUNS that have database files on the current instance 
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, 
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);


-- File names and paths for TempDB and all user databases in instance 
SELECT DB_NAME([database_id]) AS [Database Name], 
       [file_id], name, physical_name, type_desc, state_desc,
	   is_percent_growth, growth,
	   CONVERT(bigint, growth/128.0) AS [Growth in MB], 
       CONVERT(bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4 
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE)

 

Recovering from a Suspect Database

I hate when a database in SQL Server goes into Suspect mode.

If you a database administrator in SQL Server, one of the worst things that could happen to you is that you arrive at work one day and find out that one of your databases is in ‘Suspect’ mode in SQL Server.

What does Suspect mode mean, basically it means that your database is not working and you better hope you have a backup SQL Server. 🙂

You should always have a good backup and restore strategy in place for recovering from a Suspect database, but if you have not, there might be a temporary way of you turning on the database again to pull data out.
You should not use the below code as a way to recover then use the database because from my experience; your database is corrupted, (even if you run DBCC CHECKDB as it tells you everything is ok)

Recovering From Suspect Mode Code In SQL Server

EXEC sp_resetstatus <sql server database name>;
ALTER DATABASE <sql server database name> SET EMERGENCY
DBCC checkdb(<sql server database name>)
ALTER DATABASE <sql server database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (<sql server database name>, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE <sql server database name> SET MULTI_USER

Once you have run this code SQL Server., you are freely available to have a look at your data. My suggestion is that you copy all of the data into another database and use the new database in the future. Moving the data may take some time, but it will be worth it in the end. If you use the Suspect database in the future, what could happen is that the Suspect database might become corrupted again and there will be no fix for it. Also if you continue to use the Suspect database, your backups also have hidden faults in there.
After you have moved your data to a new database, I would suggest you investigate why the database went into the ‘Suspect’ mode in the first place SQL Server.

This could have happened from a power failure or a corrupted hard drive, but you need to make sure you find the root-cause because it could happen again.

Did this help? leave a comment below.