November 23, 2009 at 9:44 am
Hello, I am trying to come up with my rule work for DBA, what should DBA do every day, weekly,monthly,yearly. Can someone give me an advise?
Thank you
November 23, 2009 at 10:21 am
You could have posted the list that you have identified, it would be better for us to add / suggest few things which you don't have in your list.
Without it, all suggestions here would be repeating the same things you have already in hand.
Some I can suggest (some of these I do and others Planning to do)
Daily
Monitoring Servers from performance perspective.
Monitor the SQL Server error logs and SQL Agent Jobs for any occurrences of failures.
Weekly
Monitor the database growth for large databases and take necessary steps when needed.
Monthly
Perform a random test of restoring a backup.
Learn a new technology (may be a small portion) in and around SQL Server.
Yearly
Read at least 4 New SQL Server Books.
Review Instances / Databases and come up with projected plans for the year ahead.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 10:54 am
Something like these activities:
Main Checklist for things to do during the initial setup:
1. Automate all possible jobs and maintenance plans on the server for things like database backups, integrity checks, automatic shrinking, transaction log backups, etc. You could do this by creating Maintenance Plans in SQL Server, which would automatically generate and schedule the required jobs.
2. Install SQL Mail on all your production servers and set it up to send you notifications on your email (or cell phone or pager - whatever is convenient).
3. Through SQL Mail, set up email notifications for all the jobs and maintenance plans on the production server - for every database. Also set up email notifications for you to be notified in case of a severity alert - like file growing large and thus reducing disk space, etc.
4. Always keep a script of the functional database schema in a secure location on the network. This comes in handy if you need to know the structure of the database in production or you need to recover a database, which does not have any backup left.
5. Set the MSSQL and SQL Server Agent services to Auto-start when the server starts.
Monthly checklist:
1. Make a list of all the sa passwords for each server and save it in a secure place.
2. Make a list of all the passwords for each login created on the production boxes.
3. Save the SQL Servers’ and Windows' configuration information in a secure place. This information is needed to rebuild an NT & SQL Server box in case of a disaster.
4. Perform a test restore of a database backup. This is done in order to prepare for unforeseen situations.
5. Save information about any changes made to a server - hardware or software.
6. Maintain system logs in a secure fashion. Keep records of all service packs installed for both Microsoft Windows NT Server and Microsoft SQL Server. Keep records of network libraries used, the security mode, sa passwords and service accounts.
7. Assess the steps in recovering from a disaster ahead of time on another server, and amend the steps in your Disaster Recovery Document, as necessary to suit your environment.
8. Audit Database Access: You should periodically perform a review of who has access to your production databases and what type of rights they possess. Doing so can prevent unauthorized access to production data.
Daily Checklist:
1. Check the connectivity of each server over the network. You could do this by pinging the SQL servers twice a day or by clicking the server’s name in your Enterprise Manager and seeing if it is able to connect.
2. Check whether the services are running. For each server, go to its SQL Service Manager and check whether the SQL Server Agent and MSSQL Server services are running (showing a green light). If not, start those services. (You could also check these from the Control Panel or Enterprise Manager).
3. Check whether the scheduled tasks on the production servers are running normally. You could check this from the Enterprise Manager of each server or your email (if you have set up SQL Mail to notify you).
4. Check the hard disk space available on the SQL Servers. If system drives run low on space, they crash.
5. Check all the database and transaction log space on each server. If the database or transaction log space runs out, the transactions will fail.
6. Check NT event Logs for any error messages. SQL Server writes to the NT application log in case of application errors or SQL errors and also warns you before a problem becomes critical.
7. Check SQL Error Logs for any errors occurring within SQL Server. SQL Server warns you through these logs before the problem becomes critical.
As needed Checklist:
Run disk defragmentation utilities: You should periodically run disk defragmentation utilities on your server's hard disks. A high degree of hard disk fragmentation can lead to decreased hard disk performance.
Other Useful Tips:
While backing up or restoring databases manually from Query Analyzer using BACKUP or RESTORE commands, use the WITH STATS option. This option serves as a progress bar and displays the percentage of work done continuously.
Spread your backups across multiple backup devices residing on different hard disk drives. This lets SQL Server take advantage of parallel IO, and improves the backup and restore performance.
SQL Server 2005 lets you specify passwords for your backups. Use this feature effectively to prevent unauthorized access to backup files.
Consider implementing a combination of transaction log and differential database backups to reduce the time it takes to recover from a failure. This approach reduces the amount of transaction log that must be applied while restoring a database.
November 23, 2009 at 1:17 pm
Thank you
November 23, 2009 at 10:44 pm
You really should try Googling for stuff like this.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2009 at 11:03 pm
hey jeff, u got paid by Google or what for publishing their site?
LOL
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
November 24, 2009 at 2:00 am
sanketahir1985 (11/23/2009)
hey jeff, u got paid by Google or what for publishing their site?LOL
Jeff is just pointing out the obvious, better resources can be found by googling and quicker.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 24, 2009 at 5:21 am
Silverfox (11/24/2009)
sanketahir1985 (11/23/2009)
hey jeff, u got paid by Google or what for publishing their site?LOL
Jeff is just pointing out the obvious, better resources can be found by googling and quicker.
True. Ofcourse you can always post your queries in the forums like this, but meanwhile you can try yourself by googling as well.
November 24, 2009 at 5:27 am
sanketahir1985 (11/23/2009)
hey jeff, u got paid by Google or what for publishing their site?LOL
Hey Sanket !
What Jeff had replied is an apt reply, for such things you gotta search google and find out.
I actually replied to the Original Post and thought to include the same (search on Google) but somehow forgot to mention in my reply.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply