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.
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 AdministratorLiveJournal Blog
, and Twitter
.Freelance Writer: ShadowrunLatchkeys: Nevermore
, Latchkeys: The Bootleg War
, and Latchkeys: Roscoes in the Night
are now available on Nook and Kindle.