.mdf file does not grow.

Hi,
I'm using msde as the database server for my asp.net intranet
application. The application is running live and atleast 50 - 100
records get inserted everyday.
However I have been checking the .mdf file everyday for the last week
and surprisingly the file size is constant at 5,504 KB. and the log
file is 504 KB. I ran the sp_spaceused stored procedure which gave me
similar results, its shows database size as 6.13 MB but it has been
constant for last few days too.
Is this normal? Is the growth in the size of the data too minor to
reflect in the file size? Or am I missing something here? Its important
for me to monitor the growth in database size for maintenance purposes.
So any help on this topic will be appreciated.
Regards,
supzzz
Do those records inserted stay in the database? If yes, why worry the file
size?
SQL Server's database file/log size increase by predetermined size chunck (%
or MB, say 10%, or 10MB).
"supriya" <supzzz@.gmail.com> wrote in message
news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...
> Hi,
> I'm using msde as the database server for my asp.net intranet
> application. The application is running live and atleast 50 - 100
> records get inserted everyday.
> However I have been checking the .mdf file everyday for the last week
> and surprisingly the file size is constant at 5,504 KB. and the log
> file is 504 KB. I ran the sp_spaceused stored procedure which gave me
> similar results, its shows database size as 6.13 MB but it has been
> constant for last few days too.
> Is this normal? Is the growth in the size of the data too minor to
> reflect in the file size? Or am I missing something here? Its important
> for me to monitor the growth in database size for maintenance purposes.
> So any help on this topic will be appreciated.
> Regards,
> supzzz
>
|||A database file is different that a normal file system file. You create a
database file with a particular size and SQL Server allocates space within
the file for tables, rows, etc. This is more like a file-system partition
than a normal file. You create a partition on a disk and the OS allocates
space within the partition as the files require it. The partition itself
doesn't grow as file are added.
When a file system partition runs out of space, you can't put any more files
into it. Many database work this way - when they run out of their allocated
space, insert operations start failing because there is no place to put the
new rows. SQL Server has an option that will automatically expand the size
of the database when it runs out of room. Expanding the database is a
pretty expensive operation so instead of growing just enough to make room
for the row you are adding, it grows enough to make room for a number of
rows. You can set how much it grows each time it runs out of space.
Just as a Windows partition doesn't shrink when you remove files, a database
file doesn't shrink when you delete rows. SQL Server assumes that you are
going to add more rows so it just keeps the space around so it doesn't have
to grow when you add more data.
You can see this happening if you watch the mdf file size over a period of
time. It won't grow for a while and then the size will jump by a
significant amount and then it won't grow for a while again. You can delete
all the tables in the database and the mdf file size won't shrink. If you
really need the disk space and you know you aren't going to add more data to
the database, you can shrink the file but in general, it's best to just
leave the file at its current size because you'll probably need the space
eventually.
When SQL Server runs out of space when trying to insert data, the insert
command is suspended while the database file size is increased. It's
possible for this to take a minute or more so the program that is doing the
insert will appear to hang. This isn't a good user experience so a lot of
DBA's monitor the free space and manually increase the database size when it
is getting low. This is more work for the DBA but it makes the user's
experience better (and also prevents the call to the DBA in the middle of
the night because an application appears to be hung while SQL Server is
growing the database file).
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"supriya" <supzzz@.gmail.com> wrote in message
news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...
> Hi,
> I'm using msde as the database server for my asp.net intranet
> application. The application is running live and atleast 50 - 100
> records get inserted everyday.
> However I have been checking the .mdf file everyday for the last week
> and surprisingly the file size is constant at 5,504 KB. and the log
> file is 504 KB. I ran the sp_spaceused stored procedure which gave me
> similar results, its shows database size as 6.13 MB but it has been
> constant for last few days too.
> Is this normal? Is the growth in the size of the data too minor to
> reflect in the file size? Or am I missing something here? Its important
> for me to monitor the growth in database size for maintenance purposes.
> So any help on this topic will be appreciated.
> Regards,
> supzzz
>
|||Thank You for your replies. Roger, your reply was particularly
insightful.
Ok so I guess there is not much reason to sweat. I just wanted to
monitor to activity on the database in order to estimate how soon we'll
have to switch to SQL Server 2000 (thanks to the 2GB limit) and plan
the purge procedure accordingly!!
BTW, while we are at it... is there a way to check the database
properties from osql? E.g. grow database automatically, grow log
automatically etc.
Thanks & Regards,
supzz
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> A database file is different that a normal file system file. You create a
> database file with a particular size and SQL Server allocates space within
> the file for tables, rows, etc. This is more like a file-system partition
> than a normal file. You create a partition on a disk and the OS allocates
> space within the partition as the files require it. The partition itself
> doesn't grow as file are added.
> When a file system partition runs out of space, you can't put any more files
> into it. Many database work this way - when they run out of their allocated
> space, insert operations start failing because there is no place to put the
> new rows. SQL Server has an option that will automatically expand the size
> of the database when it runs out of room. Expanding the database is a
> pretty expensive operation so instead of growing just enough to make room
> for the row you are adding, it grows enough to make room for a number of
> rows. You can set how much it grows each time it runs out of space.
> Just as a Windows partition doesn't shrink when you remove files, a database
> file doesn't shrink when you delete rows. SQL Server assumes that you are
> going to add more rows so it just keeps the space around so it doesn't have
> to grow when you add more data.
> You can see this happening if you watch the mdf file size over a period of
> time. It won't grow for a while and then the size will jump by a
> significant amount and then it won't grow for a while again. You can delete
> all the tables in the database and the mdf file size won't shrink. If you
> really need the disk space and you know you aren't going to add more data to
> the database, you can shrink the file but in general, it's best to just
> leave the file at its current size because you'll probably need the space
> eventually.
> When SQL Server runs out of space when trying to insert data, the insert
> command is suspended while the database file size is increased. It's
> possible for this to take a minute or more so the program that is doing the
> insert will appear to hang. This isn't a good user experience so a lot of
> DBA's monitor the free space and manually increase the database size when it
> is getting low. This is more work for the DBA but it makes the user's
> experience better (and also prevents the call to the DBA in the middle of
> the night because an application appears to be hung while SQL Server is
> growing the database file).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "supriya" <supzzz@.gmail.com> wrote in message
> news:1162460873.660306.113530@.b28g2000cwb.googlegr oups.com...