Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenO
In a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Ken
http://www.sql-server-performance.com/database_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Showing posts with label growth. Show all posts
Showing posts with label growth. Show all posts
Tuesday, March 27, 2012
Database Growth Options
Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenOIn a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using
a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ken
http://www.sql-server-performance.c...se_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenOIn a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using
a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ken
http://www.sql-server-performance.c...se_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Database growth excessive when changing varchar length from 50 to 100.
Hi all,
I'm trying to get an understanding of a serious problem I have with a large DB in production. This is going to be obvious to someone (everyone probably) <bg>
I have a table which consists of numerous varchars and ints but also a Text type field. This table resides in a SQL 2000 Database. This DB currently has a data file size of 16Gb and a Transaction Log size of 17Gb. When I edit the table and increase the size of a Varchar field from 50 to 100 these files grow to more than double their size!
Why is this happening and how can I prevent this?
TIA
NozFx
Hi all,
Ok made two huge mistakes here. One I have large text data along side other fields that when changes are made the structure changes dramatically. Secondly I have full transaction logging in which all changes are logged. Going to have to make sure I learn my lessons here. Got all the sizes down now but I needed double to do that.
NozFx
sqlDatabase growth and Replication
We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database growth and Replication
We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegro ups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||That was it. Missed the autoshrink option.
Thanks
|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegrou ps.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegro ups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||That was it. Missed the autoshrink option.
Thanks
|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegrou ps.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database growth and Replication
We have two database instances replicating between them.
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
We are currently trying to manually grow the database files, when we
do they are reverting back to the original size.
Using Enterprise Manager we enter the properties and insert the new
file size. When we come out SQL pauses and when the screen refreshes
shows the new size.
If we exit enterprise manager and then go back in the original size is
being used.
We have tried ammending the publisher then the subscriber and vice
versa.
Do we need to stop merge replication prior to a database file growth?
Thanks
Graz"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178020439.331730.41150@.l77g2000hsb.googlegroups.com...
> We have two database instances replicating between them.
> We are currently trying to manually grow the database files, when we
> do they are reverting back to the original size.
> Using Enterprise Manager we enter the properties and insert the new
> file size. When we come out SQL pauses and when the screen refreshes
> shows the new size.
> If we exit enterprise manager and then go back in the original size is
> being used.
> We have tried ammending the publisher then the subscriber and vice
> versa.
> Do we need to stop merge replication prior to a database file growth?
>
Not that I'm aware.
But it almost always sounds like you have auto-shrink enabled.
> Thanks
> Graz
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||That was it. Missed the autoshrink option.
Thanks|||"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1178110568.993714.26180@.o5g2000hsb.googlegroups.com...
> That was it. Missed the autoshrink option.
> Thanks
>
You're welcome.
And yet another reason to avoid auto-shrink ;-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Database Growth & Space Recovery Problem
I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Database Growth & Space Recovery Problem
I have done this experiment on one of the tables. There is table called
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql
build havinf nvText Field with large no of records. I want to drop that
column and recover space. These are the results I got.
SP_SPACEUSED BUILD Results
name rows reserved data index_size unused
1. Before Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
2. After Deleting nvText Field
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
3. After Executing the Shrink Database from Enterprise Manager.
Build 663211 608280 KB 604744 KB 3456 KB 80 K
4. After Executing DBCC DBReindex (build,'',70)
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink data
column as well as index_size shows an increased figure whereas logically
it should be a reduced figure.
Regards,
Harcharan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!One thing is that you should be suspicious of the numbers , as you are..
SP_SPaceused reports information from Sysindexes, but the sysindexes info is
NOT updated with everyinsert update or delete...So the numbers may not
reflect the actual size..
For each sp_spaceused in your test , execute like this
exec sp_spaceused 'build', true
The second parameter tells sql to read through the table and update the
estimates, it runs longer but you will get tru results...If the numbers
still look funny, we'll try something else...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harcharan Jassal" <hjjassal@.yahoo.com> wrote in message
news:uCtYWSBhEHA.3540@.TK2MSFTNGP10.phx.gbl...
> I have done this experiment on one of the tables. There is table called
> build havinf nvText Field with large no of records. I want to drop that
> column and recover space. These are the results I got.
> SP_SPACEUSED 'BUILD' Results
> name rows reserved data index_size unused
> 1. Before Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 2. After Deleting nvText Field
> Build 663211 341440 KB 339464 KB 1944 KB 32 KB
> 3. After Executing the Shrink Database from Enterprise Manager.
> Build 663211 608280 KB 604744 KB 3456 KB 80 K
> 4. After Executing DBCC DBReindex (build,'',70)
> Build 663211 124096 KB 123392 KB 712 KB -8 KB
> Can anyone please explain me after executing step 3 i.e shrink data
> column as well as index_size shows an increased figure whereas logically
> it should be a reduced figure.
> Regards,
> Harcharan
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
I have never receive solution.
Thanks
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Check for responses to your duplicate post in the Transact-SQL forum.
Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).
I have never receive solution.
Thanks
Database Growth
I would like to know followings:
I want to see every day or weekly Database growth (%) save on table
I have some SP which will give me one time run and see the growth. which is ...
Please advice any other way to find out and save on a location ...
create procedure sp_growth as
set ansi_warnings off
declare @.l_db_name varchar(50)
,@.l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
drop table DB_Growth
create table DB_Growth (Database_Name varchar(30), Logical_File_Name varchar(15), File_Size_MB int, Growth_Factor varchar(100))
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@.l_db_name
While (@.@.fetch_status = 0)
begin
select @.l_sql_string = 'select ' + '''' + @.l_db_name + '''' + ', name, ceiling((size * 8192.0)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192.0)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from [' + @.l_db_name + '].dbo.sysfiles'
insert into DB_Growth (Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@.l_sql_string)
fetch next from db_name_cursor into
@.l_db_name
end
close db_name_cursor
deallocate db_name_cursor
select * from DB_Growth with (nolock)
if object_id('DB_Growth') is not null
drop table DB_Growth
set nocount off
set ansi_warnings on
return
GO
Thanks
Faiz Farazi
Daudkandi,Comilla, Bangladesh
http://www.databasetimes.net/
Hi Faiz,
You could use the following. Used in SQL 2000 and I use it for SQL 2005.
Code Snippet
use database_name
go
dbcc showfilestats
go
For log file sizes:
Code Snippet
dbcc SQLperf(logspace)
go
more over sysfiles, only shows the file size allocated, not being used.
regards
Jag
Thursday, March 22, 2012
Database File size monitor
What are folks using to monitor database file sizes? I have been tasked
with writing a script to monitor our db's (Yes I know there are growth
controls, etc...). I was hoping there maybe a way to keep track of this via
some widget dashboard, etc... I have Solarwinds and will look at importing
SAN mibs, but still would like to hear thoughts from others.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
Instead of just displaying the values, store then in a table and then you'll
be able to see how fast you db is growing each day, month, minute, hour...or
whatever.
--
MG
"Paul Bergson [MVP-DS]" wrote:
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this via
> some widget dashboard, etc... I have Solarwinds and will look at importing
> SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table #FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT QUOTENAME(sdb.name)
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
1024. AS UnAllocExtInMB
,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
VARCHAR(3)) + '%' AS Pct_Full
FROM @.FileStats
ORDER BY TotalExtInMB DESC
--exec sp_spaceused
DBCC sqlperf(logspace)
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I use a custom script based around aggregating data from DBCC SHOWFILESTATS.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:F8CA1619-B919-45B9-9C76-75DF14160A66@.microsoft.com...
> Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
> Instead of just displaying the values, store then in a table and then
> you'll
> be able to see how fast you db is growing each day, month, minute,
> hour...or
> whatever.
> --
> MG
>
> "Paul Bergson [MVP-DS]" wrote:
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via
>> some widget dashboard, etc... I have Solarwinds and will look at
>> importing
>> SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay" <nospam@.nospam.org> wrote in message
news:uUlhoBZ8HHA.5012@.TK2MSFTNGP02.phx.gbl...
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE
> '#FileStats__%' ) drop table #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT QUOTENAME(sdb.name)
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
> 1024. AS UnAllocExtInMB
> ,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
> VARCHAR(3)) + '%' AS Pct_Full
> FROM @.FileStats
> ORDER BY TotalExtInMB DESC
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ON2b3EZ8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>I use a custom script based around aggregating data from DBCC
>SHOWFILESTATS.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>
with writing a script to monitor our db's (Yes I know there are growth
controls, etc...). I was hoping there maybe a way to keep track of this via
some widget dashboard, etc... I have Solarwinds and will look at importing
SAN mibs, but still would like to hear thoughts from others.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
Instead of just displaying the values, store then in a table and then you'll
be able to see how fast you db is growing each day, month, minute, hour...or
whatever.
--
MG
"Paul Bergson [MVP-DS]" wrote:
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this via
> some widget dashboard, etc... I have Solarwinds and will look at importing
> SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table #FileStats
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE cDatabases CURSOR FOR
SELECT QUOTENAME(sdb.name)
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats SELECT * FROM #FileStats
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
1024. AS UnAllocExtInMB
,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
VARCHAR(3)) + '%' AS Pct_Full
FROM @.FileStats
ORDER BY TotalExtInMB DESC
--exec sp_spaceused
DBCC sqlperf(logspace)
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||I use a custom script based around aggregating data from DBCC SHOWFILESTATS.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
> What are folks using to monitor database file sizes? I have been tasked
> with writing a script to monitor our db's (Yes I know there are growth
> controls, etc...). I was hoping there maybe a way to keep track of this
> via some widget dashboard, etc... I have Solarwinds and will look at
> importing SAN mibs, but still would like to hear thoughts from others.
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:F8CA1619-B919-45B9-9C76-75DF14160A66@.microsoft.com...
> Take a look at sp_helpdb. It'll show you how the db sizes are calculated.
> Instead of just displaying the values, store then in a table and then
> you'll
> be able to see how fast you db is growing each day, month, minute,
> hour...or
> whatever.
> --
> MG
>
> "Paul Bergson [MVP-DS]" wrote:
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via
>> some widget dashboard, etc... I have Solarwinds and will look at
>> importing
>> SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jay" <nospam@.nospam.org> wrote in message
news:uUlhoBZ8HHA.5012@.TK2MSFTNGP02.phx.gbl...
> DECLARE @.DB sysname
> DECLARE @.SQL nvarchar(255)
> if exists ( select * from tempdb..sysobjects where name LIKE
> '#FileStats__%' ) drop table #FileStats
> CREATE TABLE #FileStats(
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE @.FileStats TABLE (
> [FileId] INT,
> [FileGroup] INT,
> [TotalExtents] INT,
> [UsedExtents] INT,
> [Name] sysname,
> [Filename] varchar(255)
> )
> DECLARE cDatabases CURSOR FOR
> SELECT QUOTENAME(sdb.name)
> FROM master.dbo.sysdatabases sdb
> WHERE status & 32 != 32
> AND status & 64 != 64
> AND status & 128 != 128
> AND status & 256 != 256
> AND status & 512 != 512
> AND status & 1024 != 1024
> AND status & 4096 != 4096
> AND status & 32768 !=32768
> OPEN cDatabases
> FETCH FROM cDatabases INTO @.DB
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> DELETE FROM #FileStats
> SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
> SHOWFILESTATS'')'
> EXEC (@.SQL)
> UPDATE #FileStats SET name = @.DB
> INSERT INTO @.FileStats SELECT * FROM #FileStats
> FETCH FROM cDatabases INTO @.DB
> END
> CLOSE cDatabases
> DEALLOCATE cDatabases
> SELECT
> [Name]
> ,[TotalExtents]*64/1024. AS TotalExtInMB
> ,[UsedExtents]*64/1024. AS UsedExtInMB
> ,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 /
> 1024. AS UnAllocExtInMB
> ,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS
> VARCHAR(3)) + '%' AS Pct_Full
> FROM @.FileStats
> ORDER BY TotalExtInMB DESC
> --exec sp_spaceused
> DBCC sqlperf(logspace)
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Thanks for your feedback, it is appreciated.
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ON2b3EZ8HHA.5752@.TK2MSFTNGP04.phx.gbl...
>I use a custom script based around aggregating data from DBCC
>SHOWFILESTATS.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:uxH3fPY8HHA.2004@.TK2MSFTNGP06.phx.gbl...
>> What are folks using to monitor database file sizes? I have been tasked
>> with writing a script to monitor our db's (Yes I know there are growth
>> controls, etc...). I was hoping there maybe a way to keep track of this
>> via some widget dashboard, etc... I have Solarwinds and will look at
>> importing SAN mibs, but still would like to hear thoughts from others.
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>
Subscribe to:
Posts (Atom)