January 15, 2004 at 8:21 am
I have 2 SQL 2000 Servers running Windows 2003 Server. Both are used heavily on a daily basis and have anywhere from 30-50 databases on them at any given time. Databases are added, deleted, shrink, grow, etc. all the time.
I have 3 jobs running nightly...
Is there anything else I should be doing? Should I be running steps 2 and 3 on all DBs rather than just the user DBs?
Also, I would like to run defrag on a regular basis. Is there anyway to schedule a defrag in Windows 2003? If not, is there a free utility out there that will do it for me?
January 16, 2004 at 4:13 am
Running items 1 and 2 daily is probably overkill, and running item 3 at all is too often.
1) Integrity check
You need to balance your trust in the reliability of your environment, the reduced availability (mainly locking) and cost involved in running the integrity check, and the impact on you as a DBA if corruption is not noticed for a while. Actual occurrencies of database corruption are very rare nowadays, and we have decided that running integrity checks weekly is the best trade-off.
2) sp_updatestats
Statistics need to be relevant to the underlying data to allow the optimiser to choose the best access path. They only need updating when there are statistically significant changes. If combined I/U/D activity affects only 1% of your rowcount each day, then a statistics refresh every 2 weeks is probably ample. As we have a long quiet period at weekends, we have decided to update stats weekly.
3) Shrinkdb
Repeated shrink and growth of a database will rapidly cause NTFS file fragmentation. This will harm your performance and needs SQL Server to be stopped and NTFS defrag run to fix it. Best practice for shrink is only do it when there will be a permanent reduction in the database size. If any space you release with a shrink will be reclaimed within 1 month, then you are probably causing more problems than you are fixing by doing the shrink.
All information provided is a personal opinion which may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
January 16, 2004 at 4:19 am
A final point... one thing missing from your maintenance plan is backups. You are doing backups...?
For most sites, a full daily backup is the best option. Transaction log backups can be made at 1, 3, 6 hour intervals, whatever suits you best.
If you have large databases and have problems doing a full backup in the available time, then consider moving to SQL Litespeed before you move away from daily full backups. Litespeed is not expensive, and good price reductions are normally available for non-production systems.
If you have very large databases and cannot get a daily full backup run, then you have to move to differential backups. However, you should always aim to get at least 1 full backup done every week.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply