Enable autogrowth for databases in SQL Server

Question

Enable autogrowth for databases in SQL Server

beejug1983

SSCertifiable

Points: 6491
More actions
November 27, 2011 at 9:41 am

#246756

Hi,
I have to enable autogrowth for around 150+ databases on SQL Server 2005. This is client requirement. I know it is possible by using powershell script in one hit. Is there any powershell script available on internet. Please suggest.
Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

bruce 1565 SSCrazy Points: 2574 More actions · Answer 1

This is for one server and includes system databases but I expect you know how to change to your requirements. The single commented lines are where you can change the current autogrow settings. Double comments are for explanation.

I realize there are a Lot of foreach statements but that was to make things easier to follow.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

$Server = 'YerServerNameHere'

$srv = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server

foreach ($db in $srv.Databases) {

$db.name

$fg = $db.FileGroups

foreach ($group in $fg) {

$group.Name

foreach ($file in $group.Files) {

$file.name

$file.Growth

$file.GrowthType

#$file.Growth = 524288

#$file.GrowthType = 'KB'

##For GrowthType valid values are KB, Percent, None

#$file.Growth = 10

#$file.GrowthType = 'Percent'

#$file.MaxSize = -1 ## Set to unlimited growth or replace with some value in KB

#$file.Alter()

}

foreach ($log in $db.LogFiles) {

$log.name

$log.Growth

$log.GrowthType

}

Edit to change Add-Type from loading the 2008 version of SMO to 2005.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

A couple notes.

First, I might actually try to do this more intelligently and read the current db size and then set some percentage. 10% of 10MB makes sense. 10% of 1TB doesn't. I might scale that appropriately, lowering the percentage as you get to larger sizes.

In terms of moving across servers, here's a short script in PS that does that: http://sqlvariant.com/wordpress/index.php/2011/01/powershell-week-at-sql-university-post-5/

bruce 1565 SSCrazy Points: 2574 More actions · Answer 3

Very good point Steve. I was trying to show the how, not specific values but I should have made that clear.

At least I first showed how to set the growth to a specific value which is my preference for autogrow settings in production.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 4

Bruce,

My apologies if it seemed that I was complaining. I thought you had a nice script there and a great start. I wanted the OP to think it through a touch more than just plug in values.

beejug1983,

One more note. You don't want to rely on autogrow. It shouldn't come into play, except in an emergency. You should have some alerting mechanism, maybe even based on Bruce's script, that lets you know when space is getting low in a database so you can manually grow it.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 5

bruce 1565 (11/28/2011)
This is for one server and includes system databases but I expect you know how to change to your requirements. The single commented lines are where you can change the current autogrow settings. Double comments are for explanation.
I realize there are a Lot of foreach statements but that was to make things easier to follow.
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"
$Server = 'YerServerNameHere'
$srv = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $Server
foreach ($db in $srv.Databases) {
$db.name
$fg = $db.FileGroups
foreach ($group in $fg) {
$group.Name
foreach ($file in $group.Files) {
$file.name
$file.Growth
$file.GrowthType
#$file.Growth = 524288
#$file.GrowthType = 'KB'
##For GrowthType valid values are KB, Percent, None
#$file.Growth = 10
#$file.GrowthType = 'Percent'
#$file.MaxSize = -1 ## Set to unlimited growth or replace with some value in KB
#$file.Alter()
}
}
foreach ($log in $db.LogFiles) {
$log.name
$log.Growth
$log.GrowthType
}
}
Edit to change Add-Type from loading the 2008 version of SMO to 2005.

Bruce, that's a fine script so please don't take any personal offense to the question I now as for anyone that cares to answer...

Why would anyone decide to use a SMO, PowerShell, VBS, or any scripting language for such a thing when the identical functionality can be built into T-SQL in about the same amount of code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

beejug1983 (11/27/2011)
Hi,
I have to enable autogrowth for around 150+ databases on SQL Server 2005. This is client requirement. I know it is possible by using powershell script in one hit. Is there any powershell script available on internet. Please suggest.
Thanks in advance.

Seriously... why does it have to be a Powershell script instead of T-SQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

beejug1983 SSCertifiable Points: 6491 More actions · Answer 7

Thanks all, I want to learn Powershell thats why I thought to do it using Powershell.

Thanks. 🙂

bruce 1565 SSCrazy Points: 2574 More actions · Answer 8

No offense taken Jeff, it’s a reasonable question. I posted the Powershell since the OP asked in this forum. The answer would have been different if the question was asked in one of the SQL Server 2005 forums.

I also suspect those 150 databases might be on more than one server. While I could have included the code to cycle through a list of servers I was following the lead of many of you great DBAs here on SSC by leaving some things open to discovery. Plus Steve covered my omission with his link

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 9

I tend to agree with Bruce that Powershell is a nice multi-server tool. It's easily deployable to multiple machines, and it allows you to potentially do things that are harder in T-SQL, like looping. Runnable from a separate machine as well without SQL Server.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

I agree with all the points Steve made. sp_MSforeachDB was a nice breadcrumb left for us by Microsoft and is very useful when managing a single instance but when trying to tame an environment with more than a few instances the investment in time and risk of settings becoming inconsistent becomes non-trivial. There are many ways to handle the problem (SSMS 2008R2 multi-server queries are fantastic) but for automation PowerShell gives us a lot of tools out of the box. We can easily issue the same command against many SQL Servers within a loop in PowerShell to gather information:

$newCsvObject = @()

foreach ($svr in Get-Content "C:\SQLServers-All.txt")

{

$con = "server=$svr;database=master;Integrated Security=sspi"

$cmd = "select serverproperty('servername') as Name,serverproperty('productversion') as Version,serverproperty('productlevel') ServicePack,serverproperty('edition') as Edition"

$da = New-Object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)

$dt = New-Object System.Data.DataTable

$da.fill($dt)

$newCsvObject += $dt | Select-Object Name,Version,ServicePack,Edition

}

$newCsvObject | Export-Csv "C:\SQLServers-All_stats.csv" -NoTypeInformation

Example contents of "C:\SQLServers-All.txt" :

server1
server1\instance1
server2

Issuing a "non-query" command, i.e. an update or insert is a similar task. I use Invoke-SqlCmd in the same foreach loop rather than the .NET SqlClient.

Taking it a step further we can easily employ PowerShell and WMI to discover all SQL Server instances on our network without relying on the SQL Browser. If anyone is interested in that I can post a modified version of a script Denny Cherry originally posted or you can Google for the original.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

beejug1983 SSCertifiable Points: 6491 More actions · Answer 11

Hi opc,

It would be great if you can post a script that can enable autogrowth for all databases from more than one instances. Because I have a list of servers as .txt file.

Thanks in advance.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

I think I have given you the tools you need...and there is always Google. Feel free to post what you have if you get stuck...or start a new thread.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

Steve Jones - SSC Editor (11/29/2011)
I tend to agree with Bruce that Powershell is a nice multi-server tool. It's easily deployable to multiple machines, and it allows you to potentially do things that are harder in T-SQL, like looping. Runnable from a separate machine as well without SQL Server.

Heh... agreed on all points but one... since when did it become "harder in T-SQL" to loop? 😀 I've spent most of my time on these fine forums trying to break people of the habit. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 14

opc.three (11/29/2011)
Taking it a step further we can easily employ PowerShell and WMI to discover all SQL Server instances on our network without relying on the SQL Browser. If anyone is interested in that I can post a modified version of a script Denny Cherry originally posted or you can Google for the original.

I think WMI rocks and if PS can discover all SQL Server instances and you have code for that, it would be absolutely awesome if you could post it here. Thanks, Orlando.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)