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.
hi Tarvin, is there any other parameter used by SQL Server to clear the logs for reuse apart from above 2 mentioned?
Can you be more specific with your question? Is this an informational question only or are you actually experiencing issues?
If the later... What database recovery model are you dealing with? What kinds of backups are you doing and how frequently? What are you looking for
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.