By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,903 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,903 IT Pros & Developers. It's quick & easy.

Problems when trying to rebuild indexex

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Dear 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


Jul 23 '05 #2

P: n/a

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6*************************@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/default...b;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
Jul 23 '05 #3

P: n/a
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/de...es_03_71d1.asp
http://msdn.microsoft.com/library/de...err_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" <ma**@fruitsalad.org> wrote in message
news:b6*************************@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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.