I tried the following experiment and got similar results. In both cases, fn_dblog() shows rollback occurring and it seems to happen quicker in Scenario 2 than in Scenario 1.
By the way, I placed both the MDF and LDF on the same single external (USB 2.0) disk.
My initial conclusion is that there is no difference in the operation of rollback in this case, and probably any apparent speed difference is I/O subsystem related. That is just my working hypothesis at the moment.
Scenario 1:
- Create a database with a log file that begins at 1MB, grows in 4MB chunks, and has a max size of 100MB.
- Open an explicit transaction, run it for 10 seconds, and then manually cancel it within SSMS
- Look at fn_dblog() count and log reserve size and check out DBCC SQLPERF(LOGSPACE)
Scenario 2:
- Create a database with a log file that begins at 1MB, grows in 4MB chunks, and has a max size of 100MB.
- Open an explicit transaction, run it until the log is full error shows up
- Look at fn_dblog() count and log reserve size and check out DBCC SQLPERF(LOGSPACE)
Performance Monitor Results:
Scenario 1:
Scenario 2:
Code:
USE [master];
GO
IF DATABASEPROPERTYEX (N'SampleDB', N'Version') > 0
BEGIN
ALTER DATABASE [SampleDB] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SampleDB];
END;
GO
CREATE DATABASE [SampleDB] ON PRIMARY
(
NAME = N'SampleDB'
, FILENAME = N'E:\data\SampleDB.mdf'
, SIZE = 3MB
, FILEGROWTH = 1MB
)
LOG ON
(
NAME = N'SampleDB_log'
, FILENAME = N'E:\data\SampleDB_log.ldf'
, SIZE = 1MB
, MAXSIZE = 100MB
, FILEGROWTH = 4MB
);
GO
USE [SampleDB];
GO
-- Add a table
CREATE TABLE dbo.test
(
c1 CHAR(8000) NOT NULL DEFAULT REPLICATE('a',8000)
) ON [PRIMARY];
GO
-- Ensure that we are not is pseudo-simple recovery model
BACKUP DATABASE SampleDB
TO DISK = 'NUL';
GO
-- Backup the log file
BACKUP LOG SampleDB
TO DISK = 'NUL';
GO
-- CHeck the used log space
DBCC SQLPERF(LOGSPACE);
GO
-- How many records are visible with fn_dblog()?
SELECT * FROM fn_dblog(NULL,NULL); -- Around 9 in my case
/**********************************
SCENARIO 1
**********************************/
-- Open a new transaction and then roll it back
BEGIN TRANSACTION
INSERT INTO dbo.test DEFAULT VALUES;
GO 10000 -- Let is run for 10 seconds and then hit cancel in SSMS query window
-- Cancel the transaction
-- It should take a couple seconds to finish
-- No need to rollback the transaction, since the cancel already did that for you.
-- Just try it. You'll get this error
-- Msg 3903, Level 16, State 1, Line 1
-- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
ROLLBACK TRANSACTION;
-- What is the used log space? Above 100%.
DBCC SQLPERF(LOGSPACE);
GO
-- How many records are visible with fn_dblog()?
SELECT *
FROM fn_dblog(NULL,NULL); -- Around 91,926 in my case
-- Total log reserve shown by fn_dblog()?
SELECT SUM([Log Reserve]) AS [Total Log Reserve]
FROM fn_dblog(NULL,NULL); -- Around 88.72MB
/**********************************
SCENARIO 2
**********************************/
-- Blow away the DB and start over
USE [master];
GO
IF DATABASEPROPERTYEX (N'SampleDB', N'Version') > 0
BEGIN
ALTER DATABASE [SampleDB] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SampleDB];
END;
GO
CREATE DATABASE [SampleDB] ON PRIMARY
(
NAME = N'SampleDB'
, FILENAME = N'E:\data\SampleDB.mdf'
, SIZE = 3MB
, FILEGROWTH = 1MB
)
LOG ON
(
NAME = N'SampleDB_log'
, FILENAME = N'E:\data\SampleDB_log.ldf'
, SIZE = 1MB
, MAXSIZE = 100MB
, FILEGROWTH = 4MB
);
GO
USE [SampleDB];
GO
-- Add a table
CREATE TABLE dbo.test
(
c1 CHAR(8000) NOT NULL DEFAULT REPLICATE('a',8000)
) ON [PRIMARY];
GO
-- Ensure that we are not is pseudo-simple recovery model
BACKUP DATABASE SampleDB
TO DISK = 'NUL';
GO
-- Backup the log file
BACKUP LOG SampleDB
TO DISK = 'NUL';
GO
-- Now, let's blow up the log file within our transaction
BEGIN TRANSACTION
INSERT INTO dbo.test DEFAULT VALUES;
GO 10000
-- The rollback never fires. Try it. You'll get an error.
-- Msg 3903, Level 16, State 1, Line 1
-- The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
ROLLBACK TRANSACTION;
-- Is the log file 100% full?
DBCC SQLPERF(LOGSPACE);
-- How many records are visible with fn_dblog()?
SELECT *
FROM fn_dblog(NULL,NULL); -- Around 91,926 in my case
GO
-- Total log reserve shown by fn_dblog()?
SELECT SUM([Log Reserve]) AS [Total Log Reserve]
FROM fn_dblog(NULL,NULL); -- 88.72MB
GO
