Scroll Bar


you can participate with latest Interview Questions here. Use this mail ID (bhanudba15@gmail.com) to send Your Questions.

Welcome to My Blog SQL DBA Interview Questions

What does a DBA need to check for daily, weekly, monthly, quarterly and yearly.
Introduction
This is just a quick checklist of all the things that a DBA needs to monitor on a daily, weekly, monthly, quarterly and yearly basis. Of course there are events that you want to know about immediately, for those events you can setup alerts or you can invest in a tool which will monitor SQL Server and alert you when something goes wrong
Monitor Daily


Check the SQL Server error log    
Check the error log daily or better yet several times per day. If you can set up alerts when things get written to the error log with a certain severity then try to do so

Check for failed backups   
The last thing you want to know is that your backup process has failed the day that you need to restore a database       

Check for free disk/file space      
You don't want users to tell you that their transactions are failing because the log or data file is full  

Error log/windows event logs      
Catch problems early, don't wait for the whole system to crash, if you catch it early enough you could prevent a disaster      

Monitor Buffer cache hit ratio and Page life expectancy       
Both of these counters are an indicator that you need more memory    

Check for failed SQL Agent jobs   
Make sure that you get notified when a job fails, some jobs might be time sensitive or a pain in the neck to run the next day because they might be configured in such a way that they expect to work only for the current date    

Monitor Deadlocks  
You can capture deadlocks in the errorlog by enabling the following trace flag DBCC TRACEON (3605,1204,1222,-1) 

Monitor Weekly


Cycle The SQL Server Error Log   
You don't want files that are huge, keep your log files small by recycling them once a week

Test your Full Recovery model by restoring backups 
This is similar to make sure that your backups work, how do you know that files even if completed successfully can actually be restored      

DBCC checks 
Make sure that you do a DBCC CHECKDB regularly, this will catch corrupted tables and indexes and the overall health of your databases     

Update statistics, check if statistics are stale  
If you don't have auto update statistics enabled then you need to make sure that your statitics are not stale, otherwise your queries might be slow        


Index maintenance 
Make sure that indexes are not fragmented, 
Monitor Monthly


Disaster Recovery testing 
Does your fail over strategy work, have you tried a mirror fail over? What happens if your whole datacenter goes down, do you have redundancy?
Check for Service Packs and Cumulative Updates       
Make sure that your systems are up to date. Remember the SQL Slammer worm, people who did not apply the latest SQL Server Service Pack got hit. Besides security, Service Pack contain bug fixes, improvements and sometimes even new features. Before you apply Service Packs and Cumulative Updates to you production servers make sure that you have tested them on your staging/QA servers.
Monitor Quarterly


Capacity planning    
Is your server in good shape to handle the extra data that will be stored in the next 6 months, do you have enough disk space, are the CPUs fast enough to hanle the load, do you need more CPUs? These are all question that you have to ask yourself before it is too late

Perfmon metrics (trending)         
Did you create a baseline for your server, do you know what a normal load is? The Creating a baseline for SQL Server has a couple of ideas          

Security audit           
Are users still allowed in that should not have, are users account still on the server even though they have left the company? 
Monitor Yearly


Data center/Hardware/Server planning           
Do you have enough rack space and servers for the next two years?

DisableRC