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
> > >
>
>
Showing posts with label disk. Show all posts
Showing posts with label disk. Show all posts
Friday, March 9, 2012
'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!
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 file group is full
Hi there,
I've just run some DTS packages on my test sqlserver (Which has limited
hard disk space and memory) and all the tasks have failed, due to
'PRIMARY' file group is full
Is there a query or script I can run to resolve this problem??
M3ckon
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!m3ckon (anonymous@.devdex.com) writes:
> I've just run some DTS packages on my test sqlserver (Which has limited
> hard disk space and memory) and all the tasks have failed, due to
> 'PRIMARY' file group is full
> Is there a query or script I can run to resolve this problem??
Could send a query to whomeever holds you money to get more hardware.
The message says that SQL Server cannot autogrow the database, because
there is not space on the disk. By default SQL Server grows by 10%, so
if your database is 20 GB, SQL Server tries to grow 2 GB. If you think
you can fit your data into these 2 GB, then you lower that number to
squeeze out the rest from your disk.
Else you will have to try to free up some disk space. If the log for
the database is on the same disk, and you could live with simple
recovery, you could change to simple recovery, and then issue
BACKUP LOG db WITH TRUNCATE_ONLY. You have also have to do a DBCC
SHRINKFILE on the log. But before you do any of this, check how big
your log is.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Wednesday, March 7, 2012
Prfmon questions - Disks
I am monitoring a disk which has SQL data. There are the counters i am
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i could
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIAIt depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
--
---
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> >I am monitoring a disk which has SQL data. There are the counters i am
> > monitoring
> >
> > AVg.Disk.Read.Q.Length
> > AVg.Disk.Write.Q.Length
> > Disk Reads/sec
> > Disk Writes/sec
> > Split IO/sec
> >
> > I have set all the scale to 1.0(how does this scale works?what is it
> > actually?) So, which counters i have to pay attention closely so that i
> > could
> > tell them that they are reading or writing their disk extensively and sth
> > need to be done like splitting the data to several disks.
> > Any hints would be really great
> >
> > TIA
> >
> >
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
> > I prefer the following counters:
> >
> > Current disk Q length
> > Current Read bytes/sec
> > Current Write bytes/sec
> >
> > Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> > Queuing and on the new SATA disk systems with RAID or Native Command
> > Queuing. Average times can mask short term activity spikes. Most data
> > partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> > bytes/sec very closely. Disk Q length is the real critical counter as it
> > indicates an IO bottleneck.
> >
> > These counters work best if you have run IOmeter or any IO stress before
> > deploying the server so you have an idea of its true maximum capabilities
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> > "rupart" <rupart@.discussions.microsoft.com> wrote in message
> > news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> > >I am monitoring a disk which has SQL data. There are the counters i am
> > > monitoring
> > >
> > > AVg.Disk.Read.Q.Length
> > > AVg.Disk.Write.Q.Length
> > > Disk Reads/sec
> > > Disk Writes/sec
> > > Split IO/sec
> > >
> > > I have set all the scale to 1.0(how does this scale works?what is it
> > > actually?) So, which counters i have to pay attention closely so that i
> > > could
> > > tell them that they are reading or writing their disk extensively and sth
> > > need to be done like splitting the data to several disks.
> > > Any hints would be really great
> > >
> > > TIA
> > >
> > >
> >
> >
> >|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>> I prefer the following counters:
>> Current disk Q length
>> Current Read bytes/sec
>> Current Write bytes/sec
>> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
>> Queuing and on the new SATA disk systems with RAID or Native Command
>> Queuing. Average times can mask short term activity spikes. Most data
>> partition reads and writes are in 8K blocks so IOs/sec usually follows
>> teh
>> bytes/sec very closely. Disk Q length is the real critical counter as it
>> indicates an IO bottleneck.
>> These counters work best if you have run IOmeter or any IO stress before
>> deploying the server so you have an idea of its true maximum capabilities
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "rupart" <rupart@.discussions.microsoft.com> wrote in message
>> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>> >I am monitoring a disk which has SQL data. There are the counters i am
>> > monitoring
>> >
>> > AVg.Disk.Read.Q.Length
>> > AVg.Disk.Write.Q.Length
>> > Disk Reads/sec
>> > Disk Writes/sec
>> > Split IO/sec
>> >
>> > I have set all the scale to 1.0(how does this scale works?what is it
>> > actually?) So, which counters i have to pay attention closely so that i
>> > could
>> > tell them that they are reading or writing their disk extensively and
>> > sth
>> > need to be done like splitting the data to several disks.
>> > Any hints would be really great
>> >
>> > TIA
>> >
>> >
>>
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i could
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIAIt depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
--
---
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> >I am monitoring a disk which has SQL data. There are the counters i am
> > monitoring
> >
> > AVg.Disk.Read.Q.Length
> > AVg.Disk.Write.Q.Length
> > Disk Reads/sec
> > Disk Writes/sec
> > Split IO/sec
> >
> > I have set all the scale to 1.0(how does this scale works?what is it
> > actually?) So, which counters i have to pay attention closely so that i
> > could
> > tell them that they are reading or writing their disk extensively and sth
> > need to be done like splitting the data to several disks.
> > Any hints would be really great
> >
> > TIA
> >
> >
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
> > I prefer the following counters:
> >
> > Current disk Q length
> > Current Read bytes/sec
> > Current Write bytes/sec
> >
> > Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> > Queuing and on the new SATA disk systems with RAID or Native Command
> > Queuing. Average times can mask short term activity spikes. Most data
> > partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> > bytes/sec very closely. Disk Q length is the real critical counter as it
> > indicates an IO bottleneck.
> >
> > These counters work best if you have run IOmeter or any IO stress before
> > deploying the server so you have an idea of its true maximum capabilities
> >
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> >
> > "rupart" <rupart@.discussions.microsoft.com> wrote in message
> > news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
> > >I am monitoring a disk which has SQL data. There are the counters i am
> > > monitoring
> > >
> > > AVg.Disk.Read.Q.Length
> > > AVg.Disk.Write.Q.Length
> > > Disk Reads/sec
> > > Disk Writes/sec
> > > Split IO/sec
> > >
> > > I have set all the scale to 1.0(how does this scale works?what is it
> > > actually?) So, which counters i have to pay attention closely so that i
> > > could
> > > tell them that they are reading or writing their disk extensively and sth
> > > need to be done like splitting the data to several disks.
> > > Any hints would be really great
> > >
> > > TIA
> > >
> > >
> >
> >
> >|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>> I prefer the following counters:
>> Current disk Q length
>> Current Read bytes/sec
>> Current Write bytes/sec
>> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
>> Queuing and on the new SATA disk systems with RAID or Native Command
>> Queuing. Average times can mask short term activity spikes. Most data
>> partition reads and writes are in 8K blocks so IOs/sec usually follows
>> teh
>> bytes/sec very closely. Disk Q length is the real critical counter as it
>> indicates an IO bottleneck.
>> These counters work best if you have run IOmeter or any IO stress before
>> deploying the server so you have an idea of its true maximum capabilities
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "rupart" <rupart@.discussions.microsoft.com> wrote in message
>> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>> >I am monitoring a disk which has SQL data. There are the counters i am
>> > monitoring
>> >
>> > AVg.Disk.Read.Q.Length
>> > AVg.Disk.Write.Q.Length
>> > Disk Reads/sec
>> > Disk Writes/sec
>> > Split IO/sec
>> >
>> > I have set all the scale to 1.0(how does this scale works?what is it
>> > actually?) So, which counters i have to pay attention closely so that i
>> > could
>> > tell them that they are reading or writing their disk extensively and
>> > sth
>> > need to be done like splitting the data to several disks.
>> > Any hints would be really great
>> >
>> > TIA
>> >
>> >
>>
Prfmon questions - Disks
I am monitoring a disk which has SQL data. There are the counters i am
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i could
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIA
It depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>
>
|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i could
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIA
It depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>
|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>
>
|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
Prfmon questions - Disks
I am monitoring a disk which has SQL data. There are the counters i am
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i coul
d
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIAIt depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i co
uld
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
--
---
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it a
nd
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>
monitoring
AVg.Disk.Read.Q.Length
AVg.Disk.Write.Q.Length
Disk Reads/sec
Disk Writes/sec
Split IO/sec
I have set all the scale to 1.0(how does this scale works?what is it
actually?) So, which counters i have to pay attention closely so that i coul
d
tell them that they are reading or writing their disk extensively and sth
need to be done like splitting the data to several disks.
Any hints would be really great
TIAIt depends on what your IO subsystem can handle. But assuming the data is
being spread over at least 10 disks (preferably more), I get concerned when
any of the Avg queue lengths is greater than about 10 for a sustained time.
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Split i/o is the only one here I'd be looking at. A lot of split i/o is
indicative of a problem. % disk time is an easy counter to watch since it's
relatively indicative of whether your disks need to be watched closely. A
set of disks with 100% disk time will need to be tuned for performance and
SQL watched closely. If disk time is 20% or greater you'll need to start
keeping an eye on indexes, query plans and the like.
"rupart" wrote:
> I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i co
uld
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||I prefer the following counters:
Current disk Q length
Current Read bytes/sec
Current Write bytes/sec
Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
Queuing and on the new SATA disk systems with RAID or Native Command
Queuing. Average times can mask short term activity spikes. Most data
partition reads and writes are in 8K blocks so IOs/sec usually follows teh
bytes/sec very closely. Disk Q length is the real critical counter as it
indicates an IO bottleneck.
These counters work best if you have run IOmeter or any IO stress before
deploying the server so you have an idea of its true maximum capabilities
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||> I have set all the scale to 1.0(how does this scale works?what is it
> actually?)
The counter value is multiplied by the specified scale and the result is
graphed according the min/max graph value. So, with a scale of 1 and the
default 0-100 min/max, computed values equal to or greater than over 100
will appear at the top of the graph and lower values somewhere between the
top and the bottom.
As a general rule, disk queue lengths should be no more than 2 times the
number of physical disks in the array. A higher number indicates i/o
requests are waiting for other i/o to complete rather than doing productive
work. So if you have 5 disks, you could specify a scale of 10 to that the
graphed metric will appear at the top when the value is >= 10.
Reads/Writes/Transfers per second is a measure of how much i/o is performed.
This can be a little tricky to interpret since it includes both sequential
and random i/o as well as i/o that is cached by the controller and disks.
There isn't necessarily a good or bad number but you can compare
transfers/sec to what your i/o subsystem is capable of doing to determine if
there is a problem. For example, if you run an i/o bound query and don't
have disk queuing and transfers/sec is well under your disk capability, this
could indicate a lot of disk seek time.
Hope this helps.
Dan Guzman
SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>I am monitoring a disk which has SQL data. There are the counters i am
> monitoring
> AVg.Disk.Read.Q.Length
> AVg.Disk.Write.Q.Length
> Disk Reads/sec
> Disk Writes/sec
> Split IO/sec
> I have set all the scale to 1.0(how does this scale works?what is it
> actually?) So, which counters i have to pay attention closely so that i
> could
> tell them that they are reading or writing their disk extensively and sth
> need to be done like splitting the data to several disks.
> Any hints would be really great
> TIA
>|||Thx alot guys for your replies. OK, here are my counters data for 30 days
and i only captured the average value. From here, what we can say abt it and
anything need to be done or worried? (Fyi: The scale set to 1 and data
captured for each 15mins interval)
counters Data
--
---
AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
Disk Writes/sec (average =24.881, min=6.726, max=128.97)
Split IO/sec (average =0.077, min=0, max=1.774)
"Geoff N. Hiten" wrote:
> I prefer the following counters:
> Current disk Q length
> Current Read bytes/sec
> Current Write bytes/sec
> Percent Disk time is useless on SCSI subsystems with RAID or Command Tag
> Queuing and on the new SATA disk systems with RAID or Native Command
> Queuing. Average times can mask short term activity spikes. Most data
> partition reads and writes are in 8K blocks so IOs/sec usually follows teh
> bytes/sec very closely. Disk Q length is the real critical counter as it
> indicates an IO bottleneck.
> These counters work best if you have run IOmeter or any IO stress before
> deploying the server so you have an idea of its true maximum capabilities
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:5DACEB45-B44E-4F6D-9311-1CDE02323612@.microsoft.com...
>
>|||by the way, it's in RAID 0+1 currently
"rupart" wrote:
[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it a
nd
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>|||You need to capture at 15 second to 2 minute intervals for a few days, at
least during peak hours. Then you can determine your performance
bottlenecks woth the graphs relative to each other. Note that the scale for
each counter only affects its presentation on the graph. The actual
underlying values are always what is recorded. Average, min, and max over
long periods of time really don't mean a lot. How long does a value stay
near its maximum?. Is the application slowing down during these times? Raw
numbers don't tell the story, nunbers in context do.
You might want to look at the SQLH2 performance collector module for this
type of data recording.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:66056E0C-363D-4FBF-A1FF-614508C7A552@.microsoft.com...[vbcol=seagreen]
> Thx alot guys for your replies. OK, here are my counters data for 30 days
> and i only captured the average value. From here, what we can say abt it
> and
> anything need to be done or worried? (Fyi: The scale set to 1 and data
> captured for each 15mins interval)
> counters Data
> --
> ---
> AVg.Disk.Read.Q.Length (average =1.6, min=0, max=96.84)
> AVg.Disk.Write.Q.Length (average =0.228, min=0.04, max=13.22)
> Disk Reads/sec (average =55.2, min=0.08, max=1456.990)
> Disk Writes/sec (average =24.881, min=6.726, max=128.97)
> Split IO/sec (average =0.077, min=0, max=1.774)
>
> "Geoff N. Hiten" wrote:
>
Subscribe to:
Posts (Atom)