In this 5-minute video, Doug explains how he kept filling SQL Server’s transaction log during ETL development, and how minimal logging could have prevented a lot of the log writes. You’ll learn about:
- What minimal logging is
- How to turn minimal logging on
- How to tell if minimal logging is working
- Using minimal logging for schema changes
- Words of caution about minimal logging
8 Comments. Leave new
Just watched this while making breakfast. Great video!
very informative ..thank you
Great information! Thanks Doug…
Any extra explanation on why it would not work on certain occasions? Any examples of those times or it is just random?
Sincerely,
The Happy DBA
I haven’t seen it fail to take effect myself, but when others have had problems it’s either been some obscure violation of the rules, or a poltergeist.
Oh, I don’t think it’s random. The last time I recall hitting it personally, the table in question had an XML column. I don’t recall that there’s any limitation with LOB data always making it not work, but for whatever reason in testing we just were NOT getting minimal logging.
I believe that a rewrite that used OPENROWSET actually got the minimal logging working. No idea why that was the case, it just made me realize that with large tables it’s always worth testing that the minimal logging is working first on a copy of the data and not just assuming it’s gonna work.
It’s been a couple of years, but I believe the incident I’m recalling was SQL Server 2008R2. DB was in simple recovery model and we were following all of the “rules” in the whitepaper. The data was already in a table in the database, we weren’t loading it from a file or anything.
Edit: it was probably OPENROWSET (I’d originally written OPENQUERY)
Apart from the excellent technical content worth watching alone for just the beavis and butthead impersonation!
Great vid! Your Butthead impression is uncanny!