December 7, 2009 at 8:59 am
Hi,
I got a interesting requirement where I need to delete data in all the tables where planid<>2 of a database. As there are lot of tables I wrote a query to get tables which has column planid and started delting it..As we know delete logs each row in T-log ...so my t- log is keep increasing (Of course my DB is in simple mode ..) but I dont want sql server to log anything ...
Is this option available in sql server?
December 7, 2009 at 9:08 am
as far as i know there isnt an option to disable logging, except of course changing the RM to simple. Do a search in the articles from Lynne Petis (sorry if the spelling is wrong), she has done a really good article on deleting a large number of rows in batches using rowcount. That will limit the hit on your t-log
if i find the link ill post it myself
edit: just realised that RM will make no difference as your running it as a single transaction anyway.
December 7, 2009 at 9:09 am
December 7, 2009 at 9:18 am
Animal Magic (12/7/2009)
as far as i know there isnt an option to disable logging, except of course changing the RM to simple.
Changing the recovery model to Simple doesn't disable logging. With the exception of bulk-operations, the same amount of information is logged in full recovery as in simple. The difference is how long the log records are retained and what causes a truncation.
There is no way to perform an unlogged operation in SQL. Some minimally logged (some depending on recovery model) but nothing is unlogged.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2009 at 9:20 am
GilaMonster (12/7/2009)
Animal Magic (12/7/2009)
as far as i know there isnt an option to disable logging, except of course changing the RM to simple.Changing the recovery model to Simple doesn't disable logging. With the exception of bulk-operations, the same amount of information is logged in full recovery as in simple. The difference is how long the log records are retained and what causes a truncation.
There is no way to perform an unlogged operation in SQL. Some minimally logged (some depending on recovery model) but nothing is unlogged.
cheers gail, i did notice that after i posted so went back and edited it 🙂
December 7, 2009 at 9:25 am
Thanks.
Can we force sql server to truncate log at my convience (using checkpoint...) at the earliest rather than sql server doing by its own?
December 7, 2009 at 9:29 am
kiransuram19 (12/7/2009)
Can we force sql server to truncate log at my convience (using checkpoint...) at the earliest rather than sql server doing by its own?
Yes. In Simple recovery the log is truncated on checkpoint. However, only inactive log records can be truncated, so if you're doing the delete as a single transaction, no amount of checkpoint is going to help, the transaction must be committed before checkpoint can truncate them and allow the space to be reused.
See the article referenced above for a way to delete in chunks that doesn't blow the log up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2009 at 9:35 am
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply