maxout topup fast fill sql server express 2005 2008 database

I need to test a scenario where there is no more space left (db is full). I don't mean making the db size really small. I mean leaving the max available at whatever it is (around 4gb for 2005 express and 10 gb for sql server 2008 express). Right now I have (t-sql pseudo code)

for (i=1 to 10,000)
{
   for (i=1 to 10,000)
   {
      insert into some random table
   }
   开发者_C百科commit;
}

This type of logic works but takes too damn long to fill up the db. Anyone have better ideas ? thanks

;with a as
(select 0 level
union all
select a.level + 1 from a
where a.level < 1000000
)
select * into fillbase from a
option( MAXRECURSION 0) 

Here is another way to fill it faster

create table mytable(v varchar(max))

insert into mytable values (REPLICATE('XXX', 10000))

while 1 = 1
BEGIN
    insert mytable select v + v from @a
END

Nothing magical happens, see What happens when you hit the SQL Server Express 4GB / 10GB limit?

If you really need to do this you could insert T (f) values('xxx') then loop

insert T (f)
   select f from T

after 16 iterations you would have 65k rows * length of f

When a SQL database is full (i.e. there's no free space in which to store new data), and you try and add more data, then:

• If autogrow is off, you'll get an error
• If autogrow is on and there's enough available hard drive space, the database will "grow" by grabbing more hard drive space
• If autogrow is on and there is not enough hard drive space, you'll get a different error (and, sooner or later, panicked IT staff).

If you're trying to "fill" a database up to but not over the point of requiring more disk space, well, that's going to be very hard to calibrate.

If you're trying to test situations when the database needs to grow but there's insufficient hard drive space, I'd suggest altering the database growth factor. Running something like

ALTER DATABASE MyDB
 modify file MyDb_FileXX (NAME = MyDBFileXXLogicalName, FILEGROWTH = 100TB)

would set a database (even a small one) to try and grow by 100 terrabytes when it ran out of space, and odds are that would trigger the condition you may be looking for.