TSQL Script to Free Disk Space in SQL Server

We need to check for a minimum amount of free space before proceeding with some processes that run on SQL Server 2000\2005\2008\2008R2\2012\2014\2016 and 2017 SQL Server instances.

Checking for free disk space before proceeding with a process is a wise move if disk space is tight or a high percentage of the drive is needed for the process.  It is disconcerting to have a process run for hours only to fail towards the end of the process due to insufficient disk space.  Although a few different options are available to check for disk space (CLR, WMI, PowerShell, etc.) in SQL Server.

Sample Stored Procedure to Assess the Free Disk Space on a SQL Server Disk Drive

In the sample stored procedure below, it is accepting a parameter for the minimum amount of megabytes (MB) free on a specific disk drive, then executing the master.sys.xp_fixeddrives extended stored procedure into a temporary table.  Once the data is in the temporary table the current amount of free disk space is compared to the minimum amount of free disk space to determine if the process should continue or raise an error.

There are different ways to do the same . But I am following with a customized script which allows us to perform below things from one Script :

  • Free up space on drive
  • you can perform shrink operation on mdf or ldf files by using the same script
  • You can also delete Old backup file on Disk which are not required .

Please go through below link to find the script :

https://sqlserveradmintutorial.blogspot.com/2018/06/t-sql-script-to-free-disk-space-in-sql.html

NOTE : I have been using this script without any issues .But still please do some testing of this script before executing it in your Production.

 

Script to Find SQL Server Installation Date and Time

How to get SQL Server Installation Date and Time ?

I have been asked this question by one of my colleague says ‘ How retrieve SQL Server Installation Date and Time ‘ . I immediately told him to do some research on this. Yes, he did and  came back to me with a script which gives you the required output Installation Date and Time of SQL Server. 

This is a Awesome script to find SQL Server Installation Date and Time. I asked him how you have did this, he said this script was found in one of the blog when doing google search. Search Online and get your solutions . I also googled to see who has developed this script but i saw same script in most of the blog and sites . Also not sure who is the original  creator of this script . We have to thank the person who has written this script.

For getting the exact SQL Server installation date we have to check for the object which is created at the time of installation. NT Authority\System login is getting created at the time of SQL Server installation. You can check the SQL Server installation date by querying the sys.syslogins or sys.server_principals view against the login NT Authority\System name.

NT Authority\System login which has unrestricted access to all local system resources and it is a member of the Windows Administrators group on the local computer with the sysadmin fixed SQL Server role.NT Authority\System login get created at the time of installation of SQL Server.

We can query one of the views to get the installation date. If your SQL Server is English Language compatible you can directly query by login name or for the other languages we will use the neutral language (hexadecimal code) which is same on every instance.

— work with only English language installations
SELECT createdate as ‘SQL Server Installation Date’
FROM sys.syslogins
where name = ‘NT AUTHORITY\SYSTEM’

–neutral language
SELECT createdate as ‘SQL Server Installation Date’
FROM sys.syslogins
where sid = 0x010100000000000512000000

–Using sys.server_principals
SELECT create_date as ‘SQL Server Installation Date’
FROM sys.server_principals
WHERE name=’NT AUTHORITY\SYSTEM’

–Sample CMDB Query
SELECT SERVERPROPERTY(‘productversion’) as ProductVersion
,SERVERPROPERTY (‘productlevel’) as ProductLevel
,SERVERPROPERTY (‘edition’) as Edition
,SERVERPROPERTY (‘MachineName’) as MachineName
,SERVERPROPERTY (‘LicenseType’) as LicenseType
,SERVERPROPERTY (‘NumLicenses’) as NumLicenses
,create_date as ‘SQL Server Installation Date’
FROM sys.server_principals
WHERE name=’NT AUTHORITY\SYSTEM’

Where SID 0X010100000000000512000000 is belongs to user “NT AUTHORITYSYSTEM“. This user create at the time of installation only.

If you have evaluation edition installed in your machine and you want to find the expire date when it is going to expire.

All knows SQL Server Evaluation Version Period is 180 days . If you are looking for a script to get the Expiry Date of SQL Server Evaluation Version

Below query is the query to find the answer and enter the product key to activate the SQL Server license.

— Evaluation version expire date
SELECT create_date as ‘SQL Server Installation Date’,
DATEADD(dd,180,create_date) as ‘Expiration Date’
FROM sys.server_principals WHERE name=’NT AUTHORITY\SYSTEM’

Hope this helps you a lot .

Apply SQL Server service pack In a Single Step using Command Prompt

SQL Server Patching

I’m going to share something that I use whenever I patch a server, as I am fairly sure that it will help anyone else who has to patch SQL Server on a regular basis. Especially those of us who manage large SQL Server estates.

Applying SQL Service Pack can be performed by Using Command Prompt in a Silent mode.

  1. Create folder D:\install on the windowsbox or create this folder on a fileserver.
  2.  Download SQL Server 20XX Service Pack X (SQLServer20XXSPX-KBXXXXXX-XXX-LLL.exe) from Microsoft Siteand save the file to D:\install or on to the network shared drive.
  3. Click on the Start button on the task bar, click run, type the command CMD.exe, open this by Run as Administrator ,which opens the MS-DOS shell command prompt.
  4. Now root the command prompt directory to D:\Install directory as SQL SP is placed on this location.

If your SQL SP is network Share and you want to copy the file to local target server , then we use Robocopy method from command prompt to copy the file

Execute below Robocopy command in Command Prompt for copying a File:

Robocopy “\\NetworkShare\Folder\SPX” “D:\Install\SPX” /MIR

After copying the file run below command in Command Prompt for Clustered Instances:

“D:\Install\SPX\SQLServer20XX-KBXXXXXX-x64.exe” /SkipRules=Cluster_IsOnlineIfClustered /action=Patch /allinstances /qs /IAcceptSQLServerLicenseTerms

The above command will patch for all Instances installed on the Machine or WindowsBox.

Run below command in Command Prompt for Standalone Instances :

D:\Install\SPX\SQLServer20XXSPX-KBXXXXXXX-x64-ENU.exe /allinstances /quiet

The above command will patch for all Instances installed on the Machine or WindowsBox.

If you want to install a Service pack to Specific Instance from command prompt use below command :

D:\Install\SPX\SQLServer20XXSPX-KBXXXXXXX-x64-ENU.exe /instancename=YourInstanceName /quiet

The /quiet switch runs the update in unattended mode in the background .

Hope this  post saves your time for installing sql service pack and it helps you a lot.

Powershell Script to find Sql services Version , Edition Details and store the result to .CSV file

Inventory to Get SQL Services Version and Edition Details

We have a class named ManagedComputer in SQL SMO, which provides information about all SQL Server services like SSAS, SSIS, SSRS, and SQL Server Database Engine installed on a computer. By this information, we can take a different method to retrieve version and edition values of each service. Below are points in designing the solution:

  • For SQL Server service, we use SMO Server class to get version/edition values.
  • For SSAS service, we use AMO Server class to get version/edition values.
  • For SSRS service, it is some what tricky. For SSRS 2005, we use SSRS executable file version as SSRS version number, and we use WMI class MSReportServer_Instance to find the edition value. For SSRS 2008+ version, we simply use MSReportServer_Instance to get both version and edition values.
  • For SSIS service, it is some more trickier because Microsoft never stores SSIS edition anywhere, or in other words,  there is no edition property for SSIS service. But actually in logic, it did not make any sense because if we install a Standard Version on my computer along with SSIS, I expect ]SIS to be Standard edition (instead of Enterprise ). So here, we use file version of the SSIS executable file as SSIS version and then using registry data to get the SSIS edition data.
  • Here we are using PowerShell to go through the whole logic and save results to a data table, which will be saved to a csv file or also can be saved to a SQL Server table.

The PowerShell code below is to be run on a computer with PowerShell version V3+ and SQL Server 2012 Client Tools SDK to be installed (this is minimum requirement), and you should have permissions to access all servers you want to scan. In our case, I have local admin privileges on all servers against which the script runs.

In script we have to change some parameters like :

  • Give your Server Name or Machine
  • change path where ever you want to store .CSV file or create the folder C:\temp
#Requires -version 3.0
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";
add-type -AssemblyName "Microsoft.AnalysisServices, version=11.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91";

$data_table = New-Object "system.data.datatable";
$col = New-Object "system.data.datacolumn" ('MachineName', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('ServerInstance', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Type', [System.String]); #type=SQLServer / AnalysisServer / ReprtServer / IntegrationService
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Version', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('Edition', [System.String]);
$data_table.columns.Add($col);
$col = New-Object "system.data.datacolumn" ('ServiceAccount', [System.String]);
$data_table.columns.Add($col);




[string[]]$server_list= 'NODE1';#, 'SvrQA26', 'SvrDev26', 'SvrPre57','SvrQA04'; #change to your own server names
# [string[]]$server_list = gc -path 'c:\temp\server_list.txt' #you can put your server list in a text file, each [ServerName] uses one line

foreach ($machine_name in $server_list)
{ "processing : $machine_name";
try
{
$mc = new-object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $machine_name;
$mc.services | ? {($_.type -in ("SqlServer", "AnalysisServer", "ReportServer", 'SqlServerIntegrationService') ) -and ($_.ServiceState -eq 'Running')} | # % {($_.name).contains("$")}
% { $s = $_.name;
[string]$svc_acct = $_.ServiceAccount;
switch ($_.type)
{ "sqlserver" { if ($s.contains("$")) {$sql_instance= "$($machine_name)\$($s.split('$')[1])"} else {$sql_instance=$machine_name;}
$sql_svr = new-object "microsoft.sqlserver.management.smo.server" $sql_instance;
$row = $data_table.NewRow();
$row.Edition = $sql_svr.Edition;
$row.Version = $sql_svr.Version;
$row.Type = 'SQLServer';
$row.ServerInstance = $sql_instance;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
} #sqlserver

"AnalysisServer" { if ($s.contains("$")) {$as_instance= "$($machine_name)\$($s.split('$')[1])"} else {$as_instance=$machine_name;}
$as_svr = New-Object "Microsoft.AnalysisServices.Server";
$as_svr.connect("data source=$as_instance");
$row = $data_table.NewRow();
$row.Edition = $as_svr.Edition;
$row.Version = $as_svr.Version;
$row.Type = 'AnalysisServer';
$row.ServerInstance = $as_instance;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
} #AnalysisServer

"ReportServer" {
$pathname = ($mc.services[$s]).PathName;
$pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '')

$item=get-item $pathname
[string]$ver='V' + ($item.VersionInfo.ProductMajorPart).ToString();
[string]$file_version = $item.VersionInfo.ProductVersion;

if ($s.Contains('$')) # this is a named instance of SSRS
{

[string]$instance_name = (($s.split('$'))[1]).replace('_', '_5f'); #SSRS instance name is encoded
[string]$rs_name="RS_$($instance_name)";
}

else
{
[string]$instance_name = 'MSSQLSERVER';
[string]$rs_name='RS_MSSQLServer';
}

if ($ver -eq 'V9')
{
#sql 2005 SSRS
gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\V9” -ComputerName $machine_name |
Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } |
% { $row = $data_table.NewRow();
$row.Edition = $_.EditionName;
$row.Version = $File_Version;
$row.Type = 'ReportServer';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);

}

}
else
{
gwmi -class MSReportServer_Instance –Namespace “root\microsoft\sqlserver\reportserver\$rs_name\$ver” -ComputerName $machine_name |
Where-Object {$_.__Path -like "*InstanceName=`"$($instance_name)`"" } |
% { $row = $data_table.NewRow();
$row.Edition = $_.EditionName;
$row.Version = $_.version;
$row.Type = 'ReportServer';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);
}
}
} #ReportServer
'SqlServerIntegrationService' {
$pathname = ($mc.services[$s]).PathName;
$pathname= "\\$machine_name\" + ($pathname.replace(':\', '$\')).replace('"', '');

$item=get-item $pathname;
[string]$ver= ($item.VersionInfo.ProductMajorPart).ToString() +'0';
[string]$file_version = $item.VersionInfo.ProductVersion;

#finding the SSIS edition by reading the registry
$key="SOFTWARE\MICROSOFT\Microsoft SQL Server\$ver\Tools\Setup";
$type = [Microsoft.Win32.RegistryHive]::LocalMachine;
$regkey=[Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $machine_name);
$r=$regkey.OpenSubKey($key).GetValue('edition');

$row = $data_table.NewRow();
$row.Edition = $r;
$row.Version = $file_version;
$row.Type = 'IntegrationService';
$row.ServerInstance = $s;
$row.ServiceAccount = $svc_acct;
$row.MachineName=$machine_name;
$data_table.Rows.Add($row);

} #sqlserverIntegrationService

}#switch
}
}#try
catch
{
Write-Error $Error[0].Exception
}
}#foreach

$data_table | select machineName, serverinstance, type, version, edition, ServiceAccount | ft -auto

#if you want to export to an csv file, you can do the following, assuming you have c:\temp\ folder
$data_table | select machineName, serverinstance, type, version, edition, ServiceAccount | export-csv -path c:\temp\test.csv -notypeinfo -Force

PowerShell Script to Check SQL Services status,start,stop,restart

There are many ways to check the sql services status . we will start with simple command in PowerShell ,ask PowerShell to tell you all of the commands related to services by using below command:

Get-Command -Noun Service

From the above screen shot look at the commands which we use to check the sql services status, start, stop and restart :

Get-Service ServiceName – this is to check the sql services status like Running\Stopped

Example:

Get-Service ‘MSSQL$*’ – This command gives all the Named Instances Installed on your Windows Box

Get-Service ‘MSSQL*’ – This command gives both Named and default Instances Installed on your Windows Box.

start-Service ServiceName – This is to start your specified service

Example :

start-Service MSSQLSERVER – command for Default instance start

start-Service MSSQL$SQL1 – Command for Named Instance start

stop-Service ServiceName – This is to stop your specified service

Example :

stop-Service MSSQLSERVER – command for Default instance stop

stop-Service MSSQL$SQL1 – Command for Named Instance stop

Restart-Service ServiceName – This is to restart your specified service

Example :

Restart-Service MSSQLSERVER – command for Default instance Restart

Restart-Service MSSQL$SQL1 – Command for Named Instance Restart

All the above commands will not work as we have dependent Agent service for Database Engine. So, we need to use -Force parameter in command to force restart as shown below :

restart-service -force MSSQLSERVER – for default Database engine restart

restart-service -force SQLSERVERAGENT – for default Agent engine restart

restart-service -force ‘MSSQL$SQL1’ -for Named Database engine restart

restart-service -force ‘SQLAgent$SQL1’ – for Named Agent engine restart

We can also use below PowerShell script to check the sql services status :

By using below PowerShell Command you can find all SQL related services on our Windows Machine :

Get-Service *sql* |select-object ServiceName,status|Format-Table -AutoSize

OR

By using below PowerShell Command you can find all SQL related services on Node1 Server

Example :

Get-Service -ComputerName NODE1 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

OR

By using below PowerShell Command you can find all SQL related services on multiple servers[Separate each server name by comma]

Get-Service -ComputerName NODE1,NODE2 | ?{$_.Displayname -like “*SQL*“}|select name,Displayname,status

Hope this post helps you a lot, I’ve tested above scripts by executing it on SQL Services(both default and Named instance) from my end and works good . Please do some testing before executing it in production environment.