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

SQL – dealing with a WTF moment

No clue on this one.

An engineer asked me to move his two databases from a DEV environment to his production environment.

No big deal. Let me take a backup, move it over, restore it, bam….done. Ah, crapola, looks like he was developing on a 2008 R2 instance and production is 2008. Well that’s not good but it’s still do-able.

I figured I could script out the database (and data) using the ‘generate scripts’ option in SSMS. I would make sure to check the 2008 option.

script

I copy my script to the second server, open it up, change the location, run it and we are……NOT ok.

huh?

This looks ok to me. What the hell is the problem with this one insert statement?

I copied the one insert statement over to a new query window and then it shows up like this:

huh x 2

W….T….F…Only half of the insert statement copied over.

I end up commenting this one line out and trying to run the script; zero problems.

I then went back and copied the insert statement into 2 parts, the part ending in ‘999 and the second part starting with ‘, N’Rest… and I have no problems.

So to double the crazy, the second database had one record in the same table, in the same column (varchar(100)) that did the exact same thing.

I checked the collations and they were the same. What kind of character or break could be after that 999 that would break the query? Let alone my clipboard?

Server 1 was a SQL express 2008 R2 w/ advance services and the second was a SQL standard 2008, does that matter? Both 64 bit-ers.

I’m stumped. Any ideas from the SQL world?

UPDATE:

I found out what was breaking it. Bill Fellows ( blog | @billinkc ) had a good idea of checking it in hex-editor. Turns out there was an invisible CREATE DATABASE statement shoved in there. NO idea why though – http://twitpic.com/5t88c2