SQL Server Health Check v1

health check

As a consultant, I frequently find myself in places where the on-boarding information for a new environment is incomplete or non-existent.  For Oracle environments, there are many tools available to obtain an initial review of a database server such as edb360 or OraChk.  For SQL Server, there are probably many tools as well, but I wanted something light-weight and non-intrusive.  Something that would give me a quick overview and maybe point out some trouble spots.

This was my plan when I wrote the SQL Server Health Check script.  This script allows someone new to the environment to get a quick snapshot of any existing SQL Server databases and their characteristics.  There is emphasis placed on reviewing existing or absent backups as this should be the first area of concern for any incoming DBA.   The script also briefly reviews waits in the environment, but does not go into more detail on particular performance characteristics.  As this script evolves, a more in-depth review of performance will likely be included.  The plan to keep this script non-intrusive may limit the scope of this review as well as others, though.

Special thanks to Sam Sridharagopal from Avenade for his help in developing and testing this script.

The script works on SQL Server versions 2005 through 2016 (CTP 2) and is best executed though SSMS.  If you find the script useful or have improvement suggestions, please comment.

———————————————————-Start of script

–sql_server_health_check_v1.sql
–v1
–This is a non-intrusive script written to give an overall informational view of a
–SQL Server 2005-2016 (CTP 2) server instance for the purpose of first-time review.
–Select ‘Results To’ text when running from SSMS

use master
GO

–Node name

SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS [CurrentNodeName];

–Version

select @@version as [VERSION];

–Instance parameters

print N’Instance Parameter’;

SELECT  *
FROM    sys.configurations
ORDER BY name ;

–Database listing

print N’Database list with Status and Recovery Model’;

SELECT substring(name,1,40) AS name,  substring(state_desc,1,20) AS STATE,
substring(recovery_model_desc,1,20) AS RECOVERY_MODEL
FROM sys.databases
order by name;

–Size and growth

print N’Size and Growth’;

select substring(b.name,1,40) AS DB_Name, substring(a.name,1,40) AS Logical_name,
substring(a.filename,1,100) AS File_Name,
cast((a.size * 8.00) / 1024 as numeric(12,2)) as DB_Size_in_MB,
case when a.growth > 100 then ‘In MB’ else ‘In Percentage’ end File_Growth,
cast(case when a.growth > 100 then (a.growth * 8.00) / 1024
else (((a.size * a.growth) / 100) * 8.00) / 1024
end as numeric(12,2)) File_Growth_Size_in_MB,
case when ( maxsize = -1 or maxsize=268435456 ) then ‘AutoGrowth Not Restricted’ else ‘AutoGrowth Restricted’ end AutoGrowth_Status
from sysaltfiles a
join sysdatabases b on a.dbid = b.dbid
where DATABASEPROPERTYEX(b.name, ‘status’) = ‘ONLINE’
order by b.name;

–Is Cluster Node?

SELECT ‘Clustered’, case when SERVERPROPERTY(‘IsClustered’) = 0 then ‘No’
else ‘Yes’ end;

–Nodes in Cluster

print N’Cluster Nodes’;

SELECT * FROM fn_virtualservernodes();

–Is AlwaysOn?

SELECT ‘AlwaysOn’, case when SERVERPROPERTY(‘IsHadrEnabled’) = 0 then ‘No’
when SERVERPROPERTY(‘IsHadrEnabled’) = 1 then ‘Yes’
else SERVERPROPERTY(‘IsHadrEnabled’) end;

–AlwaysOn status

declare @c int;
declare @rd nvarchar(60);
declare @osd nvarchar(60);
declare @rhd nvarchar(60);
declare @shd nvarchar(60);
declare @csd nvarchar(60);
select @c = COUNT(name)
from sys.all_objects
where name = ‘dm_hadr_availability_replica_states’;
if @c = 0
print N’No AlwaysOn Status’;
else
select @rd = role_desc, @osd= case when operational_state_desc is null then ‘Replica is not local’
else operational_state_desc end,
@rhd = recovery_health_desc, @shd = synchronization_health_desc,
@csd = connected_state_desc
from sys.dm_hadr_availability_replica_states;
print @rd
print @osd
print @rhd
print @shd
print @csd

