April 3, 2011 at 11:18 pm
Comments posted to this topic are about the item Paging Doctor Powershell
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
April 4, 2011 at 10:19 pm
Nice article and useful tool.
Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2011 at 6:04 am
Thanks. If folks have ideas on things to add to it, let me know and I can work them in and share the script.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
April 5, 2011 at 7:14 am
This looks like some great stuff, however I am new to powershell. Do you have any step by step instructions for running the scripts? I would like to try it out in my test servers.
Thanks,
David
April 5, 2011 at 12:22 pm
I don't have anything step by step - but I could easily create a step by step article or a set of blog posts that cover it. Is that something people would like to see? It could be articles or I could just blog it in small bites.
The script I uploaded should be able to be run as long as you have Powershell 2 installed without any issues. (you shouldn't need any experience) Just give it a server name and a few hours and let it run.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
April 7, 2011 at 7:08 am
nice article, going to have to look at it. right now i have perfmon running on a server dumping data to a central database where i run reports from. this is a lot lighter, but i was going to use the perfmon data for historical baselines as well. one thing i don't like about powershell is that you can't seem to dump the data into SQL
April 7, 2011 at 7:17 am
You can dump directly to SQL, I do that for gathering stats for our VM admins. I don't like looking at csvs or text files anymore than the next DBA.
Once you have a list of counters (note I have some extra properties in my object collection) - you could simply call a proc (or just directly insert them)
$CounterCollection | Where-Object{$_.Path -ne $null} | ForEach-Object{
$qry = "EXEC .dbo.pr_Add_Perf_Collection "
$qry = $qry + " @server='" + [string]$_.Server + "'"
$qry = $qry + ", @performance_metric='" + [string]$_.Path + "'"
$qry = $qry + ", @performance_value=" + [decimal]$_.CookedValue
$qry = $qry + ", @date_sampled='"+ $_.Timestamp + "'"
$qry = $qry + ", @samples_taken=" + $_.Samples
$qry = $qry + ", @sample_interval=" + [int]$_.SampleInterval
Invoke-SqlNonQuery -ServerInstance $SQLServerToStore -Query $qry
}
function Invoke-SqlNonQuery{
param(
[string]$ServerInstance,
[string]$Query
)
$QueryTimeout=30
$conn=new-object System.Data.SqlClient.SQLConnection
$constring = "Server=" + $ServerInstance + ";Integrated Security=True"
$conn.ConnectionString=$constring
$conn.Open()
if($conn){
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$cmd.ExecuteNonQuery() | out-null
$conn.Close()
}
}
Hope this helps. I will see about writing a perfmon metric gather/storage article if SSC wants to publish it.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
June 7, 2013 at 12:16 pm
Examples would be helpful how to pipe data directly to sql server database.
June 7, 2013 at 12:28 pm
This is probably what you are looking for http://gallery.technet.microsoft.com/scriptcenter/2fdeaf8d-b164-411c-9483-99413d6053ae
Note it uses bulkcopy and there are some restrictions with that.
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
July 11, 2013 at 1:45 pm
How could you exclude certain error codes in array string?
The below example does not seem to filter errors from my array.
Ex.
$IgnoreErrorCode=@(
'1608',
'17832',
'17824',
'18456';
)
# Write-Host "Reading SQL Log for $sqlServerName"
try{
$sqlServer.ReadErrorLog() | Where{$_.LogDate -is [datetime] } |
Where-Object{$_.LogDate -gt $DatetoCheck } |
Where-Object{$_.Text -like "*Error*" -or $_.Text -like "*Fail*"} -and ($_.Text -notcontains $IgnoreErrorCode) |
Select-Object LogDate,Text |
Out-GridView -Title "$sqlServerName Log Errors"
} catch {
Write-Host "Error Reading $sqlServer.Name"
}
April 13, 2014 at 5:00 pm
As a norm, I only use double quotes when I intend the string to be interpolated. When I want to use the string as it is, I use single quotes. It's not much, but why waste CPU and time for nothing?
You can filter the WMI instances in the query:
Get-WmiObject -Class Win32_Service -Filter 'Name like "%SQL%"'
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply