InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 05:56:07 GMT20http://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]Divine Flame (9/24/2012)[/b][hr]To understand it better I am pointing you to an excellent article written by Gail:[b][url]http://www.sqlservercentral.com/articles/Transaction+Logs/72488/[/url][/b][/quote]ThanksMon, 24 Sep 2012 14:15:10 GMThp_dba_ukhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]hp_dba_uk (9/24/2012)[/b][hr]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?[/quote]Oh, heavens. I think you just granted me my daily dose of database terror with that statement.If "some of these" databases have never been backed up, the first thing you should do upon reading this message is a FULL backup on all your databases. Then test the backups on a sandbox server to make sure your backup processes are working and not corrupting anything.The second thing you want to do is schedule regular backups (full, differential, file, etc.) based on your recovery strategy.Then, and only then, worry about your transaction log. There's a good chance the transaction logs are larger than they need to be if they never got backed up. Your best bet, if you really really need the drive space (OS as Divine Flame commented), only shrink by small increments and let the log sit for a few days to see if it increases in size again. If not, shrink another small increment. If you try to shrink too much of the log, you may see a performance hit as the sql engine auto-grows the log again to account for the needed space. Especially if it has to "thrash" for the growth. (Even though it isn't really disk thrashing when the engine grows the file, I do actually use the phrase "thrashing" to describe the back and forth performed by the engine when it encounters a file that's just too small and the AutoGrow setting is also too small for the current set of transactions.)If, however, you don't need the disk space, I strongly advise leaving the log file alone.Mon, 24 Sep 2012 05:04:21 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspxTo understand it better I am pointing you to an excellent article written by Gail:[b][url]http://www.sqlservercentral.com/articles/Transaction+Logs/72488/[/url][/b]Mon, 24 Sep 2012 05:00:23 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]hp_dba_uk (9/24/2012)[/b][hr]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 etcI 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?[/quote]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.Mon, 24 Sep 2012 04:51:43 GMTDivine Flamehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspxHi 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 etcI 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?Mon, 24 Sep 2012 03:17:22 GMThp_dba_ukhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[url=http://www.sqlservercentral.com/articles/T-SQL/67898/]How To Delete a Large Number of Records[/url][url=http://msdn.microsoft.com/en-us/library/ms175486.aspx]Limiting Deleted Rows by Using TOP[/url]Sat, 20 Mar 2010 22:29:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]vidya_pande (3/17/2010)[/b][hr]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. ;-)[/quote]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.Sat, 20 Mar 2010 18:52:56 GMTThe Natrixhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]vidya_pande (3/17/2010)[/b][hr]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. ;-)[/quote]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 etcGood idea, though.Sat, 20 Mar 2010 13:14:39 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]Brandie Tarvin (3/17/2010)[/b][hr]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.[/quote]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 [i]full backup[/i] 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.Sat, 20 Mar 2010 13:13:27 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx[quote][b]parthi-1705 (3/17/2010)[/b][hr]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 [/quote]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.Wed, 17 Mar 2010 07:57:50 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspxI 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. ;-)Wed, 17 Mar 2010 07:28:34 GMTvidya_pandehttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspx1) 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.Wed, 17 Mar 2010 06:54:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic884492-146-1.aspxHi1) 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 ThanksParthiWed, 17 Mar 2010 03:50:53 GMTparthi-1705