http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/12/29/tempdb-destroyer/

Printed 2014/10/02 11:48AM

TempDB Destroyer

By SQLSandwiches, 2012/12/29

One of my previous posts, I covered how to create a really big database with just garbage data in it. Recently, I was given a task to blow up tempdb. I thought about using a similar script and tried it out. Of course it worked and it blew up TempDb but it also errored out. This wasn't acceptable. I needed to find a way to blow up the tempdb with out it erroring out. I tried a couple little things but ended up using the following script:
set nocount on

Use tempdb

if OBJECT_ID('tempdb..#freespace') is not null
drop table #freespace
If OBJECT_ID('tempdb..#BigGuy1') is not null
Drop Table #BigGuy1
If OBJECT_ID('tempdb..#BigGuy2') is not null
Drop Table #BigGuy2
If OBJECT_ID('tempdb..#BigGuy3') is not null
Drop Table #BigGuy3
If OBJECT_ID('tempdb..#BigGuy4') is not null
Drop Table #BigGuy4
If OBJECT_ID('tempdb..#BigGuy5') is not null
Drop Table #BigGuy5
GO

CREATE TABLE #BigGuy1 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy2 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy3 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy4 ( column1 CHAR(7100), column2 CHAR(900))
CREATE TABLE #BigGuy5 ( column1 CHAR(7100), column2 CHAR(900))

CREATE Table #freespace
( drive varchar(1),
[MB Free] int)

declare @drive varchar(1),
@free int

SELECT @drive = left(physical_name, 1)
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
and type = 0

insert into #freespace
EXEC master..xp_fixeddrives

select @free = [MB Free]
from #freespace
where drive = @drive

while @free > 1
begin
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')
INSERT INTO #BigGuy1 VALUES('test', 'test')
INSERT INTO #BigGuy2 VALUES('test', 'test')
INSERT INTO #BigGuy3 VALUES('test', 'test')
INSERT INTO #BigGuy4 VALUES('test', 'test')
INSERT INTO #BigGuy5 VALUES('test', 'test')

delete #freespace

insert into #freespace
EXEC master..xp_fixeddrives

select @free = [MB Free]
from #freespace
where drive = @drive

print @free

End
This one uses a loop to check how much free space you have left. You can also set the threshold. For example, if you want to have 100MB free, you would just change @free > 1 to @free > 100. With a word of caution, do NOT run this on your production system. We used this for a training session in a TEST environment. The easy part about this one is that when you are finished expanding your tempdb, you can just restart your SQL services and it will reset the size. Have fun!  
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.