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

Do you need SQL Help ?

If you are in need are SQL Help but can’t afford a full-time employee, ask me and I can help you solve your problems remotely.

I am Microsoft Certified in SQL Server with over 20 years experience in using SQL Server.

I specialise in SQL Server Development and Design using T-SQL and SSIS and SQL Server Performance Tuning.

The advantages of doing this are that you get a fixed price for a piece of work or task that needs to be done without the hassle of employing somebody fulltime.

Just use the ‘Remote SQL Help‘ tab at the top of the screen to contact me.

 

What is a database?

What is a database?

I thought I would write this article for people who have no experience in SQL Server or know what a database is.

A database is a method of storing information where that information can be accessed to answer or to solve questions. There are many types of databases that you can use from many different companies like
• Oracle
• Microsoft
• MySQL
• IBM DB2
• SAP
With every database, you have to ability to insert, delete, update and search information that is stored within them.
Here are some examples that you would use a database
• Storing your music collection information
• Storing a sports team activity
• Storing pupils attendance at a school.

A vast amount of things that you use today will have some built-in database system for them to store data, i.e. your mobile phone.
Microsoft have a database software called SQL Server which they have been developing since 1989 and the current version of SQL Server is called SQL Server 2017.

There are many different versions of SQL Server 2017 that are designed for different people and different solutions. The main versions that people use are
• SQL Server Enterprise Edition
• SQL Server Standard Edition
• SQL Server Business Intelligence Edition
• SQL Server Developer Edition
• SQL Server Express

SQL Server Enterprise, SQL Server Standard and SQL Server Business Intelligence Edition all cost money to use in a production/business environment
SQL Server Developer Edition is free and can only be used on test systems and cannot be used in production/business environment
SQL Server Express can be used in a test environment and production environment for free.
SQL Server Enterprise and SQL Server Developer have all the features enabled and SQL Server Express has limited features enabled.
Within SQL Server, you can create a database and a database is a collection of tables that store the data. You access this data by using a special database language called T-SQL or you can use a frontend application as well.

If you found this useful, just leave a comment below

Query Store in SQL Server 2017

Performance Tuning with Query Store in SQL Server 2017

If you want to monitor the performance of your queries within SQL Server 2017, you can with a feature called Query Store. Query Store monitors changes to your queries to see if performance deteriorates or improves when the query plan changes.
When you create a new database,  is disabled by default; you need to enable this feature.

To enable Query Store, do the following steps
1. In Object Explorer, right-click a database, and then click Properties.
2. In the Database Properties dialogue box, select the Query Store page.
3. In the Operation Mode (Requested) box, select On.

You would use Query Store when you want to
• Find queries that have become slow over time and fix them by forcing the queries to use a different query plan.
• Find out how many times a query was run in a set period.
• Find out how many times the query plan was changed.
• Analysis why your query plan changes.

The best way to learn about

is to play around with it so just enable it and refresh the database, so it looks like this

 

Query Store in SQL Server 2017
Query Store in SQL Server 2017

Then just double click on each item in the query store to see what they do. It is better to enable Query Store on a database that has many transactions so you will be able to see results appear fast. I would suggest using a development server to test this feature as turning on this feature can have a minor effect on performance on your server.

 

If you have any comments on this article, 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)

 

SQL Server Management Studio

After you have downloaded the Microsoft SQL Server Management Studio, you will find it is easy to install. Just double click the setup file and you should see something like this.

SQL Server Management Studio
SQL Server Management Studio

Just click the ‘Install’ button.

After you have installed the software, you can find the SQL Server Management Studio in Windows menu. When you open the software, you should see a splash screen.

Once the software is open, you should see a screen like this.

SQL Server Management Studio
SQL Server Management Studio

I will write more blog posts on how to use SQL Server Managment Studio in the future.

If you find this useful, just leave a comment below.

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.