Is there a temporary 'bulk logged' clause?

[jsteph]

Hi all,
In a database under Full Recovery option, can I run a DML statement and specify that for just that statement, that I don't want every row logged?
Thanks,
--Jim

 

[markros]

You may want to create a script that changes the recovery mode, runs your statement, then changes it back. Of course, all other processes running in parallel will be affected as well.

Unfortunately, there is no way to change the logging options per DML statement, although I think there may be a Connect item already. If you find this Connect item, please post a link so others can vote. If you will not, you can create one.

PluralSight Learning Library

 

[jsteph]

markros,
I can't chance that...too busy of a system. I'll probably have to do this at night and boot all connections before doing the change.
--Jim

 

[mrdenny]

What statement are you trying to run that you want bulk logged? Most statements aren't bulk logged.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[jsteph]

mrdenny,
I have an Update statement over a table of a bit over 200M rows. I'd rather not have to change the recovery mode from Full (which I believe requires a restart correct?) and then change it back to Full.

I just want to be able to isolate this statement so that: A. It runs faster. and B. The translog and resulting backups of the logs aren't so huge.

Thanks for any advice or info,
--Jim

 

[gmmastros]

In a situation like this, I would recommend that you do your updates in batches. Updating 200M rows is likely to be slow and cause your TLog to explode. Updating in small batches will likely be faster and your TLog will probably grow less (it will still grow).

Take a look here for tips on how to do this: faq183-3141


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mrdenny]

First, update isn't a minimally logged operation no matter the recovery mode.

Second, no changing the recovery mode doesn't require a restart.

Your only real option here to prevent the transaction log from filling is to use the technique that George pointed you to.


Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Blog

 

[jsteph]

Thanks mrdenny and gmmastros, I think I have my plan.
--Jim