July 9, 2014 at 2:46 am
I understand that minimal logging can occur on a non clustered indexed heap as long as
http://technet.microsoft.com/en-us/library/ms190422(v=sql.105).aspx
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
GO
INSERT INTO logtest.dbo.test WITH (TABLOCK)
SELECT TOP 100000 a.name
FROM logtest.sys.columns a
CROSS JOIN logtest.sys.columns b
CROSS JOIN logtest.sys.columns c
CROSS JOIN logtest.sys.columns d
SELECT SUM([Log Record Length]) log_size,COUNT(*) record_count FROM fn_dblog(NULL, NULL)
GO
TRUNCATE TABLE test
CREATE INDEX i ON test (field)
GO
CHECKPOINT
GO
INSERT INTO logtest.dbo.test WITH (TABLOCK)
SELECT TOP 100000 a.name
FROM logtest.sys.columns a
CROSS JOIN logtest.sys.columns b
CROSS JOIN logtest.sys.columns c
CROSS JOIN logtest.sys.columns d
SELECT SUM([Log Record Length]) log_size,COUNT(*) record_count FROM fn_dblog(NULL, NULL)
GO
USE master
GO
DROP DATABASE logtest
July 9, 2014 at 5:31 am
What recovery model is the DB in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2014 at 5:35 am
GilaMonster (7/9/2014)
What recovery model is the DB in?
simple
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply