MS SQL Server - Disable autogrowth of file thru TSQL

Author

Disable autogrowth of file thru TSQL

Hassan

2005-04-30, 3:23 am

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

Narayana Vyas Kondreddi

2005-04-30, 7:23 am

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
>
>

Jacco Schalkwijk

2005-04-30, 7:23 am

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
>
>

John Bell

2005-04-30, 1:23 pm

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
>
>

Sponsored Links