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

6 comments:

Anonymous said...

Very useful script. As a newbie, I needed to figure out some things.

This is a Transact-SQL script. Save it to a file with extension .sql. Run it with something like:
sqlcmd -S MyServer -i ASDetect.sql

ayon saha said...
This comment has been removed by a blog administrator.
Unknown said...
This comment has been removed by a blog administrator.
argadetectives said...

The Arga Detective Agency has been for many years,
developing research as detectives in the field of the community of Madrid, nationally and internationally. Our experience and recognition guarantee us as the most prestigious Detective Agency in Spain

if you want more just look here "cliente discreto"

shuvoroy said...

All K managers are composed of 20 managers who work and are high-quality educated managers. It is a place full of uniqueness differentiated from other companies. From the thoughts and perspectives of your customers, K's business trip massage
Because it is an operational strategy, the satisfaction of managers is high, so if you believe and call me, thank you

if you want more just look here "Ulsan Branch Massage Ulsan Branch"

rakibkhan said...

If you are interested in CBD oil, you might want to know more about it first. There is no other way to do this than to visit the website of CBDTornado. The site is a one-stop shop where you can find everything that you need involving CBD oil.

if you want more just look here "https://topcbdoilonline.page.tl/"

Post a Comment