Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beanz
Starting Member
35 Posts |
 Posted - 2004-12-23 : 05:16:42
|
| Hi everyone.As part of my job I have to "look after" a dozen or so databases on a couple of servers.I have only recently become serious about doing this well and have previously left everything to defaults. What I have done is setup Maintenance Plans for each of the databases that do the following:Optimise the DBs - all options left at default.Complete Backup - Every nightTran log Backup - Every 4 hoursThere are also a couple of jobs to shrink the databases.Is this the right way to go about things or are there better ways and or other things I can do?I've read a lot of posts that recommend reindexing and defragging databases on a regular basis - how would I go about doing this?Also, what is the best practice with regard to transaction log size? I have generally tried to keep it small and also keep the amount of available space low. Is this right? What determines the amount of free space in a DB??Any feedback on this is greatly appreciated!Danny |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-12-23 : 07:38:52
|
| I'm also a "semi dba" at my company and even though I only have one db to look after, the tasks are about the same. Backup-plans seems to be in order I belive, but depending on the transaction level I would consider doing translog-backups a little more often. I have also set the backup files (both translog and full) to be transfered to another server using ftp immediately after the backup is done in case of a disk-crash, and also put on tape every night. In addition to this I have set up DBCC INDEXDEFRAG, DBCC UPDATEUSAGE and UPDATE STATISTICS to run weekly...and that's it (for me anyway)!--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
beanz
Starting Member
35 Posts |
Posted - 2004-12-23 : 07:58:40
|
| Thanks for the response.Can anyone provide more info about DBCC INDEXDEFRAG, DBCC UPDATEUSAGE and UPDATE STATISTICS??Also, can anyone comment on best practices regarding transaction log size and available space?Cheers,Danny |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-23 : 12:59:37
|
| Optimise the DBs - all options left at default.Beware that this will most likely set the FILL FACTOR, on your indexes, to 90% which will require extra effort, and log space, to reindex when you have an ascending primary key - such as IDENTITYComplete Backup - Every nightOKTran log Backup - Every 4 hoursIf you are going to do one at all I would suggest more often than this - either every hour, or every 10-15 minutes - it can't hurt, and might just save your backon one day!There are also a couple of jobs to shrink the databasesDon't do it! If you shrink the files they will only have to grow again - and allocating the new space is [relatively] hard work for SQL, plus it is likely to be using disk space which is more fragmented.If you do some massive deletion job then shrink might be worthwhile, but not if the DB is going to reuse that space again inside of a week, or two.Ditto for the Log size.recommend reindexing and defragging databases on a regular basisThe maintenance plan wizard thingie should sort this for you. That will be fine if your database is not too big (up to a gigabyte, or so). Beyond that, or if you have loads of databases and some are SIMPLE / ReadOnly/other flavours, OR if your DB needs write access 24/7 [Maintenance Plan fails if it cannot get the DB into single user mode] then it would pay to evolve your own scripts.I'd add:Copy the backup files to somewhere else, as Lumbago said.Do a "Fire drill" to restore the database periodically (daily would be good!) to prove that the backups are working OKKristen |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2004-12-23 : 14:02:19
|
| Microsoft provides a tool you can run to point out some better ways to do things. [url]http://www.microsoft.com/downloads/details.aspx?FamilyId=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en[/url]Justin"Hit me with a shovel 'cause I can't believe I dug you." |
|
|
|
beanz
Starting Member
35 Posts |
Posted - 2004-12-24 : 04:06:16
|
| Kristen, thanks for the pointers - extremely helpful.Justin, thanks for the link!Danny |
|
|
|
|
|
|
|
|
Best Practices