Database Size
-
We currently have a 2000 database approaching a size of 13G. The data is broken into yearly tables (beginning in the year 2000) along with some data that does not change from year to year (probably about 5% of the data). Each year, we create a new set of yearly tables (next year it will approach 17G...). Although not common, we are still editing/reviewing data from all the previous years.
We've had a few people concerned about the size of the database and possibly putting each subsequent years data into its own database instance.
Should we be concerned about the size of the database (backups and such take longer, but we only access the data during normal business hours)? Are there performance hits we are taking with the larger database?
Please note, I'm not a DBA (obviously). And as per the design of having yearly tables, it is a business requirement we cannot change (I'm certain there are better ways, but not available to us at this time).
-
first thing i would suggest have sufficient disk space for the databases.......you can schedule the backups in the off bussiness hours so that i would not cause any performance problems......if the db is extremely large you can schedule the full backup on every weekends and differential backup every day with tran log backups if required...........yes you can put each years datas into a seperate db but anyways the size will be the same....
[font="Verdana"]- Deepak[/font]
-
IF the data doesn't change, then one thing you can do is create a new filegroup/files and put that data in there. You can move existing tables by moving their clustered index into the new filegroup. You can mark it as read only and then back it up separately.
It doesn't hurt to have one large db, I've had Dbs in the 100s of GB before, but your backup and restore windows are larger. If you separate the filegroups to different arrays, then you can also protect yourself in case of a drive failure and only have to restore one of the file groups.
I wouldn't put the data into a separate instance, if I wanted to, I'd use another DB on the same server.
-
Those are all good suggestions and I'll suggest a bit more to go with those... put the Archived years in their own "read only" database, back it up once, and then, stop backing it up until next year
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply