Autogrowth for MDF and LDF files

I have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

CarlosI have a SQL 2005 DB that its MDF file is growing at a rate of 1 GB per day, I currently have it set up to unrestricted growth by 500 MB. Should I increase that growth to 1 GB? what would the impact of this change be? what are best practices when it comes to setting up autogrowth for MDF and LDF files?

Thanks,

Carlos

I generally allow dbs that are smaller that 20 GB to autogrow by the default setting (10%). But once they get above that size, I manage them manually and ensure that there is enough empty space in the datafile to get through until the next maintenance window.

Growing a data file in SQL 2005 is not as expensive (IO wise) as it was in SQL 2000, but I still think you want to keep a closer eye on things once they get above 20 GB. 20 GB is admittedly arbitrary. If you have space issues, you might consider a lower threshold.

Regards,

hmscott|||It depends on what your database is used for. Mine are configured to grow by several hundred MB\ a few GB but that is because there are small numbers of massive modifications. An OLTP database should not, IMHO, be growing that much each time. The user that submitted the modification that triggers a 500MB growth could be twiddling their thumbs for quite some time cursing the system as they do.

I too would manage the growth at peak periods with a view to eliminating\ reducing autogrowth as much as possible.|||Your file growth is because of your 500MB setting. When SQL starts to run out of space it will adjust by 500MB. If has to adjust twice a day, there's your 1GB. I think you're fine where you're at. Just make sure you have enough drive space. I'm sure your growth will plateau.

auto number?

Hi, I define a field as auto number field, usually how people deal with if
data is growing near 2147483647? Thanks.The simplest solution is to change the data type on the column to BigInt.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||Are you talking about access (--> autonumber) or SQl server (->identity)
Identities at sql serv can store up to
+-2^63-1 (9223372036854775807)
HTH, Jens SUessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
> Hi, I define a field as auto number field, usually how people deal with if
> data is growing near 2147483647? Thanks.
>
>|||is it any archive function avaliable?
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:uPOzm2NUFHA.3344@.TK2MSFTNGP10.phx.gbl...
> The simplest solution is to change the data type on the column to BigInt.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||That's big...
So I can just design it and forget it, assume not problem at all?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Are you talking about access (--> autonumber) or SQl server (->identity)
> Identities at sql serv can store up to
> +-2^63-1 (9223372036854775807)
> HTH, Jens SUessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:%23BzLMyNUFHA.2096@.TK2MSFTNGP14.phx.gbl...
>|||As far as you wont reach 9223372036854775807 and your client app can handle
that, no.
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> That's big...
> So I can just design it and forget it, assume not problem at all?
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eE%23uB3NUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>|||I'm think of archive the data and reset the seed? what other people handle
that? Thanks.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> As far as you wont reach 9223372036854775807 and your client app can
> handle that, no.
> Jens Suessmeyer.
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:e1MXhCOUFHA.3140@.TK2MSFTNGP14.phx.gbl...|||I would not recommend that solution. I would instead recommend using a BigIn
t
for the data type of your identity column. If that table gets big, then by a
ll
means archive some of the data into a different table. But I would not chang
e
the identity values nor the seed when I archived the data.
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote i
n
> message news:ebWIfGOUFHA.1796@.TK2MSFTNGP15.phx.gbl...
>|||> I'm think of archive the data and reset the seed? what other people handle
> that? Thanks.
What? How often do you plan on archiving? If you have three archives and
all have a row where idNumber = 1, which one is the one you're looking for?
If you are building a system where you really think you will need to reset
the IDENTITY value, perhaps you are going about this the wrong way
altogether.|||I agree with that now...
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%235ODlQOUFHA.3544@.TK2MSFTNGP12.phx.gbl...
>I would not recommend that solution. I would instead recommend using a
>BigInt for the data type of your identity column. If that table gets big,
>then by all means archive some of the data into a different table. But I
>would not change the identity values nor the seed when I archived the data.
>
> Thomas
> "js" <js@.someone@.hotmail.com> wrote in message
> news:%23C3l1JOUFHA.3532@.TK2MSFTNGP09.phx.gbl...
>

Auto grow enabled but DB wont grow

I've been through the forum and read a number of threads on people's DBs not growing and the answer usually is they don't have auotmatically grow data file. Unfortunately I have this on, but when I look at the properties of the database it reports the space available is 0.00 MB? Up until about two weeks ago I was showing appx 48% space utilization. When I ran an SP to show growth, it tells me that it was expanded by 20% yesterday, but SQL Server is still telling me the space available is zero.

The log file is also set for auto growth. The DB is 14.5 GB in size and the drives still have around 92 GB of space.

Has anyone experienced this before? Any ideas? Does anyone know of an SPs that can give me detailed info on internal data file size compared to stated size (i.e. wasted space in data file)? Is SQL Server doing something funny in the way it is seeing the database or data files individually? Any help is appreciated.Hi,
Have you tried DBCC SHRINKFILE? It can move the pages to beginning of the data file and freeing unused space.
Regards,
Leila|||Are you getting any messages in the errorlog? Are you loosing any data? My suspicion is that the database is functioning according to the parameters you've set, and that it is perfectly content.

The first thing I'd suggest that you check is the MS-SQL errorlog (either via Enterprise Mangler or by simply printing out the file). If there are no error messages there, I'd be really surprised if you've lost any data at all.

The next thing I'd check would be the NT Event log (either via "Manage my computer" or the Control Panel | Administrative Tools | Event Viewer). Look for the red icons for errors and the yellow ones for warnings... Other messages are just status information.

If those come up clean (no serious error messages), you haven't lost any data yet. At that point, you might want to think about performance issues which might exist, but integrity issues have to come first.

-PatP|||I had not run a DBCC SHRINKFILE yet as I was unsure about the validity of the DB and didn't want to cause more headaches than I have. My initial concern was not necessarily that it was not using its existing space effectively as much as it was why the data files weren't grwing when they should. I checked the logs at the time, and again just now; sorry I hadn't put that in the thread. Everything looks good as far as the NT event logs and the SQL logs, no errors and everything looks to be in order.

I will run the SHRINKFILE tonight and see how it goes. Pat, what kind of performance issues do you think could be involved in this?

Thanks to both of you, I'll keep you posted as to what I find.|||sp_spaceused will give a more detailed info on space utilization. And I don't see anything that can be posted in Windows event log that is not recorded by SQL Server error log that can indicate "a data loss." Pat is probably day-dreaming again ;)|||That sp_spaceused comes in pretty handy, thanks. I ended up running
DBCC UPDATEUSAGE and that resolved the problem. Still not sure why this occured, but I'm hoping this resolves it.

Thanks again for all of your help, it was very good to bounce this off others.|||SQL Server has historically been very bad about keeping size data in the system tables up to date. The DBCC UPDATEUSAGE(0) command checks these numbers against what is actually used, and makes corrections. Even so, I doubt it is worth setting up a job to run UPDATEUSAGE on a regular basis, so long aas you are not getting errors and such.