Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

An examination of bulk-logged recovery model Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 3:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 8:45 AM
Points: 44, Visits: 377
I re-created the statement(with a new database) and everything went OK!
But I really can't understand why I'm n0t getting minimal logging in the code posted on the following url:

This is the steps I'am doing

1º Create database
2 º Set Recovery Model to Bulk Logged
3º Verify on sys.databases the recovery model
4º Backup database (.bak)
5º SELECT INTO + SHUTDOWN WITH NOWAIT
6º DELETE MDF
7ºRESTART SQL
8º BACKUP TAIL LOG WITH NO_TRUNCATE
9ºRESTORE DATABASE WITH NORecovery
10º RESTORE LOG WITH RECOVERY(Error)
11 º RESTORE LGO WITH RECOVERY,CONTINUE_AFTER_ERROR(OK)
12 º SELECT * From SomeTable( AAAAAAAA)

Code Used: http://pastebin.com/NZqkcKSY


Thanks for your help


GilaMonster (1/14/2013)
Just go over it a couple times, make sure you are getting minimal logging. I ran that particular test a number of times, as did a friend and, while we got a variety of different errors, we did not get the table back intact after any of the tests.
Post #1406972
Posted Monday, January 18, 2016 4:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 2, 2016 6:59 AM
Points: 3, Visits: 51
Great! So informative with detailed scenarios.
Worth a read.

One more disadvantage of Bulk logged recovery model is that if there are Bulk inserts, transaction log backup contains modified extents instead of individual log records (in case of Full recovery model). Hence the size of log backup increases tremendously.
Post #1753315
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse