Print layout

Hi all!
I have a problem trying to make ma report print in LEGAL format by default.
I had set:
Interactive size to 14in, 8.5in
Page size to 14in, 8.5in
Now my report is in landscape by default wich is ok, but I can't find the
way to make it print in legal format by default instead of letter.
Someone knows how to do this?
Thanks, JulienHello Julien,
I would like to know how you configure your body size of the report.
How long did your body size plus the page header and page foot?
Based on my test, I have a report without page header/foot. And I set the
body to be 14in, 8.5in. And I could see that when I try to print the
report, it will use Legal as the format.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||I didn't change de body size, so it's 7.25in, 0.84in
But even when I set it to 14in, 8.5in and I deploy, refresh, print. My
original paper size is set to Letter and my Print paper size is set to same
as original, so Letter.
At least the orientation is the one I want, landscape.
Can it be an incompatibility with my printer?
I'm using a Toshiba e-Studio282.
Thanks in advance.
Julien
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:%238zWbnCbHHA.928@.TK2MSFTNGHUB02.phx.gbl...
> Hello Julien,
> I would like to know how you configure your body size of the report.
> How long did your body size plus the page header and page foot?
> Based on my test, I have a report without page header/foot. And I set the
> body to be 14in, 8.5in. And I could see that when I try to print the
> report, it will use Legal as the format.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Julien,
What about when you preview the report in the development environment?
If you click the Page Setup, What's the result?
Per my experience, this issue may caused by the incompatibility with your
printer if your printer did not support Letter.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||My Printer support letter, legal, A4 and many more.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:y60B5pRbHHA.752@.TK2MSFTNGHUB02.phx.gbl...
> Hello Julien,
> What about when you preview the report in the development environment?
> If you click the Page Setup, What's the result?
> Per my experience, this issue may caused by the incompatibility with your
> printer if your printer did not support Letter.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Julien,
Could you please let me know what's the version of Reporting Services you
use? Have you applied the Services pack?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||RS 2005
no sp applied yet
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:DVkTe%233bHHA.1820@.TK2MSFTNGHUB02.phx.gbl...
> Hello Julien,
> Could you please let me know what's the version of Reporting Services you
> use? Have you applied the Services pack?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Julien,
Could you please applied the latest Services Pack for Reporting Services?
How to obtain the latest service pack for SQL Server 2005
http://support.microsoft.com/kb/913089/
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Julien, hi -- I have a couple of wacky questions for you...
1) What is the DEFAULT printer on your box (I know that the printer you are
printing to is the Tosh, but if this is a server-mode report, it may not be
being constructed under your user identity... you can see where I'm going
withi this) ?
2) If question #1 does not help at all, what if you created a second Tosh
printer setup in which the DEFAULT layout for that setup was legal size, and
then you printed to that second printer setup?
BTW: I've seen something similar to this. For example, I have difficulty
getting the Excel export to respect a landscape orientation in a report that
prints landscape just fine to the printer driver.
The question is figuring out which rendering component is seeing the wrong
setup information, and at what moment it's having the trouble. I think
there really is a bug here but if we understand the bug we will be able to
figure out our workarounds <shrug>.
>L<
"Julien Bonnier" <julien@.m0851.com> wrote in message
news:erCs3m7bHHA.2088@.TK2MSFTNGP05.phx.gbl...
> RS 2005
> no sp applied yet
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
> news:DVkTe%233bHHA.1820@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Julien,
>> Could you please let me know what's the version of Reporting Services you
>> use? Have you applied the Services pack?
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/subscriptions/support/default.aspx.
>> ==================================================>> (This posting is provided "AS IS", with no warranties, and confers no
>> rights.)
>

Print File Size

Just installed SP2 and started experimenting with the print functionality.
The size of the print jobs are huge. I had a fairly simple six page report
with a spooling size of over 100 mb, a single page over 17mb. Is there
anything that can be done to reduce this size . If not, we will not be able
to use the functionality as the network just will bog down - it is back to
printing out pdf files - which the users do not like.
Any hope of getting around this?This is the current behavior and there is no workaround. We are looking at
what we can do to make this better for the Yukon Release.
--
| Thread-Topic: Print File Size
| thread-index: AcVi/0NJpHDoEkgjQrGWFSnnPT784w==| X-WBNR-Posting-Host: 24.97.250.114
| From: "=?Utf-8?B?Y2hyaXM=?=" <chris@.discussions.microsoft.com>
| Subject: Print File Size
| Date: Fri, 27 May 2005 14:01:33 -0700
| Lines: 12
| Message-ID: <BBD25630-CC0A-4176-AEE4-03EC7BE22C6D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:44927
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Just installed SP2 and started experimenting with the print
functionality.
| The size of the print jobs are huge. I had a fairly simple six page
report
| with a spooling size of over 100 mb, a single page over 17mb. Is there
| anything that can be done to reduce this size . If not, we will not be
able
| to use the functionality as the network just will bog down - it is back
to
| printing out pdf files - which the users do not like.
|
| Any hope of getting around this?
|
|
|
|
|

Primary versus unique keys

Hi

What impact is there in using a unique index instead of a primary key?

How does this impact performance?

How does this impact data file size?

Does it impact anything else?

Thanks

Hi,

http://www.mssqlcity.com/FAQ/General/primary_vs_unique_constraints.htm

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi

It that the only difference?

Is there any impact on Replication, Database Mirroing (SQL 2005), or any other features?

Thanks

|||

There is an impact on transactional replication (all options) since it requires a primary key (unique indexes do not work). No impact to either merge or snapshot replication.

Database Mirroring and other features don't care.

Primary Key falling short

Plz tell me what to do if Primary key fall short and execed the max size of
the datatype like integer with Identity increment option.If the identity value falls short you will get an arithmetic overflow error.
But then you could change your identity field to another data type. For
example, if your identity is using the int data type you can then change to
bigint that, according to SQL Server 2000 BOL, can hold integers from from
-2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ravi" wrote:
> Plz tell me what to do if Primary key fall short and execed the max size of
> the datatype like integer with Identity increment option.|||you have to drop the primary key and change the column datatype.
e.g.
create table tb1(i tinyint identity(254,1),constraint pk primary key(i))
go
insert tb1 default values
insert tb1 default values
--overflow error here
insert tb1 default values
go
alter table tb1 drop constraint pk
go
alter table tb1 alter column i bigint
go
alter table tb1 add constraint pk primary key(i)
go
--okay now
insert tb1 default values
go
select * from tb1
go
drop table tb1
-oj
"Ravi" <Ravi@.discussions.microsoft.com> wrote in message
news:CEAF4CD0-8F20-4E8F-ADF2-6490A60857E5@.microsoft.com...
> Plz tell me what to do if Primary key fall short and execed the max size
> of
> the datatype like integer with Identity increment option.sql

'PRIMARY' filegroup is full - no it's not!

I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space ? that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB ? more that twice
its original 5GB size ? but it still fails with the error message
above. And - here's another weird thing ? at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
RayTry running:
DBCC UPDATEUSAGE (0)
This will correct entries in sysindexes so that sp_spaceused will give you
the correct values for the amount of space actually used in the database.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.com...
I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space - that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB - more that twice
its original 5GB size - but it still fails with the error message
above. And - here's another weird thing - at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
Ray|||It's a good practice to pre-allocate enough space to handle anticipated
space requirements. Allow autogrow only as a safety net rather than allow
files to grow routinely during normal operation. This includes both normal
growth as well as space needed for maintenance.
Rebuilding a clustered index requires about 120% of the table size so you
should have free space of at least 1.2 times the largest table in your
database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
@.updateusage=true to correct inaccurate space reporting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.com...
>I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space - that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB - more that twice
> its original 5GB size - but it still fails with the error message
> above. And - here's another weird thing - at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray|||Thank you for the quick replies ? these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output ? I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here ? any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both normal
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.com...
> >I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray|||The sp_spaceused proc reports space actually used within your database.
Thus, although the file size is 12GB, you're only using 5GB.
I suspect that you have auto-shrink turned on. Turn this off. It's one of
those 'features' that can come up and bite you in cases like this.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.com...
Thank you for the quick replies - these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output - I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here - any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both
normal
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.com...
> >I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray|||Tom is right about auto-shrink. I never turn on this option in production
and shrink manually if there is a decrease in estimated space requirements.
For best performance, it's best to pre-allocate the space needed.
With your single file group, you can calculate the available space in your
data files by subtracting the reserved space reported by sp_spaceused from
the sum of the data file sizes reported by sp_helpdb.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.com...
> Thank you for the quick replies - these commands do help me get a
> better picture of the true database size. However, things are still
> not adding up properly. Please bear with me and I'll explain:
> After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
> database_name database_size unallocated space
> DataLoad 5081.06 MB 107.53 MB
> reserved data index_size unused
> 4928224 KB 2632800 KB 2204624 KB 90800 KB
> So, the db is approximately 5 GB in size. And when I look at the mdf
> data file, it is indeed 5 GB.
> According to sp_spaceused, my largest table is about 4 GB in size. So
> using Dan's 120% rule, I need just under 5GB of free space, in
> addition to the 5 GB already used, for a total of about 10 GB. Just
> to allow more than enough margin for error, I went ahead and allocated
> 12 GB:
> ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
> SIZE=12000MB)
> 'DataLoad_Data' is the primary filegroup, and the only filegroup for
> the database. And I can see that after running the ALTER DATABASE
> command, the mdf file is now 12,288,000 KB in size, just as expected.
> Question 1: sp_spaceused still gives exactly the same output.
> Shouldn't it reflect this new larger 12 GB size somewhere in the
> output - I expected the "database_size" or "reserved" number to jump
> up, but they didn't.
> Question 2: Even with the database at this huge new size, my attempt
> to rebuild an index fails with the "Could not allocate space" error,
> and the mdf file has shriveled itself back down to 5 GB (or could
> something be shrinking the file back down to 5 GB before the index
> creation has finished, which would account for the error?). And I'm
> not even rebuilding the index on the largest table -- the table whose
> index I'm recreating only takes up a measly 500 MB. It's not
> necessarily a problem with this particular index; I've also hit the
> error on rebuilding a different index, and on a large INSERT
> operation. Obviously I am missing something here - any ideas?
> For the record, here's how I'm attempting to recreate the index:
> CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
> (LoadId)
> WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
> Thanks again for sharing your expertise!
> Ray Scott
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
>> It's a good practice to pre-allocate enough space to handle anticipated
>> space requirements. Allow autogrow only as a safety net rather than
>> allow
>> files to grow routinely during normal operation. This includes both
>> normal
>> growth as well as space needed for maintenance.
>> Rebuilding a clustered index requires about 120% of the table size so you
>> should have free space of at least 1.2 times the largest table in your
>> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
>> @.updateusage=true to correct inaccurate space reporting.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ray.Net" <scott@.csgsolutions.com> wrote in message
>> news:10a1d257.0412050543.11b31a32@.posting.google.com...
>> >I have a SQL Server 2000 database, in which the size of the primary
>> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
>> > space - that should be more than enough free space for a 5 GB
>> > database, right?
>> >
>> > The problem is that when I perform certain operations (esp. recreating
>> > a clustered index), it still seems to run out of space with this
>> > error:
>> > Could not allocate space for object 'ActivityTracker' in database
>> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
>> > been terminated.
>> >
>> > Here's where it gets weird: in the database properties "Automatically
>> > grow file" IS checked, and Maximum file size is unrestricted. So
>> > there's lots of free space (nearly 3X the size of the db), and the
>> > growth is not restricted - what makes it think the filegroup is full?
>> >
>> > I found a few newsgroup postings that suggested sometimes the
>> > auto-grow cannot grow fast enough, so you should manually increase the
>> > size of the database. I tried this, using "alter database" to raise
>> > the size of the primary filegroup as high as 12GB - more that twice
>> > its original 5GB size - but it still fails with the error message
>> > above. And - here's another weird thing - at the time the error
>> > occurs, the database shrinks itself back down to 5 GB (even though I
>> > do NOT have the "Auto shrink" property checked). What's going on
>> > here?
>> >
>> > I appreciate any ideas you can offer on why this database seems to run
>> > out of space when there's plenty of space to be had.
>> >
>> > Ray|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Ray.Net" wrote:
> I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space â' that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB â' more that twice
> its original 5GB size â' but it still fails with the error message
> above. And - here's another weird thing â' at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray
>|||We have having the same exact problem. There is 30+ GB Free on the Drive
(Dynamic Disk, by the way). Database is set to unrestricted file growth.
Everything looks fine with the configuration of the sql database...however,
the database thinks there is no free space on the drive. Very wierd!!
Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks like
ill be calling MS soon!!
Dale
txskibum@.hotmail.com
"Ray.Net" wrote:
> I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space â' that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB â' more that twice
> its original 5GB size â' but it still fails with the error message
> above. And - here's another weird thing â' at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray
>|||Hi
Yes, you might have it on autogrow, but think about this:
Processes are filling your DB, your DB is virtually full, Autogrow kicks in,
Growing a DB by 10% does take a while, during this time, the last remainder
of the pages gets used up. Bingo, no more space as the DB is still growing.
Primary Group Full gets returned to your processes. The newly allocated
pages are not available for use until the DB has grown fully.
The lesson here. Grow your DB in advance of large operations so that you
don't run into this problem.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:64492BF2-BB58-48D5-A458-D1BE70FC9372@.microsoft.com...
> We have having the same exact problem. There is 30+ GB Free on the Drive
> (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> Everything looks fine with the configuration of the sql
database...however,
> the database thinks there is no free space on the drive. Very wierd!!
> Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks
like
> ill be calling MS soon!!
> Dale
> txskibum@.hotmail.com
>
> "Ray.Net" wrote:
> > I have a SQL Server 2000 database, in which the size of the primary
> > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > space - that should be more than enough free space for a 5 GB
> > database, right?
> >
> > The problem is that when I perform certain operations (esp. recreating
> > a clustered index), it still seems to run out of space with this
> > error:
> > Could not allocate space for object 'ActivityTracker' in database
> > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > been terminated.
> >
> > Here's where it gets weird: in the database properties "Automatically
> > grow file" IS checked, and Maximum file size is unrestricted. So
> > there's lots of free space (nearly 3X the size of the db), and the
> > growth is not restricted - what makes it think the filegroup is full?
> >
> > I found a few newsgroup postings that suggested sometimes the
> > auto-grow cannot grow fast enough, so you should manually increase the
> > size of the database. I tried this, using "alter database" to raise
> > the size of the primary filegroup as high as 12GB - more that twice
> > its original 5GB size - but it still fails with the error message
> > above. And - here's another weird thing - at the time the error
> > occurs, the database shrinks itself back down to 5 GB (even though I
> > do NOT have the "Auto shrink" property checked). What's going on
> > here?
> >
> > I appreciate any ideas you can offer on why this database seems to run
> > out of space when there's plenty of space to be had.
> >
> > Ray
> >|||Thanks Mike! I will try this!!
Dale
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Yes, you might have it on autogrow, but think about this:
> Processes are filling your DB, your DB is virtually full, Autogrow kicks in,
> Growing a DB by 10% does take a while, during this time, the last remainder
> of the pages gets used up. Bingo, no more space as the DB is still growing.
> Primary Group Full gets returned to your processes. The newly allocated
> pages are not available for use until the DB has grown fully.
> The lesson here. Grow your DB in advance of large operations so that you
> don't run into this problem.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dale" <Dale@.discussions.microsoft.com> wrote in message
> news:64492BF2-BB58-48D5-A458-D1BE70FC9372@.microsoft.com...
> > We have having the same exact problem. There is 30+ GB Free on the Drive
> > (Dynamic Disk, by the way). Database is set to unrestricted file growth.
> > Everything looks fine with the configuration of the sql
> database...however,
> > the database thinks there is no free space on the drive. Very wierd!!
> > Database is just shy of 5gb, but General Tab says 0.00Mb Free. Looks
> like
> > ill be calling MS soon!!
> >
> > Dale
> > txskibum@.hotmail.com
> >
> >
> >
> > "Ray.Net" wrote:
> >
> > > I have a SQL Server 2000 database, in which the size of the primary
> > > filegroup is 5 GB. The disk where the data resides has 14 GB of free
> > > space - that should be more than enough free space for a 5 GB
> > > database, right?
> > >
> > > The problem is that when I perform certain operations (esp. recreating
> > > a clustered index), it still seems to run out of space with this
> > > error:
> > > Could not allocate space for object 'ActivityTracker' in database
> > > 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> > > been terminated.
> > >
> > > Here's where it gets weird: in the database properties "Automatically
> > > grow file" IS checked, and Maximum file size is unrestricted. So
> > > there's lots of free space (nearly 3X the size of the db), and the
> > > growth is not restricted - what makes it think the filegroup is full?
> > >
> > > I found a few newsgroup postings that suggested sometimes the
> > > auto-grow cannot grow fast enough, so you should manually increase the
> > > size of the database. I tried this, using "alter database" to raise
> > > the size of the primary filegroup as high as 12GB - more that twice
> > > its original 5GB size - but it still fails with the error message
> > > above. And - here's another weird thing - at the time the error
> > > occurs, the database shrinks itself back down to 5 GB (even though I
> > > do NOT have the "Auto shrink" property checked). What's going on
> > > here?
> > >
> > > I appreciate any ideas you can offer on why this database seems to run
> > > out of space when there's plenty of space to be had.
> > >
> > > Ray
> > >
>
>

'PRIMARY' filegroup is full - no it's not!

I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB more that twice
its original 5GB size but it still fails with the error message
above. And - here's another weird thing at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
Ray
Try running:
DBCC UPDATEUSAGE (0)
This will correct entries in sysindexes so that sp_spaceused will give you
the correct values for the amount of space actually used in the database.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.c om...
I have a SQL Server 2000 database, in which the size of the primary
filegroup is 5 GB. The disk where the data resides has 14 GB of free
space - that should be more than enough free space for a 5 GB
database, right?
The problem is that when I perform certain operations (esp. recreating
a clustered index), it still seems to run out of space with this
error:
Could not allocate space for object 'ActivityTracker' in database
'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
been terminated.
Here's where it gets weird: in the database properties "Automatically
grow file" IS checked, and Maximum file size is unrestricted. So
there's lots of free space (nearly 3X the size of the db), and the
growth is not restricted - what makes it think the filegroup is full?
I found a few newsgroup postings that suggested sometimes the
auto-grow cannot grow fast enough, so you should manually increase the
size of the database. I tried this, using "alter database" to raise
the size of the primary filegroup as high as 12GB - more that twice
its original 5GB size - but it still fails with the error message
above. And - here's another weird thing - at the time the error
occurs, the database shrinks itself back down to 5 GB (even though I
do NOT have the "Auto shrink" property checked). What's going on
here?
I appreciate any ideas you can offer on why this database seems to run
out of space when there's plenty of space to be had.
Ray
|||It's a good practice to pre-allocate enough space to handle anticipated
space requirements. Allow autogrow only as a safety net rather than allow
files to grow routinely during normal operation. This includes both normal
growth as well as space needed for maintenance.
Rebuilding a clustered index requires about 120% of the table size so you
should have free space of at least 1.2 times the largest table in your
database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
@.updateusage=true to correct inaccurate space reporting.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412050543.11b31a32@.posting.google.c om...
>I have a SQL Server 2000 database, in which the size of the primary
> filegroup is 5 GB. The disk where the data resides has 14 GB of free
> space - that should be more than enough free space for a 5 GB
> database, right?
> The problem is that when I perform certain operations (esp. recreating
> a clustered index), it still seems to run out of space with this
> error:
> Could not allocate space for object 'ActivityTracker' in database
> 'DataLoad' because the 'PRIMARY' filegroup is full. The statement has
> been terminated.
> Here's where it gets weird: in the database properties "Automatically
> grow file" IS checked, and Maximum file size is unrestricted. So
> there's lots of free space (nearly 3X the size of the db), and the
> growth is not restricted - what makes it think the filegroup is full?
> I found a few newsgroup postings that suggested sometimes the
> auto-grow cannot grow fast enough, so you should manually increase the
> size of the database. I tried this, using "alter database" to raise
> the size of the primary filegroup as high as 12GB - more that twice
> its original 5GB size - but it still fails with the error message
> above. And - here's another weird thing - at the time the error
> occurs, the database shrinks itself back down to 5 GB (even though I
> do NOT have the "Auto shrink" property checked). What's going on
> here?
> I appreciate any ideas you can offer on why this database seems to run
> out of space when there's plenty of space to be had.
> Ray
|||Thank you for the quick replies these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both normal
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.c om...
|||The sp_spaceused proc reports space actually used within your database.
Thus, although the file size is 12GB, you're only using 5GB.
I suspect that you have auto-shrink turned on. Turn this off. It's one of
those 'features' that can come up and bite you in cases like this.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.c om...
Thank you for the quick replies - these commands do help me get a
better picture of the true database size. However, things are still
not adding up properly. Please bear with me and I'll explain:
After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
database_name database_size unallocated space
DataLoad 5081.06 MB 107.53 MB
reserved data index_size unused
4928224 KB 2632800 KB 2204624 KB 90800 KB
So, the db is approximately 5 GB in size. And when I look at the mdf
data file, it is indeed 5 GB.
According to sp_spaceused, my largest table is about 4 GB in size. So
using Dan's 120% rule, I need just under 5GB of free space, in
addition to the 5 GB already used, for a total of about 10 GB. Just
to allow more than enough margin for error, I went ahead and allocated
12 GB:
ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
SIZE=12000MB)
'DataLoad_Data' is the primary filegroup, and the only filegroup for
the database. And I can see that after running the ALTER DATABASE
command, the mdf file is now 12,288,000 KB in size, just as expected.
Question 1: sp_spaceused still gives exactly the same output.
Shouldn't it reflect this new larger 12 GB size somewhere in the
output - I expected the "database_size" or "reserved" number to jump
up, but they didn't.
Question 2: Even with the database at this huge new size, my attempt
to rebuild an index fails with the "Could not allocate space" error,
and the mdf file has shriveled itself back down to 5 GB (or could
something be shrinking the file back down to 5 GB before the index
creation has finished, which would account for the error?). And I'm
not even rebuilding the index on the largest table -- the table whose
index I'm recreating only takes up a measly 500 MB. It's not
necessarily a problem with this particular index; I've also hit the
error on rebuilding a different index, and on a large INSERT
operation. Obviously I am missing something here - any ideas?
For the record, here's how I'm attempting to recreate the index:
CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
(LoadId)
WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
Thanks again for sharing your expertise!
Ray Scott
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
> It's a good practice to pre-allocate enough space to handle anticipated
> space requirements. Allow autogrow only as a safety net rather than allow
> files to grow routinely during normal operation. This includes both
normal[vbcol=seagreen]
> growth as well as space needed for maintenance.
> Rebuilding a clustered index requires about 120% of the table size so you
> should have free space of at least 1.2 times the largest table in your
> database. As Tom suggested, you can use DBCC UPDATEUSAGE or sp_spaceused
> @.updateusage=true to correct inaccurate space reporting.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412050543.11b31a32@.posting.google.c om...
|||Tom is right about auto-shrink. I never turn on this option in production
and shrink manually if there is a decrease in estimated space requirements.
For best performance, it's best to pre-allocate the space needed.
With your single file group, you can calculate the available space in your
data files by subtracting the reserved space reported by sp_spaceused from
the sum of the data file sizes reported by sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.c om...[vbcol=seagreen]
> Thank you for the quick replies - these commands do help me get a
> better picture of the true database size. However, things are still
> not adding up properly. Please bear with me and I'll explain:
> After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
> database_name database_size unallocated space
> DataLoad 5081.06 MB 107.53 MB
> reserved data index_size unused
> 4928224 KB 2632800 KB 2204624 KB 90800 KB
> So, the db is approximately 5 GB in size. And when I look at the mdf
> data file, it is indeed 5 GB.
> According to sp_spaceused, my largest table is about 4 GB in size. So
> using Dan's 120% rule, I need just under 5GB of free space, in
> addition to the 5 GB already used, for a total of about 10 GB. Just
> to allow more than enough margin for error, I went ahead and allocated
> 12 GB:
> ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
> SIZE=12000MB)
> 'DataLoad_Data' is the primary filegroup, and the only filegroup for
> the database. And I can see that after running the ALTER DATABASE
> command, the mdf file is now 12,288,000 KB in size, just as expected.
> Question 1: sp_spaceused still gives exactly the same output.
> Shouldn't it reflect this new larger 12 GB size somewhere in the
> output - I expected the "database_size" or "reserved" number to jump
> up, but they didn't.
> Question 2: Even with the database at this huge new size, my attempt
> to rebuild an index fails with the "Could not allocate space" error,
> and the mdf file has shriveled itself back down to 5 GB (or could
> something be shrinking the file back down to 5 GB before the index
> creation has finished, which would account for the error?). And I'm
> not even rebuilding the index on the largest table -- the table whose
> index I'm recreating only takes up a measly 500 MB. It's not
> necessarily a problem with this particular index; I've also hit the
> error on rebuilding a different index, and on a large INSERT
> operation. Obviously I am missing something here - any ideas?
> For the record, here's how I'm attempting to recreate the index:
> CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
> (LoadId)
> WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
> Thanks again for sharing your expertise!
> Ray Scott
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
|||Thanks guys for the additional responses. It does seem to be
auto-shrinking, BUT auto shrink is not turned on. Here's how I
checked, maybe I'm looking in the wrong place? Enterprise manager,
right-clicked database, Properties, Options tab. In the Settings
section, the only checked properties are Auto update statistics, Auto
create statistics, and Torn page detection. The other settings,
including Auto shrink, are not checked. Strange, eh?
I'm still puzzled as to why SQL Server doesn't seem to recognize the new
large file size after I expand the file using "ALTER DATABASE DataLoad
MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
these SPs still report the old 5 GB size.
Thanks again for your help,
Ray Scott
===============================================
From: Dan Guzman
Tom is right about auto-shrink. I never turn on this option in
production
and shrink manually if there is a decrease in estimated space
requirements.
For best performance, it's best to pre-allocate the space needed.
With your single file group, you can calculate the available space in
your
data files by subtracting the reserved space reported by sp_spaceused
from
the sum of the data file sizes reported by sp_helpdb.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray.Net" <scott@.csgsolutions.com> wrote in message
news:10a1d257.0412052210.609fb2c9@.posting.google.c om...[vbcol=seagreen]
> Thank you for the quick replies - these commands do help me get a
> better picture of the true database size. However, things are still
> not adding up properly. Please bear with me and I'll explain:
> After running DBCC UPDATEUSAGE (0), the output of sp_spaceused is:
> database_name database_size unallocated space
> DataLoad 5081.06 MB 107.53 MB
> reserved data index_size unused
> 4928224 KB 2632800 KB 2204624 KB 90800 KB
> So, the db is approximately 5 GB in size. And when I look at the mdf
> data file, it is indeed 5 GB.
> According to sp_spaceused, my largest table is about 4 GB in size. So
> using Dan's 120% rule, I need just under 5GB of free space, in
> addition to the 5 GB already used, for a total of about 10 GB. Just
> to allow more than enough margin for error, I went ahead and allocated
> 12 GB:
> ALTER DATABASE DataLoad MODIFY FILE(NAME='DataLoad_Data',
> SIZE=12000MB)
> 'DataLoad_Data' is the primary filegroup, and the only filegroup for
> the database. And I can see that after running the ALTER DATABASE
> command, the mdf file is now 12,288,000 KB in size, just as expected.
> Question 1: sp_spaceused still gives exactly the same output.
> Shouldn't it reflect this new larger 12 GB size somewhere in the
> output - I expected the "database_size" or "reserved" number to jump
> up, but they didn't.
> Question 2: Even with the database at this huge new size, my attempt
> to rebuild an index fails with the "Could not allocate space" error,
> and the mdf file has shriveled itself back down to 5 GB (or could
> something be shrinking the file back down to 5 GB before the index
> creation has finished, which would account for the error?). And I'm
> not even rebuilding the index on the largest table -- the table whose
> index I'm recreating only takes up a measly 500 MB. It's not
> necessarily a problem with this particular index; I've also hit the
> error on rebuilding a different index, and on a large INSERT
> operation. Obviously I am missing something here - any ideas?
> For the record, here's how I'm attempting to recreate the index:
> CREATE CLUSTERED INDEX idx_Exceptions_LoadID ON dbo.Exceptions
> (LoadId)
> WITH FILLFACTOR = 90, DROP_EXISTING ON [PRIMARY]
> Thanks again for sharing your expertise!
> Ray Scott
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<#u$VEzt2EHA.1564@.TK2MSFTNGP09.phx.gbl>...
anticipated[vbcol=seagreen]
you[vbcol=seagreen]
your[vbcol=seagreen]
sp_spaceused[vbcol=seagreen]
free[vbcol=seagreen]
recreating[vbcol=seagreen]
has[vbcol=seagreen]
"Automatically[vbcol=seagreen]
full?[vbcol=seagreen]
the[vbcol=seagreen]
I[vbcol=seagreen]
run[vbcol=seagreen]
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> Tom is right about auto-shrink. I never turn on this option in
> production
> and shrink manually if there is a decrease in estimated space
> requirements.
> For best performance, it's best to pre-allocate the space needed.
> With your single file group, you can calculate the available space in
> your
> data files by subtracting the reserved space reported by sp_spaceused
> from
> the sum of the data file sizes reported by sp_helpdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412052210.609fb2c9@.posting.google.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Consider using sp_helpfile to report the sizes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eYJnuJ62EHA.4072@.TK2MSFTNGP10.phx.gbl...
sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> Tom is right about auto-shrink. I never turn on this option in
> production
> and shrink manually if there is a decrease in estimated space
> requirements.
> For best performance, it's best to pre-allocate the space needed.
> With your single file group, you can calculate the available space in
> your
> data files by subtracting the reserved space reported by sp_spaceused
> from
> the sum of the data file sizes reported by sp_helpdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412052210.609fb2c9@.posting.google.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Use sp_dboption 'MyDatabase' to tell you whether or not the AUTOSHRINK is
REALLY set or not.
Use sp_spaceused @.updateusage = 'true' to get your current space allocations
synch'd up.
Make sure in your Database Maintenance Plan, Reorganize Database, you DO NOT
have the REMOVE UNUSED SPACE option checked.
Take a look at your SQL Agent Job steps and make sure you DO NOT have any
hand-coded sqlmaint.exe jobs created, especially ones with
the -RmUnusedSpace parameter set. Also look for any Shrink Jobs that
someone may have one-off'ed but scheduled it to be reoccuring.
When you run the ALTER DATABASE statement, run a Profiler Trace and keep it
running while you run the CREATE CLUSTERED INDEX WITH DROP EXISTING command
to see if there are any executed DBCC SHRINKDATABASE or DBCC SHRINKFILE
operations occurring.
Finally, you modify files not file groups. File Groups are collections of
files that grow with the porportional fill algorythm, not the File Group
itself, although, that is the error message you get when none of the
contained files within the File Group are allowed to grow.
Also, I am suspecting that you have more than one file and that, perhaps,
not all of them are allowed to grow. The sp_helpdb 'MyDatabase' command
will show you all of the existing files and contained filegroups. You need
to validate all of them.
Hope this helps.
Sincerely,
Anthony Thomas

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDR$Yt92EHA.2568@.TK2MSFTNGP11.phx.gbl...
Consider using sp_helpfile to report the sizes.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eYJnuJ62EHA.4072@.TK2MSFTNGP10.phx.gbl...
sp_helpdb should report the increased size immediately after the ALTER. If
it shrinks afterward, perhaps a maintenance plan is setup to 'remove unused
space from data files'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ray Scott" <scott@.csgsolutions.com> wrote in message
news:OalrpF62EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Thanks guys for the additional responses. It does seem to be
> auto-shrinking, BUT auto shrink is not turned on. Here's how I
> checked, maybe I'm looking in the wrong place? Enterprise manager,
> right-clicked database, Properties, Options tab. In the Settings
> section, the only checked properties are Auto update statistics, Auto
> create statistics, and Torn page detection. The other settings,
> including Auto shrink, are not checked. Strange, eh?
> I'm still puzzled as to why SQL Server doesn't seem to recognize the new
> large file size after I expand the file using "ALTER DATABASE DataLoad
> MODIFY FILE(NAME='DataLoad_Data', SIZE=12000MB)". I do not see the
> larger 12 GB file size reflected in either sp_helpdb or sp_spaceused -
> these SPs still report the old 5 GB size.
> Thanks again for your help,
> Ray Scott
> ===============================================
> From: Dan Guzman
> Tom is right about auto-shrink. I never turn on this option in
> production
> and shrink manually if there is a decrease in estimated space
> requirements.
> For best performance, it's best to pre-allocate the space needed.
> With your single file group, you can calculate the available space in
> your
> data files by subtracting the reserved space reported by sp_spaceused
> from
> the sum of the data file sizes reported by sp_helpdb.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ray.Net" <scott@.csgsolutions.com> wrote in message
> news:10a1d257.0412052210.609fb2c9@.posting.google.c om...
> anticipated
> you
> your
> sp_spaceused
> free
> recreating
> has
> "Automatically
> full?
> the
> I
> run
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

'Primary filegroup is full

I have an application that receives the error that the primary filegroup is
full a couple of times at day. I can bump up the database size but I would
think having it set to grow at 10 percent I shouldn't have to worry about
this. I just looked and there is 3 percent of available space. How do I
make sure there is always available space?
ThanksNever set the DB to a % for autogrow since the size changes with each
growth. Determine what size in MB can be grown in about 10 seconds or less
and use that amount instead. But just make the data files large enough to
hold what you expect for a long time out and you should never need to use
autogrow. Do not shrink the database or turn on autoshrink.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Don" <Don@.discussions.microsoft.com> wrote in message
news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>I have an application that receives the error that the primary filegroup is
> full a couple of times at day. I can bump up the database size but I
> would
> think having it set to grow at 10 percent I shouldn't have to worry about
> this. I just looked and there is 3 percent of available space. How do I
> make sure there is always available space?
> Thanks
>|||Be sure you checked "Enable Autogrowth" of your database in the Primary File
Group and then be sure that Maximum File Size is "Unrestricted File Growth"
if you do not wanna bother yourself with this issue again.
You can do these settings from the properties of your database.
And do neglect setting values of File Growth and Miximum File Size according
to your system needs. These settings affect SQL Server's performance
(especially for huge ones).
--
Ekrem Ã?nsoy
"Don" <Don@.discussions.microsoft.com> wrote in message
news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>I have an application that receives the error that the primary filegroup is
> full a couple of times at day. I can bump up the database size but I
> would
> think having it set to grow at 10 percent I shouldn't have to worry about
> this. I just looked and there is 3 percent of available space. How do I
> make sure there is always available space?
> Thanks
>|||Don,
Pay attention to all of what Andrew is saying, it is important. Nothing, and
I mean nothing replaces knowing your enviornment and what your database
resource needs are.
If you allow growth (or shrinkage) you are fragmenting the partition/drive
your database resides on and will pay for it in performance.
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> Never set the DB to a % for autogrow since the size changes with each
> growth. Determine what size in MB can be grown in about 10 seconds or less
> and use that amount instead. But just make the data files large enough to
> hold what you expect for a long time out and you should never need to use
> autogrow. Do not shrink the database or turn on autoshrink.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>>I have an application that receives the error that the primary filegroup
>>is
>> full a couple of times at day. I can bump up the database size but I
>> would
>> think having it set to grow at 10 percent I shouldn't have to worry about
>> this. I just looked and there is 3 percent of available space. How do I
>> make sure there is always available space?
>> Thanks
>|||Thanks for the help, so I went into Enterprise Manager and bumped up the size
for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
does not exiist/
when trying to save the new setting
"Jay" wrote:
> Don,
> Pay attention to all of what Andrew is saying, it is important. Nothing, and
> I mean nothing replaces knowing your enviornment and what your database
> resource needs are.
> If you allow growth (or shrinkage) you are fragmenting the partition/drive
> your database resides on and will pay for it in performance.
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> > Never set the DB to a % for autogrow since the size changes with each
> > growth. Determine what size in MB can be grown in about 10 seconds or less
> > and use that amount instead. But just make the data files large enough to
> > hold what you expect for a long time out and you should never need to use
> > autogrow. Do not shrink the database or turn on autoshrink.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "Don" <Don@.discussions.microsoft.com> wrote in message
> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
> >>I have an application that receives the error that the primary filegroup
> >>is
> >> full a couple of times at day. I can bump up the database size but I
> >> would
> >> think having it set to grow at 10 percent I shouldn't have to worry about
> >> this. I just looked and there is 3 percent of available space. How do I
> >> make sure there is always available space?
> >>
> >> Thanks
> >>
> >
>
>|||Did you change the name of the file by mistake in the dialog? Have you
tried it again? If that doesn't work you might want to try and use ALTER
DATABASE instead.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Don" <Don@.discussions.microsoft.com> wrote in message
news:77A8F234-BD7D-4683-AB2E-FF0A8F9315B3@.microsoft.com...
> Thanks for the help, so I went into Enterprise Manager and bumped up the
> size
> for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
> does not exiist/
> when trying to save the new setting
>
> "Jay" wrote:
>> Don,
>> Pay attention to all of what Andrew is saying, it is important. Nothing,
>> and
>> I mean nothing replaces knowing your enviornment and what your database
>> resource needs are.
>> If you allow growth (or shrinkage) you are fragmenting the
>> partition/drive
>> your database resides on and will pay for it in performance.
>> Jay
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
>> > Never set the DB to a % for autogrow since the size changes with each
>> > growth. Determine what size in MB can be grown in about 10 seconds or
>> > less
>> > and use that amount instead. But just make the data files large enough
>> > to
>> > hold what you expect for a long time out and you should never need to
>> > use
>> > autogrow. Do not shrink the database or turn on autoshrink.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "Don" <Don@.discussions.microsoft.com> wrote in message
>> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
>> >>I have an application that receives the error that the primary
>> >>filegroup
>> >>is
>> >> full a couple of times at day. I can bump up the database size but I
>> >> would
>> >> think having it set to grow at 10 percent I shouldn't have to worry
>> >> about
>> >> this. I just looked and there is 3 percent of available space. How
>> >> do I
>> >> make sure there is always available space?
>> >>
>> >> Thanks
>> >>
>> >
>>|||Thanks for the help, I did figure out what the issue was. The drive was set
as Fat32 (4 GB limit) so I had to change how the 300 GB drive was set.
Thanks
"Andrew J. Kelly" wrote:
> Did you change the name of the file by mistake in the dialog? Have you
> tried it again? If that doesn't work you might want to try and use ALTER
> DATABASE instead.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:77A8F234-BD7D-4683-AB2E-FF0A8F9315B3@.microsoft.com...
> > Thanks for the help, so I went into Enterprise Manager and bumped up the
> > size
> > for 4 GB to 5GB but get the error Error 5041 Modify File Failed File. It
> > does not exiist/
> > when trying to save the new setting
> >
> >
> > "Jay" wrote:
> >
> >> Don,
> >>
> >> Pay attention to all of what Andrew is saying, it is important. Nothing,
> >> and
> >> I mean nothing replaces knowing your enviornment and what your database
> >> resource needs are.
> >>
> >> If you allow growth (or shrinkage) you are fragmenting the
> >> partition/drive
> >> your database resides on and will pay for it in performance.
> >>
> >> Jay
> >>
> >> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> >> news:OlR4Lii8HHA.4420@.TK2MSFTNGP02.phx.gbl...
> >> > Never set the DB to a % for autogrow since the size changes with each
> >> > growth. Determine what size in MB can be grown in about 10 seconds or
> >> > less
> >> > and use that amount instead. But just make the data files large enough
> >> > to
> >> > hold what you expect for a long time out and you should never need to
> >> > use
> >> > autogrow. Do not shrink the database or turn on autoshrink.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> > Solid Quality Mentors
> >> >
> >> >
> >> > "Don" <Don@.discussions.microsoft.com> wrote in message
> >> > news:52A45F80-F8C3-4766-9F3C-D1BE2D3E9066@.microsoft.com...
> >> >>I have an application that receives the error that the primary
> >> >>filegroup
> >> >>is
> >> >> full a couple of times at day. I can bump up the database size but I
> >> >> would
> >> >> think having it set to grow at 10 percent I shouldn't have to worry
> >> >> about
> >> >> this. I just looked and there is 3 percent of available space. How
> >> >> do I
> >> >> make sure there is always available space?
> >> >>
> >> >> Thanks
> >> >>
> >> >
> >>
> >>
> >>
>

Primary file & tan Log grow crazy!

HI:
I have a Table about 3GB in size. Whenever I try to add a column or set
a primary key to this table from the Enterprise Manager, the operation
would take a long time and the MDF and LOG file will just keep growing
crazy (more then 3GB) that it finanlly eats up all my hard disk space
and I have to kill the process.
So, how many HD space does it need to modify a 3GB Table (eg. add a
column). Actually what is the ratio? Is there any better way to modify
a large Table?
I have also enable the Auto-Shrink properties of the Table but still no
use.
Much appreciate for any advise.JJ wrote:
> HI:
> I have a Table about 3GB in size. Whenever I try to add a column or
> set a primary key to this table from the Enterprise Manager, the
> operation would take a long time and the MDF and LOG file will just
> keep growing crazy (more then 3GB) that it finanlly eats up all my
> hard disk space and I have to kill the process.
> So, how many HD space does it need to modify a 3GB Table (eg. add a
> column). Actually what is the ratio? Is there any better way to modify
> a large Table?
> I have also enable the Auto-Shrink properties of the Table but still
> no use.
> Much appreciate for any advise.
You should always make these types of changes using good old ALTER TABLE
commands from your query tool and avoid using SQL EM for these tasks.
While SQL EM seems like an easy alternative, depending on the operation,
SQL EM may actually be creating a copy of the table with the changes and
manually migrating the data. To see what SQL EM is doing you can either
watch the activity in Profiler or script out the changes in SQL EM.
Setting a PK in a table should not cause a recreate of the table, but if
you create the PK as clustered, then SQL Server has to reorg the entire
3GB table, which could be quite time consuming.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Use change scripts for that, EMs doing something a useless stuff around
the scripts. If you make your changes in a DDL scripts you should be
able to execute that much easier, even because the addition of a
primary key has no need to recreate the table as it is in the case of
an identity column.
HTH, Jens Suessmeyer.