Test SQL Server with Windows Powershell - Part 6

This guide provides a practical overview of test SQL server with Windows powershell - part 6 with clear explanations and useful details. It focuses on the information users are most likely to need.

Test SQL Server with Windows PowerShell - Part 1 Test SQL Server with Windows PowerShell - Part 2 Test SQL Server with Windows PowerShell - Part 3 Test SQL Server with Windows PowerShell - Part 4 Test SQL Server with Windows PowerShell - Part 5

How Test SQL Server with Windows Powershell - Part 6 Works

Muthusamy Anantha Kumar aka The MAK

. com - Part 1 of this series introduced the first test on SQL Server - ping a host. Part 2 is an introduction to how to check all Windows services related to SQL Server, part 3 is how to check hardware and software information, part 4 is an introduction to collecting information. about network card and hard drive from server. In Part 5, I showed you how to check if you can connect to SQL Server and see if we can query some properties related to SQL Server. This guide provides a practical overview of test SQL server with Windows powershell - part 6 with clear explanations and useful details. It focuses on the information users are most likely to need.

Step 1 Type or copy and paste the following code into the C: CheckSQLServerCheckdatabases. ps1 file. Function checkdatabases ( [string] $ servername ) { $ SqlConnection = New-Object System. Data. SqlClient. SqlConnection $ SqlCmd = New-Object System. Data. SqlClient. SqlCommand $ SqlAdapter = New-Object System. Data. SqlClient. SqlDataAdapter $ DataSet = New-Object System. Data. DataSet $ DataSet2 = New-Object System. Data. DataSet $ DataSet3 = New-Object System. Data. DataSet $ DataSet4 = New-Object System. Data. DataSet $ SqlConnection. ConnectionString = "Server = $ servername; Database = master; Integrated Security = True" $ SqlCmd. CommandText = "select name from master. dbo. sysdatabases" $ SqlCmd. Connection = $ SqlConnection $ SqlAdapter. SelectCommand = $ SqlCmd $ SqlAdapter. Fill ($ DataSet) | out-null $ dbs = $ DataSet. Tables [0] # $ dbs Foreach ($ db in $ dbs) { # $ db. name $ SqlCmd. CommandText = $ db. name + ". sp_spaceused" $ SqlCmd. Connection = $ SqlConnection $ SqlAdapter. SelectCommand = $ SqlCmd $ SqlAdapter. Fill ($ DataSet2) | out-null } $ DataSet2. Tables [0] | -autosize format-table Foreach ($ db in $ dbs) { # $ db. name $ SqlCmd. CommandText = " Select '"+ $ db. name +"' as Dbname, DATABASEPROPERTY ('"+ $ db. name +"', 'IsInRecovery') as Inrecovery, DATABASEPROPERTY ('"+ $ db. name +"', 'IsInLoad') as InLoad, DATABASEPROPERTY ('"+ $ db. name +"', 'IsEmergencyMode') as InEmergency, DATABASEPROPERTY ('"+ $ db. name +"', 'IsOffline') as Isoffline, DATABASEPROPERTY ('"+ $ db. name +"', 'IsReadOnly') as IsReadonly, DATABASEPROPERTY ('"+ $ db. name +"', 'IsSingleUser') as IsSingleuser, DATABASEPROPERTY ('"+ $ db. name +"', 'IsSuspect') as IsSuspect, DATABASEPROPERTY ('"+ $ db. name +"', 'IsInStandBy') as IsStandby, DATABASEPROPERTY ('"+ $ db. name +"', 'Version') as version, DATABASEPROPERTY ('"+ $ db. name +"', 'IsTruncLog') as IsTrunclog " # $ SqlCmd. CommandText $ SqlCmd. Connection = $ SqlConnection $ SqlAdapter. SelectCommand = $ SqlCmd $ SqlAdapter. Fill ($ DataSet4) | out-null } $ DataSet4. Tables [0] | -autosize format-table $ SqlCmd. CommandText = "DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS" $ SqlCmd. Connection = $ SqlConnection $ SqlAdapter. SelectCommand = $ SqlCmd $ SqlAdapter. Fill ($ DataSet3) | out-null $ DataSet3. Tables [0] | -autosize format-table $ SqlConnection. Close () } Step 2 Attach to the file C: CheckSQLServerCheckSQL_Lib. ps1 the following code. ../checkdatabases. ps1 Now file C: CheckSQLServerCheckSQL_Lib. ps1 will include pinghost, checkservices, checkhardware, checkOS, checkHD, checknet, checkinstance, Checkconfiguration and checkdatabases as shown below. #Source all the relate to functions CheckSQL ../PingHost. ps1 ../checkservices. ps1 ../checkhardware. ps1 ../checkOS. ps1 ../checkHD. ps1 ../checknet. ps1 ../checkinstance. ps1 ../checkconfiguration. ps1 ../checkdatabases. ps1 Note: The CheckSQL_Lib. ps1 file will be updated with the source of the new scripts, such as checkdatabases. ps1. Step 3 Append to the file C: CheckSQLServerCheckSQLServer. ps1 the following code. #Objective: To check various status of SQL Server #Host, instances and databases. #Author: MAK #Date Written: June 5, 2008 Param ( [string] $ Hostname, [string] $ instancename ) $ global: errorvar = 0 ../CheckSQL_Lib. ps1 Write-host "Checking SQL Server." Write-host "…" "Write-host" Write-host "Arguments accepted: $ Hostname" Write-host "…" Write-host "Pinging the host machine" Write-host "…" Pinghost $ Hostname If ($ global: errorvar -ne "host not reachable") { Write-host "Check Windows services on the host related to SQL Server" Write-host "… " Checkservices $ Hostname Write-host "Checking hardware Information." Write-host "…" Checkhardware $ Hostname Write-host "Checking OS Information." Write-host "…" CheckOS $ Hostname Write-host "Checking HDD Information." Write-host "…" CheckHD $ Hostname Write-host "Checking Network Adapter Information." Write-host "…" Checknet $ Hostname Write-host "Checking Configuration information." Write-host "…" Checkconfiguration $ instancename | format-table Write-host "Checking Instance property Information." Write-host "…" Checkinstance $ instancename | format-table Write-host "Checking the SQL Server databases." Write-host "Checking Database status and size." Write-host "…" Checkdatabases $ instancename | format-table } Note: The CheckSQLServer. ps1 file will be updated with new conditions and new parameters in the next sections of this series. The source basically loads the functions listed in the script file and makes it available during the entire PowerShell session. In this case, we source a script, but this scenario is sourced from many other scenarios. Step 4 Now let's execute the CheckSQLServer. ps1 script using 'PowerServer3' as an argument as shown below. ./CheckSQLServer. ps1 PowerServer3 PowerServer3SQL2008 You will get the results shown below (refer to Figure 1.0). Result

 database_name database_size unallocated space ------------- ------------- ----------------- master 5.00 MB 1.28 MB tempdb 8.75 MB 6.70 MB model 1.75 MB 0.16 MB msdb 11.00 MB 0.46 MB ReportServer $ SQL2008 9.38 MB 0.55 MB ReportServer $ SQL2008TempDB 3.00 MB 1.02 MB AdventureWorksDW2008 71.06 MB 3.20 MB AdventureWorks2008 182.06 MB 0.00 MB Dbname Inrecovery InLoad InEmergency Isoffline IsReadonly IsSingleuser I ------ ---------- ------ ----------- --------- ---- ------ ------------ - master 0 0 0 0 0 0 tempdb 0 0 0 0 0 0 model 0 0 0 0 0 0 msdb 0 0 0 0 0 0 ReportServer $ SQL2008 0 0 0 0 0 0 ReportServer $ SQL2008TempDB 0 0 0 0 0 0 AdventureWorksDW2008 0 0 0 0 0 0 AdventureWorks2008 0 0 0 0 0 0 test 0 0 1 0 0 Database Name Log Size (MB) Log Space Used (%) Status ------------- ------------- ------------------ ------ master 0.9921875 50.3937 0 tempdb 0.7421875 63.68421 0 model 0.4921875 59.52381 0 msdb 0.4921875 61.90476 0 

Figure 1.0

Step 5 Now let's do the script on the computer that doesn't exist, see below. ./CheckSQLServer. ps1 TestServer testserver The results are shown below (refer to Figure 1.1) Result Checking SQL Server. … Arguments accepted: TestMachine … Pinging the host machine … TestMachine is NOT reachable

Figure 1.1

Conclude This is part 6 of this series. In this article, I have shown you how to access database status and size information using Windows PowerShell.

Download the script for this section.