I have multiple databases on SQL Server. I loaded data into these database which took up 80GB. A few months later the database was at 120GB. I deleted a bunch of data from the database and only 3GB was freed up. I used the Database Maintenance Plan Wizard to free up space which it did. The database is now back to its original size. One step I skipped in the wizard was the backups. These are fine. Back ups and transaction logs are not causing the size issue. This wizard has narrowed down the problem because it removed unused space. My question is what space is it removing and what is causing the space to increase approximatley 40Gb in a number of months?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Controlling Database Size
- Thread starter drallabs
- Start date
Noramlly only a couple of things cause the database to grow like that. First are you deleting and populating tables repeatedly as if from a data import? If you are doing this by creating a work table and then renaming the work table to the old name at some point you have both tables and thus the database grew. If you are doing this, you may need to regularly schedule a shrink database command to run.
Of course you could just have a lot of new records.
Often the uncontrolled growth has to do with the transaction log not being backed up as it will continue to grow until the hard drive is taken up completely. Are you sure you are backing up the transaction log?
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
Of course you could just have a lot of new records.
Often the uncontrolled growth has to do with the transaction log not being backed up as it will continue to grow until the hard drive is taken up completely. Are you sure you are backing up the transaction log?
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
- Thread starter
- #3
I do delete and populate tables repeatedly. The type of deleting and populating is record based and table based. So are you suggesting to manage this issue using the Shrink Database utilitiy in Enterprise Manager vs the Database Maintenance Plan wizard? Is the issue page file fragmentation because of multiple edits which the shrink database will defrag?
The transaction logs are being backed up. Although I will notify the IT folks your suggestion, maybe they have to modify their backup scripts.
The transaction logs are being backed up. Although I will notify the IT folks your suggestion, maybe they have to modify their backup scripts.
Are the transaction logs being backed up in SQL Server not a network backup?
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
- Thread starter
- #5
- Thread starter
- #6
Then there is your problem. You must always use a SQL server backup of the transaction log in order to be able to shrink the log. Your transaction log currently has every transaction you have ever done in it.
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question