Monitor Database Growth and Usage

  • Willem G

    SSC Eights!

    Points: 809

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

  • gordon.feeney

    SSC Enthusiast

    Points: 186

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

    Gordon.

  • dastagiri16

    Hall of Fame

    Points: 3392

    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

  • Willem G

    SSC Eights!

    Points: 809

    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?

  • Willem G

    SSC Eights!

    Points: 809

    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)

  • gordon.feeney

    SSC Enthusiast

    Points: 186

    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.

  • Willem G

    SSC Eights!

    Points: 809

    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

  • dastagiri16

    Hall of Fame

    Points: 3392

    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...

  • dastagiri16

    Hall of Fame

    Points: 3392

    William,

    Please advice..

  • WAL

    Mr or Mrs. 500

    Points: 594

    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.

  • Willem G

    SSC Eights!

    Points: 809

    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.

  • Willem G

    SSC Eights!

    Points: 809

    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.

  • Hommer

    Mr or Mrs. 500

    Points: 530

    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.

  • Willem G

    SSC Eights!

    Points: 809

    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.

  • quackhandle1975

    SSChampion

    Points: 11055

    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 31 total)

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