July 10, 2008 at 7:49 am
Hi Experts,
I am assigned to monitor a couple of SQL servers and this is what I do:
Check DB and Log sizes
Check for correct backups of Logs and DBs
Verify sufficient drive free space
Run DBCC CheckDB on alll databases
My question:
Is this a complete list of what I should be doing or are there other issues I should check/track/investigate?
I would like to do as complete a job as possible to make sure the SQL servers are on line 100%. The servers are active about 12 hours per day during the week so I can do what is necessary after hours or on weekends.
Thanks in advance!
July 10, 2008 at 8:06 am
Brad McGehee has a great ebook. There are two books in the download and one of them is DBA Best Practices and goes over daily activities.
http://www.red-gate.com/products/SQL_Backup/offers/brad_exceptional_dba_ebook.htm
July 10, 2008 at 8:11 am
Excellent.. and adding to above:
Check Index Fragmentation:
REINDEX if Index Fragmentation are HIGH. based on the Values of Logical Scan Fragmentation in DBCC SHOWCONTIG OR
select db_name(database_id) as DatabaseName,object_name (object_id,database_id) as ObjectName,*
from sys.dm_db_index_physical_stats (NULL,NULL,NULL,NULL,'DETAILED') and check for avg_fragmentation_in_Percent colmn with Values greater than 40.. (everybody has its own perspective)
Do Not RUN DBCC Frequently, Make it a once a Week Task.
Check for Failed JOBS.
CHeck Error Logs Regularly for Errors.
Check Windows Event Viewer for issues.
Check Failed Login Attempts or Unknown Login attempts.
Check Performance (IO)-Run perf Mon.
Truncate Logs at regular intervals, if they Grow tooo BIG.
Check for Frequent Locking/Blocking Scenarios.
Maninder
www.dbanation.com
July 10, 2008 at 8:36 am
Wow - fast respones and thanks for the tips! I downloaded the ebook and will look at it shortly.
Can you tell me how to check for blocked processes and block history? Is that in the event log?
Also what is perf Monitor?
Thank you so much!
July 10, 2008 at 8:44 am
There are scripts that will help you see blocking in this Microsoft KB article:
How to monitor blocking in SQL Server 2005 and in SQL Server 2000
Perfmon is an OS utility. If you go Start | Run | and type perfmon, it'll come up. It allows you to see performance counters for memory, disk, CPU, etc. SQL Server related performance counters can typically be queried inside SQL Server. But OS related ones you've got to use PerfMon or a WMI script or something of that sort.
K. Brian Kelley
@kbriankelley
July 10, 2008 at 8:46 am
Mani Singh (7/10/2008)
Not RUN DBCC Frequently, Make it a once a Week Task.
But do run it, so you know how long it takes when everything is okay. That gives you an idea of the minimum time to diagnose health on a DB if you suspect corruption issues.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply