Creating enough empty pages in the database.

Question

Creating enough empty pages in the database.

ben.brugman

SSChampion

Points: 13369
More actions
May 28, 2013 at 7:55 am

#278241

I use the following script to create empty space in a database.
(So that during an opperation the database does not have to extend itself).
The script see below.
I wanted this to share with this community, because I learn so much from here I want to make an efford to attribute to this group.
If this is the wrong way to handle this.
Or if there is a better way, please correct me.
Greetings
Ben Brugman
---------------------------------------------------------------------------------
-- Generate a number of rows with one row per page --
---------------------------------------------------------------------------------
--
-- 20130528
-- ben brugman
--
-- To allocate a number of pages to the database.
-- Create a table of the required size.
-- The table is build with one row for each page. Number of rows therefore is the number of pages.
-- At the end of the process the table is dropped.
declare @longstring varchar(8000) =
replicate('The quick brown fox jumps over the lazy dog.
',90)
declare @number_of_pages_needed bigint = 123456
print datalength(@longstring)
-- Hoeveel pages moeten er worden gevuld.
;
WITH
L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4
L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256
L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga
L9 AS(Select *, row_number() OVER(PARTITION BY x order by x ) as nr from L2) -- voeg rijnummers toe
SELECT
nr as getal,
@longstring large_field
INTO Number_of_Pages
FROM L9 where nr <= @number_of_pages_needed
drop table number_of_pages
---------------------------------------------------------------------------------

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If all you want to do is grow the database ahead of time, may I suggest ALTER DATABASE ... MODIFY FILE and specify how large you want the file to be?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ben.brugman SSChampion Points: 13369 More actions · Answer 2

GilaMonster (5/28/2013)
If all you want to do is grow the database ahead of time, may I suggest ALTER DATABASE ... MODIFY FILE and specify how large you want the file to be?

I do agree that is a better method.

But I am preparing a batch were I know the approximate amount of new data that get's added and do not know the size of the database or the amount free space in the target database. This is the best I could do, because I do not know (in advance) how large I want the file to be.

Thanks for the script,

Ben Brugman