Disable autogrowth of file thru TSQL

Is there a way to programatically disable autogrowth of all database files
residing on a particular drive ? Can someone help ?
Even if I cant do it for all database files in one shot, how can I do them
for individual databases ? Using SQL 2K
Have you tried the ALTER DATABASE...MODIFY FILE command?
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>
|||The basic syntax for disabling autogrowth on a database file is:
ALTER DATABASE <dbname> MODIFY FILE (NAME = <logical file name>, FILEGROWTH
= 0)
You can do that for all database files with the following script:
DECLARE @.dbname SYSNAME
DECLARE @.filename SYSNAME
CREATE TABLE #dbfiles (dbname sysname NOT NULL, filenm nchar(128))
DECLARE dbs CURSOR FAST_FORWARD
FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
-- No messing around with system databases
OPEN dbs
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbs INTO @.dbname
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('INSERT INTO #dbfiles(dbname, filenm)
SELECT ''' + @.dbname + ''', name FROM ' + @.dbname + '..sysfiles
WHERE growth > 0
AND filename LIKE ''C:\%''')
END
CLOSE dbs
DEALLOCATE dbs
SELECT * FROM #dbfiles
DECLARE dbfiles CURSOR FAST_FORWARD FOR
SELECT dbname, filenm FROM #dbfiles
OPEN dbfiles
WHILE 1 = 1
BEGIN
FETCH NEXT FROM dbfiles INTO @.dbname, @.filename
IF @.@.FETCH_STATUS <> 0 BREAK
EXEC ('ALTER DATABASE ' + @.dbname + ' MODIFY FILE (NAME = '
+ @.filename + ', FILEGROWTH = 0)')
END
CLOSE dbfiles
DEALLOCATE dbfiles
DROP TABLE #dbfiles
GO
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>
|||Hi
You can use the alter database command on each database. sp_MSForEachDB will
allow you to perform the code for each database, but as this is undocumented
it should not be used in production code, alternatively you can use a cursor
to get the databases from master..sysdatabases. sp_helpfile will give you
where the files are located.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OP7kEkUTFHA.612@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically disable autogrowth of all database files
> residing on a particular drive ? Can someone help ?
> Even if I cant do it for all database files in one shot, how can I do them
> for individual databases ? Using SQL 2K
>