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 |
|
cosy
Starting Member
22 Posts |
 Posted - 2008-11-20 : 18:17:05
|
| HI All, I set my MP to as follows and noticed when the database are backing up no transaction file create. but after the backup it will create the big transaction file.MP: Optimize:12.00AM, Index:11.30PM, Backup: 1.00AM, Trans backup: 12.00am to 11.59PM every 20 minSo my backup take 3 hours ( 65Gig) so at 4.00AM i can see 17GB transaction (trn)file?So how do i avoid this? do i need to run trn bak after the backup or just the trn backup only 6.00AM to 11.59PM? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-20 : 19:49:09
|
| Also - 65G full basckup and 17G tr log after 3 hours?Sounds like you are updating an awful lot of rows - which the full backup would need to include after it's completed it's first pass.I would have a look at your processes and make sure you are only updating necessary rows - e.g. not updating to the same value.How do you do the backup? Single thread/file to same disk as the database?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
cosy
Starting Member
22 Posts |
Posted - 2008-11-20 : 23:55:08
|
| HI, With my Maintenance Plan i do the backup to RAID 5 disks. Transaction logs backups to the different volume.Do i need to run trn backup at night time? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-11-21 : 03:16:43
|
| raid 5 - good for reads bad for writes. Backup = writes.Why?Do you have your database and backups striped over the same raid 5 disc?Have you seen raid 10.Think this needs a Goddess.I don't think the figures you are showing will be just down to disk performance though.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
cosy
Starting Member
22 Posts |
Posted - 2008-12-04 : 19:38:12
|
| HI All, New to SQL administration, I setup SQL DB bak to weekley but still i can see the 17 gig TRN file at 3.00AM?I run my Backup exec on night just to backup these bak file so is this doing something? sql agent? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Transaction Log Backup & DB Backup