How To Set Autogrowth Off / On (T-SQL)

Oct 2, 2007

I am testing a msde 2000 sp4 database. (would like to use it against sqlserver 2005 later)
I need to turn the property for autogrowth on and check that an application alterts the user that it is on

and then off and verify the user is altered that it was set off.

I am not sure if this will work for mydb and mydblog
--turn off autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 0MB)
GO

--turn on autogrowth
USE master
GO
ALTER DATABASE mydb
MODIFY FILE
(NAME = mydb,
FILEGROWTH = 10MB)
GO

USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 0MB)
GO

--turn on autogrowth
USE master
GO
ALTER DATABASE mydblog
MODIFY FILE
(NAME = mydblog,
FILEGROWTH = 10MB)
GO


Also I need to know the t-sql for setting unrestricted growth on and off but I can not seem to find that.

Any help would be great.

We can not use enterprise manager so I'm planning to use osql thru a cmd prompt.

thanks
ced

View 2 Replies


ADVERTISEMENT

Autogrowth For MDF And LDF Files

Nov 29, 2007

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,

Carlos

View 3 Replies View Related

What Should I Set The Autogrowth Size To?

Jul 13, 2007

Hi Guys,



Should I keep all my databases to have a standard growth size for the data and log files? What would be good practice?



Also should be be shrinking the log file every so often?



Regards,



Af

View 4 Replies View Related

Autogrowth For MDF And LDF Files

Nov 29, 2007



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,


Carlos

View 1 Replies View Related

Autogrowth Values Keep Changing

Sep 21, 2006

Hi!

We have 2 dbs on our dw-server and the autogrowth values for both the data- and logfiles on both of these dbs changes about once a month. The data autogrowth value changes from 10megs to a percentage value between 3200 and 6400 and the log-file value changes from 10 percent to a percentage value between 3200 and 6400. Resulting in huge files and filling the drive.

What am I missing here?

BR John

View 17 Replies View Related

Can I Change The AutoGrowth Option?

Jul 13, 2007

Hi,



Is it possible to change the Autogrowth option of a database is none is set? I recieved an alert saying that one of the databases has 39.9% of freespace. Having checked the properties of the database, I noticed that the Autogrowth option had not been used.



The database is SQL 2005.



Thanks.



Af.

View 5 Replies View Related

Exponential Database Autogrowth

Dec 7, 2007

Whenever we restart the services on this one SQL server 2005 instance, the database autogrowth changes to grow by 2500%. We have to manually change the autogrowth of data file to some sane number. Has anyone faced this issue? We tried to put SP2 on but even that doesn't help. Any help would be appreciated

View 1 Replies View Related

SQL 2012 :: How To Find Autogrowth OFF In DMVs

Feb 25, 2015

So I need to write a script to look for available free space percentage in my databases, but I only want it to look at capped files. We consider a file with autogrowth off as capped for our purposes.

This is my problem: in sys.database_files and sys.master_files, if I have autogrowth off then max_size is -1, which is the same value as unlimited growth.

I cannot find another setting anywhere to determine how SQL Server recognizes that a particular file is set to not allow autogrowth. Any setting in a DMV anywhere where I can see whether autogrowth is disabled or not?

View 2 Replies View Related

Find Autogrowth Setting Of All Databases

Jul 1, 2015

give me query or script to find autogrowth setting of all databases in instance

View 5 Replies View Related

Bug: Data File Autogrowth Set To 12800%

May 11, 2006

SQL2K5 SP1the autogrowth setting in one of my database's primary filegroup datafile keeps having the value of 12800%, which was originally set to100MB, everytime the service is restarted. the same occurs whenever irestore a backup of this database in our development environment.WTF? this issue does not happen to other filegroups. only on theprimary data file. whenever this happens the 4GB data file grows tomore than 70GB (even the math is incorrect) with about 95% of UNUSEDspace.has anyone else come accross this BS or anyone knows how to preventthis from happening? is MS aware of this not-so-funny joke?thanks

View 2 Replies View Related

Autogrowth And Initial Size Setting For Database

Jul 8, 2015

give me the best autogrowth & initial size setting for database

View 7 Replies View Related

DB Engine :: DB Autogrowth Turned On In Secondary Data File Still 0% Internal Free Space

Oct 30, 2015

Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below

name    fileid    filename    filegroup    size    maxsize    growth    usage
XX    1    I:DataMSSQL.1MSSQLDataNew XX.mdf    PRIMARY    68140032 KB    Unlimited    0 KB    data only
XX_log    2    I:DataMSSQL.1MSSQLDataNew XX_log.LDF    NULL    1050112 KB    2147483648 KB    102400 KB    log only
XX_2    3    I:DataMSSQL.1MSSQLDataNew XX_2.ndf    PRIMARY    15458304 KB    Unlimited    0 KB    data only
XX_3    4    I:DataMSSQL.1MSSQLDataNew XX_3.ndf    PRIMARY    13186048 KB    Unlimited    0 KB    data only
XX_4    5    I:DataMSSQL.1MSSQLDataNew XX_4.ndf    PRIMARY    19570688 KB    Unlimited    204800 KB    data only
XX_5    6    I:DataMSSQL.1MSSQLDataNew XX_5.ndf    PRIMARY    19591168 KB    Unlimited    204800 KB    data only

2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.

What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its  other files.

What more data i need to ensure i submit an analysis to this.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved