define Hierarchy: space

Showing posts with label space. Show all posts

Wednesday, March 28, 2012

How to determine the free space in a datafile

Hi,
I'm trying to figure out the amount of free space in the datafiles but
since sys.dm_db_file_space_usage works only for temp, I dont know which
table to join on sys.database_files or sys.master_files.
Can anyone help me please?Hi
Have you tried
exec sp_helpfile ?
<ulli77@.web.de> wrote in message
news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...
> Hi,
> I'm trying to figure out the amount of free space in the datafiles but
> since sys.dm_db_file_space_usage works only for temp, I dont know which
> table to join on sys.database_files or sys.master_files.
> Can anyone help me please?
>|||for data files look at
dbcc showfilestats
This is undocumented dbcc command.
It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.
For log files
look at
dbcc sqlperf(logspace)
Regards
Amish shah
http://shahamishm.tripod.com
*** Sent via Developersdex http://www.codecomments.com ***|||Uri Dimant schrieb:

> Hi
> Have you tried
> exec sp_helpfile ?
Yes, I have but it doesnt show any information about the usage.
thank you[vbcol=seagreen]
> <ulli77@.web.de> wrote in message
> news:1154260839.713320.283310@.b28g2000cwb.googlegroups.com...|||Amish Shah schrieb:

> for data files look at
> dbcc showfilestats
> This is undocumented dbcc command.
> It shows total size and used size in extents.
> If you multiply it by 64 you will get size in KB.
> For log files
> look at
> dbcc sqlperf(logspace)
>
> Regards
> Amish shah
> http://shahamishm.tripod.com
> *** Sent via Developersdex http://www.codecomments.com ***
Thank you Amish,
that really does the job but how do i get this information into a
table?
like:
select into table ( dbcc showfilestat )
thats what i would like for capacity planning and charting.
Thank you very much, Ulli|||Hi Ulli
You have to create your own table. By looking at the output of dbcc
showfilestats you can determine the number of columns and an appropriate
datatype.
Once you have the table created, you can do the following:
INSERT INT MyFileStatsTable
EXEC ('DBCC showfilestats')
HTH
Kalen Delaney, SQL Server MVP
<ulli77@.web.de> wrote in message
news:1154272978.472934.27330@.b28g2000cwb.googlegroups.com...
> Amish Shah schrieb:
>
>
> Thank you Amish,
> that really does the job but how do i get this information into a
> table?
> like:
> select into table ( dbcc showfilestat )
> thats what i would like for capacity planning and charting.
> Thank you very much, Ulli
>

How to determine the free space in a datafile

Monday, March 26, 2012

How to determine space used by a file

How do I determine the space used in a file ? We have a 4 Gig file pre
allocated. We want to be sure that there is enough space in that. We want
to run scripts to warn us if the space available in a file group is less
than one Gig. If it is less then 1 Gig we want to allocate another 4 Gig
file.
-NagsNags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay
>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>|||My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
> >--Original Message--
> >How do I determine the space used in a file ? We have a
> 4 Gig file pre
> >allocated. We want to be sure that there is enough space
> in that. We want
> >to run scripts to warn us if the space available in a
> file group is less
> >than one Gig. If it is less then 1 Gig we want to
> allocate another 4 Gig
> >file.
> >
> >-Nags
> >
> >
> >.
> >|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
> > Nags,
> >
> > You could automate all of this using 'Performance
> > Condition' Alerts.
> >
> > You can define a alert to be fired if the db size goes
> > above 3 GB (you have to put this number in KB's though).
> > As a response to the alert, you could setup a SQL Server
> > job to increase the database to appropriate size.
> >
> > The only catch here is that you will have to be creative
> > in automatically increasing the size by 4 GB. Usually the
> > jobs I creat jobs that have a fixed db size specified.
> >
> > You can also be notified when this alert fires and
> > increase db size in your script after the script has ran.
> > This will work the next time your db gets full. You will
> > also have to adjust the parameter for the db size (KB
> > value)
> >
> >
> > Hope this helps,
> >
> > DeeJay
> >
> > >--Original Message--
> > >How do I determine the space used in a file ? We have a
> > 4 Gig file pre
> > >allocated. We want to be sure that there is enough space
> > in that. We want
> > >to run scripts to warn us if the space available in a
> > file group is less
> > >than one Gig. If it is less then 1 Gig we want to
> > allocate another 4 Gig
> > >file.
> > >
> > >-Nags
> > >
> > >
> > >.
> > >
>|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> > My Database is about 60 Gig with several filegroups. My problem is not
to
> > preallocate too much and not to have space issues. We want to
preallocate
> > to avoid disk defragmentation. I need to find out how much space is
free
> in
> > a file, calculate the total free space in a file group and if it is <
than
> 1
> > gig, only then add the new file. I hope you understand my requirement.
> Its
> > not the db size I am interested, I need to know the amount on data in
each
> > file.
> >
> > -Nags
> >
> > "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> > news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
> > > Nags,
> > >
> > > You could automate all of this using 'Performance
> > > Condition' Alerts.
> > >
> > > You can define a alert to be fired if the db size goes
> > > above 3 GB (you have to put this number in KB's though).
> > > As a response to the alert, you could setup a SQL Server
> > > job to increase the database to appropriate size.
> > >
> > > The only catch here is that you will have to be creative
> > > in automatically increasing the size by 4 GB. Usually the
> > > jobs I creat jobs that have a fixed db size specified.
> > >
> > > You can also be notified when this alert fires and
> > > increase db size in your script after the script has ran.
> > > This will work the next time your db gets full. You will
> > > also have to adjust the parameter for the db size (KB
> > > value)
> > >
> > >
> > > Hope this helps,
> > >
> > > DeeJay
> > >
> > > >--Original Message--
> > > >How do I determine the space used in a file ? We have a
> > > 4 Gig file pre
> > > >allocated. We want to be sure that there is enough space
> > > in that. We want
> > > >to run scripts to warn us if the space available in a
> > > file group is less
> > > >than one Gig. If it is less then 1 Gig we want to
> > > allocate another 4 Gig
> > > >file.
> > > >
> > > >-Nags
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > Adding new files in response to low free space in the db is a very bad
> idea.
> > SQL Server uses a proportional fill algorithm that is based on the
amount
> of
> > free space in each file in the filegroup. The ideal situation would be
to
> > have multiple files all of the same size with the same amount of free
> space.
> > In your case if you added a new file it would put most of the new data
in
> > the new file and defeat some of the purpose of spreading the data over
> > multiple files. What you should do is manually (or script not autogrow)
> > grow each existing file so they all are always the same size.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nags" <nags@.DontSpamMe.com> wrote in message
> > news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> > > My Database is about 60 Gig with several filegroups. My problem is
not
> to
> > > preallocate too much and not to have space issues. We want to
> preallocate
> > > to avoid disk defragmentation. I need to find out how much space is
> free
> > in
> > > a file, calculate the total free space in a file group and if it is <
> than
> > 1
> > > gig, only then add the new file. I hope you understand my
requirement.
> > Its
> > > not the db size I am interested, I need to know the amount on data in
> each
> > > file.
> > >
> > > -Nags
> > >
> > > "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> > > news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
> > > > Nags,
> > > >
> > > > You could automate all of this using 'Performance
> > > > Condition' Alerts.
> > > >
> > > > You can define a alert to be fired if the db size goes
> > > > above 3 GB (you have to put this number in KB's though).
> > > > As a response to the alert, you could setup a SQL Server
> > > > job to increase the database to appropriate size.
> > > >
> > > > The only catch here is that you will have to be creative
> > > > in automatically increasing the size by 4 GB. Usually the
> > > > jobs I creat jobs that have a fixed db size specified.
> > > >
> > > > You can also be notified when this alert fires and
> > > > increase db size in your script after the script has ran.
> > > > This will work the next time your db gets full. You will
> > > > also have to adjust the parameter for the db size (KB
> > > > value)
> > > >
> > > >
> > > > Hope this helps,
> > > >
> > > > DeeJay
> > > >
> > > > >--Original Message--
> > > > >How do I determine the space used in a file ? We have a
> > > > 4 Gig file pre
> > > > >allocated. We want to be sure that there is enough space
> > > > in that. We want
> > > > >to run scripts to warn us if the space available in a
> > > > file group is less
> > > > >than one Gig. If it is less then 1 Gig we want to
> > > > allocate another 4 Gig
> > > > >file.
> > > > >
> > > > >-Nags
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> > Let me explain on how we are managing our disk space and file groups
more
> > clearly.
> >
> > What we had before
> > - we have at present taken size estimations of all our current tables
and
> > our database size.
> > - production database is increasing by about 50 % every year based on 3
> year
> > data
> > - we had disk contention
> > - we had performance issues because of thisly made more work athn
> >
> > What we did to avoid it
> > - we have 3 major tables which we have put them in individual file group
> and
> > their indexes in individual file group
> > - we moved all transactions tables (only inserts) into a separate file
> group
> > and their indexes into another file group
> > - we moved all major accessed tables into a separate file group and
their
> > indexes into another file group
> > - we moved all other tables into a separate file group and their indexes
> > into another file group
> > - we distributed these file groups onto 4 disk volumes, two volumes are
3
> > disk raid 5 and 2 volumes are disk mirrored.
> > - each file group has been allocated twice the space required
> > - each file has been created with 4 Gig
> > - file groups have been created so that we can move individual file
groups
> > to different disk sets when volume grows and we need better performance
> > - each file has been created with 4 Gig so that disk space is
preallocated
> > and does not expand as required, which might cause disk defragmentation
> > - we are collecting statistics periodically to find out which disk drive
> has
> > contention, and if we find it, we will be putting new volumes and moving
> the
> > file groups on to them
> > - each file group is made of multiple files
> >
> > The requirement is to find out if any of the file group is running out
of
> > space. In short I want to determine how much is being used and how much
> > space is free within a file. Can you give me this info please ?
> >
> >
> > -Nags
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> > > Adding new files in response to low free space in the db is a very bad
> > idea.
> > > SQL Server uses a proportional fill algorithm that is based on the
> amount
> > of
> > > free space in each file in the filegroup. The ideal situation would
be
> to
> > > have multiple files all of the same size with the same amount of free
> > space.
> > > In your case if you added a new file it would put most of the new data
> in
> > > the new file and defeat some of the purpose of spreading the data over
> > > multiple files. What you should do is manually (or script not
autogrow)
> > > grow each existing file so they all are always the same size.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> > > > My Database is about 60 Gig with several filegroups. My problem is
> not
> > to
> > > > preallocate too much and not to have space issues. We want to
> > preallocate
> > > > to avoid disk defragmentation. I need to find out how much space is
> > free
> > > in
> > > > a file, calculate the total free space in a file group and if it is
<
> > than
> > > 1
> > > > gig, only then add the new file. I hope you understand my
> requirement.
> > > Its
> > > > not the db size I am interested, I need to know the amount on data
in
> > each
> > > > file.
> > > >
> > > > -Nags
> > > >
> > > > "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> > > > news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
> > > > > Nags,
> > > > >
> > > > > You could automate all of this using 'Performance
> > > > > Condition' Alerts.
> > > > >
> > > > > You can define a alert to be fired if the db size goes
> > > > > above 3 GB (you have to put this number in KB's though).
> > > > > As a response to the alert, you could setup a SQL Server
> > > > > job to increase the database to appropriate size.
> > > > >
> > > > > The only catch here is that you will have to be creative
> > > > > in automatically increasing the size by 4 GB. Usually the
> > > > > jobs I creat jobs that have a fixed db size specified.
> > > > >
> > > > > You can also be notified when this alert fires and
> > > > > increase db size in your script after the script has ran.
> > > > > This will work the next time your db gets full. You will
> > > > > also have to adjust the parameter for the db size (KB
> > > > > value)
> > > > >
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > DeeJay
> > > > >
> > > > > >--Original Message--
> > > > > >How do I determine the space used in a file ? We have a
> > > > > 4 Gig file pre
> > > > > >allocated. We want to be sure that there is enough space
> > > > > in that. We want
> > > > > >to run scripts to warn us if the space available in a
> > > > > file group is less
> > > > > >than one Gig. If it is less then 1 Gig we want to
> > > > > allocate another 4 Gig
> > > > > >file.
> > > > > >
> > > > > >-Nags
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
>
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each[vbcol=seagreen]
>|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.[vbcol=seagreen]
> each
>|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in[vbcol=seagreen]
>

How to determine space used by a file

Nags,
You could automate all of this using 'Performance
Condition' Alerts.
You can define a alert to be fired if the db size goes
above 3 GB (you have to put this number in KB's though).
As a response to the alert, you could setup a SQL Server
job to increase the database to appropriate size.
The only catch here is that you will have to be creative
in automatically increasing the size by 4 GB. Usually the
jobs I creat jobs that have a fixed db size specified.
You can also be notified when this alert fires and
increase db size in your script after the script has ran.
This will work the next time your db gets full. You will
also have to adjust the parameter for the db size (KB
value)
Hope this helps,
DeeJay

>--Original Message--
>How do I determine the space used in a file ? We have a
4 Gig file pre
>allocated. We want to be sure that there is enough space
in that. We want
>to run scripts to warn us if the space available in a
file group is less
>than one Gig. If it is less then 1 Gig we want to
allocate another 4 Gig
>file.
>-Nags
>
>.
>
My Database is about 60 Gig with several filegroups. My problem is not to
preallocate too much and not to have space issues. We want to preallocate
to avoid disk defragmentation. I need to find out how much space is free in
a file, calculate the total free space in a file group and if it is < than 1
gig, only then add the new file. I hope you understand my requirement. Its
not the db size I am interested, I need to know the amount on data in each
file.
-Nags
"DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...[vbcol=seagreen]
> Nags,
> You could automate all of this using 'Performance
> Condition' Alerts.
> You can define a alert to be fired if the db size goes
> above 3 GB (you have to put this number in KB's though).
> As a response to the alert, you could setup a SQL Server
> job to increase the database to appropriate size.
> The only catch here is that you will have to be creative
> in automatically increasing the size by 4 GB. Usually the
> jobs I creat jobs that have a fixed db size specified.
> You can also be notified when this alert fires and
> increase db size in your script after the script has ran.
> This will work the next time your db gets full. You will
> also have to adjust the parameter for the db size (KB
> value)
>
> Hope this helps,
> DeeJay
> 4 Gig file pre
> in that. We want
> file group is less
> allocate another 4 Gig
|||Adding new files in response to low free space in the db is a very bad idea.
SQL Server uses a proportional fill algorithm that is based on the amount of
free space in each file in the filegroup. The ideal situation would be to
have multiple files all of the same size with the same amount of free space.
In your case if you added a new file it would put most of the new data in
the new file and defeat some of the purpose of spreading the data over
multiple files. What you should do is manually (or script not autogrow)
grow each existing file so they all are always the same size.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
> My Database is about 60 Gig with several filegroups. My problem is not to
> preallocate too much and not to have space issues. We want to preallocate
> to avoid disk defragmentation. I need to find out how much space is free
in
> a file, calculate the total free space in a file group and if it is < than
1
> gig, only then add the new file. I hope you understand my requirement.
Its
> not the db size I am interested, I need to know the amount on data in each
> file.
> -Nags
> "DeeJay Puar" <deejaypuar@.yahoo.com> wrote in message
> news:00ae01c423f4$cf7dff70$a101280a@.phx.gbl...
>
|||Let me explain on how we are managing our disk space and file groups more
clearly.
What we had before
- we have at present taken size estimations of all our current tables and
our database size.
- production database is increasing by about 50 % every year based on 3 year
data
- we had disk contention
- we had performance issues because of this
What we did to avoid it
- we have 3 major tables which we have put them in individual file group and
their indexes in individual file group
- we moved all transactions tables (only inserts) into a separate file group
and their indexes into another file group
- we moved all major accessed tables into a separate file group and their
indexes into another file group
- we moved all other tables into a separate file group and their indexes
into another file group
- we distributed these file groups onto 4 disk volumes, two volumes are 3
disk raid 5 and 2 volumes are disk mirrored.
- each file group has been allocated twice the space required
- each file has been created with 4 Gig
- file groups have been created so that we can move individual file groups
to different disk sets when volume grows and we need better performance
- each file has been created with 4 Gig so that disk space is preallocated
and does not expand as required, which might cause disk defragmentation
- we are collecting statistics periodically to find out which disk drive has
contention, and if we find it, we will be putting new volumes and moving the
file groups on to them
- each file group is made of multiple files
The requirement is to find out if any of the file group is running out of
space. In short I want to determine how much is being used and how much
space is free within a file. Can you give me this info please ?
-Nags
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> Adding new files in response to low free space in the db is a very bad
idea.
> SQL Server uses a proportional fill algorithm that is based on the amount
of
> free space in each file in the filegroup. The ideal situation would be to
> have multiple files all of the same size with the same amount of free
space.[vbcol=seagreen]
> In your case if you added a new file it would put most of the new data in
> the new file and defeat some of the purpose of spreading the data over
> multiple files. What you should do is manually (or script not autogrow)
> grow each existing file so they all are always the same size.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:unOwBl$IEHA.3120@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
preallocate[vbcol=seagreen]
free[vbcol=seagreen]
> in
than[vbcol=seagreen]
> 1
> Its
each
>
|||A 3 disk RAID 5 is about the worst performing RAID there is. You have
probably made much more work than necessary and certainly do not have an
optimum setup. I would venture to guess you could take those 10 disks and
make a RAID 10 with them instead. Then you could place all the files on the
RAID 10 and would probably get better performance than spreading it out like
you have. This is assuming you already have the logs on their own RAID 1.
If your getting a lot of disk contention you might also ensure you have
enough ram as well.
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
> Let me explain on how we are managing our disk space and file groups more
> clearly.
> What we had before
> - we have at present taken size estimations of all our current tables and
> our database size.
> - production database is increasing by about 50 % every year based on 3
year
> data
> - we had disk contention
> - we had performance issues because of thisly made more work athn
> What we did to avoid it
> - we have 3 major tables which we have put them in individual file group
and
> their indexes in individual file group
> - we moved all transactions tables (only inserts) into a separate file
group
> and their indexes into another file group
> - we moved all major accessed tables into a separate file group and their
> indexes into another file group
> - we moved all other tables into a separate file group and their indexes
> into another file group
> - we distributed these file groups onto 4 disk volumes, two volumes are 3
> disk raid 5 and 2 volumes are disk mirrored.
> - each file group has been allocated twice the space required
> - each file has been created with 4 Gig
> - file groups have been created so that we can move individual file groups
> to different disk sets when volume grows and we need better performance
> - each file has been created with 4 Gig so that disk space is preallocated
> and does not expand as required, which might cause disk defragmentation
> - we are collecting statistics periodically to find out which disk drive
has
> contention, and if we find it, we will be putting new volumes and moving
the[vbcol=seagreen]
> file groups on to them
> - each file group is made of multiple files
> The requirement is to find out if any of the file group is running out of
> space. In short I want to determine how much is being used and how much
> space is free within a file. Can you give me this info please ?
>
> -Nags
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:#R35MOAJEHA.1144@.TK2MSFTNGP10.phx.gbl...
> idea.
amount[vbcol=seagreen]
> of
to[vbcol=seagreen]
> space.
in[vbcol=seagreen]
not[vbcol=seagreen]
> to
> preallocate
> free
> than
requirement.
> each
>
|||Nags
>- we have 3 major tables which we have put them in >individual file group
and
>their indexes in individual file group
Do you have separate filegroups for the tables and their indexes?
If they are in separate filegroups, then you cannot back up and restore them
as a single unit
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eLYz5gnJEHA.3688@.TK2MSFTNGP10.phx.gbl...
> A 3 disk RAID 5 is about the worst performing RAID there is. You have
> probably made much more work than necessary and certainly do not have an
> optimum setup. I would venture to guess you could take those 10 disks and
> make a RAID 10 with them instead. Then you could place all the files on
the
> RAID 10 and would probably get better performance than spreading it out
like[vbcol=seagreen]
> you have. This is assuming you already have the logs on their own RAID 1.
> If your getting a lot of disk contention you might also ensure you have
> enough ram as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23tGJozhJEHA.3216@.tk2msftngp13.phx.gbl...
more[vbcol=seagreen]
and[vbcol=seagreen]
> year
> and
> group
their[vbcol=seagreen]
3[vbcol=seagreen]
groups[vbcol=seagreen]
preallocated[vbcol=seagreen]
> has
> the
of[vbcol=seagreen]
> amount
be[vbcol=seagreen]
> to
> in
autogrow)[vbcol=seagreen]
> not
<[vbcol=seagreen]
> requirement.
in
>

Wednesday, March 21, 2012

How to determine % of free space

Ok I can't seem to find an answer to this question. I have a NDF file in a
second file group and want to know how much space it used or how much space
is free. Can I do this? If so how? I've just got to be missing it.
Thanks,
PaulWThis isn't documented, but easy to use:
DBCC SHOWFILESTATS
For 2005, there are also a bunch of dynamic management views, and I assume that one of them can give
you the answer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul Widner" <paul_widner@.hotmail.com> wrote in message
news:uC3vnSltGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Ok I can't seem to find an answer to this question. I have a NDF file in a second file group and
> want to know how much space it used or how much space is free. Can I do this? If so how? I've
> just got to be missing it.
> Thanks,
> PaulW
>|||Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
extents, but not KB....
Paul Widner wrote:
> Ok I can't seem to find an answer to this question. I have a NDF file in a
> second file group and want to know how much space it used or how much space
> is free. Can I do this? If so how? I've just got to be missing it.
> Thanks,
> PaulW|||Sorry about that Tibor, I should have hit refresh! ;)
SGCSNA wrote:
> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> extents, but not KB....
>
> Paul Widner wrote:
> > Ok I can't seem to find an answer to this question. I have a NDF file in a
> > second file group and want to know how much space it used or how much space
> > is free. Can I do this? If so how? I've just got to be missing it.
> >
> > Thanks,
> > PaulW|||No problem. Happens to all of us, all of the time. Also, refresh might not help, as you might be
connected to a different news server than I am, and replication between the news server incurs a
delay. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SGCSNA" <sean.gorman@.computershare.com> wrote in message
news:1154540131.071946.269940@.m79g2000cwm.googlegroups.com...
> Sorry about that Tibor, I should have hit refresh! ;)
> SGCSNA wrote:
>> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
>> extents, but not KB....
>>
>> Paul Widner wrote:
>> > Ok I can't seem to find an answer to this question. I have a NDF file in a
>> > second file group and want to know how much space it used or how much space
>> > is free. Can I do this? If so how? I've just got to be missing it.
>> >
>> > Thanks,
>> > PaulW
>|||Are those extents in 8k pages?
Paul
"SGCSNA" <sean.gorman@.computershare.com> wrote in message
news:1154540044.752066.187980@.b28g2000cwb.googlegroups.com...
> Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> extents, but not KB....
>
> Paul Widner wrote:
>> Ok I can't seem to find an answer to this question. I have a NDF file in
>> a
>> second file group and want to know how much space it used or how much
>> space
>> is free. Can I do this? If so how? I've just got to be missing it.
>> Thanks,
>> PaulW
>|||An extent is 64K. A page is 8k or 8192 bytes. Pages go into extents.
Paul Widner wrote:
> Are those extents in 8k pages?
> Paul
> "SGCSNA" <sean.gorman@.computershare.com> wrote in message
> news:1154540044.752066.187980@.b28g2000cwb.googlegroups.com...
> > Maybe not what you wanted but - DBCC SHOWFILESTATS will show it in
> > extents, but not KB....
> >
> >
> > Paul Widner wrote:
> >> Ok I can't seem to find an answer to this question. I have a NDF file in
> >> a
> >> second file group and want to know how much space it used or how much
> >> space
> >> is free. Can I do this? If so how? I've just got to be missing it.
> >>
> >> Thanks,
> >> PaulW
> >

Wednesday, March 28, 2012

How to determine the free space in a datafile

How to determine the free space in a datafile

Monday, March 26, 2012

How to determine space used by a file

How to determine space used by a file

How to determine space used by a file

Wednesday, March 21, 2012

How to determine % of free space

How to determine % of free space

define Hierarchy

Blog Archive

About Me