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?
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';