Re: How to determine space used by a file
From: Nags (nags_at_DontSpamMe.com)
Date: 04/19/04
- Next message: Jonte_at_bson.se: "No time for db maintenance"
- Previous message: Geoff N. Hiten: "Re: hyperthreaded processors running on win2k"
- Next in thread: Andrew J. Kelly: "Re: How to determine space used by a file"
- Reply: Andrew J. Kelly: "Re: How to determine space used by a file"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 19 Apr 2004 10:44:59 -0400
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
> > > >
> > > >
> > > >.
> > > >
> >
> >
>
>
- Next message: Jonte_at_bson.se: "No time for db maintenance"
- Previous message: Geoff N. Hiten: "Re: hyperthreaded processors running on win2k"
- Next in thread: Andrew J. Kelly: "Re: How to determine space used by a file"
- Reply: Andrew J. Kelly: "Re: How to determine space used by a file"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
