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!