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