From what I'm finding, there is no way of issuing a "backup db with no_log
or truncate log..." in SQL 2008. So what do we do when our tran log fills
up and our normal backups are not taking care of this? Is there no way of
purging the log anymore? I can't change the db to simple mode.

Thanks, Andre

Andre (no (AT) spam (DOT) com) writes:
The answer is that if you want to truncate the log, is that you change
to simple recovery. And if you say that you cannot change to simple
recovery, you have also hit the nail why no_log was removed.

If you think that you cannot change to simple mode, then neither
should you fiddle with NO_LOG. In both cases, you ditch what's in
the transaction log and lose the ability to do point-in-time recovery
from that point until the next full backup.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

What do you do when you have a situation where SQL is supposedly backing up
the tran log, defined as 75 GB, and yet jobs are failing because the log is
full? These are jobs that in no way could fill up a 75 GB tran log too.

What's happening here is that we restored a SQL 2000 backup into SQL 2008 to
do some testing, and we did set the db to compatibility level 100. I don't
know if there were any transactions in the backup when we restored it, but
everything we try to do fails with a message that the tran log is full. I
did flip the db into simple mode to clear the tran log out and flipped it
back to full and it's working now. But obviously when we go live with SQL
2008 we can't be flipping the db back and forth between recovery modes.

Andre

When you get that error - it usually comes with another message that tells
you how to identify what the problem is. Something along the lines of:
check the log_reuse_wait_desc in sys.databases to identify why the log is
full.

Next time you see this - take a look in that table and identify why the log
is not able to be reused. It usually comes down to either waiting for a log
backup or replication.

"Andre" <no (AT) spam (DOT) com> wrote

Andre (no (AT) spam (DOT) com) writes:

My point is that if you can think of using NO_LOG, there is no reason
to stay away from switching to simple recovery forth and back. If you
think simple recovery is bad, you need to understand that NO_LOG is
equally bad.

You need to find out why your log grows full. Since you were able to
truncate it by switching to simple, BACKUP LOG should do the trick as
well.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I agree that backup log should have truncated the log, but it didn't. The
db I restored into SQL 2008 is approximately 275 GB in size, including a 75
GB tran log. I couldn't even run dbcc checkdb without it telling me the log
was full - and this was immediately after the backup tranlog job had run.
It sure seemed to me like there was a "stuck" transaction in the log that
wasn't getting backed up. While I'm no expert on the internals of the
tranlog, all I'm telling you is what I saw. The tranlog backup job ran, and
still just about anything I tried to do in the db filled up the log. Once I
switched to simple mode I was able to run dbcc checkdb no problem.

Maybe this is a good time to ask what is the recommended method to upgrade a
db from SQL 2000 to SQL 2008? I restored a backup, because the wizard
failed.

Andre

More frustrations. I set the db back to Full recovery mode so we could test
the backups, tran log backups and optimization. The backup worked fine.
The optimizations, rebuild index and update stats, filled up the tran log -
again. What could be happening here? As I mentioned, it's a 75 GB tranlog.
The db was in simple mode, so there was nothing in the tranlog, right? So
how can I change it to full, run a backup and optimization and have it fill
up the tranlog? There was absolutely nothing else happening on the db - I
was the only one on it tonight.

Andre

Andre (no (AT) spam (DOT) com) writes:
Rebuilding all indexes is definitely going to eat a lot of transaction
log.

It's a good idea to switch for bulk_logged recovery while rebuilding
indexes, as that makes index creation minimlally logged.

There is also reason to make index rebuild dependent on whether there is
any actual fragmentation.

There is no need to update statistics if you also rebuild indexes. Then
again, statistics update does not consume that much translog.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Andre (no (AT) spam (DOT) com) writes:
The log will never be truncated past the oldest active transaction, so
if you have an orphan transaction that can inflate the log considerably,
because it never gets truncated. In this situation it does not help
to set recovery to simple. But if you set the database in single_user
mode in the process, that might have killed the offending process.

I think restoring a backup is the best way. The upgrade wizard is good
if you want to upgrade the entire instance. But often you want to move
to a new machine when you get a new SQL Server version anyway.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I take it since you're mentioning this that SQL 2008 isn't smart enough to
figure out if index rebuilding is really necessary? If not, I've seen
several sample queries on the web that are used to rebuild an index - based
on necessity. Is that what you recommend? Do you have any recommendations
of good queries out there? And what percent fragmented do you want to see
before you rebuild an index?

Andre