backup background process

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » backup background process

13 posts, Page 2 of 2

««1 2

backup background process

Rate Topic

Display Mode

Topic Options

Author

Message

Yuri55

Posted Monday, October 15, 2012 3:38 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 400, Visits: 1,431

Make sense- thanks. If you do not mind 1 more question- regarding TrLog- same story or not? I mean original DB has 90% free log (let say after huge index rebuild and no shrinking issued) Seems to me there is no reason to restore same huge Trlog but only active part of it (i.e. 10%). Or am I wrong? Thanks

Post #1372972

GilaMonster

Posted Tuesday, October 16, 2012 2:34 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

Same story and same reason.

How is SQL to know that the 90% free space in the log is necessary or not? Let's say the log reaches 20GB used every night at 7pm but by the time the backup runs whatever job that was has finished and the log is using maybe 50MB. If a restore of that shrunk the log to 50MB then there would be either unpleasant surprises the next night at 7pm (log growth is resource-intensive) or the DBA would have to remember to grow the log after any restore.

There's one time SQL does shrink the log by itself (reverting from a snapshot), and it's considered a huge bug/flaw.

Oh, and to do what you want, the restore would have to do huge amounts of work that it does not currently do, reading pages and allocation structures, changing page headers, updating allocation pages, index next, previous, parent and child pointers and a ton more. Would make restores significantly slower than they are now, which is really not a good thing.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1373078

Yuri55

Posted Tuesday, October 16, 2012 8:34 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 400, Visits: 1,431

To resume- restored DB is always identical backed up one (like mirror copy). Thanks a lot, Gail for your time and detailed explanation. Yuri

Post #1373312

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions