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 |
|
pearsont74
Starting Member
27 Posts |
 Posted - 2007-02-21 : 15:27:48
|
| ok...just want to get a oppion on this. looks like it could be useful to me but i also thought the auto shrink look like a good feature lolpro and cons and setting that are recommended.goal is to trim the size of the db as much as possible. we ahve a db we import new datasets to monthlyand its gettin huge...not to mention the log is too....thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 15:34:37
|
"but i also thought the auto shrink look like a good feature"OK, so you are willing to learn ...  "goal is to trim the size of the db as much as possible"bad idea. If the database needs xGB to get through the week then give it X to work with. If you give it some smaller number it will have to extend it during the week, and shrink it back again at some point. That will consume loads of resources, and fragment the file.Work on preventing "exceptional" growth of the files. Make TLog backups as often as possible (every 10 minutes is good), worry about processes that extent the TLog hugely (Reindex for example) and split them into smaller process were prudent.if you have enough disk space, and the Log Size is tolerable, then just Live With It."we ahve a db we import new datasets to monthlyand its gettin huge...not to mention the log is too"Consider importing, minimally-logged, into a staging database, and then from there "optimally" into the real database.Optimally might mean:"In small batches" so that TLog backup gets a chance to backup regularly - rather than just once at the end of the job"Where changed" - use a more slinky process:1. DELETE if no-longer-in-the-import-file2. UPDATE if in-the-file-but-changed-in-at-least-one-column3. INSERT if not-in-the-fileKristen |
 |
|
|
pearsont74
Starting Member
27 Posts |
Posted - 2007-02-21 : 16:10:35
|
| absolutely im willing to learn :)ok...so run the back up the tlog often...should the db maintenance plan be used for this? everthing else we basically do...one question, if reindexing is a tlog filler...how do we get around having to do this. i was told somewhere along the line that when i do imports its best to drop the indexes...is this true? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-21 : 16:39:09
|
"should the db maintenance plan be used for this?"My "take" would be:The "serious" folk here don;t use Maintenance PlansHowever, they probably took countless hours coming up with "better mousetraps"So, unless you have the time & inclination the Maintenance Plans are a good place to start.However, be prepared to discover their shortcomings and build your own replacements"if reindexing is a tlog filler...how do we get around having to do this"You could use INDEXDEFRAG instead; the relevant WhitePaper suggests that this is pretty much as good as a REINDEX. Particularly in combination with DBCC SHOWCONTIG so that you ONLY rebuild the indexes which are sufficiently fragmented to warrant it. For my money it keeps the MDF smaller (and the LDF of course), and that reduces the overall size of the "elbow room" for the database, which in turn means that a restore is quicker (this is more so under SQL 2000 than SQL 2005)"i was told somewhere along the line that when i do imports its best to drop the indexes...is this true?"If you are reimporting ALL the data (or maybe MOST of it) then yes.DROP INDEXESTRUNCATE TABLEIMPORT DATACREATE INDEXESis going to be more efficient than:CREATE INDEXESTRUNCATE TABLEIMPORT DATAHowever, it would probably be best to:1. Have the Clustered Index in pace during the Import2. Make sure that the import data is pre-sorted in Cluster Index order3. Provide the appropriate "hint" to the Bulk Import that the import data is pre-sorted  Kristen |
|
|
|
|
|
|
|
|
Database maintenance plan? good or bad