Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

backup background process Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 3:38 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:12 AM
Points: 426, Visits: 1,582
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
Posted Tuesday, October 16, 2012 2:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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
Posted Tuesday, October 16, 2012 8:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 24, 2014 8:12 AM
Points: 426, Visits: 1,582
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 »

Add to briefcase ««12

Permissions Expand / Collapse