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)

 

Configuring SQL Server 2017

Configuring SQL Server 2017

How that you have installed SQL Server 2017, it is time to get your hands dirty and start to create databases. But before we do that, it is a good idea to configure your SQL Server 2017 environment correctly.

To do this, just Right Click the server and click properties.

Configuring SQL Server 2017
Configuring SQL Server 2017

 

Once you have done that, the following screen should appear.

Configuring SQL Server 2017
Configuring SQL Server 2017

Here is where you can set some properties for your SQL Server 2017 environment that will help you control the performance, administration and security of your databases.

In the General tab, you can see it just shows you general information about your server that you may need to know from time-to-time.

The Memory tab controls how much of the operating system SQL Server will use. Most of the time, the only option to you need to change on this screen is the Maximum Server Memory option. There are many calculators on the internet on how to work out the correct value, but if you just want a good value, subtract 4-8 gig from the total amount of memory you have on your pc/laptop and put in the value i.e.
So If you have 14 gigabytes installed on your hardware, type in ‘10000’ and that will mean SQL Server will use 10 gigabytes and 4 gigabytes will be used for Windows. You might need to type in a less number if you have other memory intensive software on your hardware.

Processor tab, you don’t usually change anything in this tab so just leave things as default.

Security tab, again you don’t change anything in this tab unless you have a security issue that you need to resolve. In a working environment, you might need to audit more processes in your situation so changing some of the settings for security purposes might need to happen. In a testing environment, I usually leave these as default.

The Connection tab governs how many connections you want your SQL Server to handle and if you’re going to allow remote connections to your server.

The Database Settings tab is where you can set where decide files are stored on your hard drive. Databases have two files when they are created, an MDF file and an LDF File. It is good practice to place this files on different hard drives for performance. In this table, you can set the default location on where this files will be created.

For setting up the environment for testing, you don’t need to change the ‘Advanced’ and ‘Permissions’ tabs but future posts, I will go into more details.

This is just a simple guide on how to configure your SQL Server 2017 environment and will go into more details in future blogs. If there is anything you want me to cover in more detail, just leave a comment below.

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.

Install SQL Server 2017:Installation

SQL Server 2017 Installation Guide

If you have downloaded SQL Server 2017 and you want to install it but you are not too sure how to? Well, this is a guide will help you through the steps needed to install SQL Server 2017.
I will presume you have already downloaded SQL Server 2017 so the first thing to do after you have download SQL Server 2017 is to run the setup file.

Install SQL Server Developer Edition
Install SQL Server Developer Edition

You should see a screen like the above.
After the splash screen has finished loading, you will see several options that you can pick.

Install SQL Server Developer Edition
Install SQL Server Developer Edition

I will show you how to do a custom install of SQL Server so you can see the options that are available to you. Press the ‘Custom’ button then on the next screen press ‘Install.’

You should see a screen like this if anything needed downloading.

Install SQL Server Developer Edition
Install SQL Server Developer Edition

Once this is complete, it is now time to install SQL Server 2017.
If you click on ‘Planning’ option, you can navigate through all the options to see if your PC/laptop has the correct specifications to run SQL Server 2017. After you are satisfied that your PC/laptop will be able to run SQL Server 2017, click the Installation tab and Click “New SQL Server stand-alone installation or add features to an existing installation.”

On the Product Key screen press Next (Developer should be highlighted)
On the Licence Screen press Next, after you have read the terms and conditions (I bet you will not read the terms and conditions)
Check the box if you want the Microsoft Updates, it is a good idea to accept them.
Click next until you come to Install Rules and if you cannot click Next on this screen, fix any errors that it shows you.
On the feature selection screen, you have many options that you can select for features you want to install within SQL Server. The only one that you need to install is the database engine so select ‘Database Engine Services’ and press next.

 

Install SQL Server Developer Edition
Install SQL Server Developer Edition

 

On the Instance Configuration screen, you have two choices; you can pick Default Instance or Named Instance.
An Instance is the name of your SQL Server.
You will use Default Instance if this is going to be the first installation of a SQL Server.
You would use Named Instances if you wanted to install multiple SQL Servers on to the same PC/Laptop
InstanceID is usually the name of your PC/Laptop if you have selected the Default Instance. If you choose Named Instance, then you can give the new installation a name. So if your PC is called ‘SELECTSQLSERVER’ and you wanted your SQL Server to have a special name like ‘STOCK’ then you would type in STOCK in the InstanceID.
I will show in a later blog on how you would connect to a named server and default server.
Click Next on the Instance Configuration.
Server Configuration – Click Next
Database Engine Configuration– Click Add Current User. You have two ways to connect to SQL Server. Windows Authentication or Mixed Mode. You will use Mixed Mode if you want to access SQL Server without a Windows Account. Click Next
On the Ready To Install screen, click install.
That is is, let me know in the comments below if you have trouble installing SQL Server 2017.

Constraints in SQL Server

Constraints in SQL Server play a big role in keeping data accurate in a database.

Within SQL Server there are several methods of doing this and one way is using constraints
• NOT NULL – Indicates that a column cannot store a NULL value
• UNIQUE – Ensures that each row for a column must have a unique value
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY – Ensure the referential integrity of the data in one table to match values in another table
• CHECK – Ensures that the value in a column meets a specific condition
• DEFAULT – Specifies a default value when specified none for this column

This is the code to assign a NOT NULL constraint to a Column. Column1 doesn’t allow NULLS, Column2 allows NULLS.

CREATE TABLE TestTable1
(
PKID int NOT NULL,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

This is the code to assign UNIQUE constraint to a Column

CREATE TABLE TestTable2
(
PKID int NOT NULL UNIQUE,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

This is the code to assign Primary Key constraint to a column

CREATE TABLE TestTable3
(
PKID int PRIMARY KEY,
Column1 varchar(255) NOT NULL,
Column2 varchar(255)
)

If you assign a primary key to a column, it automatically gains a unique constraint. You can have many unique constraints on a table, but you can only have one primary key. Each table should have a primary key and primary keys cant have null values.

This is the code to assign a foreign key constraint to a column
CREATE TABLE TestTable4
(
P_Id int PRIMARY KEY,
PKID2 int NOT NULL,
Column1 int NOT NULL,
CONSTRAINT FK_CON FOREIGN KEY (P_Id)
REFERENCES TestTable3(PKID)
)

A foreign key constraint is used to make sure that data can’t be deleted if it matches data in another table.

This is the code to assign a CHECK constraint to a column

CREATE TABLE TestTable5
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL,
CONSTRAINT chk_Column1 CHECK (Column1> 0 AND Column2=’Test’)

)

This makes sure that the value in Column1 is greater than 0 and Column2 contains the word ‘Test.’

This is the code to assign DEFAULT constraint to a column
CREATE TABLE TestTable6
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL DEFAULT (‘Test’),

)

If a value is not entered in column2, the word ‘Test’ will be inserted.

JOIN and APPLY

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

So you want to improve your SQL skills and have come across some new operators like JOIN and APPLY and are wondering when you should use them.

In this blog, I will focus on the following operators in the simplest terms and show you how to use them.

CROSS APPLY and INNER JOIN are very similar and
OUTER APPLY and LEFT JOIN are very similar.

In certain situations, you can write queries using CROSS APPLY or INNER JOIN and use OUTER APPLY and LEFT JOIN and they could bring back the exact results.

The only real difference is that CROSS APPLY and OUTER APPLY are better than LEFT and INNER JOIN when you are using a function, or you want to write an advanced SQL Command to bring back certain results

Run the following script in a SQL Server Environment to see these operators in action.

You can examine the script to see how they differ and you should be able to play around with the text to fully understand what is going on.
create table Company (

    companyId int identity(1,1)

,   companyName varchar(100)

,   zipcode varchar(10) 

,   constraint PK_Company primary key (companyId)

)

GO

 

create table Person (

    personId int identity(1,1)

,   personName varchar(100)

,   companyId int

,   insertdate DATETIME

,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)

,   constraint PK_Person primary key (personId)

)

GO

 

insert Company

select ‘ABC Company’, ‘19808’ union

select ‘XYZ Company’, ‘08534’ union

select ‘123 Company’, ‘10016’ 

 

insert Company

select ‘No Employees’, ‘11111’ 

 

 

insert Person

select ‘Alan’, 1, ‘20141011 09:00:00’ union

select ‘Bobby’, 1,’20141011 10:00:00′ union

select ‘Chris’, 1, ‘20141011 11:00:00’ union

select ‘Xavier’, 2, ‘20141011 12:00:00’ union

select ‘Yoshi’, 2, ‘20141011 13:00:00’ union

select ‘Zambrano’, 2, ‘20141011 14:00:00’ union

select ‘Player 1’, 3, ‘20141011 15:00:00’ union

select ‘Player 2’, 3, ‘20141011 16:00:00’ union

select ‘Player 3’, 3, ‘20141011 17:00:00’

 

 

/* using CROSS APPLY */

select *

from Person P

cross apply (

    select *

    from Company C

    where p.companyid = c.companyId

) CompanyPerson

 

/* the equivalent query using INNER JOIN */

select *

from Person p

inner join Company c on p.companyid = c.companyId

 

 

/* using CROSS APPLY – brings back the oldest inserted person by company*/

select *

from  Company c 

cross apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

 order by insertdate

 

) CompanyPerson

 

 

/* using OUTER APPLY – brings back all the companies and the oldest inserted person by company*/

select *

from  Company c 

outer apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

 order by insertdate

 

) CompanyPerson

order by companyName desc

 

/*  brings back all the companies and the people */

select *

from company c

left join person p on p.companyid = c.companyId

order by companyName desc

 

The script produces the following results

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY
CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

I hope the above examples help you understand the difference. Leave a comment below if you want to leave a comment.