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»»

simple recovery model Log truncation Expand / Collapse
Author
Message
Posted Wednesday, March 17, 2010 3:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi
1) At what time intervel does the simple recovery model truncates the trans Log
2) I am having a 30 million records i need to delete 25 million old records with minimum log how it can be done there will be continues users accessing the table

Thanks
Parthi


Thanks
Parthi
Post #884492
Posted Wednesday, March 17, 2010 6:54 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: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
1) Whenever a checkpoint occurs. Only log records up to the beginning of the oldest open transaction can be truncated.
2) Do it in batches and run CHECKPOINT between those batches.



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 #884620
Posted Wednesday, March 17, 2010 7:28 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, May 11, 2014 8:07 PM
Points: 891, Visits: 235
I have simple solution for you.

Copy 5 Million records to another dummy or staging table.
Truncate the original table.
And then copy 5 Million rows from Dummy table to original table.

If you do not have partitions created on table this is the fastest way.




Post #884650
Posted Wednesday, March 17, 2010 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,112, Visits: 6,280
parthi-1705 (3/17/2010)
I am having a 30 million records i need to delete 25 million old records with minimum log how it can be done there will be continues users accessing the table


Understand that minimum logging is different than "no" logging. You might want to change the Recovery mode to Bulk-Logged instead of Simple (unless the db is already set to Simple) before doing your delete. This way, if you need to recover, you'll still have a transaction log to recover from.

In Simple mode, checkpoints happen quite regularly and automatically. Usually, you'll hear people say "the transaction log truncates all transactions as soon as they are commited." It's not quite a true statement, but it does mean that transactions are unrecoverable almost instantaneously. The logging that occurs is only for the SQL Server engine, not for the DBA.

Per BOL:
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full.

The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #884673
Posted Saturday, March 20, 2010 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
Brandie Tarvin (3/17/2010)
Understand that minimum logging is different than "no" logging. You might want to change the Recovery mode to Bulk-Logged instead of Simple (unless the db is already set to Simple) before doing your delete. This way, if you need to recover, you'll still have a transaction log to recover from.

Deletes are always fully logged (truncate table excepted, which is always 'minimally logged' - sort of) regardless of the recovery model.
Changing from simple recovery also changes nothing until a full backup is taken to establish a base for a future restore.
The log stays in auto-truncate mode until the first full backup after a change from simple recovery.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #886951
Posted Saturday, March 20, 2010 1:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
vidya_pande (3/17/2010)
I have simple solution for you.

Copy 5 Million records to another dummy or staging table.
Truncate the original table.
And then copy 5 Million rows from Dummy table to original table.

If you do not have partitions created on table this is the fastest way.


Simple, yes. But not always practical on a production system.
Also, remember that foreign key constraints will need to be dropped, indexes re-created etc etc etc
Good idea, though.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #886952
Posted Saturday, March 20, 2010 6:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 1:55 PM
Points: 45, Visits: 624
vidya_pande (3/17/2010)
I have simple solution for you.

Copy 5 Million records to another dummy or staging table.
Truncate the original table.
And then copy 5 Million rows from Dummy table to original table.

If you do not have partitions created on table this is the fastest way.




I had originally thought to do that since the table I had wasn't referenced by foreign keys. But its a replicated database, so no truncations allowed.
Post #887007
Posted Saturday, March 20, 2010 10:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
How To Delete a Large Number of Records
Limiting Deleted Rows by Using TOP




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #887028
Posted Monday, September 24, 2012 3:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 3, 2012 8:04 PM
Points: 37, Visits: 123
Hi there,

I used a script to changed around 60 cust-test DBs from Full to simple recovery model as they were not needed to be in Full recovery mode etc

I did the change on Fri and today is Monday..I was expecting the see lot of space freed up on the drive where files are stored, by this as thinking it will run checkpoint etc....but below reasons tells me it wont happen straight away:
================
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full.

The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.
=================


Some of these DBs have never been backed up but were in Full recovery model. So does this change anything?
or is it a simple matter of watining?







Post #1363377
Posted Monday, September 24, 2012 4:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 1,306, Visits: 2,468
hp_dba_uk (9/24/2012)
Hi there,

I used a script to changed around 60 cust-test DBs from Full to simple recovery model as they were not needed to be in Full recovery mode etc

I did the change on Fri and today is Monday..I was expecting the see lot of space freed up on the drive where files are stored, by this as thinking it will run checkpoint etc....but below reasons tells me it wont happen straight away:
================
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:

The log becomes 70 percent full.

The number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval option.
=================


Some of these DBs have never been backed up but were in Full recovery model. So does this change anything?
or is it a simple matter of watining?



Are you waiting for the space to be available to OS? If yes, that won't happen automatically. Changing the recovery model to SIMPLE does break the log chain & allows the transaction log to be truncated on every check point. However, truncatation of the transaction log means making the inactive VLFs inside the transaction log file as reusable & nothing else.

In case you want the freed space returned to OS itself, you need to shrink the transaction log file.

Note: If you know that after few days the transaction log file is going to be of the same size again (after shrinking), there is no point in shrinking it.



Sujeet Singh
Post #1363409
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse