June 6, 2017 at 4:41 pm
Hi Senior DBA's.
Do you know of a way to automate the below requirement to create a daily report ( on a web page ) for the upper management?
O/p of master..sysdatabases order by crdate desc
size of each database and database file and growth day after day.
Database names are same but we need to show them the O/p for multiple enviornments like Development, UAT, Production
This is for production where a new database is created each week.
Thanks,
June 7, 2017 at 6:19 am
You should be able to automate this pretty easily through Powershell. You provide a list of servers and then you can query for the list of databases on each server and then run the query to get the database sizes and files. Not a problem. To show growth you'll have to be able to compare yesterday to today which means you just have to store this. Again, not an issue, just have a database available for keeping the information you collect. From there, easy enough to pop it all into a report, a spreadsheet, or best of all a PowerBI display.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2017 at 6:45 am
As Grant said, Powershell is your friend.
Start with defining a central db where you'll store all your collected data and tables to store data (redacted for your requirement only)
$SQLSRVR = “xxx”
$DB = “DBA”
$TableServerInfo = “PS.ServerInfo”
$TableDatabaseinfo = "PS.DatabaseInfo"
$TableDiskSpace = "PS.DiskSpaceInfo"
Then define the connection to each server you'll collect::
$SQLCON = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLSRVR;Initial Catalog=$DB;Integrated Security=SSPI")
$SQLCON.open()
$SQL = $SQLCON.CreateCommand()
and where the script should find your server list:
ForEach ($instance in Get-Content "C:\PS\servers.txt") #change the directory to where your site list is stored
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
Define query spaces:
$dbs=$s.Information
$db=$s.Databases
$res = $instance.split('\')[0]
Then define your queries as you need the information from SQL:
$DatabaseServerInfo = $dbs | Select Parent, FullyQualifiedNetName, Physicalmemory, Processors, PlatForm, NetName, OSVersion, language, Edition, Version, BuildNumber, Collation, IsClustered, IsHADREnabled, IsSingleUser, Get-Date
$DatabaseInfo = $db | select Parent, Name, Size, AvailabilityDatabaseSynchronizationState, CompatibilityLevel, DataSpaceUsage, SpaceAvailable, IndexSpaceUsage, IsAccessible, IsUpdateable, RecoveryModel, LastBackupDate, LastLogBackupDate, MirroringStatus, Owner, ReadOnly, Get-Date, CreateDate
$DiskSpaceInfo = Get-WmiObject win32_logicaldisk -computername $instance.Split('\')[0] | Where-Object { $_.DriveType -eq 3 } | Select-Object SystemName,Name, DeviceID, Size, FreeSpace
Insert statements from each query into your predefined tables:
Foreach($DatabaseServer in $DatabaseServerInfo)
{
$Parent = $DatabaseServer.parent
$FullyQualifiedNetName = $DatabaseServer.FullyQualifiedNetName
$PhysicalMemory = $DatabaseServer.PhysicalMemory
$Processors = $DatabaseServer.Processors
$Platform = $DatabaseServer.Platform
$NetName = $DatabaseServer.NetName
$OSVersion = $DatabaseServer.OSVersion
$language = $DatabaseServer.language
$Edition = $DatabaseServer.Edition
$BuildNumber = $DatabaseServer.BuildNumber
$Collation = $DatabaseServer.Collation
$IsClustered = $DatabaseServer.IsClustered
$IsHADREnabled = $DatabaseServer.IsHADREnabled
$DateUpdated = Get-Date
$Version = $DatabaseServer.Version
$IsSingleUser = $DatabaseServer.IsSingleUser
$SQLupdate = "Insert into $TableServerInfo values ('$Parent', '$FullyQualifiedNetName', '$PhysicalMemory', '$Processors', '$Platform',
'$NetName', '$OSVersion', '$language', '$Edition','$Version', '$BuildNumber', '$Collation', '$IsClustered', '$IsHADREnabled', '$IsSingleUser', '$DateUpdated')"
Write-host $SQLupdate
$SQL.CommandText = $SQLupdate
$SQL.ExecuteNonQuery() > $null
}
Just posting sample code, but this should at least get you started, this is not perfect by any means, so feel free to modify and improve.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 7, 2017 at 7:05 am
Hi
I´ve tried to create a script in Powershell to list all the SQL servers in our enviroment but hasn´t really had any success. I´m a newbee in Powershell. Haven´t really find any seaching the web, althou i found a lot of scripts...
We have SQL 2008 R2 , SQL 2012 R2 and SQL 2016 + some Express servers and I want to get a list of which servers is running SQL and which version. ( to check that no developer installs SQL and not pay for it.. 🙂 ) . In the long run there is also the check of the growt of the databases that we want to keep an eye on
Do You have some nice ones ?
With Regards
Jonas
June 7, 2017 at 7:08 am
Jonas Jisendal - Wednesday, June 7, 2017 7:05 AMHi
I´ve tried to create a script in Powershell to list all the SQL servers in our enviroment but hasn´t really had any success. I´m a newbee in Powershell. Haven´t really find any seaching the web, althou i found a lot of scripts...
We have SQL 2008 R2 , SQL 2012 R2 and SQL 2016 + some Express servers and I want to get a list of which servers is running SQL and which version. ( to check that no developer installs SQL and not pay for it.. 🙂 ) . In the long run there is also the check of the growt of the databases that we want to keep an eye on
Do You have some nice ones ?With Regards
Jonas
Firstly, always good practice to start your own thread when asking a question.
Secondly, see my previous response, and Grant's. Scheduled collecting of info with PS, then report of it with SSRS, Excel. PowerBI, pretty much anything that can read a table.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 7, 2017 at 7:18 am
Your post came up when I posted mine... 🙂
The textfile,servers.txt, is it a list of all servers or just servers running SQL ?? If just SQL servers , how do you get it ?
"ForEach ($instance in Get-Content "C:\PS\servers.txt") #change the directory to where your site list is stored
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance "
June 7, 2017 at 7:20 am
Jonas Jisendal - Wednesday, June 7, 2017 7:05 AMHi
I´ve tried to create a script in Powershell to list all the SQL servers in our enviroment but hasn´t really had any success. I´m a newbee in Powershell. Haven´t really find any seaching the web, althou i found a lot of scripts...
We have SQL 2008 R2 , SQL 2012 R2 and SQL 2016 + some Express servers and I want to get a list of which servers is running SQL and which version. ( to check that no developer installs SQL and not pay for it.. 🙂 ) . In the long run there is also the check of the growt of the databases that we want to keep an eye on
Do You have some nice ones ?With Regards
Jonas
I would exercise extreme caution when polling your entire network for SQL Server instances. Your network admins might find you to be a virus and shut you down. I'd work with them on any project of this type.
And yeah, posting your own question is the right way to go. This is considered hijacking in forum parlance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 7, 2017 at 7:24 am
Jonas Jisendal - Wednesday, June 7, 2017 7:18 AMYour post came up when I posted mine... 🙂
The textfile,servers.txt, is it a list of all servers or just servers running SQL ?? If just SQL servers , how do you get it ?"ForEach ($instance in Get-Content "C:\PS\servers.txt") #change the directory to where your site list is stored
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance "
Correct, list the known SQL Servers/Instances.
You can get all, and this is the important part, accessible SQL Instances, by running osql -L from command prompt.
Be careful running this though, see Grant's comments.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 7, 2017 at 7:26 am
Oops sorry ... Didn´t attend to hijack this post.... my bad... 🙁
just thought it were in the same area.....
August 14, 2017 at 10:04 am
Instead of a text file with the SQL Servers listed, would it be possible to actually use a SQL Server database with this information? If so, how would you set it up using PowerShell?
Thanks
August 14, 2017 at 10:29 am
chuck.goehler - Monday, August 14, 2017 10:04 AMInstead of a text file with the SQL Servers listed, would it be possible to actually use a SQL Server database with this information? If so, how would you set it up using PowerShell?
Thanks
This example that reads the instances and databases from a table in a database using invoke-sqlcmd. Explains everything step by step so seems to be a good template to learn the procedures and make any modifications needed:
Using Powershell to loop through a list of SQL Server Databases
Sue
August 15, 2017 at 9:33 am
you may want to consider Sql Server Management Data Warehouse, you will get more then you are looking for and its ease and free. https://docs.microsoft.com/en-us/sql/relational-databases/data-collection/configure-the-management-data-warehouse-sql-server-management-studio
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply