Steps for Service Pack (SP) Installation on RTM SQL Server

Difference between Hotfix, Cumulative Update and Service Pack?

Hotfix is released to fix a specific issue. A single hotfix can be used to resolve multiple issues related to a single scenario.
A Cumulative Update (CU) is a pack of all the previous hotfixes since the last cumulative update. This means if you have not installed all the hotfixes, a cumulative update installation will install those hotfixes for you.
A Service pack is a tested cumulative update of all the hotfixes and cumulative updates since the last service pack. Normally, service pack also contains new features and enhancements in addition to security fixes.
From SQL Server 2017, Microsoft has discontinued the Service Pack model.

Download Link for SQL Server 2016 Service Pack 2:

1. Go to this SQL Server download link
2. Select your language and press the Download button.

Prerequisites for Installing SQL Server 2016 Service Pack 2 on SQL Server 2016RTM:

System Requirements:

Current MSSQL Server Service Pack Details:

Activity Checklist or Steps:

SQL Server Service Pack Pre-upgrade planning checklist:

1. Analyze the disk space of the target server for the new database, if the disk space is not enough add more space on the target server
2. Confirm the data and log file location for the target server
3. Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
4. Collect the information of dependent applications, make sure application services will be stopped during the database migration
5. Collect the information of database logins, users and their permissions. (Optional)
6. Check the database for the Orphan users if any
7. Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers)
8. Check, if the database is part of any maintenance plan

---

Below are various scripts you can run to collect data.
Script to Check the Disk and Database Size

-- Procedure to check disc space
exec master..xp_fixeddrives
-- To Check database size
exec sp_helpdb [dbName]
or
use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2)  + 'MB'
from dbo.sysfiles
GO

Script to Check Database Properties

select 
 sysDB.database_id,
 sysDB.Name as 'Database Name',
 syslogin.Name as 'DB Owner',
 sysDB.state_desc,
 sysDB.recovery_model_desc,
 sysDB.collation_name, 
 sysDB.user_access_desc,
 sysDB.compatibility_level, 
 sysDB.is_read_only,
 sysDB.is_auto_close_on,
 sysDB.is_auto_shrink_on,
 sysDB.is_auto_create_stats_on,
 sysDB.is_auto_update_stats_on,
 sysDB.is_fulltext_enabled,
 sysDB.is_trustworthy_on
from sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid

Script to List Orphan Users

sp_change_users_login 'report'
GO

Script to List Linked Servers

select  *
from sys.sysservers

Script to List Database Dependent Jobs

select 
 distinct 
 name,
 database_name
from sysjobs sj
INNER JOIN sysjobsteps sjt on sj.job_id = sjt.job_id

---