–Memory usage per database

print N’Memory Usage per User Database’;

SELECT
substring(DB_NAME(database_id),1,40) AS [Database Name]
,COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM
sys.dm_os_buffer_descriptors
WHERE
database_id > 4
AND database_id <> 32767
AND db_name(database_id) <> ‘SSISDB’
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

–Last backup time

SELECT substring(sdb.Name,1,40) AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),’-‘) AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
where sdb.Name <> ‘tempdb’
GROUP BY sdb.Name;

–No log backups for FULL or BULK_LOGGED recovery model databases

print N’Databases with FULL or BULK_LOGGED recovery model and no log backups in last 30 days’;

SELECT name AS at_risk_database
FROM sys.databases
where recovery_model_desc in(‘FULL’,’BULK_LOGGED’)
and name not in(
SELECT
msdb.dbo.backupset.database_name AS DBName
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 30)
and msdb..backupset.type = ‘L’
group by msdb.dbo.backupset.database_name
);

–Databases with no backups in the last 30 says

print N’Databases with NO backups in last 30 days’;

SELECT name AS at_risk_database
FROM sys.databases
where name <> ‘tempdb’
and name not in(
SELECT
substring(msdb.dbo.backupset.database_name,1,40) AS DBName
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 30)
group by msdb.dbo.backupset.database_name
);

–Backups for the previous week

print N’All backups for previous week’;

SELECT
CONVERT(CHAR(40), SERVERPROPERTY(‘Servername’)) AS Server,
substring(msdb.dbo.backupset.database_name,1,40) AS DBName,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN ‘D’ THEN ‘Database’
WHEN ‘L’ THEN ‘Log’
WHEN ‘F’ THEN ‘File’
WHEN ‘P’ THEN ‘Partial’
WHEN ‘I’ THEN ‘Differential database’
WHEN ‘G’ THEN ‘Differential file’
WHEN ‘Q’ THEN ‘Differential partial’
WHEN NULL THEN msdb..backupset.type
END AS backup_type,
msdb.dbo.backupset.backup_size,
substring(msdb.dbo.backupmediafamily.logical_device_name,1,50) AS logical_device_name,
substring(msdb.dbo.backupmediafamily.physical_device_name,1,50) AS physical_device_name,
substring(msdb.dbo.backupset.name,1,50) AS backupset_name,
substring(msdb.dbo.backupset.description,1,50) AS description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date;

–Jobs that failed in the last 24 hours

print N’Jobs Failing in last 24 hours’;

— Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT
— Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) — Last 1 day
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)
— Final Logic
SELECT substring(j.[name],1,40) AS JOB,
substring(s.step_name,1,40) AS Step,
h.step_id,
substring(h.step_name,1,40) AS Step,
h.run_date,
h.run_time,
h.sql_severity,
substring(h.message,1,100) AS Message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE h.run_status = 0 — Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC;

–Missing indexes

print N’Missing Indexes’;

SELECT substring(so.name,1,40) AS Name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact
, ddmid.equality_columns
, ddmid.inequality_columns
, ddmid.included_columns
FROM sys.dm_db_missing_index_group_stats AS ddmigs
INNER JOIN sys.dm_db_missing_index_groups AS ddmig
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.objects so WITH (nolock)
ON ddmid.object_id = so.object_id
WHERE ddmigs.group_handle IN (
SELECT TOP (5000) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact)*(user_seeks+user_scans)DESC);

–Duplicate indexes

print N’Duplicate Indexes’;

