InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 00:37:58 GMT20http://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks LynnTue, 20 Nov 2012 08:36:18 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspx[quote][b]Ratheesh.K.Nair (11/20/2012)[/b][hr]In the procedure they are deleting values before a particular date.Values are deleted from a master table and inserting into a another table.Smaller chunks in the sense changing the date value to a shorter period will help??[/quote]This article may be of assistance: [url=http://www.sqlservercentral.com/articles/T-SQL/67898/][b]Deleting Large Number of Records[/b][/url].Tue, 20 Nov 2012 07:07:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks againTue, 20 Nov 2012 04:45:54 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxMaybe, but I meant deleting smaller chunks in a while loop.Have a search for batched deletes, should find you somethingTue, 20 Nov 2012 02:32:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxIn the procedure they are deleting values before a particular date.Values are deleted from a master table and inserting into a another table.Smaller chunks in the sense changing the date value to a shorter period will help??Tue, 20 Nov 2012 02:19:23 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxDelete in smaller chunks.Tue, 20 Nov 2012 02:15:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks a lot Gail.What i need to do to resolve this high space usage in this case?Tue, 20 Nov 2012 02:11:34 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxNope.Simple and bulk-logged allow for minimal loggingBulk-logged and full allow for log backupsTue, 20 Nov 2012 02:09:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks Gail.I was under the impression that only BULK LOG=minimal loggingTue, 20 Nov 2012 02:05:06 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxNo, it'll make it worse or no change at all.Simple recovery - minimal logging, log truncated on checkpointBulk-logged recovery - minimal logging, log truncated on log backup (if a full or diff backup has been taken since switching)So why would you switch to bulk-logged and have the log be harder to reuse with no other gain?Tue, 20 Nov 2012 02:02:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks Gail for the reply.We have a proc from developers to delete around 700 millions records from a table,this caused database log files to grow very high that the disk space reduced to 100GB from 1.9TB. The database is in SIMPLE recovery mode and i hope changing it BULK LOGGED RECOVERY model will help.Tue, 20 Nov 2012 01:59:23 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspx[quote][b]Ratheesh.K.Nair (11/20/2012)[/b][hr]Thanks Everyone.Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLE[/quote]My first question would be why you're changing the recovery model to bulk-logged for a short period.If you want to initialise the log chain so that you can take log backups, you need to take a full or diff backup after switching to bulk-logged, but the fact that you're switching back to simple suggests you don't want log backups, so I fail to see the point of changing to bulk logged.Tue, 20 Nov 2012 01:55:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks Everyone.Do we need to take any kind of backup when changing database recovery model from SIMPLE-->BULK LOGGED-->SIMPLEAlso please let know whether its necessary to take these backups after changing recovery model for those changes to come in or is it as part of recovery??Tue, 20 Nov 2012 01:14:38 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks PerryTue, 09 Oct 2012 06:00:45 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspx[quote][b]Ratheesh.K.Nair (10/9/2012)[/b][hr]Hi Experts,I know we have to run a full backup if we change the recovery model from full--> simple-->full. [/quote]No, you can just run a differential to restart the log chain, no need for a fullTue, 09 Oct 2012 05:42:18 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxThanks Donor:-DTue, 09 Oct 2012 05:05:38 GMTRatheesh.K.Nairhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxNo you don't, but you should run a log backup again, to re-gain point-in-time recovery.[url]http://msdn.microsoft.com/en-us/library/ms190203(v=sql.105).aspx[/url]Tue, 09 Oct 2012 04:54:39 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1370300-146-1.aspxHi Experts,I know we have to run a full backup if we change the recovery model from full--> simple-->full. My question is do we need to run full backup if changed from full--> bulk logged-->full?TIATue, 09 Oct 2012 04:42:25 GMTRatheesh.K.Nair