Problems when trying to rebuild indexex

Hello

I am trying to rebuild indexes on our DB, but I am running into an
error I am not able to solve.

Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'ft' in database 'HastaDemo'
because the 'PRIMARY' filegroup is full.
The statement has been terminated.

There is enough diskspace on the disk system, so it must be something
trivial.

Also our DB is heavy on writes, about 2-3 the amount of reads, do you
think a fillfactor of 60 is low enough? I am seing lots of page splits
in perfmon when I used 90% and above, so I am trying to adjust it down
to minimize my pagesplits and thus minimize the Disk I/O, is this the
correct thing to do?

rgds

MattDear Matt,

see this article
http://www.windowsitpro.com/Windows.../3472/3472.html

go to sub-heading "SQL Server Transaction Logs", may be helpfull for
your problem.

Saghir Taj(MCDBA)
DBNest(www.dbnest.com): The Nest of DB Professionals

Matt wrote:
> Hello
> I am trying to rebuild indexes on our DB, but I am running into an
> error I am not able to solve.
> Server: Msg 1105, Level 17, State 2, Line 1
> Could not allocate space for object 'ft' in database 'HastaDemo'
> because the 'PRIMARY' filegroup is full.
> The statement has been terminated.
> There is enough diskspace on the disk system, so it must be something
> trivial.
> Also our DB is heavy on writes, about 2-3 the amount of reads, do you
> think a fillfactor of 60 is low enough? I am seing lots of page
splits
> in perfmon when I used 90% and above, so I am trying to adjust it
down
> to minimize my pagesplits and thus minimize the Disk I/O, is this the
> correct thing to do?
> rgds
> Matt|||"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0502120126.215bb58@.posting.google.co m...
> Hello
> I am trying to rebuild indexes on our DB, but I am running into an
> error I am not able to solve.
> Server: Msg 1105, Level 17, State 2, Line 1
> Could not allocate space for object 'ft' in database 'HastaDemo'
> because the 'PRIMARY' filegroup is full.
> The statement has been terminated.
> There is enough diskspace on the disk system, so it must be something
> trivial.
> Also our DB is heavy on writes, about 2-3 the amount of reads, do you
> think a fillfactor of 60 is low enough? I am seing lots of page splits
> in perfmon when I used 90% and above, so I am trying to adjust it down
> to minimize my pagesplits and thus minimize the Disk I/O, is this the
> correct thing to do?
> rgds
> Matt

It sounds like autogrow is disabled for that database - have you tried
enlarging the file with ALTER DATABASE or Enterprise Manager?

http://support.microsoft.com/defaul...kb;en-us;315512

As for the fillfactor, 60% sounds reasonable, although unless it's a major
issue, it might be better to adjust downwards in steady increments of 10% or
so and benchmark again each time, to see exactly where you start to get a
significant reduction in page splits. By going straight to a much lower
factor, you might be lower than you really need, and end up with fewer page
splits, but too many pages, which means more read operations are required.

Simon|||Hi

You do not say if the database is set to automatically grow or by how much
it is set to grow by? If it is set to expand by a percentage then your free
space may not be enough to cover that amount (even if it seems enough!). You
may also want to check if disc quotas are enforced.

http://msdn.microsoft.com/library/d...des_03_71d1.asp
http://msdn.microsoft.com/library/d...serr_1_6drp.asp

Your technique of watching for page splits should allow you to incrementally
decrease the size of the fill factor until their frequency is reasonable. It
will be a trade off between the number of page splits against read
performance, therefore the figure you come across needs to provide
acceptable performance for both. You will need to run profiler to gather the
stats for a resonable period (for each fill factor) to determine the value
to use, gathering the stats from too small sample may not give a full
picture. You should also be defragging the indexes periodically.

John

"Matt" <matt@.fruitsalad.org> wrote in message
news:b609190f.0502120126.215bb58@.posting.google.co m...
> Hello
> I am trying to rebuild indexes on our DB, but I am running into an
> error I am not able to solve.
> Server: Msg 1105, Level 17, State 2, Line 1
> Could not allocate space for object 'ft' in database 'HastaDemo'
> because the 'PRIMARY' filegroup is full.
> The statement has been terminated.
> There is enough diskspace on the disk system, so it must be something
> trivial.
> Also our DB is heavy on writes, about 2-3 the amount of reads, do you
> think a fillfactor of 60 is low enough? I am seing lots of page splits
> in perfmon when I used 90% and above, so I am trying to adjust it down
> to minimize my pagesplits and thus minimize the Disk I/O, is this the
> correct thing to do?
> rgds
> Matt