DECLARE @SCHEMANAME VARCHAR(30);
DECLARE @TABLENAME VARCHAR(127);
WITH ind_list AS(
select o.schema_id, i.object_id, i.index_id,
i.name, i.type_desc,
i.is_unique, i.is_primary_key,
STUFF( (SELECT ‘,’ + tc.name
FROM sys.index_columns ic
JOIN sys.columns tc
ON tc.column_id = ic.column_id AND
tc.object_id = ic.object_id
WHERE ic.object_id = i.object_id AND
ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.index_column_id
FOR XML PATH (”) ),1,1,” ) index_columns,
STUFF( (SELECT ‘,’ + tc.name
FROM sys.index_columns ic
JOIN sys.columns tc
ON tc.column_id = ic.column_id AND
tc.object_id = ic.object_id
WHERE ic.object_id = i.object_id AND
ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH (”) ),1,1,” ) include_columns
FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
WHERE i.index_id > 0 AND i.type_desc <> ‘XML’
AND object_name(i.object_id) LIKE @TABLENAME
AND i.is_disabled = 0
AND schema_name(o.schema_id) LIKE @SCHEMANAME )
SELECT substring(schema_name(included_indexes.schema_id),1,30) AS owner,
object_name(included_indexes.object_id) table_name,
(SELECT SUM(st.row_count) FROM sys.dm_db_partition_stats st
WHERE st.object_id = included_indexes.object_id
AND st.index_id < 2 ) num_rows,
included_indexes.name included_index_name,
included_indexes.index_columns included_index_columns,
included_indexes.include_columns
included_index_include_columns,
included_indexes.type_desc included_index_type,
included_indexes.is_unique included_index_uniqueness,
included_indexes.is_primary_key included_index_PK,
(SELECT SUM(a.total_pages) * 8 FROM sys.allocation_units a
JOIN sys.partitions p ON a.container_id = p.partition_id
WHERE p.object_id = included_indexes.object_id AND
p.index_id = included_indexes.index_id
) included_index_size_kb,
including_indexes.name including_index_name,
including_indexes.index_columns including_index_columns,
including_indexes.include_columns
including_index_include_columns,
including_indexes.type_desc including_index_type,
including_indexes.is_unique including_index_uniqueness,
including_indexes.is_primary_key including_index_PK,
(SELECT SUM(a.total_pages) * 8 FROM sys.allocation_units a
JOIN sys.partitions p ON a.container_id = p.partition_id
WHERE p.object_id = including_indexes.object_id AND
p.index_id = including_indexes.index_id
) including_index_size_kb
FROM ind_list included_indexes
JOIN ind_list including_indexes
ON including_indexes.object_id = included_indexes.object_id
JOIN sys.partitions ing_p
ON ing_p.object_id = including_indexes.object_id AND
ing_p.index_id = including_indexes.index_id
JOIN sys.allocation_units ing_a
ON ing_a.container_id = ing_p.partition_id
WHERE including_indexes.index_id <> included_indexes.index_id
AND LEN(included_indexes.index_columns) <=
LEN(including_indexes.index_columns)
AND included_indexes.index_columns + ‘,’ =
SUBSTRING(including_indexes.index_columns,1,
LEN(included_indexes.index_columns + ‘,’))
ORDER BY 2 DESC;

–Index fragmentation check

print N’Index with HIGH Fragmentation’;

EXEC sp_MSforeachdb ‘
USE [?]
SELECT ”?” AS DB_NAME,
QUOTENAME(sysind.name) AS [index_name],
indstat.*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ”LIMITED”)
AS indstat
INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND
indstat.index_id = sysind.index_id
where avg_fragmentation_in_percent >= 30
ORDER BY avg_fragmentation_in_percent DESC;

use master
GO

 

–Wait stats

print N’Wait Stats’;

SELECT *
FROM sys.dm_os_wait_stats
where wait_time_ms > 10000
ORDER BY wait_time_ms DESC;

–Users and roles

print N’Users and Roles’;

WITH Roles_CTE(Role_Name, Username)
AS
(
SELECT
User_Name(sm.[groupuid]) as [Role_Name],
user_name(sm.[memberuid]) as [Username]
FROM [sys].[sysmembers] sm
)

SELECT
Roles_CTE.Role_Name,
[DatabaseUserName] = princ.[name],
[UserType] = CASE princ.[type]
WHEN ‘S’ THEN ‘SQL User’
WHEN ‘U’ THEN ‘Windows User’
WHEN ‘G’ THEN ‘Windows Group’
WHEN ‘A’ THEN ‘Application Role’
WHEN ‘R’ THEN ‘Database Role’
WHEN ‘C’ THEN ‘User mapped to a certificate’
WHEN ‘K’ THEN ‘User mapped to an asymmetric key’
END
FROM
sys.database_principals princ
JOIN Roles_CTE on Username = princ.name
where princ.type in (‘S’, ‘U’, ‘G’, ‘A’, ‘R’, ‘C’, ‘K’)
ORDER BY princ.name;

