Technical Article

SQLPS Meets the DMV's

,

The vb script will launch the powershell script which will prompt you for the1st part of the sql named instance name then second part as:

Prompt 1: MSQL1

Prompt 2: SQL_Instance1

SQL Named intance full name: MSSQL1\SQL_Instance1

You can just call the.ps1 file also but since I am a bit lazy these days...Enjoy !

#This is one line in the .ps1 file designed for a named instance capture
#edit the path that the sqlps script saves the file to match your work station.
#I use a .vbs script to launch the SQLPS shell  
#Currently I have a wrapper that remotely brings me 32 csv files in 15 seconds or less with a plethora of performance metrics to review.
#This is an example that fetches the Performance monitor Memory Manager counter and all the underlying objects.
#From SQLSHARK 
Clear-Host
$SQLServername = Read-Host "SQLServer Name to run the capture against?"
$SQLServerInstname = Read-Host "Instance Name to run the capture against?"
$ServerStamp = $SQLServername #+ "\" +$SQLServerInstname
invoke-sqlcmd -ServerInstance $SQLServerName\$SQLServerInstname -Database msdb –Query "Declare @sqlinstance varchar(25)
SELECT @sqlinstance = CONVERT(varCHAR(100), SERVERPROPERTY('instancename')) select * from sys.dm_os_performance_counters where object_name = 'MSSQL$' +@sqlinstance + ':Memory Manager'" | Export-Csv –path C:\KPMG_Scripting\powershell_dba\output\MemoryManager_$SQLServerName_$(get-date -f MM_dd_yyyy_HHmm)_$SQLServername.csv -notype


'VB Script has to be the same name as the .ps1 script, same directory and it launches the sqlps script with a click.
Option Explicit 
Dim oShell, appCmd 
Set oShell  = CreateObject("WScript.Shell") 
appCmd      = "sqlps -noexit &'" & Replace(WScript.ScriptFullName, ".vbs", ".ps1") & "'"
oShell.Run appCmd, 4, false

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating