Database Space Capacity Planning

  • slk55guy

    SSC Veteran

    Points: 260

    Yeah throughout the debugging, the @dt variable never appeared to have a value assigned. All other variables were populated, including server name etc

  • slk55guy

    SSC Veteran

    Points: 260

    I have narrowed it down. Removing the Primary Key from the table vol_space and allowing NULLS on the server_name field inserts records but without a server name. Somewhere the server\computer name is not being populated, maybe in the Out-DataTable funtion. The column names picked and added to the data table are:

    FreeGb

    Label

    Name

    PercentFree

    SizeGB

    SystemName ?

    UsageDT

    SystemName appears to be the culprit?

  • cmille19

    SSCertifiable

    Points: 5950

    Not sure why SystemName would be null. Let's try forcing the systemname. Replace the existing Get-Vol function with this version:

    #######################

    function Get-Vol

    {

    param($computerName)

    Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" |

    foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

    add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Capacity/1GB),2))

    add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))

    add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Capacity) * 100),2))

    add-member -in $_ -MemberType noteproperty SystemName $ComputerName -force -PassThru } |

    select UsageDT, SystemName, Name, Label, SizeGB, FreeGB, PercentFree

    }# Get-Vol

  • slk55guy

    SSC Veteran

    Points: 260

    Yes, that has fixed it. Thank you. 🙂

    I wonder why this happened.

  • narayanaswamy

    SSC Enthusiast

    Points: 127

    Hi All,

    In SQL Server 2008 instance i have created spacedm database and created all objects tables,views,sp's

    I've executed this command prompt .\write-dbspacetodb.ps1 'instancename' spacedm

    I am getting below error from powershell. An exception calling "fill" with "1" can't open database spacedm. Login failed to open Spacedm database

    Please help me how to resolve this issue. I have created one login assigned db_owner to spacedm even then same issue.

  • cmille19

    SSCertifiable

    Points: 5950

    narayanaswamy (10/18/2012)


    Hi All,

    In SQL Server 2008 instance i have created spacedm database and created all objects tables,views,sp's

    I've executed this command prompt .\write-dbspacetodb.ps1 'instancename' spacedm

    I am getting below error from powershell. An exception calling "fill" with "1" can't open database spacedm. Login failed to open Spacedm database

    Please help me how to resolve this issue. I have created one login assigned db_owner to spacedm even then same issue.

    It seems like you're having trouble making a connection. Try just running a test connection:

    $serverName = 'instancename'

    $databasename = "spacedm"

    $connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

    $con = new-object System.Data.SqlClient.SqlConnection

    $con.ConnectionString = $connString

    $con.Open()

  • sqlfriend

    SSC Guru

    Points: 52463

    Thanks, Chad, this is one of the best scripts on internet I can find about space monitoring.

    I tried serveral before and compare them with this one, this is the cleanest and more powerfull code

    and implementation.

    Thanks much!

  • sqlfriend

    SSC Guru

    Points: 52463

    A question about permissions.

    When I schedule the job, because it will run using SQL agent service account, it won't work because it cannot login to other computers using that service account to check disk and db size.

    So I created a proxy account using my own credentials, for I am a dba, so I have access to those servers that I need to check.

    Is that the right approach to go? I mean for the account that run the SQL job - it has to be sysadmin on all those servers that need to be checked, correct?

    Thanks

  • cmille19

    SSCertifiable

    Points: 5950

    sqlfriends (1/24/2013)


    A question about permissions.

    When I schedule the job, because it will run using SQL agent service account, it won't work because it cannot login to other computers using that service account to check disk and db size.

    So I created a proxy account using my own credentials, for I am a dba, so I have access to those servers that I need to check.

    Is that the right approach to go? I mean for the account that run the SQL job - it has to be sysadmin on all those servers that need to be checked, correct?

    Thanks

    The way I run it--I use an account that has sysadmin rights on servers being collected. I'm not sure if less rights will work easily.

  • sqlfriend

    SSC Guru

    Points: 52463

    Thanks much.

    Does this job have to be scheduled to run daily? How about if I just want to collect the data once a week, or once a month?

    If so, do I need to change the view for the column of daily_growth_mb,

    because in the view it is like AND d2.dt = DATEADD(dd, - 1, d1.dt)

    If I don't change it, and I collect only once a week, then I think it will has no records. correct?

    Thanks

  • cmille19

    SSCertifiable

    Points: 5950

    I haven't looked at the code in a while, but I think you're right on the view.

  • slk55guy

    SSC Veteran

    Points: 260

    Can anyone tell me why the calculation for usable size is (v1.percent_free, v1.size_gb * .8 AS usable_size_gb).

    I know it's got to do with the initial disk formating and so forth but why the .8? Something to do with 8 bytes?

  • cmille19

    SSCertifiable

    Points: 5950

    An assumption around always keeping at least 20% free space on NTFS volumes.

  • i130895

    SSC Journeyman

    Points: 81

    I removed primary key from dbo.vol_space table because I was getting PK vialotion error. Now I am getting below error. Please help.

    ob Name Space Collector

    Step Name Write-VolToDb.ps1

    Duration 00:00:04

    Sql Severity 0

    Sql Message ID 0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    Executed as user:. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 51 in a PowerShell script. The corresponding line is ' Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" | '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invalid class ' A job step received an error at line 52 in a PowerShell script. The corresponding line is ' foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd")) '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot bind parameter because parameter 'InputObject' is specified more than once. To provide multiple values to parameters that can accept multiple values, use the array syntax. For example, "-parameter value1,value2,value3". '. Process Exit Code -1. The step failed.

  • SumonB

    SSC-Addicted

    Points: 490

    When I execute the Database Space Capacity Report, it's not showing all databases in all instances. Please Help.

Viewing 15 posts - 76 through 90 (of 93 total)

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