SQL Server Performance Forum – Threads Archive

Shrinking a transaction log

Hello, If I set a databases recovery model to simple, run dbcc shrinkfile on the log and then set the DB back recovery model back to full; does that mean I have wiped out all of the transactions yet to be written to the database? Or are they written before the switch to simple is made. I was working with a database whose log was 13 gigs, 11 used and 4 free. I found a script that did what I just described. Well, I ran it without thinking – I know, stupid. So, I am wonder what I just did to this databases trans log. Because after I ran it, the log file shrunk to 19 megs. Thanks
J

quote:Originally posted by loiter99 Hello, If I set a databases recovery model to simple, run dbcc shrinkfile on the log and then set the DB back recovery model back to full; does that mean I have wiped out all of the transactions yet to be written to the database? Or are they written before the switch to simple is made. I was working with a database whose log was 13 gigs, 11 used and 4 free. I found a script that did what I just described. Well, I ran it without thinking – I know, stupid. So, I am wonder what I just did to this databases trans log. Because after I ran it, the log file shrunk to 19 megs. Thanks
J

You have lost nothing, but you have to backup database now.

True, chaging the recovery model from FULL – SIMPLE – FULL will make database tlog inconsistent, so you must perform FULL database backup in order to continue next set of transaction log backup. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you for the reply. Can you elaborate? Why does it make the tlog inconsistent? Thanks
J

Because it doesn’t store all the transactions for historical backup purposes once the recovery mode is set to simple. Therefore, when you reset the mode, you have to do FULL followed by the normal transaction log backups again. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Hi,
suggest to read *Recovery Models : Selecting a Recovery Model * for more details…
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

RECOVERY FULL | BULK_LOGGED | SIMPLE When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged. For more information, see Full Recovery. When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. For more information, see Bulk-Logged Recovery. When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup. For more information, see Simple Recovery. SIMPLE is the default setting for SQL Server Personal Edition and the Desktop Engine (MSDE 2000), and FULL is the default for all other editions. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>