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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2006-06-11 : 20:40:01
|
| Hi,Recently I've stopped deleting data from my database after it was 14 days old, so my database is getting quite a bit larger.I have always had a DB Maintenance plan that ran daily in the middle of the night that did optimizations, created from sqlserver wizard. I ran it an hour before my backup plan, and from what I remember it reduced the size of the backup by quite a bit. (Storage is a bit of an issue for the time being)The problem is now that the database has grown so much, the database is taking so long at night to do its routine that it is effectively knocking the web application offline by timing out all inbound queries. Is there a way to throttle the resources allowed for this type of operation? I don't mind if it takes a lot longer, but knocking the website offline has to be rectified. Is nightly too frequent? Any suggestions on the best way to handle this ? I'm running on a dual xeon 2.8ghz with scsi drives in a mirrored RAID. Thanks very much.mike123 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-06-12 : 11:27:53
|
| You could do a full backup weekly, and an incremental backup each evening. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-06-12 : 12:59:37
|
| What is the size of the database in question?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2006-06-12 : 19:46:08
|
| I could do incremental backups yes, I will look at that possibility however I think its more of the optimizations that are slowing it down?The database is 5050 MB with 1420 space available.The last 2 file backups that are done after all the optimizations are 3643mb and 3578mb. From what I remember, they would be alot bigger if I backed up before these optimizations.Thanks for your guys help!!mike123 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-12 : 19:54:20
|
| You certainly don't need to run the optimizations part of the maintenance plan nightly. In fact, a database that is around 5GB doesn't need the reindex anyway. I reference a Microsoft article in the following blog that explains why:http://weblogs.sqlteam.com/tarad/archive/2006/04/11/9585.aspxAlso, it would be very helpful if you showed us what each of the screens in the maint plan had selected. You might be selecting something in there that is not only not needed by maybe not recommended.The optimizations part of the maint plan runs DBCC DBREINDEX. Tara Kizeraka tduggan |
|
|
|
ras2a
Yak Posting Veteran
66 Posts |
Posted - 2006-06-21 : 11:27:23
|
| I am thinking of enabling optimization features on our database. However, we only have around 30 users and the main database is very small (around 250MB). So, do I need to carry out 'any' of the optimizations features, and if so, which ones and how often?Many thanksras |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-21 : 12:28:46
|
| Did you read the link?Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
How often to optimize your database?