March 22, 2002 at 3:06 am
Windows 2000 (SP2) SQL 2000 (SP2)
DB: 23000 Tables 22 GB
Maintenace plan features:
18:00 Reorganization (about 3h40m)
22:00 Integrity Check (about 1h20m)
00:00 Full Backup (about 1h20m it results a file about 22 GB)
All these 3 operations are performed once in a week in this order.
Daily every hour: Transaction log backup (ready in seconds)
The problem: The first transaction log backup after the reorganization produces a file about 12 GB!! When the reorganization is not performed has the file a normally size (always under 120 MB).
I suspended on the day of the full backup the backup of the transaction log between 18:00 and 02:00, but the file resulting from the first transaction log backup is again huge: 12 GB!!
How is after a full backup such a huge transaction log backup file possible?
How can I prevent this?
The database was created under SQL 7 but the problem appeared only after the migration to SQL 2000. I administrate 3 identically servers and I have the same problem.
Thank you for your advises
Andreas Marner
March 22, 2002 at 5:47 am
Rebuilding your db is a huge amount of work, lots of stuff getting moved around in the mdf. Not much you can do about the log file size. That's why having tons of free disk space is so critical.
Andy
March 22, 2002 at 8:41 am
How are you reorganizing your database and how much space does the largest table take up. You could reorg each table and index if they are not too large and between doing this to each table release the log and shirnk it back down. Takes a little work but can save you hassel if you cannot give more space anytime soon.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply