| Author |
Topic |
|
dbMartiN
Starting Member
26 Posts |
 Posted - 2004-08-30 : 09:24:06
|
| Hello...all of youI usually work as a programmer but in my company I also has a little responsible for the SQL Servers that we are running, but my knowledge about it is not that big... :)I have som questions regarding how to setup the proper maintenance jobs for SQL Server 2000...We take backups every night and that works fine, but I really want to know what other maintenance jobs is necessary for the databases to work fine and without any errors...Shrink? Backup log transactions? Integrity jobs? ..and so on...Thanks in advice.../MartinThanks for your help! |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-30 : 10:16:31
|
| If you don't have that much time to work on this, use the maintenance plans. You need to schedule the following to run weekly or monthly, depending on how big your databases are and how much they are used:--Reorganize data and index pages.--Update statistics.--Remove unused space from database files.--Check database integrity.Also, on the backup, make sure you always check the integrity after it runs.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-08-31 : 08:53:13
|
| Thanks for your support...Thats right... I dont work full time with database administration so my time is limited.Our databases is from 500MB to 15GB, and is used frequently. We take backup every night on every databases (almost 40).After backup, how do you check the integrity then?At last, I have one more question:A Maintenance Plan job with optimization job, integrity check job and so on.. is that enough administration or am I going to do Shrink jobs also? (what is the different? is the shrink job within the maintenance plan?)That was a lot of questions but I hope you can help me... :)Thanks in advance.../MartinThanks for your help! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-31 : 22:57:06
|
| Do that before the backups Martin. You need to schedule your shrink job seperately. I would normally do it once a week on a weekend for example. The shrinkdb is in the maintenance plan. Create a seperate one just for that though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-01 : 05:43:01
|
| Hi derreck,Sorry to butt in but instead of creating another thread i think my question is related to Martin's.What are the statistics that you usually update, so far I just had the "inherited" servers sorted out and now rolling out long term maintenance plans. What do i need to look out for?thanks in advance... |
|
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-09-20 : 04:09:45
|
| Hello again... :)So if I make a Maintenance Plan with: 1. Integrity Job (Before backing up the database), scheduled once a week.2. Complete Backup, scheduled every night.And then I make a separate Job:3. Optimizations Job (Reorganize, Update, Remove), scheduled once a week.Is this a good way to admin the databases??What about Backing up the Transactions Log??Grateful for all your help!Kind regardsMartinThanks for your help! |
|
|
|
Kristen
Test
22859 Posts |
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-09-20 : 10:05:34
|
| Ok, thanks Kristen, do you run Maintenance Plans and how did you set it up?How are you scheduling the clearing of historical stuff from MSDB?KRMartinThanks for your help! |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-09-20 : 10:55:00
|
| Jen, the statistics that need updating can be donw by running this against your db :exec sp_updatestats |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-20 : 13:43:56
|
| "do you run Maintenance Plans"I wrote my own SProcs to do the job. I wanted differential backups, as well as Full and Transaction, and the Wizard doesn't offer this. Plus the wizard tries to do "fixes" on readonly databases; and databases which are offline etc.; which cause the mainteannce plan to fail. And it tries to put databases into single user mode and when it can't, e.g. because someone is connected, it fails. Basically this means it fails all the time!And it doesn't clear down its own MSDB history.Yeah, we agree: MS got a summer student to write the wizard ... right? :-(Tara has got some good stuff in her Blog which may help youhttp://weblogs.sqlteam.com/tarad/Kristen |
|
|
|
hismightiness
Posting Yak Master
164 Posts |
Posted - 2004-09-21 : 09:29:19
|
| I was going to post my own questions on this topic, but low and behold some of my questions are answered here. However, should there be a difference in our maintenance plans for User DBs vs. System DBs?- - - -- Will -- - - -http://www.servicerank.com/ |
|
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-09-22 : 07:59:34
|
| "Shrink Database" and the option "Remove unused space" is that the same thing?KRMartinThanks for your help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-22 : 12:49:39
|
| "User DBs vs. System DBs?"System DBs don't have any LOGs, so you can't do Transaction Log backups. Unless your databases have no apprecable real time data updating then I reckon you should have transaction backups on your User DBs (either hourly or every 10 minutes seems to be the norm around these parts, depending on how much data you can afford to lose)Kristen |
|
|
|
dbMartiN
Starting Member
26 Posts |
Posted - 2004-10-21 : 09:37:24
|
| Hello again :)Is there someone who knows a good site where this topic is described, where I can see all the steps to get the perfect administration jobs for my SQL Servers..?About the shrink job:What is the different between making a Maintenance Plan with "Remove unused space" or to do a script like this??:"dbcc shrinkdatabase (ABC)use ABCgoBackup Log ABS with no_logDBCC Shrinkdatabase (ABC)Go"Thanks for your help!MartinThanks for your help! |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
Database Maintenance..(shrink?, maintenance plan?)