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!