/*Listing 1: Creating the MyMessages database and Messages table*/ USE master; GO IF DB_ID('MyMessages') IS NOT NULL DROP DATABASE MyMessages; GO CREATE DATABASE MyMessages ON PRIMARY ( NAME = N'MyMessages' , FILENAME = N'C:\SQLData\MyMessages.mdf' , SIZE = 199680KB , FILEGROWTH = 16384KB ) LOG ON ( NAME = N'MyMessages_log' , FILENAME = N'D:\SQLData\MyMessages.ldf' , SIZE = 2048KB , FILEGROWTH = 2048KB ); GO ALTER DATABASE MyMessages SET RECOVERY FULL; GO USE master; GO BACKUP DATABASE MyMessages TO DISK ='D:\SQLBackups\MyMessages_full.bak' WITH INIT; GO USE MyMessages; GO CREATE TABLE dbo.Messages ( MessageText nvarchar(200) NOT NULL , MessageDate datetime2 NOT NULL ); GO /*Listing 2: An uncommitted transaction on the Messages table*/ USE MyMessages; GO INSERT INTO dbo.Messages ( MessageText , MessageDate ) VALUES ( 'A first message' , '2013-02-26 17:54:47' ); GO BEGIN TRANSACTION UPDATE dbo.Messages SET MessageText = 'A newer message' -- ROLLBACK TRANSACTION /*Listing 3: Insert 1.3 million rows into Messages*/ USE MyMessages; GO INSERT INTO Messages ( MessageText, MessageDate ) SELECT TOP 1000000 REPLICATE('a', 200) , GETDATE() FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b; GO -- repeat above to add another 300K rows INSERT INTO Messages ( MessageText, MessageDate ) SELECT TOP 300000 REPLICATE('a', 200) , GETDATE() FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b; GO /*Listing 4: Log size and space usage stats for MyMessages*/ DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 836 MB, Log Space Used: 86% /*Listing 5: Log backup does not truncate the log*/ BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log1.trn' WITH INIT; GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 836 MB, Log Space Used: 68% /*Listing 6: Adding a secondary 3 GB log file*/ USE master; GO ALTER DATABASE MyMessages ADD LOG FILE ( NAME = N'MyMessages_log2', FILENAME = N'D:\SQLData\MyMessages2.ldf' , SIZE = 512000KB , FILEGROWTH = 512000KB ); GO USE master GO ALTER DATABASE MyMessages MODIFY FILE ( NAME = N'MyMessages_log2', SIZE = 3146752KB ); GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 3909 MB, Log Space Used: 18.4% GO /*Listing 7: A second log backup, after clearing the open transaction, truncates the log*/ BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log2.trn' WITH INIT; GO DBCC SQLPERF(LOGSPACE); -- MyMessages Log Size: 3909 MB, Log Space Used: 1.4% GO /**/ USE master; go RESTORE DATABASE MyMessages FROM DISK ='D:\SQLBackups\MyMessages_full.bak' WITH REPLACE, NORECOVERY; RESTORE DATABASE MyMessages FROM DISK='D:\SQLBackups\MyMessages_log1.trn' WITH NORECOVERY; RESTORE DATABASE MyMessages FROM DISK='D:\SQLBackups\MyMessages_log2.trn' WITH RECOVERY; GO /*Processed 89539 pages for database 'MyMessages', file 'MyMessages_log' on file 1. RESTORE LOG successfully processed 89539 pages in 24.409 seconds (28.658 MB/sec). Processed 0 pages for database 'MyMessages', file 'MyMessages' on file 1. Processed 3 pages for database 'MyMessages', file 'MyMessages_log' on file 1. Processed 0 pages for database 'MyMessages', file 'MyMessages_log2' on file 1. RESTORE LOG successfully processed 3 pages in 37.085 seconds (0.000 MB/sec).*/ /*Listing 9: Removing the secondary log file*/ USE MyMessages; GO ALTER DATABASE MyMessages REMOVE FILE MyMessages_Log2; GO /*Listing 10: A fragmented log!*/ USE MyMessages DBCC Loginfo; -- returns 1672 rows GO /*Listing 11: Shrinking the primary log file (partial success)*/ USE MyMessages; GO DBCC SHRINKFILE (N'MyMessages_log' , target_size=0); GO /*Listing 12: Shrinking the primary log file after log backup*/ BACKUP LOG MyMessages TO DISK = 'D:\SQLBackups\MyMessages_log3.trn' WITH INIT; USE MyMessages; GO DBCC SHRINKFILE (N'MyMessages_log' , 0); GO