Post reply

Monitor Database Growth and Usage

  • December 3, 2014 at 11:15 pm

    #295645

    Comments posted to this topic are about the item Monitor Database Growth and Usage

  • December 4, 2014 at 2:15 am

    #1762619

    Hi, I'm getting an 'unexpected token' error when trying to specify a named instance of SQL Server on line 64.

    Gordon.

  • December 4, 2014 at 2:25 am

    #1762622

    i am not able to configured it in my server..please give simple steps one by one to execute

    I have followed like...

    I have executed script in my server (cre_sp_Mail_DB_Sizediffs.sql) under MonitorDB after changing the recipient and profilername.

    next created job like please find the attachment after i ran the job it is not executing

  • December 4, 2014 at 2:45 am

    #1762631

    gordon.feeney (12/4/2014)

    Hi, I'm getting an 'unexpected token' error when trying to specify a named instance of SQL Server on line 64.

    Gordon.

    Hi,

    I take it you mean you specified a named server as Monitor Server?

    I should have specified you need to quote the string if you specify a named server there, so "server\instance". Could you check that please?

  • December 4, 2014 at 3:29 am

    #1762644

    dastagiri16 (12/4/2014)

    i am not able to configured it in my server..please give simple steps one by one to execute

    I have followed like...

    I have executed script in my server (cre_sp_Mail_DB_Sizediffs.sql) under MonitorDB after changing the recipient and profilername.

    next created job like please find the attachment after i ran the job it is not executing

    Your command line should start with "Powershell.exe", not "windows type: Powershell.exe" (the first two words including the colon are part of the text)

  • December 4, 2014 at 3:42 am

    #1762647

    Thanks Willem. Changed line 64 to $SqlHost = "(local)\<myserver>" and that worked. Getting a problem connecting via sqlcmd now but that's a a problem for me to solve 🙂

    Gordon.

  • December 4, 2014 at 3:50 am

    #1762650

    gordon.feeney (12/4/2014)

    Thanks Willem. Changed line 64 to $SqlHost = "(local)\<myserver>" and that worked. Getting a problem connecting via sqlcmd now but that's a a problem for me to solve 🙂

    Gordon.

    Good to hear Gordon! Let me know if you run into further problems, testing on one's own systems is by definition limited...

    Willem

  • December 4, 2014 at 10:08 pm

    #1762860

    MY SCRIPT will not give any result after my change ..my job status still running how much time it will take to complete..

    attached my script

    the extension should be .ps1 or if any..

    i changed it to .txt then the job ran sucessfull but i did not get either any table in my db or not received any notification...

  • December 5, 2014 at 7:36 am

    #1762962

    William,

    Please advice..

  • December 5, 2014 at 9:56 am

    #1763016

    Hi,

    I like the article, but there is a drawback in your solution. There is no proper error handling in powershell and related sql job step. If monitoring server fails to connect to one of the monitored servers then nothing will be reported, correct?!

    Cheers,

    Val.

  • December 5, 2014 at 10:21 am

    #1763027

    Val Urban (12/5/2014)

    Hi,

    I like the article, but there is a drawback in your solution. There is no proper error handling in powershell and related sql job step. If monitoring server fails to connect to one of the monitored servers then nothing will be reported, correct?!

    Cheers,

    Val.

    Hi Val,

    You are quite right, there is that limitation. In this article, I just wanted to show how recording database device metrics can be done. The previous article I published (Verify SQL Backup Integrity) contains a PowerShell script (SQLRestorer.ps1) in which most steps are indeed subject to error handling.

    If you like my script, by all means feel free to copy some of the error handling from the script in my previous article into this one, or script some yourself and integrate that into the current script.

  • December 5, 2014 at 10:28 am

    #1763029

    dastagiri16 (12/4/2014)

    MY SCRIPT will not give any result after my change ..my job status still running how much time it will take to complete..

    attached my script

    the extension should be .ps1 or if any..

    i changed it to .txt then the job ran sucessfull but i did not get either any table in my db or not received any notification...

    Before trying to get the script executed as a job, please can you indicate whether it runs successfully from the PowerShell command line, that should be the first step to try.

  • December 5, 2014 at 1:42 pm

    #1763063

    Hi,

    I like the script but...

    I like it as it replaced my broken ps which was fine with sql2008R2 but failed on sql2012. Also it runs flawlessly when my servers sitting across different AD domains, which was also a challenge.

    Now the but part, the data it collected are not that useful. It only let you compare used vs. allocated, rather than say to the actual drive free space. So if a db is near its next auto growth limit, it will shows as around 100%.

    Nevertheless, it is a good starting point if only we want to modify its DMO SELECT in dynamic SQL.

  • December 5, 2014 at 2:12 pm

    #1763075

    Hommer (12/5/2014)

    Hi,

    I like the script but...

    I like it as it replaced my broken ps which was fine with sql2008R2 but failed on sql2012. Also it runs flawlessly when my servers sitting across different AD domains, which was also a challenge.

    Now the but part, the data it collected are not that useful. It only let you compare used vs. allocated, rather than say to the actual drive free space. So if a db is near its next auto growth limit, it will shows as around 100%.

    Nevertheless, it is a good starting point if only we want to modify its DMO SELECT in dynamic SQL.

    Thanks for your suggestions! I am sure the script could be extended to do what you suggest (e.g. by using 'Get-WMIObject' and selecting 'FreeSpace from Win32_LogicalDisk'), but I have not (yet) felt a need for that because in our shop free disk space monitoring and alerting are taken care of by SCOM.

  • January 18, 2015 at 5:32 am

    #1770988

    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

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply