This week I was reading Pinal Daves post about Autogrowth Events
as it happened I had a requirement to make use of the script only a few days later. I was asked to provide the information in a CSV so that the person who required the information could manipulate it in Excel.
I am a great believer in Automation. If you are going to do something more than once then automate it so I wrote two functions, added them to TFS and now they will be available to all of my team members next time they load PowerShell.
Why two functions? Well Pinal Daves script gets the information from the default trace for a single database but there may be times when you need to know the autogrowth events that happened on a server with multiple databases.
I use a very simple method for doing this as I have not found the correct way to parse the default trace with PowerShell. The functions rely on Invoke-SQLCMD2 which I also have in my functions folder and pass the query from Pinal Daves Blog post as a here string
$Results = Invoke-Sqlcmd2 -ServerInstance $Server -Database master -Query $Query
To output to CSV I use the Export-CSV cmdlet
And to open the CSV I add a
[switch] parameter. You can find out more about parameters here or by
so the parameter block of my function looks like
Now when I am asked again to provide this information it is as easy as typing
Show-AutogrowthServer -Server SQL2014Ser12R2
Show-AutogrowthDatabase -Server SQL2014Ser12R2 -Database Autogrowth
and the results will be displayed as below
just a side note. Pinal Daves script uses @@servername in the where clause and if you have renamed your host the script will be blank. The resolution to this is to runt he following T-SQL
You can find the scripts here
and all of my Script Center Submissions are here
As always - The internet lies, fibs and deceives and everything you read including this post should be taken with a pinch of salt and examined carefully. All code should be understood and tested prior to running in a live environment.