Databases Reference
In-Depth Information
####################################################
# Source in our library file
####################################################
. C:\DBAScripts\dbaLib.ps1
[String] $strQuerySql
=
""
[String] $strInsertSql
=
""
[String] $strUpsertSql
=
""
[String] $strUpdateSql
=
""
# Empty the utility table DiskUsageByServer.
[String] $sqlQuerySql
=
"TRUNCATE TABLE [dbo].[DiskUsageByServer];"
Invoke-Sqlcmd -Query $sqlQuerySql -ServerInstance $inventoryServer -Database
$inventoryDatabase
# Gets all the SQL Server hosts.
$strQuerySql
=
"SELECT hostID, hostName FROM Hosts"
$sqlhosts
=
Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $inventoryServer
-Database $inventoryDatabase
# Loop through the list of hosts.
Foreach ($sqlhost in $sqlhosts) {
$strUpsertSql
=
""
$sqlHostID
=
$sqlhost.hostID
$sqlHostName
=
$sqlhost.hostName
#################################################################
# First step, for every host, gets all the local drives.
# Insert into or Update the DiskSpace table with the total size and free space
of each drive on the host.
$devices
=
Get-WmiObject -computerName $sqlHostName -Class Win32_LogicalDisk -
filter "DriveType
=
3"
Foreach ($device in $devices) {
$strUpsertSql
=
$strUpsertSql
+
"exec uspUpsertDiskSpace "
+
$sqlHostID
+
", '" '
+
$device.DeviceID.SubString(0,1)
+
"', "
+
$device.Size/1024
+
", "
+
$device.FreeSpace/1024
+
";`n"
}
$strUpsertSql
Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $inventoryServer -Database
$inventoryDatabase
#################################################################
# Second step, query every instance on the host.
# Insert into the DiskUsageByServer table with the space used for data files,
log files, FILESTREAM and full text catalogs on every instance.
Search WWH ::
Custom Search