I have a script that creates a table in a database and populates it using
bulk copy from a comma-delimited file.
The number of rows of data to be entered is fairly large (approximately 24
million rows of data).
However, last night this fell over with the error message:
quote:
>Could not allocate space for object 'myTableName' in database
'myDatabase'
quote:
>because the 'PRIMARY' filegroup is full.
Using the Enterprise manager, I click on the database symbol and choose
"properties". The size of the database is '8132 MB' and it claims the space
available is '0.00 MB'.
However, on both the data file and the log file, I've got the following
properties set:
Automatically Grow File: YES
By Percent: 10%
Maximum file size: Unrestricted file growth
I've checked the disk and there's ~50 GB of free disk space, and it's fairly
contiguous space too.
Is my problem just that I need to increase the % growth from the current
value of 10% to something higher? I ask only because I have another
database that is smaller (but comparable in size) and this sort of data
import works with a growth size set of just 5%.
A second related question is whether there is a way to identify what % of
the database size is taken up by one particular (existing) table? If I knew
this, I'm sure I could answer the first question definitively myself.
Thanks in advance
GriffIt probably timed out before it could allocate the new space for the file.
You should never rely on autogrow and always ensure you have plenty of free
space in the database and log files. If you know your going to do a large
load you should check beforehand and manually grow the files before you
attempt the load. I would increase it an try again.
Andrew J. Kelly
SQL Server MVP
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:OUSGyfO4DHA.2332@.TK2MSFTNGP10.phx.gbl...
quote:
> Hi
> I have a script that creates a table in a database and populates it using
> bulk copy from a comma-delimited file.
> The number of rows of data to be entered is fairly large (approximately 24
> million rows of data).
> However, last night this fell over with the error message:
>
> 'myDatabase'
> Using the Enterprise manager, I click on the database symbol and choose
> "properties". The size of the database is '8132 MB' and it claims the
space
quote:
> available is '0.00 MB'.
> However, on both the data file and the log file, I've got the following
> properties set:
> Automatically Grow File: YES
> By Percent: 10%
> Maximum file size: Unrestricted file growth
> I've checked the disk and there's ~50 GB of free disk space, and it's
fairly
quote:
> contiguous space too.
> Is my problem just that I need to increase the % growth from the current
> value of 10% to something higher? I ask only because I have another
> database that is smaller (but comparable in size) and this sort of data
> import works with a growth size set of just 5%.
> A second related question is whether there is a way to identify what % of
> the database size is taken up by one particular (existing) table? If I
knew
quote:|||Andrew
> this, I'm sure I could answer the first question definitively myself.
> Thanks in advance
> Griff
>
>
Thanks for your response. Can I ask though why one should never rely on the
"auto-grow" feature? Is it one of those features that "does not always do
what it says on the tin"...
Cheers
Griff|||The reason is just what you (probably) have got -- in an application you
have too large a data load, that grows the db size for which it needs such a
long time that your application times out.
To look at the size of a table run
sp_spaceused TableName, true
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...
quote:
> Andrew
> Thanks for your response. Can I ask though why one should never rely on
the
quote:|||Quentin is correct. Also growing the db is an expensive process and
> "auto-grow" feature? Is it one of those features that "does not always do
> what it says on the tin"...
> Cheers
> Griff
>
>
something you don't want to happen when the users are making updates in the
db if you can help it. It is better to manually (or schedule it) grow the
db during off peak times so as not to impact the users.
Andrew J. Kelly
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:%236Wzg7Q4DHA.1704@.tk2msftngp13.phx.gbl...
quote:
> The reason is just what you (probably) have got -- in an application you
> have too large a data load, that grows the db size for which it needs such
a
quote:
> long time that your application times out.
> To look at the size of a table run
> sp_spaceused TableName, true
> "GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
> news:u521Q9O4DHA.488@.TK2MSFTNGP12.phx.gbl...
> the
do[QUOTE]
>
No comments:
Post a Comment