January 3, 2012 at 2:06 pm
We have an app that runs against a SQL 2008 database, and one of the admins of this app has to run a procedure from within the app that (I assume) rebuilds and index on a very large table. When he ran this, it bloated the transaction log file till we ran out of disk. I told him to talk to the developers and have them specify nologging for the table while the index was being rebuilt, but they told him to avoid that we need to take the ENTIRE DATABASE out of logging mode then put it back. I'm an Oracle guy and assumed that it could be turned on / off at a table or tablespace level.
Is there no option within MSSQL to turn logging off / on) just for one table?
thanks!
January 3, 2012 at 2:12 pm
No, there isn't. SQL Server always logs. There isn't a 'nologging' option for a database either, it's just a simpler mode where it'll re-use existing log space once the transaction is completed, and an index rebuild is one huge, long transaction.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 3, 2012 at 2:13 pm
No. All operations are logged always. There is no way to disable logging, whether at a database or a table level.
If you're in full recovery model, index rebuilds are fully logged and you need log space = around 1.2*the size of the index you're rebuilding (assuming you take a log backup after each one)
If the minor risks are acceptable, you can switch to bulk-logged recovery for the duration of the index rebuilds. That means the index rebuilds are minimally logged (page allocations only, rather than data changes). The downside of that is that point-in-time restores can't be done to a log interval containing a minimally logged operation and tail-log backups can't be done. Do remember to switch back to full recovery afterwards. This can't be done if you're using database mirroring (that requires full recovery model)
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
January 3, 2012 at 2:23 pm
Thanks for the prompt replies - that clears things up for what options I have anyway. I think Oracles alter database noarchivelog; basically does the same thing as putting an MSSQL database into simple recovery mode, it just has the database reuse the log space once the allocated log (in Oracles case, archive log files) fills up. I do like Oracles archiving approach better though, where it just writes the full logfile to disk then continues on and they stay the same size, there is still the problem of filling up the disk with logfiles but the log files are inert and can me moved if neccesary.
However, that is neither here nor there, I'll look into temporarily putting the database into simple recovery mode for the next time we need to do this, and back into full when we are done.
thanks again for your help!
January 3, 2012 at 2:27 pm
Glenstr (1/3/2012)
However, that is neither here nor there, I'll look into temporarily putting the database into simple recovery mode for the next time we need to do this, and back into full when we are done.
Not simple, unless you're happy to completely throw away the ability to restore to point of failure until the rebuild has finished and another backup (full or diff) has been taken.
See my earlier post for suggestions on managing log size with index rebuilds.
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
January 3, 2012 at 2:32 pm
Thanks Gail - will do just that (read your earlier post), and yes you are right, at least a transaction log backup taken before putting into simple recovery mode, then a full database backup after putting it back into full recovery mode, would have to be done for sure.
January 4, 2012 at 5:00 am
Glenstr (1/3/2012)
I think Oracles alter database noarchivelog; basically does the same thing as putting an MSSQL database into simple recovery mode, it just has the database reuse the log space once the allocated log (in Oracles case, archive log files) fills up.
Quite true, but what you're looking for seems to be NOLOGGING rather than NOARCHIVELOG.
NOLOGGING brings serious recovery issues and I tend to avoid it whenever I can. What I find really surprising is Oracle allowing NOLOGGING tablespaces on ARCHIVELOG databases or, if you prefer, taking backups of ARCHIVELOG databases with NOLOGGING tablespaces (the backup becomes unusable). If the software was written by me, I would never allow taking invalid backups.
-- Gianluca Sartori
January 4, 2012 at 7:42 am
Gianluca Sartori (1/4/2012)
Glenstr (1/3/2012)
I think Oracles alter database noarchivelog; basically does the same thing as putting an MSSQL database into simple recovery mode, it just has the database reuse the log space once the allocated log (in Oracles case, archive log files) fills up.Quite true, but what you're looking for seems to be NOLOGGING rather than NOARCHIVELOG.
NOLOGGING brings serious recovery issues and I tend to avoid it whenever I can. What I find really surprising is Oracle allowing NOLOGGING tablespaces on ARCHIVELOG databases or, if you prefer, taking backups of ARCHIVELOG databases with NOLOGGING tablespaces (the backup becomes unusable). If the software was written by me, I would never allow taking invalid backups.
NOLOGGING is not something I would use a lot, but in some cases is quite preferable to putting the database in NOARCHIVELOG mode, for example, if I have a huge OLTP database where P.I.T. recovery at all times is critical and I have a huge table in said database I want to either (a) rebuild an index on (which should be seldom, if ever done in Oracle) or (b) do a huge import on from an export file, I can turn logging off for that table during the operation, then back on again, while the rest of the database continues to archivelog all transactions. Of course a backup needs to be taken immediately after, but that can be done online.
One thing I like about MS SQL is that switching between simple recovery mode can be done without bouncing the database, doing a startup mount, change archive mode then opening.
January 4, 2012 at 8:37 am
Glenstr (1/3/2012)
at least a transaction log backup taken before putting into simple recovery mode
The recommendation you were given was to switch to bulk logged recovery model!
Glenstr (1/3/2012)
then a full database backup after putting it back into full recovery mode, would have to be done for sure.
you only need to ensure you take a log backup before switching from Full to bulk logged and after the switch from bulk logged to full recovery, no full or even diff is necessary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 21, 2015 at 10:31 am
However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.
He is looking for table level nologging which can be useful at times, for example, if you are loading a large staging table, along with using flashback recovery.
The answer to his question is no, it does not exist.
Jason
http://dbace.us
😛
October 21, 2015 at 10:35 am
Please note, three year old thread.
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
October 21, 2015 at 11:15 am
jswong05 (10/21/2015)
However, the bulk-logged recovery model increases the risk of data loss for these bulk-copy operations, because bulk logging operations prevents recapturing changes on a transaction-by-transaction basis. If a log backup contains any bulk-logged operations, you cannot restore to a point-in-time within that log backup; you can restore only the whole log backup.He is looking for table level nologging which can be useful at times, for example, if you are loading a large staging table, along with using flashback recovery.
The answer to his question is no, it does not exist.
Thanks Jason - appreciate the input. Yes being able to turn logging on or off at a table or object level can come in very handy.
I've since also found out that index rebuilds during production hours on MSSQL SE can be quite the pain.. 😛
Glen
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply