| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2010-10-24 : 10:21:22
|
| Just a general question around a converation I was having the other day:When a FULL backup is performed, is the transaction log read or affected in any way?Thanks. |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-10-24 : 12:28:52
|
| No it wont.Why do you think how will it perform the transactional log?PBUH |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-24 : 13:11:16
|
| Read, yes. Affect, no.A full backup will read the log as it needs to include within the backup file enough of the log so that it can be restored to a consistent state.A full backup does not truncate the transaction log (in full and bulk-logged recovery) and so has no effect on the log chain.--Gail ShawSQL Server MVP |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-10-24 : 16:33:08
|
| Is this true also when recovery mode is simple? Does the backup still need to read from the transaction log? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-24 : 17:54:23
|
| Yes. In all recovery models the full backup has to read the transaction log and include within the backup file enough of the log so that it can be restored to a consistent state.In simple recovery, the log is auto-truncated whenever a checkpoint occurs and a full backup does a checkpoint before it starts. In full and bulk-logged the log is truncated only when a log backup occurs, a full backup does not (and never has) truncate the transaction log.Am curious. Why are you asking?--Gail ShawSQL Server MVP |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-10-25 : 07:05:37
|
| Hi Gail,Thanks for your input here. Basically we have been having performance problems on a SQL 2008 instance on a Hyper V. Our infra team had set up the shared discs to expand dynamically which after some investigation appeared to be the wrong setup in this scenario. We were seeing poor performance and high avg wait times etc. They then changed the disc holding the .MDF's to a dedicated fixed disc, removing the software layer. The question then arose as to whether the same should be done for the disc holding the .LDF's also. Currently, all databases are in simple recovery so only full backups are taken. The nightly full backups are taking a long time to complete for the spec of the machine (3.5 hours for around 30gb backup data stored locally on this drive). One of the infra team asked if when a full backup was taken was the transaction log read/written/modified in any way as because the disc was potentially not configured correctly the I/O could be the cause of the slow backup times, hence the requirement to convert this disc also. I said that reads would be used in the backup (as confirmed by yourself) but couldn't see why any writes would occur for a full backup, transaction log backup yes, but not a full backup.Anyway, the log disc was rebuilt and backup performance hasn't improved at all although the server in other non-SQL areas does appear to perform better due to the new disc configurations. I am now wondering whether the slow backup time may be due to the native backup compression which I am using for the first time. I've used Hyperbac and Redgate SQL Backup extensively in the past and never noticed slow backups from them, maybe the native backup compression is different. I'll know in about 1 hour or so.Sorry for the long winded reply, don't know if you have anything to add here Gail.Pat. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-10-25 : 07:41:58
|
| Backups are all about IO, on the mdf drive and on the backup destination. Where are you writing these backups to? If it's the same spindles as the mdf then that's going to really slow things down, as you're reading and writing to the same device. Ideally backups should be done to a separate physical drive array.Typically backup compression makes backups faster (often much faster) as it reduces the IOs and backups are typically IO-bound operations. Only time it's likely to cause slower backups if is the server was already experiencing severe CPU contention.--Gail ShawSQL Server MVP |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-10-25 : 08:29:25
|
| Backups are currently writing to the drive the logs reside on. I tried both, writing to logs drive and then data drive and both have almost identical times. Even if there was disc contention while reading and writing to the data drive I strongly suspect that 3.5 hours is far too much for a 30gb (uncompressed) backup file. When using compression this is down to 5gb and still takes 3.5 hours to complete. I am currently running the backup without compression on the logs drive and it's still going after 2hrs 15 minutes so I suspect that this will take at least the same time as the compressed backup. Like you, i would expect the compressed backup to be faster which it may be but still takes 3.5 hours to complete on either drive. CPU never grumbles, never moving over 10%. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-10-25 : 08:41:41
|
| Our infra team are confident that this VM is now set up correctly but I am not convinced. Are there any special considerations for SQL Server on a VM? Short of recreating this instance on another machine to see if backup performance improves I am at a bit of a loss as to why backups take so long. The actual backup process is not the main concern for me, it is more about if this is a pointer which suggests something is very wrong with the VM. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-25 : 09:00:46
|
"I strongly suspect that 3.5 hours is far too much for a 30gb (uncompressed) backup file"By way of comparison, I've checked our latest full backup. 20GB Backup file, uncompressed, SQL2008 took 8 minutes. This is a busy server (it would have been in use at the time) and is reasonably well spec'd server, but not a full-blown fire-breathing dragon  |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2010-10-25 : 09:16:06
|
| Thanks Kristen. While not a monster this is a reasonable server also (albeit a VM but the only active VM on the physical machine). CPU isn't under any stress at all, SQL Server has 6gb RAM dedicated. The server appears very healthy. When the scheduled backups ran there was absolutely no other activity on the server.My uncompressed backup test has just completed. 2 hours 47 minutes to produce a 30gb file on the logs drive. This file has been produced while there are always (a few) active connections and has beaten the compressed backup time (which took place with no active connections) by over 30 minutes?? This can't be down to compression. 2 hours 47 minutes is still very poor....... |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2010-12-29 : 10:10:11
|
| I'd ask to see a build sheet. What did they buy?. Typically mid level drive arrays are putting out about 5Gig a minute for reads and writes. Ask them to do a file copy of 5Gig on and off the arrays or VHD's, take the SQL server contribution out of this optimization until you are assured the problem does not lie elsewhere."it's definitely useless and maybe harmful". |
|
|
|
|
Full Backup and transaction logs