April 11, 2008 at 2:02 pm
I am working on a scenario that would mimic doing a large amount of INSERTS into a table to watch the effects on the log...
I have a DB that is 50 mb's in size
I have a DB log file that is 35 mb's in size
The DB is in FULL recovery mode.
I created a loop which is supposed to INSERT records into a dummy table with the hopes of watching the log grow...ect...just to understand how things totally work....kind of Cause & Effect stuff
My understanding is as follows.
1. When the DB is in FULL RECOVERY mode it will log all the transactions in the log file and they will stay there until the log is cleared (i.e. Backed up.)
2. If you INSERT more rows than can be logged (based on the log size) the log will fill up and you will get a transaction log error.
OK these are pretty basic I guess...however I guess I am missing something obvious...and I am not seeing this however when I run my test...
I run a loop which simply inserts ONE row per loop into the table and I then watch the log space via DBCC SQLPERF (LogSpace) command.
I would expect to watch the log space used % to continue to grow until the log space is exhausted and give me an error...this doesn't happen however in my test and I don't know why...
I get this sort of experience
Database Name, Log Size (MB), Log Space Used (%), Status
Lee_Test, 34.992188, 14.77855, 0
Lee_Test, 34.992188, 42.98789, 0
Lee_Test, 34.992188, 69.411755, 0
.
.
.
Lee_Test, 34.992188, 19.44587345, 0 (JUST DROPS DOWN)
QUESTION
Can someone please tell me why this doesn't fill the log and it just appears to clear the transaction log on its own and continue to INSERT rows?
I was expecting it to get to a certain point and then fail with the INSERTS saying it can't allocate the space...ect...
I have tried this on two separate SQL 2000 SP 4 machines and I got the same results.
Here is the code I am using
---------------------------------------------------------------------------------------------------------
-- create db, tables, load tables and display rows
---------------------------------------------------------------------------------------------------------
USE master
GO
-- DROP DATABASE Test_db
-- GO
CREATE DATABASE Test_db ON
(NAME = N'Test_db_data', FILENAME = N'E:\MSSQL2000\MSSQL\DATA\Test_db_data.mdf', SIZE = 50, MAXSIZE = 220, FILEGROWTH = 0%)
LOG ON
(NAME = N'Test_db_log', FILENAME = N'E:\MSSQL2000\MSSQL\DATA\Test_db_log.ldf', SIZE = 35, MAXSIZE = 100, FILEGROWTH = 0%)
GO
EXEC Test_db..sp_changedbowner 'sa'
GO
ALTER DATABASE Test_db SET RECOVERY FULL
GO
USE Test_db
GO
CREATE TABLEtest_table
(
ID INT IDENTITY,
fieldVARCHAR(250)
)
SET NOCOUNT ON
DECLARE @i INT
SET @i = 1
WHILE @i < 150001
BEGIN
INSERT INTO test_table VALUES (REPLICATE('x', 200))
SET @i = @i + 1
END
SET NOCOUNT OFF
April 11, 2008 at 2:43 pm
Your understanding is about the same as mine.
However, one full database backup is required before the log chain is started.
April 11, 2008 at 2:50 pm
Todd Engen (4/11/2008)
Your understanding is about the same as mine.However, one full database backup is required before the log chain is started.
Well I will be DIPPED...
So basically if I create a database (DON'T run the backup) and then INSERT 1,000,000 rows it will go ahead and insert those rows (which follows the activity that I was seeing)...
The log appears to never fill up (or it does fill up and then empty for lack of a better explanation)
HOWEVER
if I create that database, table and run a FULL backup...
BACKUP DATABASE Test_DB TO DISK = 'c:\test_db.bak' WITH INIT, NAME = N'Test DB Complete', SKIP , STATS = 10, NOFORMAT
and then run the record insert process I can watch that fill up and give me the error I was expecting...
Server: Msg 9002, Level 17, State 6, Line 6
The log file for database 'Test_db' is full. Back up the transaction log for the database to free up some log space.
Thanks for the information...do you by chance know where I can find that documented anywhere for future reference?
Thanks again Todd,
Lee
April 11, 2008 at 3:02 pm
Lee,
You're welcome.
The only two places I recall seeing that mentioned were either in Inside SQL Server or a blog entry on sqlskills.com.
April 11, 2008 at 3:25 pm
I'll be dipped too.
I never knew that and would never have expected it to be the case. You learn something everyday.
MS must have decided it was more important not to have a database come to a halt due to a lack of a full backup then lose the ability to recover transactions.
You should put this forward as a QOTD and watch the fallout! (don't menton the bit about not taking a full backup)
---------------------------------------------------------------------
April 11, 2008 at 3:35 pm
...actually, thinking about it it's logical, you can't recover a tran log backup till after a full backup restore, so whats the point in keeping log records until a full backup is done...........
kudos to MS. Still an excellent QOTD though. :satisfied:
---------------------------------------------------------------------
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply