Sql-server – Unable to trace the transaction log space

sql servertransaction-log

im new to sql server. One of the instance encounters a scenario it shows the transaction log full, and the database need to perform backup in order to solve the issue.

I want to replicate the scenario again to show there is potential that the transaction log would go full, and suggest my client to increase the transaction log space and grow factor. (The database is using Full recovery mode)

However, the scenario is unable to replicate whatever how demanding query executed (40000 deletion and then 40000 insertion, repeatedly), i found that the transaction log occupied space is just trembling by querying it

DBCC SQLPERF(LOGSPACE)

I expects the transaction log will grow continuously when executing bulk insertion and deletion (no backup or checkpoint is done between the query). But the fact is the log space isn't really behaved as expected.

I would like to know

  1. when the db throw exception transaction log is full – is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?

  2. why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?

Best Answer

why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?

It's likely that you've missed one of these two steps in your test setup:

  • the test database is not using the FULL recovery model
ALTER DATABASE [TestDB] SET RECOVERY FULL WITH NO_WAIT;
  • you have not performed a FULL backup of the test database
BACKUP DATABASE  [TestDB] TO DISK = N'NUL';

Until you've taken a full backup, even if the database is set to use the full recovery model, it's still essentially using the Simple model. Here's a post where Paul Randal discusses the situation:

Misconceptions around the log and log backups: how to convince yourself

If you switch recovery models to FULL or BULK_LOGGED, until you take the first full backup, you are still essentially in the SIMPLE recovery model, and so the log will truncate on checkpoint.

Since your question says "checkpoint is done between the query)", this second point is likely your issue.

To answer your other question:

when the db throw exception transaction log is full - is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?

See this related question on this site for a lot of details on that topic: Why Does the Transaction Log Keep Growing or Run Out of Space?

It could be that the disk is out of space, or the log can't grow because it's reached it's max size and is unable to grow anymore. Some solutions to this issue would be to take log backups more frequently, change to a different recovery model, increase the size of the disk where the log file resides, etc.'

Regarding a couple of comments on this answer:

sorry, i missed one important question, you are correct that actually my database was not in full recovery mode, but in the SSMS UI, it did show the db is in full recovery mode. in this case, where can i check the actually recovery mode?

and

Is there a way to see if the recovery mode is fully active or not yet because of missing backup (besides query last backup date). And does it count to have a full backup before switching to full recovery mode?

The only way I know of to tell if a backup hasn't been performed since the DB was switch to full is to attempt a log backup. It will fail with the error "BACKUP LOG cannot be performed because there is no current database backup."

This really shouldn't be a problem in practice, since you're backing up real production databases, right? =P But in a test environment, I would just run this to see where things stand:

BACKUP LOG NCEB_DMS TO DISK = N'NUL';
Related Question