Friday, December 5, 2014

Find all Databases with AutoShrink Turned On

As far as I can tell, the only thing that all SQL Server DBAs agree on is that AutoShrink should be turned off. You never even see anyone say, "It depends." This level of agreement is, I think, unique in all of SQL Server-land. I mean, you would think we'd all agree that clustering on a non-sequential GUID is a Very Bad Idea, but there's actually people who argue in favor of it. Go figure.

So anyway, the script below will find all the databases (SQL Server 2000 and up) that for some mysterious reason have it turned on. (Of course, we all know the reason is that some yo-yo doesn't understand that big, empty databases run faster than small, full databases, but that's another story...) If you run this script on CMS, it will work across all the servers in the group (that's why we cursor through the resultsets instead of just returning them - it makes the output in CMS much easier to read).

-- Find all databases with AutoShrink turned on. 
declare @DatabaseName sysname

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from sys.databases
         where is_auto_shrink_on = 1
           and state_desc = 'ONLINE'
           and database_id > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end
else
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from master..sysdatabases
         where status & 4194304 = 4194304
           and status & 512 = 0
           and dbid > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end

0 comments:

Post a Comment