0 Not enough log space for roll back transaction in tempdb

By following Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011. I want to try to reproduce the tempdb log full and cause the SQL Server shutdown.scenario. 

My test version of SQL Server is 2008R2.

Here are the steps to reproduce the error

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE =1034KB,maxsize=1024KB, FILEGROWTH = 0 )

GO

sp_helpdb tempdb

Then run below query over and over again. The error is hard to reproduce because SQL Server would reserve the room in the log for redo. The error would only happen when SQL server squeeze so hard and no room left for rollback. I did successfully create the error 3 times today but still not  found the way to consistently recreate it .

BEGIN TRAN

drop table #t1;

create table #t1 (c1 int, c2 char(5000) not null)

go

declare @x int

set @x = 0

while (@x < 560)

begin

insert into #t values (1, '1')

set @x = @x + 1

end

go

ROLLBACK

 

Once there is not enough free log space to roll back, the SQL Server would shutdown. Below is the error message I got.

Msg 10061, Level 20, State 0, Line 0
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)
In the error log, I will see the message as below

2013-06-30 11:39:32.40 spid53      Error: 9002, Severity: 17, State: 4.
2013-06-30 11:39:32.40 spid53      The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
2013-06-30 11:39:34.06 spid53      Error: 9004, Severity: 23, State: 6.
2013-06-30 11:39:34.06 spid53      An error occurred while processing the log for database 'tempdb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
2013-06-30 11:39:34.06 spid53      Error: 3314, Severity: 21, State: 4.
2013-06-30 11:39:34.06 spid53      During undoing of a logged operation in database 'tempdb', an error occurred at log record ID (23:308:789). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2013-06-30 11:39:34.06 spid53      Error: 3449, Severity: 21, State: 1.
2013-06-30 11:39:34.06 spid53      SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
2013-06-30 11:39:34.06 spid53      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

If the issue happen on the tempdb log, the SQL Server would shutdown. If this happen on the user database, the SQL Server would take the database offline.

I am not able to reproduce this issue on the user database.