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
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
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
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
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
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
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
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
Jul 1, 2015
give me query or script to find autogrowth setting of all databases in instance
View 5 Replies
View Related
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
Jul 8, 2015
give me the best autogrowth & initial size setting for database
View 7 Replies
View Related
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