January 23, 2015 at 3:24 am
quackhandle1975 (1/18/2015)
Hi Wilem,I like your script and plan to test it on a few VM SQL instances but how different is your script to say Data Collector?
Rgds,
qh
Hi qh,
Good question! I do not currently have the answer and will look into Data Collector in the - hopefully near - future..
Be back..
Willem
February 3, 2015 at 2:40 pm
Good article. I am looking to use this on my work environment as part of a solution to monitor growth.
I followed all the steps and the job runs without errors. Only the empty table dbo.dbspace was created.
When i run the .ps1 script from PS Command line it does populate the table. I am just having issues running it from the SQL Agent job:
I used this in the Job Step Properties:
C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe -ExecutionPolicy ByPass -File "C:\Monitoring\Record_Device_Usage_ps1.ps1"
Any advise on what i might be missing? First time i call a Powershell script from SQL Agent job.
February 6, 2015 at 5:36 am
SeaTiger (2/3/2015)
Good article. I am looking to use this on my work environment as part of a solution to monitor growth.I followed all the steps and the job runs without errors. Only the empty table dbo.dbspace was created.
When i run the .ps1 script from PS Command line it does populate the table. I am just having issues running it from the SQL Agent job:
I used this in the Job Step Properties:
C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe -ExecutionPolicy ByPass -File "C:\Monitoring\Record_Device_Usage_ps1.ps1"
Any advise on what i might be missing? First time i call a Powershell script from SQL Agent job.
Hi,
Did you try running it the way I suggested in the article?
In a SQL Agent job step, for Type choose Operating system (CmdExec) and in the Command windows type:
powershell.exe -command "& 'd:\monitoring\record_device_usage.ps1'"
(or any other path you are using).
Make sure you use both the double and the single quotes..
February 9, 2015 at 11:50 am
Yes i did. It does run as a success on the Job now but i dont get the table populated yet.
I get something like this on the log
CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserExceptio n + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Ma nagement.PowerShell.GetScriptCommandInvoke-Sqlcmd : Cannot validate
February 10, 2015 at 1:39 am
SeaTiger (2/9/2015)
Yes i did. It does run as a success on the Job now but i dont get the table populated yet.I get something like this on the log
CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserExceptio n + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Ma nagement.PowerShell.GetScriptCommandInvoke-Sqlcmd : Cannot validate
Mm, maybe we can discuss this outside the forum, because it seems very specific to your situation. Can you send me an email or a private message?
You can find my profile on the 'Authors' page, then click on 'SQLServerCentral.com profile' beneath my name and send me a message.
It would interest me to see the part (now missing in your quote) behind 'Cannot validate'. And also to know whether the exact same command you now try to execute from within SQL Server Agent, does work ok if you run it from the PowerShell command line.
February 10, 2015 at 6:06 am
Willem G (1/23/2015)
quackhandle1975 (1/18/2015)
Hi Wilem,I like your script and plan to test it on a few VM SQL instances but how different is your script to say Data Collector?
Rgds,
qh
SQL Data Collection Sets is a very useful tool but it serves a much broader purpose than just monitoring database data and file growth.
With some additional configuration you can limit its usage to just that aspect. In that case, however, I would prefer a solution like my script because it is easier to implement and offers more in the way of reporting options and the added bonus of email alerts.
If you have a need for the more extended monitoring options of Data Collection Sets, and include disk and database usage (which it offers by default), then that seems a perfectly sensible option to me.
February 10, 2015 at 10:21 am
Cheers for the reply, yes email alerts are very useful!
qh
April 9, 2015 at 11:47 am
I really like the idea of this script, and see value for it in my environment. Is there a suggestion you can make to alter the trigger of a message being sent to only do so upon a change in size +/- 100MB?
Also, is there a good way to determine which servers are unable to poll successfully (due to connectivity or otherwise) when using a server list?
April 10, 2015 at 8:56 am
bdmelvil (4/9/2015)
I really like the idea of this script, and see value for it in my environment. Is there a suggestion you can make to alter the trigger of a message being sent to only do so upon a change in size +/- 100MB?Also, is there a good way to determine which servers are unable to poll successfully (due to connectivity or otherwise) when using a server list?
1) Yes, that is easy: add a 'where' clause to cre_sp_Mail_DB_Sizediffs.sql. For example, if you only want to see changes (increases and decreases) of more than 100 MB, add a where clause just after this line:
FROM [tempdb].[dbo].[DB_Dev_Sizediff]
Add (for example):
WHERE (MB_Growth > 100 OR MB_Growth < -100)
2) In another script I posted a while ago, I used a function in PS to check connectivity to a server
( for the whole post, see http://www.sqlservercentral.com/articles/Backup/112463/ ).
I did not test it for this script, but it for an idea:
Create the function somewhere at the top of the PS script. The function code is:
# === Function to check whether we can connect to a SQL server ===
function CheckServerConnection {
Try {
Invoke-Sqlcmd -ServerInstance ($Server.Name) -Query "SELECT @@SERVERNAME" -ConnectionTimeout 1 `
-ErrorAction SilentlyContinue
return 1
}
Catch {
"ERROR: SQL connection to server '" +$Server.Name+ "' failed; server will be skipped."
return -1
}
}
# ======================
Then, replace these lines in the script (sorry about the layout, did not have time to fix it in the post..):
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$SQL = "SELECT '" +$Server.Server_Name+ "' AS 'Server_Name',
[name] AS 'Database_Name'
FROM master.sys.databases
WHERE state = 0 ;"
$ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQL"
}
BY:
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$ConnectionCheck = CheckServerConnection "$Server.Server_Name"
if ($ConnectionCheck -eq 1) {
$SQL = "SELECT '" +$Server.Server_Name+ "' AS 'Server_Name',
[name] AS 'Database_Name'
FROM master.sys.databases
WHERE state = 0 ;"
$ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQL"
}
}
Try that, and let me know in case you run into problems.
April 10, 2015 at 9:30 am
Wow man, thanks. Really appreciate the detailed assistance. I'll give this a shot shortly and let you know what happens.
April 10, 2015 at 1:16 pm
So the edit to the SP worked great.
The edit to the PS script is giving an error now. I edited the section you specified like this (the bolded section being the addition)
# Create list of online databases per server
$ServerDBList = @()
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$ConnectionCheck = CheckServerConnection "$Server.Server_Name"
if ($ConnectionCheck -eq 1) {
$SQL = "SELECT '" +$Server.Server_Name+ "' AS 'Server_Name',
[name] AS 'Database_Name'
FROM master.sys.databases
WHERE state = 0 ;"
$ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQL"
}
}
The error I receive is:
Invoke-Sqlcmd :
At C:\Users\username\PowerShell\SQL Trend Analysis\Record_Device_Usage.ps1:140 char:19
+ $LogSpaceTMP += Invoke-Sqlcmd –ServerInstance ($row.Server_Name) –Query "$SQL"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ParserError: (:) [Invoke-Sqlcmd], ParserException
+ FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
The function piece is at the top of the script right after the "User Defined Parameters" section and right before the "Start Work" section. (Again bolded my additions)
Looks like this:
# If $SendMail=1, a mail alert is sent when changes in database device sizes were found, or devices were added/deleted.
$SendMail = 1
# === Function to check whether we can connect to a SQL server ===
function CheckServerConnection {
Try {
Invoke-Sqlcmd -ServerInstance ($Server.Name) -Query "SELECT @@SERVERNAME" -ConnectionTimeout 1 `
-ErrorAction SilentlyContinue
return 1
}
Catch {
"ERROR: SQL connection to server '" +$Server.Name+ "' failed; server will be skipped."
return -1
}
}
# ======================
### ===== START WORK ===== ###
Import-Module SQLPS -DisableNameChecking
Thanks for your assistance in getting this going.
April 10, 2015 at 2:34 pm
bdmelvil (4/10/2015)
So the edit to the SP worked great.The edit to the PS script is giving an error now. I edited the section you specified like this (the bolded section being the addition)
Give me a few days (weekend has started her in Europe..), I'll have to run a test myself and get back to you.
April 13, 2015 at 11:06 am
I've modified the PS script, and I think pretty much have what I'm looking for. I used your 'SQLRestorer' script as a reference.
So now I have a function statement following 'Import-Module SQLPS' that looks like this:
# === Function to check whether we can connect to a SQL server ===
function CheckServerConnection {
Try {
Invoke-Sqlcmd -ServerInstance ($Server.Server_Name) -Query "SELECT @@SERVERNAME" -ConnectionTimeout 1 `
-ErrorAction SilentlyContinue
return 1
}
Catch {
"ERROR: SQL connection to server '" +$Server.Server_Name+ "' failed; server will be skipped. `r`n" `
| Out-File $LogFile -Append
return -1
}
}
# ======================
and a call to that function directly following the import of the server list:
# Determine which server(s) to query
if ($ServerFile) {
# retrieve server names from text file
$ServerList = Import-Csv -Header Server_Name $ServerFile
}
else {
# or run in standalone mode
$ServerList = @{Server_Name=$SqlHost}
}
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$ConnectionCheck = CheckServerConnection "$Server.Server_Name"
if ($ConnectionCheck -eq 1) {
}
}
My issue now is I'm receiving an error in two sections that I didn't modify at all (at least I don't see where I did, if I did)
Error:
Invoke-Sqlcmd : Line 4: Incorrect syntax near ';'.
At C:\Users\BDMelvil\PowerShell\SQL Trend Analysis\Record_Device_Usage.ps1:137 char:20
+ $ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Invoke-Sqlcmd : Line 1: Incorrect syntax near ';'.
At C:\Users\BDMelvil\PowerShell\SQL Trend Analysis\Record_Device_Usage.ps1:145 char:19
+ $LogSpaceTMP += Invoke-Sqlcmd –ServerInstance ($row.Server_Name) –Query "$SQL"
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Lines that are referenced:
# Create list of online databases per server
$ServerDBList = @()
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$SQL = "SELECT '" +$Server.Server_Name+ "' AS 'Server_Name',
[name] AS 'Database_Name'
FROM master.sys.databases
WHERE state = 0 ;"
137: $ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQL"
}
# Record details of log devices per server
$LogSpace = @()
ForEach ($row in $ServerList | where {$_.Server_Name -ne ''}) {
$SQL = "DBCC sqlperf(logspace) WITH NO_INFOMSGS;"
$LogSpaceTMP = @()
145: $LogSpaceTMP += Invoke-Sqlcmd –ServerInstance ($row.Server_Name) –Query "$SQL"
$LogSpaceTMP | Add-Member -type NoteProperty -name Server_Name -Value $row.Server_Name
$LogSpace += $LogSpaceTMP
}
The script seems to run completely despite the errors, but I'm curious what is causing them.
April 14, 2015 at 8:36 am
My issue now is I'm receiving an error in two sections that I didn't modify at all (at least I don't see where I did, if I did)
The changed code, i.e. with the connection check function added, works like a charm on my server. Invoke-sqlcmd appears no longer to understand the semicolon command terminator ("Incorrect syntax near ';' "). Could sth have gone wrong with the quotes/double quotes in the copy process?
I posted the versions I just used below, perhaps you can give it another try?
function CheckServerConnection {
Try {
Invoke-Sqlcmd -ServerInstance ($Server.Name) -Query "SELECT @@SERVERNAME" -ConnectionTimeout 1 -ErrorAction SilentlyContinue
return 1
}
Catch {
"ERROR: SQL connection to server '" +$Server.Name+ "' failed; server will be skipped."
return -1
}
}
ForEach ($Server in $ServerList | where {$_.Server_Name -ne ''}) {
$ConnectionCheck = CheckServerConnection "$Server.Server_Name"
if ($ConnectionCheck -eq 1) {
$SQL = "SELECT '" +$Server.Server_Name+ "' AS 'Server_Name', [name] AS 'Database_Name' FROM master.sys.databases WHERE state = 0 ;"
$ServerDBList += Invoke-Sqlcmd –ServerInstance $Server.Server_Name –Query "$SQL"
}
}
April 14, 2015 at 8:56 am
I'm still receiving the same error, but I'm able to get what I needed from the script. I'll keep working at it. Thanks for your assistance with this and the excellent script.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply