maxout topup fast fill sql server express 2005 2008 database

Question

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
   }
   commit;
}

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

t-clausen.dk · Accepted Answer · 2011-07-08T11:53:05.030

1

;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

edited Jul 08 '11 at 11:53

answered Jul 07 '11 at 15:16

t-clausen.dk

43,517
12
59
92

score 1 · Answer 2 · edited May 23 '17 at 12:12

1

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

edited May 23 '17 at 12:12

Community

1
1

answered Jul 07 '11 at 15:17

Alex K.

171,639
30
264
288

score 0 · Answer 3 · answered Jul 07 '11 at 15:17

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.

We have lot of logic that triggers when a sql server express db gets maxed out. Note this is express so autogrow does not do jack. thanks for the info. — Gullu, Jul 07 '11 at 15:56

maxout topup fast fill sql server express 2005 2008 database

3 Answers3