Scripting the Auto-shrink to value 0

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Scripting the Auto-shrink to value 0

Author

Topic

wisertime
Starting Member

28 Posts

Posted - 2008-04-14 : 15:54:54

I would like to do an update statement to set auto shrinks to zero. A value of one means they are on. I have been using this select statement and manually changing them, but can't use it in a where clause.

select name, databasepropertyex(name, 'isautoshrink') shrink
from master.dbo.sysdatabases

I would like to do "where shrink = 1", but it won't work.

Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-14 : 16:06:55

Use ALTER DATABASE command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

wisertime
Starting Member

28 Posts

Posted - 2008-04-14 : 16:14:04

That was my thought, but I would still have to know the DB that was set to auto-shrink to run the command. I was hoping there would be a way to automatically find them w/ autoshrink and change them

quote:
Originally posted by tkizer

Use ALTER DATABASE command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-14 : 16:20:05

You don't have to know the database name. It can easily be scripted:

SELECT 'ALTER DATABASE ' + name + ' SET AUTO_SHRINK OFF'
FROM sys.databases

Just copy the output, paste into a new window, and then run it. Add a WHERE clause to the SELECT to get only the database that you want or copy/paste the rows you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

wisertime
Starting Member

28 Posts

Posted - 2008-04-14 : 16:27:07

I see what your getting at. Thank you![

quote]Originally posted by tkizer

You don't have to know the database name. It can easily be scripted:

SELECT 'ALTER DATABASE ' + name + ' SET AUTO_SHRINK OFF'
FROM sys.databases

Just copy the output, paste into a new window, and then run it. Add a WHERE clause to the SELECT to get only the database that you want or copy/paste the rows you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources