Rollback impact on fragmentation

· Uncategorized

Following script demonstrates the impact of a rollback on fragmentation. Even when you might expect a rollback brings back a database to it’s original state, it does so only at the transactional level and not at the physical level. Although pagesplits are stored in the transactionlog, there are not undone in case of a rollback.

SET NOCOUNT ON
GO
-- Create a db to play with
CREATE DATABASE testFragmentation;
GO
use testFragmentation
GO
-- Create a table to play with
CREATE TABLE dbo.testTable (c1 uniqueidentifier default newid(), JustALargeCol CHAR(1000) default 'A');
GO
--insert 10000 records
INSERT dbo.testTable DEFAULT VALUES
GO 10000
-- Create clustered index
CREATE CLUSTERED INDEX c_ix ON dbo.testTable (c1);
GO
-- Check fragmentation, should be minimal
SELECT object_name (object_id), index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('testFragmentation'), NULL, NULL, NULL, NULL);
-- rollback a tran that inserts 500 records
BEGIN TRAN
GO
INSERT dbo.testTable DEFAULT VALUES
GO 1000
ROLLBACK
GO
-- Check fragmentation
SELECT object_name (object_id), index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('testFragmentation'), NULL, NULL, NULL, NULL);
-- cleanup
use master
go
DROP DATABASE testFragmentation;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: