<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Kenneth Fisher
»
Full Backups
43 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Full Backups
Rate Topic
Display Mode
Topic Options
Author
Message
Gazareth
Gazareth
Posted Friday, July 20, 2012 2:58 AM
UDP Broadcaster
Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474,
Visits: 2,344
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.
Agreed.
Post #1332782
Lokesh Vij
Lokesh Vij
Posted Friday, July 20, 2012 4:31 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 1,230,
Visits: 1,364
+3
But someone from the moderator's team should have checked this before posting :)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions
Link to my Blog Post -->
www.SQLPathy.com
Follow me
@Twitter
Post #1332833
sqlnaive
sqlnaive
Posted Friday, July 20, 2012 5:08 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 2,797,
Visits: 1,745
This implies two things:
1. Any open transaction before full DB backup READ but not committed or rollbacked will be included in the full backup. [It sounds strange to me as ideally backups should not have consider any open transaction]
2. New transaction which is opened after full DB backup READ starts, which is also not committed or rolled back even after full DB backup READ ends, should not be included in the backup. [Which looks ok to me]
Could someone help me with the first point ?
Post #1332860
rfr.ferrari
rfr.ferrari
Posted Friday, July 20, 2012 6:13 AM
Ten Centuries
Group: General Forum Members
Last Login: Thursday, June 06, 2013 4:06 PM
Points: 1,219,
Visits: 13,509
WayneS (7/19/2012)
The full backup will also contain the uncommitted transactions from transaction 4 at the time that the backup finishes. If a restore is necessary, the UNDO portion of the restore will roll back those transactions. Therefore, the full backup will contain transactions from all four of the transactions.
A small error in the question: transaction 1 never is committed, and transaction 2 is committed twice (which can't happen). Obviously, one of those transaction 2 commits is for transaction 1.
+1
rfr.ferrari
DBA
- SQL Server
2008
MCITP | MCTS
remember is live or suffer twice!
Post #1332908
sestell1
sestell1
Posted Friday, July 20, 2012 6:45 AM
SSC Eights!
Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 856,
Visits: 1,278
Wow, interesting. I'd been under the false impression that the full backup used an internal snapshot to capture the state of the database at the time the backup started. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.
Post #1332920
george sibbald
george sibbald
Posted Friday, July 20, 2012 7:15 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 5,314,
Visits: 11,305
A full backup contains enough of the transaction log so the database can be made consistent on restore. The way the question is worded transaction 4 commits before the end of the backup so would be included in the backup.
---------------------------------------------------------------------
Post #1332947
thisisfutile
thisisfutile
Posted Friday, July 20, 2012 7:34 AM
Valued Member
Group: General Forum Members
Last Login: Friday, June 14, 2013 10:11 AM
Points: 56,
Visits: 492
Deleted by author (I neglected to read the first post before posting).
Post #1332959
mtassin
mtassin
Posted Friday, July 20, 2012 7:59 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 3,250,
Visits: 65,553
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.
Agreed +1
the hard part was with the execution being done so poorly, making sure of the intent of the question became much harder.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1332983
Kenneth.Fisher
Kenneth.Fisher
Posted Friday, July 20, 2012 8:22 AM
Hall of Fame
Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 3,367,
Visits: 1,577
I should apologize. I read the silly thing several times unfortunately I missed the fact that I failed to commit transaction 1 at all. To much copy and paste I guess. Obviously I need to read it a few more times next time.
Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Link to my Blog Post -->
www.SQLStudies.com
Post #1333003
tilew-948340
tilew-948340
Posted Friday, July 20, 2012 8:32 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Tuesday, April 02, 2013 8:28 PM
Points: 555,
Visits: 2,137
I just focussed on the last three lines and counted the 'begins' and 'commits': 4 begin, 3 commit and only the transaction 4 had been commit after the backup reading process...
12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase commits
12:20 AM - Full backup of QotDDatabase ends
Nice and simple logic...
Thanks!
Post #1333007
« Prev Topic
|
Next Topic »
43 posts, Page 2 of 5
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
