| Author |
Topic |
|
lmm
Starting Member
1 Post |
 Posted - 2004-07-29 : 15:05:50
|
| I am normally a programmer but I voluteered to be the database administrator for our new sql server. So, I don't have much experience with database administration. I was wondering how often I have to do certain maintenance checks such as when to check for index fragmentation, or when to check for data integrity?I would appreciate any sort of timeline on when to do important tasks. And, any ideas on what kind of alerts should be created that would be handy.Thanks so much, laura |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-29 : 15:11:28
|
| Integrity checks should be performed every night. Defrags less often. We run DBCC DBREINDEX once a week and DBCC INDEXDEFRAG every few days. It just depends on your system. You can use DBCC SHOWCONTIG to view the fragmentation levels.Check these out for alternatives to the database maintenance plans:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-23 : 06:25:25
|
| I just found this post and I wondered the exact same thing. Is this all you do, CHECKDB and REINDEX/INDEXDEFRAG?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-23 : 08:46:02
|
| You need to schedule a shrinkdb once a week or so also.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-23 : 08:53:02
|
| Once a week? Is that really necissary (or how the he** you spell that word)?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-23 : 08:59:05
|
| necessaryYes, maybe not once a week, but definitely necessary.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-23 : 09:02:00
|
Hm ok, wasn't aware... (and thanx for the spelling lesson  )--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-23 : 09:06:08
|
| Is there really a benefit to regularly shrinking the database files? The advice that I have heard suggests set the files to the expected data volume before loading and leave them there, thus reducing the probability of OS level fragmentation, as well as shrink / re-grow overhead.-------Moo. :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-23 : 09:12:16
|
| We archive off our old data a lot, as do many companies. We also delete old data out of the system periodically. If you ever delete data, you need to do a periodic shrink of the database. Otherwise, you end up with a lot of empty space in the data files that will never be used. It's a waste of space; and can be a real problem.set the files to the expected data volume before loading and leave them there, thus reducing the probability of OS level fragmentation, as well as shrink / re-grow overhead.I guess if your files will never grow beyond that leve, you would be fine. That's not very realistic at most places though. Unless you're on a SAN, you should do periodic OS defrags anyway. The SAN or an advanced enclosure will take care of that for you though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-23 : 14:55:16
|
| YOu need to clear the old history data from MSDB. Tara's blogg has the details.I would suggest storing the disk freespace in the DB too - so you can see how it has changed over time. (We do a DIR / S and store that in the database too - in terms of any file that has an update-date or filesize which is different from the previous run we store as a transaction [against the filename]. This saved our backon when we got clobbered by a virus a while back, 'coz we could tell exactly which files had been altered. We also use it to monitor what files are changing, sometimes unexpectedly, and deciding if our backups "cover" enough areas of the system, etc.)Kristen |
|
|
|
|
How often to do certain maintanence tasks.