How can I Check to see if all logs and databases are set to Auto Grow in SQL 2008 Server?

Try This:

--select * from sys.sysfiles  

declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + '
UNION ALL ','') + 

'SELECT CONVERT(varchar(100),
SERVERPROPERTY(''Servername'')) AS Server, ' + 
quotename(name,'''') +'  as DatabaseName,
    CAST(name as varchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS as name,
    CAST(filename as varchar(128)) COLLATE SQL_Latin1_General_CP1_CI_AS as FileName,
    Autogrowth = ''Autogrowth: ''
        +
        CASE
            WHEN (status & 0x100000 = 0 AND CEILING((growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR growth = 0 THEN ''None''
            WHEN status & 0x100000 = 0 THEN ''By '' + 
            CONVERT(VARCHAR,CEILING((growth * 8192.0) / (1024.0 * 1024.0))) + '' MB''
            ELSE ''By '' + CONVERT(VARCHAR,growth) + '' percent''
        END
        +
        CASE
            WHEN (status & 0x100000 = 0 AND CEILING((growth * 8192.0) / (1024.0 * 1024.0)) = 0.00) OR growth = 0 THEN ''''
            WHEN CAST([maxsize] * 8.0 / 1024 AS DEC(20,2)) <= 0.00 THEN '', unrestricted growth''
            ELSE '', restricted growth to '' + CAST(CAST([maxsize] * 8.0 / 1024 AS DEC(20)) AS VARCHAR) + '' MB''
        END
FROM '  + quotename(name) + '.sys.sysfiles  s'
from sys.databases

set @SQL = @SQL + ' 
ORDER BY DatabaseName'

print @SQL

execute(@SQL)

Select allOpen in new window

Refer: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/105c00c4-eae8-4875-b63c-8b953ee43e23/

check

SQL Server Database Growth and Autogrowth Settings
http://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/
and the same idea as above post:


SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
'MaximumSizeinMB' =
    CASE max_size
       WHEN 0 THEN 'No growth is allowed.'
       WHEN -1 THEN 'Autogrowth is on.'
       WHEN 268435456
          THEN 'Log file will grow to a maximum size of 2 TB.'
       ELSE CAST (max_size*1.0/128 AS nvarchar(30))
    END,
growth AS 'GrowthValue',
'GrowthIncrement' =
    CASE
       WHEN growth = 0 THEN 'File size is fixed and will not grow.'
       WHEN growth > 0 AND is_percent_growth = 0
          THEN 'Growth value is in units of 8-KB pages.'
       ELSE 'Growth value is a percentage.'
    END
FROM sys.database_files

Here is another solution through sys.master_files

SELECT
CAST(cast(g.name as varbinary(256)) AS sysname) AS [FileGroup_Name],
s.name AS [Name],
CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],
CAST(CASE when s.growth=0 THEN (CASE WHEN s.type = 2 THEN 0 ELSE 99 END) ELSE s.is_percent_growth END AS int) AS [GrowthType],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CASE when s.max_size=-1 then -1 else s.max_size * CONVERT(float,8) END AS [MaxSize],
s.file_id AS [ID],
'Server[@Name=' + quotename(CAST(
        serverproperty(N'Servername')
       AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/FileGroup[@Name=' + quotename(CAST(cast(g.name as varbinary(256)) AS sysname),'''') + ']' + '/File[@Name=' + quotename(s.name,'''') + ']' AS [Urn],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE s.growth*8 END AS float) AS [Growth],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_read_only AS [IsReadOnly],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_sparse AS [IsSparse]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) --and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
ORDER BY
[FileGroup_Name] ASC,[Name] ASC

Thanks guys. Quick question. Where can i go int the SSMM to check if logs and DBs are set to AUTO GROW?

Thanks

This article can guide you to locate auto growth in SSMS.

http://www.mytechmantra.com/LearnSQLServer/How-to-Change-SQL-Server-Database-Auto-Growth-Settings.html

Query below will identity all files that do NOT have autogrow set:


SELECT DB_NAME(database_id) AS Db_Name, *
FROM sys.master_files
WHERE
    growth = 0