–Job listing (see Sam query)

print N’Job Information’;

SELECT     [JobName] = [jobs].[name]
,[Category] = [categories].[name]
,[Owner] = SUSER_SNAME([jobs].[owner_sid])
,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN ‘Yes’ ELSE ‘No’ END
,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN ‘Yes’ ELSE ‘No’ END
,[Description] = [jobs].[description]
,[Occurs] =
CASE [schedule].[freq_type]
WHEN   1 THEN ‘Once’
WHEN   4 THEN ‘Daily’
WHEN   8 THEN ‘Weekly’
WHEN  16 THEN ‘Monthly’
WHEN  32 THEN ‘Monthly relative’
WHEN  64 THEN ‘When SQL Server Agent starts’
WHEN 128 THEN ‘Start whenever the CPU(s) become idle’
ELSE ”
END
,[Occurs_detail] =
CASE [schedule].[freq_type]
WHEN   1 THEN ‘O’
WHEN   4 THEN ‘Every ‘ + CONVERT(VARCHAR, [schedule].[freq_interval]) + ‘ day(s)’
WHEN   8 THEN ‘Every ‘ + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ‘ weeks(s) on ‘ +
LEFT(
CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN ‘Sunday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN ‘Monday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN ‘Tuesday, ‘   ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN ‘Wednesday, ‘ ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN ‘Thursday, ‘  ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN ‘Friday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN ‘Saturday, ‘  ELSE ” END ,
LEN(
CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN ‘Sunday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN ‘Monday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN ‘Tuesday, ‘   ELSE ” END +
CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN ‘Wednesday, ‘ ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN ‘Thursday, ‘  ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN ‘Friday, ‘    ELSE ” END +
CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN ‘Saturday, ‘  ELSE ” END
) – 1
)
WHEN  16 THEN ‘Day ‘ + CONVERT(VARCHAR, [schedule].[freq_interval]) + ‘ of every ‘ + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ‘ month(s)’
WHEN  32 THEN ‘The ‘ +
CASE [schedule].[freq_relative_interval]
WHEN  1 THEN ‘First’
WHEN  2 THEN ‘Second’
WHEN  4 THEN ‘Third’
WHEN  8 THEN ‘Fourth’
WHEN 16 THEN ‘Last’
END +
CASE [schedule].[freq_interval]
WHEN  1 THEN ‘ Sunday’
WHEN  2 THEN ‘ Monday’
WHEN  3 THEN ‘ Tuesday’
WHEN  4 THEN ‘ Wednesday’
WHEN  5 THEN ‘ Thursday’
WHEN  6 THEN ‘ Friday’
WHEN  7 THEN ‘ Saturday’
WHEN  8 THEN ‘ Day’
WHEN  9 THEN ‘ Weekday’
WHEN 10 THEN ‘ Weekend Day’
END + ‘ of every ‘ + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ‘ month(s)’
ELSE ”
END
,[Frequency] =
CASE [schedule].[freq_subday_type]
WHEN 1 THEN ‘Occurs once at ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)
WHEN 2 THEN ‘Occurs every ‘ +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Seconds(s) between ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)
WHEN 4 THEN ‘Occurs every ‘ +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Minute(s) between ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)
WHEN 8 THEN ‘Occurs every ‘ +
CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Hour(s) between ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)
ELSE ”
END
,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
,[Next_Run_Date] =
CASE [jobschedule].[next_run_date]
WHEN 0 THEN CONVERT(DATETIME, ‘1900/1/1’)
ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ‘ ‘ +
STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’))
END
FROM     [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
ON [jobs].[job_id] = [jobschedule].[job_id]
LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
ON [jobs].[category_id] = [categories].[category_id]
LEFT OUTER JOIN
(    SELECT     [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
(([run_duration] % 10000) / 100 * 60) +
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
FROM     [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
WHERE     [step_id] = 0
GROUP BY [job_id]
) AS [jobhistory]
ON [jobhistory].[job_id] = [jobs].[job_id];

–Existing linked server listing

print N’Linked Server Information’;

declare @x int;
select @x = COUNT(name)
from sys.all_objects
where name = ‘Servers’;
if @x <> 0
SELECT *
–c.name, provider, data_source, is_remote_login_enabled, b.modify_date
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
where a.server_id <> 0;
else
exec sp_linkedservers;

 

———————————————————-End of script

Advertisements

SQL Server startup options for troubleshooting

When something goes wrong with your SQL Server database and you cannot connect, it may be necessary to place it in a mode better suited for troubleshooting.  Two of these modes are single-user and minimal-configuration.

Single-user mode (-m)

This mode is used to change server configuration options or recover a damaged master database or other system database.

NOTE – Since only one user can connect to the SQL Server database in single-user mode (hence the name), it is important to stop and/or temporarily disable the SQL Server Agent service going through Start -> Administrative Tools -> Services and right-clicking on the service to change its status.  If started, this service will automatically occupy the only available connection in single-user mode, preventing further connections.  Also, the clustered service DLL will occupy the only available connection in a clustered environment, so this work around is necessary, but only in a clustered environment:

  1. Remove the –m startup parameter from the SQL Server advanced Properties (more on this process later under “Setting or removing a special startup mode in SQL Server”).
  2. Take the SQL Server service offline.
  3. From the current owner node of this group, issue the following command from the command prompt: net start MSSQLSERVER /m.
  4. Verify from the cluster administrator or failover cluster management console that the SQL Server resource is still offline.
  5. Connect to the SQL Server now using the following command and do the necessary operation: SQLCMD -E -S<servername>.
  6. Once the operation is complete, close the command prompt and bring back the SQL and other resources online through cluster administrator.

Minimal-configuration mode (-f)

This option starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-allocating memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.

Setting or removing a special startup mode parameter in SQL Server

  • Log into the server hosting the SQL Server database or log into one of the nodes hosting the SQL Server cluster.
  • Start the SQL Server Configuration Manager.  If you do not see it under the SQL Server application group in the Start menu, execute “SQLSERVERMANAGER10.msc” from the Run command window for SQL Server 2008 or “SQLSERVERMANAGER11.msc” from the Run command window for SQL Server 2012.
  • Select “SQL Server Services” in the left window pane and find the service called “SQL Server (instance)” in the right window pane where instance is the name given the named instance.
  • Right-click on the service name and select Properties.
  • In SQL Server 2012, the process is easy.  Simply go to the Startup parameter tab, type in the desired parameter in the “specify a startup parameter” box and click Add.  You can remove the parameter from “existing parameters” window under this tab and click Remove.
  • In SQL Server 2008, go to the Advanced tab, and scroll-down to Startup parameters. Click on the values on the right and use the drop-down arrow to enable a small window to add or remove parameters.
  • Restart this service to enable or disable the startup mode

 

Contrasting the different SQL Server recovery models

Those of us just getting our feet wet in the SQL Server pool should know about the different recovery models available as one of the first pieces of knowledge to set in stone.  Since I am approaching this from the aspect of being experienced in the Oracle database environment, I will compare the SQL Server recovery models to their Oracle database analogs where applicable.  Here we go!

Simple Recovery Model – This model is like the Oracle noarchivelog mode.  Transactions performed between backups are lost in the event of a failure and HA features such as mirroring or AlwaysOn cannot be used on a database with this recovery model.  On the plus side, the log file is kept small and tidy.

Full Recovery Model – Like the Oracle archivelog mode, this model will allow you to recover the database to the point of failure.  However, it requires log backups, like archivelog backups in Oracle.  Log backups should be executed in between full backups at an interval based on the amount of activity in the database and the rate of transaction log growth.

Bulk-Logged Recovery Model – The recovery mode is like the Full recovery mode with the exception that it allows for minimal transaction logging during bulk-loading to increase performance during these operations.  Log backups are also required with this recovery model.

More details on the three recovery models can be found here – https://msdn.microsoft.com/en-us/library/ms189275.aspx