April 11, 2011 at 10:06 am
What would happen if I switched from Full to Simple to Full in a stored procedure?
When I back up the transaction log after I set it back to Full, would it be able to know that there was a full database backup (.bak)? I am running SQL Server 2008.
Thank you.
April 11, 2011 at 10:15 am
The same as would happen if you change it anywhere. The log chain is broken and you will need to take another full backup before you can take further transaction log backups. Until you do, the database is at risk of data loss in case of a disaster
Why are you considering changing to simple recovery in a stored proc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 11:05 am
Thank you very much for the info. 🙂
As for why change it. This is a vendor supplied stored procedure. It is doing reindexing of several tables.
April 11, 2011 at 11:25 am
Politely suggest to your vendor that they switch to bulk-logged recovery. It'll minimally log then index rebuilds, but won't break the log chain.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 2:39 pm
GilaMonster (4/11/2011)
The same as would happen if you change it anywhere. The log chain is broken and you will need to take another full backup before you can take further transaction log backups. Until you do, the database is at risk of data loss in case of a disasterWhy are you considering changing to simple recovery in a stored proc?
I have seen this before from vendors - where they believe this will 'solve' the problem with the transaction log growing too large.
All this does, as Gail stated above - is break the log chain and prevent you from being able to restore to a point in time if you have to revert back to a prior known good backup. For example, if the latest available backup file is corrupted - with an unbroken log chain you could use the prior backup and restore all log files to the current point in time. However, once the log chain is broken - that is impossible.
If you really need to limit the growth of the transaction log, it would be much better to switch to bulk_logged and then back to full. This does not break the log chain and still allows for possibly reducing the transaction log usage during the maintenance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2011 at 2:12 am
no need to take a FULL backup here to restart the log chain, a DIFFERENTIAL will suffice and allow subsequent transaction log backups
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2011 at 7:25 am
Perry Whittle (4/12/2011)
no need to take a FULL backup here to restart the log chain, a DIFFERENTIAL will suffice and allow subsequent transaction log backups
This is true, but the log chain is still broken and you have a problem if you need to recover using a prior backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply