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.

 

Migrate SQL Server 2005 Database to 2014

Side-by-Side Migration from SQL 2005 to SQL 2014

First, you need to review what has changed with each versioned release to understand how those changes could affect your application.Below is a breakdown of the categories of each change:

  • Discontinued Features: Features that are no longer available
  • Deprecated Features: Features that will be removed in a future release
  • Breaking Changes: Database engine changes that will affect applications
  • Behavioral Changes: Database engine changes that could affect applications
  • Run the SQL Server Upgrade Advisor

The SQL Server Upgrade Advisor is a great tool provided by Microsoft to help you understand what needs to be changed within the database before and after migration. The Upgrade Advisor will capture the majority of issues to help make your migration successful, but keep in mind, it is not going to provide you each and everything in detail. Be sure to review all the breaking and behavioral changes mentioned above.

  • Back Up the Database

Protect yourself and your data by taking a full backup of the database prior to any migration. This way you have the ability to recover the database in the event anything goes sideways with the migration.

Migration

With planning work complete, we can now move on to the migration. Each scenario below assumes there is a shiny new SQL Server 2014 instance as the migration target. In-place upgrades should typically be avoided as they can be very risky with minimal rollback options.

With that said, there are few ways we will discuss to migrate a database to SQL Server 2014:

  • Backup and Restore

This is the safest and simplest migration, as a full database backup can be restored to the 2014 instance without affecting the 2005 instance, leaving us with a stable rollback option. To minimize downtime, the 2005 instance can remain online while the full backup is restored to the 2014 instance. Once the restore of the full backup has completed and we are ready to migrate, a differential backup can be used to further synchronize the 2014 instance. At this point you’ll need a maintenance window to take the 2005 instance database offline, take a final transaction log backup on the 2005 instance and restore the transaction log backup to the 2014 instance. Now that the databases are fully synchronized we can point the application to the 2014 instance and validate.

This approach helps to mitigate risk as there is a simple rollback to the 2005 instance if anything goes wrong during the migration window.

  • Detach and Attach

Another simple migration option is to detach the database from the 2005 instance and then attach the database to the 2014 instance. This is a good option if your storage system is not changing and your database size might make it prohibitive to consume storage for duplicate copies of multiple databases. (Please note: This won’t allow for a quick rollback option if something goes wrong with the migration. You can restore your initial backup before migration to the 2005 instance, but that will take time depending on the size of the database.).

  • Server Objects

Be sure to identify and migrate server logins, SSIS packages, SQL Jobs, and linked servers. This can be a great time to clean up and carry forward only the needed server level objects. For assistance with identifying and scripting logins and roles for a migration see here.

But what about migrating DTS packages? Unfortunately, DTS is a discontinued feature that is no longer supported, which means all DTS packages need to be converted to SSIS packages moving forward. Microsoft shipped a DTS Package Migration Wizard in SQL Server 2008 and 2008 R2 but it only worked for very simple packages, resulting in rewrites for most packages.

DTS xChange is a Microsoft-recommended third-party tool and available from Pragmatic Works. While this tool does have an associated cost, it is more successful at converting DTS packages to SSIS packages and can help avoid long hours rewriting packages manually.

Post Migration

After the migration is complete it is important to perform the following tasks:

  • Upgrade Database Compatibility Level
    • The database compatibility level needs to be manually changed on the 2014 instance
  • Run DBCC CHECKDB WITH DATA_PURITY
    • Checks for invalid data values based on column data type
  • Run DBCC UPDATEUSAGE
    • Corrects page count inaccuracies to reflect accurate information when using sp_spaceused
  • Run sp_refreshview
    • Ensures that views are up to date
  • Update Statistics
    • Brings database statistics are up to date to allow the query optimizer to choose the best query plan
  • Take a Full Backup of the Database
    • Make sure you can recover the database with all of the migration efforts

 

SQL Server Components Check Utility Script

T-SQL Script which returns information about the SQL Server, Full-Text, SQL Server Agent services,Browser Services,Integration Services,Reporting Services and Analysis Services in the current instance of SQL Server.

To get installed services of the current instance ,this Script needs to be executed after connecting the instance in SSMS and open new query window ,paste the below code to get result.

And in result panel we are going to get below columns :

  • Physical ServerName
  • SQL Instance Name
  • SQL Server Services
  • Current Service Status
  • Date and time of Service Status

Above are the columns we will get after executing the script

/*——————————————*/
/* SQL Server Components Check Utility */
/*——————————————*/
/*——————————————*/
SET NOCOUNT ON
/* —————————————— Inital Setup —————————————————–*/
CREATE TABLE #RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE #ServicesServiceStatus /*Create temp tables*/
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus varchar(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
DECLARE
@ChkInstanceName nvarchar(128) /*Stores SQL Instance Name*/
,@ChkSrvName nvarchar(128) /*Stores Server Name*/
,@TrueSrvName nvarchar(128) /*Stores where code name needed */
,@SQLSrv NVARCHAR(128) /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
,@FTS nvarchar(128) /*Stores Full Text Search Service name*/
,@RS nvarchar(128) /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
,@OLAP nvarchar(128) /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY(‘MachineName’) AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY(‘INSTANCENAME’) AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = ‘MSQLSERVER’
SELECT @OLAP = ‘MSSQLServerOLAPService’ /*Setting up proper service name*/
SELECT @FTS = ‘MSFTESQL’
SELECT @RS = ‘ReportServer’
SELECT @SQLAgent = ‘SQLSERVERAGENT’
SELECT @SQLSrv = ‘MSSQLSERVER’
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY(‘INSTANCENAME’) AS VARCHAR(128))
SET @SQLSrv = ‘$’+@ChkSrvName
SELECT @OLAP = ‘MSOLAP’ + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = ‘MSFTESQL’ + @SQLSrv
SELECT @RS = ‘ReportServer’ + @SQLSrv
SELECT @SQLAgent = ‘SQLAgent’ + @SQLSrv
SELECT @SQLSrv = ‘MSSQL’ + @SQLSrv
END
/* ———————————- SQL Server Service Section ———————————————-*/
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@SQLSrv
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/
EXEC xp_servicecontrol N’QUERYSTATE’,@SQLSrv
UPDATE #ServicesServiceStatus set ServiceName = ‘MS SQL Server Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘MS SQL Server Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- SQL Server Agent Service Section —————————————–*/
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@SQLAgent
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/
EXEC xp_servicecontrol N’QUERYSTATE’,@SQLAgent
UPDATE #ServicesServiceStatus set ServiceName = ‘SQL Server Agent Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘SQL Server Agent Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- SQL Browser Service Section ———————————————-*/
SET @REGKEY = ‘System\CurrentControlSet\Services\SQLBrowser’
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,N’sqlbrowser’
UPDATE #ServicesServiceStatus set ServiceName = ‘SQL Browser Service – Instance Independent’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘SQL Browser Service – Instance Independent’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- Integration Service Section ———————————————-*/
SET @REGKEY = ‘System\CurrentControlSet\Services\MsDtsServer’
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,N’MsDtsServer’
UPDATE #ServicesServiceStatus set ServiceName = ‘Intergration Service – Instance Independent’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘Intergration Service – Instance Independent’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- Reporting Service Section ————————————————*/
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@RS
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@RS
UPDATE #ServicesServiceStatus set ServiceName = ‘Reporting Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘Reporting Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- Analysis Service Section ————————————————-*/
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = ‘MSSQLServerOLAPService’
END
ELSE
BEGIN
SET @OLAP = ‘MSOLAP’+’$’+@ChkSrvName
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@OLAP
END
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@OLAP
UPDATE #ServicesServiceStatus set ServiceName = ‘Analysis Services’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘Analysis Services’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ———————————- Full Text Search Service Section —————————————–*/
SET @REGKEY = ‘System\CurrentControlSet\Services\’+@FTS
INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey=’HKEY_LOCAL_MACHINE’, @key=@REGKEY
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/
EXEC master.dbo.xp_servicecontrol N’QUERYSTATE’,@FTS
UPDATE #ServicesServiceStatus set ServiceName = ‘Full Text Search Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES (‘NOT INSTALLED’)
UPDATE #ServicesServiceStatus set ServiceName = ‘Full Text Search Service’ where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
/* ————————————————————————————————————-*/
SELECT PhysicalSrverName AS ‘Physical Server Name’ /*Display finding*/
,ServerName AS ‘SQL Instance Name’
,ServiceName AS ‘SQL Server Services’
,ServiceStatus AS ‘Current Service Service Status’
,StatusDateTime AS ‘Date/Time Service Status Checked’
FROM #ServicesServiceStatus
/* ————————————————————————————————————-*/
DROP TABLE #ServicesServiceStatus /*Perform cleanup*/
DROP TABLE #RegResult

 

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.