Weekly SQL Server Health Check

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Weekly SQL Server Health Check

Author

Topic

NickStan
Starting Member

36 Posts

Posted - 2010-07-07 : 13:58:23

Hi Guys

I have recently 'inherited' 4 SQL server machines (3 SQL 2000/Windows Server 2003 and 1 SQL 2008/Windows Server 2008) and they are my responsibility.

I am busy creating a weekly report to email me the 'Health Status' of each of the servers so I am trying to get a list together of things that I need to check:

- Free disk space
- Backups working/successful
- SQL server up and running
- Errors from event log (which ones? system/application)
- Any databases offline

Is there anything here that I have missed or should consider?

Thanks

Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-07 : 14:11:24

Job failures, missing indexes report, any errors in SQL Server Error log or Event Log, overall performance, ...

We use Microsoft's MOM/SCOM product to monitor all of our database servers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-07 : 14:20:52

Also, 1)space available on the database,
2)is Auto Shrink turned on?
3)Database status
4)Security Audit
these are ideas , and you need to focus in on things that are important to the good running of your servers
We have a combination of Tivoli Monitoring and custom regular auditing

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-07 : 16:11:18

Database consistency check

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-07-08 : 06:13:12

I would want to know about many of those things way sooner than a weekly run ...

But a weekly run that determined that, for some reason, an alert had NOT been sent / received / acted on would be a GREAT "Long Stop"

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-07-09 : 09:34:19

Autoshrink "on" and databases offline should be controlled only by the admin - I assume you - nobody else should have these capabilities. And autoshrink "on" is NOT a recommended practice - usually will do more harm than good. Determine what your maintenance window is and get Gail's suggestion going ASAP - you need to know the health of your databases. Available disk space can be critical so that should be a high priority, job failures as well. Some of the others, while important, can be a bit lower on the priority list intially. We use what's up gold -http://www.whatsupgold.com/ - to montior server disk space and an assortment of other items. It may be worth a look. I'm sure there are other tools as well, maybe even some freeware. Look around.

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-09 : 11:40:04

In a very large environment with multiple administrators - individuals will make mistakes, incorrect configuarions etc, despite standardisation processes. Apart from checking things such a Autoshrink , it might be worth considering: Last Backup, Log File Size .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Kristen
Test

22859 Posts

Posted - 2010-07-26 : 13:10:20

I had the need to write this today, in case useful to anyone / starter for a larger script??


SELECT	name,
	CASE WHEN T_SystemDB = 1 OR compatibility_level = 100 THEN '' ELSE 'compatibility_level=' + CONVERT(varchar(20), compatibility_level) + ' ' END
	+ CASE WHEN collation_name = 'SQL_Latin1_General_CP1_CI_AS' THEN '' 
		ELSE 'collation_name=' + COALESCE(collation_name, 'NULL') + ' ' COLLATE SQL_Latin1_General_CP1_CI_AS END
	+ CASE WHEN user_access_desc = 'MULTI_USER' THEN '' ELSE 'user_access=' + COALESCE(user_access_desc, 'NULL') + ' ' END
	+ CASE WHEN is_read_only = 0 THEN '' ELSE 'is_read_only=' + CONVERT(varchar(20), is_read_only) + ' ' END
	+ CASE WHEN is_auto_close_on = 0 THEN '' ELSE 'is_auto_close_on=' + CONVERT(varchar(20), is_auto_close_on) + ' ' END
	+ CASE WHEN is_auto_shrink_on = 0 THEN '' ELSE 'is_auto_shrink_on=' + CONVERT(varchar(20), is_auto_shrink_on) + ' ' END
	+ CASE WHEN state = 0 THEN '' ELSE 'state=' + COALESCE(state_desc, 'NULL') + ' ' END
	+ CASE WHEN is_in_standby = 0 THEN '' ELSE 'is_in_standby=' + CONVERT(varchar(20), is_in_standby) + ' ' END
	+ CASE WHEN is_cleanly_shutdown = 0 THEN '' ELSE 'is_cleanly_shutdown=' + CONVERT(varchar(20), is_cleanly_shutdown) + ' ' END
	+ CASE WHEN is_supplemental_logging_enabled = 0 THEN '' 
		ELSE 'is_supplemental_logging_enabled=' + CONVERT(varchar(20), is_supplemental_logging_enabled) + ' ' END
	+ CASE WHEN T_SystemDB = 1 OR snapshot_isolation_state = 0 THEN '' ELSE 'snapshot_isolation_state=' + COALESCE(snapshot_isolation_state_desc, 'NULL') + ' ' END
	+ CASE WHEN T_SystemDB = 1 OR is_read_committed_snapshot_on = 1 THEN '' ELSE 'READ_COMMITTED_SNAPSHOT=OFF ' END
	+ CASE WHEN T_SystemDB = 1 OR recovery_model = 1 THEN '' ELSE 'recovery_model=' + COALESCE(recovery_model_desc, 'NULL') + ' ' END
	+ CASE WHEN page_verify_option = 2 THEN '' ELSE 'page_verify_option=' + COALESCE(page_verify_option_desc, 'NULL') + ' ' END
	+ CASE WHEN is_auto_create_stats_on = 1 THEN '' ELSE 'is_auto_create_stats_on=' + CONVERT(varchar(20), is_auto_create_stats_on) + ' ' END
	+ CASE WHEN is_auto_update_stats_on = 1 THEN '' ELSE 'is_auto_update_stats_on=' + CONVERT(varchar(20), is_auto_update_stats_on) + ' ' END
	+ CASE WHEN is_auto_update_stats_async_on = 0 THEN '' ELSE 'is_auto_update_stats_async_on=' + CONVERT(varchar(20), is_auto_update_stats_async_on) + ' ' END
	+ CASE WHEN is_ansi_null_default_on = 0 THEN '' ELSE 'is_ansi_null_default_on=' + CONVERT(varchar(20), is_ansi_null_default_on) + ' ' END
	+ CASE WHEN is_ansi_nulls_on = 0 THEN '' ELSE 'is_ansi_nulls_on=' + CONVERT(varchar(20), is_ansi_nulls_on) + ' ' END
	+ CASE WHEN is_ansi_padding_on = 0 THEN '' ELSE 'is_ansi_padding_on=' + CONVERT(varchar(20), is_ansi_padding_on) + ' ' END
	+ CASE WHEN is_ansi_warnings_on = 0 THEN '' ELSE 'is_ansi_warnings_on=' + CONVERT(varchar(20), is_ansi_warnings_on) + ' ' END
	+ CASE WHEN is_arithabort_on = 0 THEN '' ELSE 'is_arithabort_on=' + CONVERT(varchar(20), is_arithabort_on) + ' ' END
	+ CASE WHEN is_concat_null_yields_null_on = 0 THEN '' ELSE 'is_concat_null_yields_null_on=' + CONVERT(varchar(20), is_concat_null_yields_null_on) + ' ' END
	+ CASE WHEN is_numeric_roundabort_on = 0 THEN '' ELSE 'is_numeric_roundabort_on=' + CONVERT(varchar(20), is_numeric_roundabort_on) + ' ' END
	+ CASE WHEN is_quoted_identifier_on = 0 THEN '' ELSE 'is_quoted_identifier_on=' + CONVERT(varchar(20), is_quoted_identifier_on) + ' ' END
	+ CASE WHEN is_recursive_triggers_on = 0 THEN '' ELSE 'is_recursive_triggers_on=' + CONVERT(varchar(20), is_recursive_triggers_on) + ' ' END
	+ CASE WHEN is_cursor_close_on_commit_on = 0 THEN '' ELSE 'is_cursor_close_on_commit_on=' + CONVERT(varchar(20), is_cursor_close_on_commit_on) + ' ' END
	+ CASE WHEN is_local_cursor_default = 0 THEN '' ELSE 'is_local_cursor_default=' + CONVERT(varchar(20), is_local_cursor_default) + ' ' END
	+ CASE WHEN T_SystemDB = 1 OR is_fulltext_enabled = 1 THEN '' ELSE 'is_fulltext_enabled=' + CONVERT(varchar(20), is_fulltext_enabled) + ' ' END
	+ CASE WHEN is_trustworthy_on = 0 THEN '' ELSE 'is_trustworthy_on=' + CONVERT(varchar(20), is_trustworthy_on) + ' ' END
	+ CASE WHEN T_SystemDB = 1 OR is_db_chaining_on = 0 THEN '' ELSE 'is_db_chaining_on=' + CONVERT(varchar(20), is_db_chaining_on) + ' ' END
	+ CASE WHEN is_parameterization_forced = 0 THEN '' ELSE 'is_parameterization_forced=' + CONVERT(varchar(20), is_parameterization_forced) + ' ' END
	+ CASE WHEN is_master_key_encrypted_by_server = 0 THEN '' 
		ELSE 'is_master_key_encrypted_by_server=' + CONVERT(varchar(20), is_master_key_encrypted_by_server) + ' ' END
	+ CASE WHEN is_published = 0 THEN '' ELSE 'is_published=' + CONVERT(varchar(20), is_published) + ' ' END
	+ CASE WHEN is_subscribed = 0 THEN '' ELSE 'is_subscribed=' + CONVERT(varchar(20), is_subscribed) + ' ' END
	+ CASE WHEN is_merge_published = 0 THEN '' ELSE 'is_merge_published=' + CONVERT(varchar(20), is_merge_published) + ' ' END
	+ CASE WHEN is_distributor = 0 THEN '' ELSE 'is_distributor=' + CONVERT(varchar(20), is_distributor) + ' ' END
	+ CASE WHEN is_sync_with_backup = 0 THEN '' ELSE 'is_sync_with_backup=' + CONVERT(varchar(20), is_sync_with_backup) + ' ' END
	+ CASE WHEN is_broker_enabled = 0 THEN '' ELSE 'is_broker_enabled=' + CONVERT(varchar(20), is_broker_enabled) + ' ' END
	+ CASE WHEN log_reuse_wait IN (0, 2) THEN '' ELSE 'log_reuse_wait=' + COALESCE(log_reuse_wait_desc, 'NULL') + ' ' END
	+ CASE WHEN is_date_correlation_on = 0 THEN '' ELSE 'is_date_correlation_on=' + CONVERT(varchar(20), is_date_correlation_on) + ' ' END
	+ CASE WHEN is_cdc_enabled = 0 THEN '' ELSE 'is_cdc_enabled=' + CONVERT(varchar(20), is_cdc_enabled) + ' ' END
	+ CASE WHEN is_encrypted = 0 THEN '' ELSE 'is_encrypted=' + CONVERT(varchar(20), is_encrypted) + ' ' END
	+ CASE WHEN is_honor_broker_priority_on = 0 THEN '' ELSE 'is_honor_broker_priority_on=' + CONVERT(varchar(20), is_honor_broker_priority_on) + ' ' END
-- SELECT TOP 100 *
FROM
(
	SELECT	[T_SystemDB] = CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE 0 END,
		*
	FROM	sys.databases 
) AS X
WHERE	1=1
--	AND name = 'MyDatabase'
ORDER BY 1-T_SystemDB, name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources