How to make a Big Ugly Nasty Test Table

Do you need to fill up a database with terrible data to test restore times? Do you want to see how fast you can fill up your harddrive?

 

Well try out this little bit of code on your DB and watch the MB’s get sucked up!

CREATE TABLE BigGuy (column1 CHAR(8000))
CREATE TABLE BigGuy2 (column1 CHAR(8000))
CREATE TABLE BigGuy3 (column1 CHAR(8000))
CREATE TABLE BigGuy4 (column1 CHAR(8000))
CREATE TABLE BigGuy5 (column1 CHAR(8000))
DECLARE @counta INT = 1 while @counta <= 1000000000 
 BEGIN
INSERT INTO BigGuy VALUES('test')
 INSERT INTO BigGuy2 VALUES('test')
 INSERT INTO BigGuy3 VALUES('test') 
 INSERT INTO BigGuy4 VALUES('test') 
 INSERT INTO BigGuy5 VALUES('test') 
 SET @counta = @counta +1 
END

This should suck up about a GB a minute. Want to suck up more space? Add more tables and make sure your database is set to FULL recovery model.

WARNING! – This is NOT A GOOD IDEA and should ONLY be used in controlled environments FOR TESTING PURPOSES

Share