DBA Morning Check List
-
Comments posted to this topic are about the item DBA Morning Check List
-
Dear Richard,
Nice Article. 🙂
If you can put more lines for Database Optimization,
would be more greatful for me.
My Company's present SQL database scenarios is as below.
Database Name: Buzz_Data
Database Size: 12.3 GB
Data Files: 1 (Primary)
Transaction Log File: 1
Database Tables: 500+
At Present my user's are facing problem to retrive data in time inspite of having Server's config like Intel XEON (IBM) , 2 GB Ram, 80 GB HDD.
Can you advice me to help my users.
Thanks, in anticipation.
Regards,
Ritesh Mehta
DBA
Gujarat (India)
-
Great article. I currently have a checklist that I now live by.
Especially when one thing goes wrong that requires immediate attention, a checklist allows to easily pick up where you left off on your daily routines. especially at 5-6 am, coffee alone isn't always enough...
I also group my daily checklist with weekly and monthly processes. I Identify and track errors now by a high-level type to help identify problems like recurring issues, server issues and network issues unrelated to the databases but affect them.
deb-salem, ma
-
Excellent article. You can combined with the Microsoft SQL Server 2000 operations guide (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx )
-
Excellent Article!
The operations guides and checklists Microsoft offers are pretty helpful, especially the one for setting up clustering.
-
Bill,
Thanks for taking the time to write this article. I have implemented a similar process in my environment. To collect the majority of my data, I use a modified version of David Bird's SQL Overview SSIS Package.
SQL Overview Part 1
By David Bird, 2008/01/07
http://www.sqlservercentral.com/articles/Integration+Services/61621/
SQL Overview SSIS Package II - Retrieving SQL Error Log
By David Bird, 2008/01/14
http://www.sqlservercentral.com/articles/Integration+Services/61714/
SQL Overview SSIS Package III - Full Package
By David Bird, 2008/01/22
http://www.sqlservercentral.com/articles/Integration+Services/61774/
Using David's articles and SSIS code, I have customized it for the uniqueness of the systems which I am responsible for. Your idea of printing everything out and storing in a binder with signatures, will be a a topic for discussion with my next planning meeting with my boss. I look forward to your future SSC postings.
Regards,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP -
Nice article. I would just like to point out that for those of us that have numerous servers, automation of the checklist is critical. If you're dealing with only one server, manually checking these things does not take a lot of time. But imagine checking job failures or drive space on 50 sql servers. We get paid too much to perform these menial tasks by hand. There are many 3rd party tools out there that do this for us. It's also pretty easy to write your own scripts and sql jobs... many starter scripts could probably be found on this forum.
One benefit of automating your checklist is time. The other benefit is proactive in nature. If a drive is out of space because tempdb exploded in size over night, it's better to get notified via email at 4 am. Sure, the cell phone disturbs your precious sleep, but you now have 4 hours to fix the situation before business opens at 8 am and people start screaming.
Also, if there are numerous DBAs on your team, automating these checks helps greatly with standardization.
Regards,
Rubes -
Damon Wilson (4/14/2008)
Bill,Thanks for taking the time to write this article. I have implemented a similar process in my environment. To collect the majority of my data, I use a modified version of David Bird's SQL Overview SSIS Package.
SQL Overview Part 1
By David Bird, 2008/01/07
http://www.sqlservercentral.com/articles/Integration+Services/61621/
SQL Overview SSIS Package II - Retrieving SQL Error Log
By David Bird, 2008/01/14
http://www.sqlservercentral.com/articles/Integration+Services/61714/
SQL Overview SSIS Package III - Full Package
By David Bird, 2008/01/22
http://www.sqlservercentral.com/articles/Integration+Services/61774/
Using David's articles and SSIS code, I have customized it for the uniqueness of the systems which I am responsible for. Your idea of printing everything out and storing in a binder with signatures, will be a a topic for discussion with my next planning meeting with my boss. I look forward to your future SSC postings.
Regards,
I also use the SQL Overview package from David. It is a great help to minitor the SQL infrastructure.
-
rubes (4/14/2008)
Nice article. I would just like to point out that for those of us that have numerous servers, automation of the checklist is critical. If you're dealing with only one server, manually checking these things does not take a lot of time. But imagine checking job failures or drive space on 50 sql servers. We get paid too much to perform these menial tasks by hand. There are many 3rd party tools out there that do this for us. It's also pretty easy to write your own scripts and sql jobs... many starter scripts could probably be found on this forum.Could you please name some of the third party tools.
Thanks in advance.
-
Useful discussion
It is a bit harder when the SQL Servers are hosted off-site to collect "stuff"
and that I am not yet on the "critical error" production email list 🙁
SQLServerNewbieMCITP: Database Administrator SQL Server 2005 -
rubes (4/14/2008)
Nice article. I would just like to point out that for those of us that have numerous servers, automation of the checklist is critical. If you're dealing with only one server, manually checking these things does not take a lot of time. But imagine checking job failures or drive space on 50 sql servers. We get paid too much to perform these menial tasks by hand. There are many 3rd party tools out there that do this for us. It's also pretty easy to write your own scripts and sql jobs... many starter scripts could probably be found on this forum.One benefit of automating your checklist is time. The other benefit is proactive in nature. If a drive is out of space because tempdb exploded in size over night, it's better to get notified via email at 4 am. Sure, the cell phone disturbs your precious sleep, but you now have 4 hours to fix the situation before business opens at 8 am and people start screaming.
Also, if there are numerous DBAs on your team, automating these checks helps greatly with standardization.
I totally agree with automation being essential. We shouldn't need to manually check all these things esp where there are a lot of servers involved.
Essentially as DBA's we want to know about backups, Jobs' status, Disk space usage vs free space (free space within db files as well as at total drive capacity), auditing (configuration changes, failed passwords, database growth), SQL error log messages , number of page splits, locking and a lot more besides.
There are a variety of ways that you can achieve the above with a combination of Alerts, querying system tables, performance monitoring and third party tools.
The thing to do is to find the best fit for you and your organisation with the infrastructure you have and the time you are allowed to devote to implementing a solution.
-
Preet (4/14/2008)
Essentially as DBA's we want to know about backups, Jobs' status, Disk space usage vs free space (free space within db files as well as at total drive capacity), auditing (configuration changes, failed passwords, database growth), SQL error log messages , number of page splits, locking and a lot more besides.
There are a variety of ways that you can achieve the above with a combination of Alerts, querying system tables, performance monitoring and third party tools.
The thing to do is to find the best fit for you and your organisation with the infrastructure you have and the time you are allowed to devote to implementing a solution.
I think that there should be some kind of Best Practices to manage SQL servers, something every DBA should use. You are right about the things that as DBA we need to check, but I refuse to accept that there is not a standard for managing databases. It is really a pitty that everybody is using a different aproach to manage their SQL infrastructure. We would get better results if we decide to implement a standard for this.
-
Database Name: Buzz_Data
Database Size: 12.3 GB
Data Files: 1 (Primary)
Transaction Log File: 1
Database Tables: 500+
At Present my user's are facing problem to retrive data in time inspite of having Server's config like Intel XEON (IBM) , 2 GB Ram, 80 GB HDD.
Hmm… Performance problems retrieving data - that could be a lot of things. However, here is some ideas how you might trouble shoot the issues you are experiencing.
1.SQL Server is most likely taking up most of your 2 GB of memory. Make sure there are no other applications competing for this memory. One method is to use task manager, click “show processes from all users” and sort by CPU and memory.
2.Setup a SQL Profiler trace on a single user. When that user starts having performance problems, you will have all the commands sent to the database. Look for the commands with long durations.
3.Run SP_WHO2 active when you start having performance issues determine if there is a process that is taking up a lot of memory or cpu time.
4.Setup a Server Side Script. Capture the statements with long durations.
5.Setup Performance counters. Create a baseline, when you are not having issues. When you do have issues, compare the current values to the baseline values. I pull perfmon file into excel and create charts. Below are the counters I use. This will determine if there are any bottlenecks.
Memory\Available MBytes
Memory\Pages/sec
Network Interface(Broadcom NetXtreme Gigabit Ethernet _2)\Output Queue Length
Network Interface(Broadcom NetXtreme Gigabit Ethernet)\Output Queue Length
Paging File(_Total)\% Usage
PhysicalDisk(*)\% Disk Time
PhysicalDisk(*)\Avg. Disk Queue Length
Processor(_Total)\% Processor Time
SQLServer:Buffer Manager\Buffer cache hit ratio
SQLServer:General Statistics\User Connections
SQLServer:Latches\Latch Waits/sec
SQLServer:Locks(_Total)\Lock Requests/sec
SQLServer:Memory Manager\Total Server Memory (KB)
System\Processor Queue Length
Bill Richards
-
Thank you for taking time to read my article. I hope that you have found the DBA Morning Check List article valuable. At one time, I worked at a company that did not have a standard DBA morning checklist, and I helped develop one. I wrote this article to challenge those who do not yet have a checklist to develop one. To the shops that do have a checklist, I desired to offer my ideas to improve their shops even more.
Managing many servers can be difficult. Below are my thoughts on posted comments
Third Party Tools: I have used Idera Diagnostic Manager and Quest Spotlight. Both are excellent tools to help capture and fix issues.
Scripts – At one company, I wrote sql scripts that created HTML pages. Part of my morning routine was checking the web pages to see what issues were reported.
Integration Services and Reporting Services – Currently, we use SSIS to pull information from all the sql servers, place the results in a database and pull the information using Reporting Serives.
Proactive – I believe in being proactive. I also have a pager that notifies me in the middle of the night if there is an issue. However, it is good to have a morning checklist to fix any issues that I didn’t get paged on.
Thanks,
Bill Richards, MCSE, MCDBA
Senior Database Analyst
-
Bill, I want to thank you for your tips.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply