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)

 

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.

ISNULL or COALESCE

So you have come across a problem in a SQL Server query where you want to find out if a value is null and if it is, use another value. Which function should you use, ISNULL or COALESCE?

Well if you just want to do something simple and to evaluate the value to see if it’s NULL and use another value, then both commands would work if the other number is NOT NULL.

If you wanted to do something more advanced and compare a series of numbers and use the first number that is not null, then COALESCE is the function that you have to use.
Run the code into a SQL Server Environment to see how COALESCE works

ISNULL vs Coalese Code Example


SET NOCOUNT ON;

GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
      WHERE name = ‘wages’)
   DROP TABLE wages;
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS ‘Total Salary’
FROM wages;

There are some things to think about when using COALESCE, if you want to evaluate a series of numbers and all the values are NULL, at least one has to be a typed NULL

e.g

SELECT COALESCE(NULL, NULL);

will result in

Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an
expression that is not the NULL constant.

but this will work

SELECT COALESCE(CAST(NULL AS INT), NULL);

Using ISNULL does not error the code, so this will work
SELECT ISNULL(NULL, NULL);

There are some minor differences between ISNULL and COALESCE and can act differently in different scenarios that you have to think about the outcome.

Did this help you, 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.

 

Rebuilding Indexes Using A Script On SQL Server

There comes a time when you have to rebuild all the indexes in a database on SQL Server, and you have to figure out a quick way of doing this. You can set up different jobs and routines to schedule the rebuild, but it is handy to have a script.

You have to rebuild indexes when you are the administrator of a database and realised it is going very slow after you have done some investigation, and you have found out that databases have not been build for a very long time (or never).

If you want a quick way to rebuild your indexes on SQL Server using a script, then use the script below.

This script will find all the indexes within a database and rebuild them. You can change the script if you like to add ONLINE rebuilding if you want your tables to be accessible through the rebuild. I have used this index rebuild many times to speed up the process of fixing performance related problem within a database when you know that there has not been a proper maintenance plan set up for the database.

This script on SQL Server 2012, SQL Server 2016 and SQL Server 2017. I have tried this script on these editions and they work and probably will work on future editions.

I would try this script out in a development environment first just to make sure you are happy with the performance because it will have a performance hit on your server when this is running. It may also take a while to run the script as this is dependant on how big your tables are and how many indexes you have.

SQL Server Rebuilding Indexes Code

DECLARE @Database VARCHAR(255) 
DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 100

DECLARE DatabaseCursor CURSOR FOR 
SELECT name 
FROM sys.databases

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
 table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
 WHERE table_type = ''BASE TABLE'''

-- create table cursor 
 EXEC (@cmd) 
 OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Table 
 WHILE @@FETCH_STATUS = 0 
 BEGIN

IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)
 BEGIN
 -- SQL 2005 or higher command
 SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD '
 
 --PRINT @cmd
 EXEC (@cmd)
 END
 ELSE
 BEGIN
 -- SQL 2000 command
 --DBCC DBREINDEX(@Table,' ',@fillfactor) 
 PRINT 1
 END

FETCH NEXT FROM TableCursor INTO @Table 
 END

CLOSE TableCursor 
 DEALLOCATE TableCursor

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor 
DEALLOCATE DatabaseCursor

Why not try it and give it a go to see if it works for you?

Please leave a comment below if this script to rebuild your indexes work or what improvements you could do to it.

SQL Server 2017 Developer Edition is FREE

GUESS WHAT!

SQL Server 2017 Developer Edition is free!
Yes that is right, you can download SQL Server 2017 for free just by clicking on this link from Microsoft

Download SQL Server 2017

The Developer Edition is the same as the Enterprise Edition, but the only difference is that you can’t use the Developer Edition in a production environment.

Downloading SQL Server 2017 Developer Edition is still great if you want to learn about all the new features that are now in SQL Server 2017.

If you don’t know what SQL Server is, well SQL Server from Microsoft it is a software solution to store and analysis data using a variety of tools.
You will mainly use
SQL Server Engine
SQL Server Integration Services
SQL Server Analysis Services

SQL Server Engine

This is where raw data is stored as information, and you can do a variety of tasks on this data to suit your needs.

SQL Server Integration Services

This tool is used to extract, transform and load information in a database or another storage locations

SQL Server Analysis Services

This is used for analysis information to try and find out patterns or summary information.

If you have never used SQL Server before, just download it and install it and try it out.

What features are you looking forward to in this Edition? Leave a comment below