Database Space Capacity Planning

  • graeme.black (10/20/2009)


    cmille19 (10/20/2009)


    graeme.black (10/20/2009)


    The error message I get is

    still get the following error

    Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa

    ce_lku'."

    At C:\BIN\Write-DbSpaceToDb.ps1:27 char:19

    + [void]$da.fill( <<<< $dt)

    I am running

    C:\BIN>Write-DbSpaceToDb.ps1 "IEOAK-SQL2005-B" "spacedb"

    If you log into IEOAK-SQL2005-B with SQL Server Management Studio, does this T-SQL statement complete successfully?

    USE spacedb;

    SELECT * FROM server_space_lku

    yes i get IEOAK-SQL2005-B

  • Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters

    ./test.ps1 IEOAK-SQL2005-B spacedb

    If successful you should see the list of servers from the server_space_lku table.

    param($destServer, $destdb)

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

    function Get-SqlData

    {

    param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),

    [string]$query=$(throw 'query is required.'))

    Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

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

    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)

    $dt = New-Object "System.Data.DataTable"

    [void]$da.fill($dt)

    $dt

    } #Get-SqlData

    Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"

  • cmille19 (10/20/2009)


    Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters

    ./test.ps1 IEOAK-SQL2005-B spacedb

    If successful you should see the list of servers from the server_space_lku table.

    param($destServer, $destdb)

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

    function Get-SqlData

    {

    param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),

    [string]$query=$(throw 'query is required.'))

    Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

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

    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)

    $dt = New-Object "System.Data.DataTable"

    [void]$da.fill($dt)

    $dt

    } #Get-SqlData

    Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"

    I am getting the same error

    G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm

    Security Warning

    Run only scripts that you trust. While scripts from the Internet can be useful,

    this script can potentially harm your computer. Do you want to run

    G:\powershell\test.ps1?

    [D] Do not run [R] Run once Suspend [?] Help (default is "D"): R

    Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa

    ce_lku'."

    At G:\powershell\test.ps1:14 char:15

    + [void]$da.fill( <<<< $dt)

    could i have the wrong version of powershell or .net?

  • graeme.black (10/20/2009)


    cmille19 (10/20/2009)


    Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters

    ./test.ps1 IEOAK-SQL2005-B spacedb

    If successful you should see the list of servers from the server_space_lku table.

    param($destServer, $destdb)

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

    function Get-SqlData

    {

    param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),

    [string]$query=$(throw 'query is required.'))

    Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

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

    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)

    $dt = New-Object "System.Data.DataTable"

    [void]$da.fill($dt)

    $dt

    } #Get-SqlData

    Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"

    I am getting the same error

    G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm

    Security Warning

    Run only scripts that you trust. While scripts from the Internet can be useful,

    this script can potentially harm your computer. Do you want to run

    G:\powershell\test.ps1?

    [D] Do not run [R] Run once Suspend [?] Help (default is "D"): R

    Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa

    ce_lku'."

    At G:\powershell\test.ps1:14 char:15

    + [void]$da.fill( <<<< $dt)

    could i have the wrong version of powershell or .net?

    It looks like your execution policy is set to allsigned or restricted. Run this command to set the execution policy to remote signed and try executing the test.ps1 script again:

    set-executionpolicy RemoteSigned

  • cmille19 (10/20/2009)


    graeme.black (10/20/2009)


    cmille19 (10/20/2009)


    Let's try just running portions of the script to see if we can't find a problem. Save this PowerShell code to a script file (for example test.ps1). Then execute the script with the parameters

    ./test.ps1 IEOAK-SQL2005-B spacedb

    If successful you should see the list of servers from the server_space_lku table.

    param($destServer, $destdb)

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

    function Get-SqlData

    {

    param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),

    [string]$query=$(throw 'query is required.'))

    Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

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

    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)

    $dt = New-Object "System.Data.DataTable"

    [void]$da.fill($dt)

    $dt

    } #Get-SqlData

    Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku"

    I am getting the same error

    G:\powershell>test.ps1 IEOAK-SQL2005-B spacedm

    Security Warning

    Run only scripts that you trust. While scripts from the Internet can be useful,

    this script can potentially harm your computer. Do you want to run

    G:\powershell\test.ps1?

    [D] Do not run [R] Run once Suspend [?] Help (default is "D"): R

    Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa

    ce_lku'."

    At G:\powershell\test.ps1:14 char:15

    + [void]$da.fill( <<<< $dt)

    could i have the wrong version of powershell or .net?

    It looks like your execution policy is set to allsigned or restricted. Run this command to set the execution policy to remote signed and try executing the test.ps1 script again:

    set-executionpolicy RemoteSigned

    PS C:\WINDOWS\system32\windowspowershell\v1.0> set-executionpolicy RemoteSigned

    PS C:\WINDOWS\system32\windowspowershell\v1.0> G:\powershell\test.ps1

    File G:\powershell\test.ps1 cannot be loaded. The file G:\powershell\test.ps1 i

    s not digitally signed. The script will not execute on the system. Please see "

    get-help about_signing" for more details..

    At line:1 char:22

    + G:\powershell\test.ps1 <<<<

  • What's the output of this command?

    get-executionpolicy

  • Hi,

    I get the following error when the job step Write-VolToDb runs:

    "The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 38 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'. Process Exit Code 0. The step succeeded."

    Therefore I see that the db_space table is populated but vol_space is not.

    Looks like there is a class name missing after Get-WmiObject. I am running this on XP SP3. Any suggestions?

  • Win32_Volume class isn't available on XP. It is on Server 2003 and Vista and above. Change the vol script o use Win32_LogicalDisk instead.

  • Hi,

    When executing the powershell script for Write-DbSpaceToDb.ps1 i get the following error :

    PS SQLSERVER:\SQL\JHB-BARTUSD\DEFAULT> param($destServer, $destdb)

    The term 'param' is not recognized as a cmdlet, function, operable program, or

    script file. Verify the term and try again.

    At line:1 char:6

    + param( <<<< $destServer, $destdb)

    Please help.

  • bartusp (11/20/2009)


    Hi,

    When executing the powershell script for Write-DbSpaceToDb.ps1 i get the following error :

    PS SQLSERVER:\SQL\JHB-BARTUSD\DEFAULT> param($destServer, $destdb)

    The term 'param' is not recognized as a cmdlet, function, operable program, or

    script file. Verify the term and try again.

    At line:1 char:6

    + param( <<<< $destServer, $destdb)

    Please help.

    You need to execute the script as follows:

    ./Write-DbSpaceToDb.ps1 ' Z002\SQL2K8' spacedm

    Replacing Z002\SQL2K8 with your server name. Also you need to navigate to the filesystem directory or fully qualify the script name:

    C:\bin\Write-DbSpaceToDb.ps1 ' Z002\SQL2K8' spacedm

  • Thanks a lot for this post, great stuff.

    Not sure if graeme.black managed to resolve the error about "invalid object server_space_lku", but I found the cause when I had the same issue. I had to prefix the table with the schema name in the 2 powershell scripts to get it to work, e.g. capplan.server_space_lku. Most installatoions would default to dbo.server_space_lku, might be worth a try for anyone having this same issue.

    Hope this helps

  • I have made changes to both the scripts and reflected correct servername\instancename (' VMSTDVRD001\SQL01'). The "Space Collector" job completes successfully but I do now see any rows when I issue queries. Any help will be highly appreciated.

    Job completes successfully with the folowing:

    Date 9/2/2010 10:34:30 AM

    Log Job History (Space Collector)

    Step ID 2

    Server VMSTDVRD001\SQL01

    Job Name Space Collector

    Step Name Write-DbSpaceToDb

    Duration 00:00:16

    Sql Severity 0

    Sql Message ID 0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    Executed as user: VMSTDVRD001\SYSTEM. The step did not generate any output. Process Exit Code 0. The step succeeded.

    The following queries do not gvie me any rows:

    SELECT d1.server_name, d1.dbname, d1.physical_name, d1.dt, d1.size_mb, d1.free_mb,

    ROUND(CAST(d1.free_mb AS float)/d1.size_mb * 100,2) AS percent_free,

    (d1.size_mb - d1.free_mb) AS allocate_mb,

    (d1.size_mb - d1.free_mb) - (d2.size_mb -d2.free_mb) AS daily_growth_mb

    FROM dbo.db_space d1

    JOIN dbo.db_space d2

    ON d1.server_name = d2.server_name

    AND d1.dbname = d2.dbname

    AND d1.physical_name = d2.physical_name

    AND d2.dt = DATEADD(dd,-1,d1.dt)

    WHERE d1.size_mb > 0

    SELECT v1.server_name, v1.vol_name, v1.dt, v1.vol_lbl, v1.size_gb, v1.free_gb, v1.percent_free,

    (v1.size_gb * .8) AS usable_size_gb, (v1.size_gb - v1.free_gb) AS allocated_gb,

    (v1.size_gb - v1.free_gb) - (v2.size_gb -v2.free_gb) AS daily_growth_gb

    FROM dbo.vol_space v1

    JOIN dbo.vol_space v2

    ON v1.server_name = v2.server_name

    AND v1.vol_name = v2.vol_name

    AND v2.dt = DATEADD(dd,-1,v1.dt)

    execute dbo.db_space_capacity_sp '1/1/2010', '12/31/2010'

  • The capacity report/query calculates an average growth rate over a given period, so in order for the query to return results you must have at least two days of collected data. The more data collected, the more likely the average will be accurate.

    Do you have rows in dbo.server_space_lku and dbo.vol_space?

  • I am getting this error:

    A job step received an error at line 83 in a PowerShell script. The corresponding line is 'foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY KEY constraint 'PK_db_space'. Cannot insert duplicate key in object 'dbo.db_space'. The duplicate key value is (****\***, _DB, R:\DB.mdf, Feb 10 2011 12:00AM). The statement has been terminated." Violation of PRIMARY KEY constraint 'PK_db_space'. Cannot insert duplicate key in object 'dbo.db_space'. The duplicate key value is (****\***, DB, R:\DB.mdf, Feb 10 2011 12:00AM). The statement has been terminated. '. Process Exit Code -1. The step failed.

    I assume this is because we cannot run this more than once on a day. But what if we added a new instance to monitor. Is there a way to rather replace any existing data so that the job continues.

  • Hi, fantastic article, but like some of the others I to am getting the following error:

    Exception calling "Fill" with "1" argument(s): "A network-related or instance-s

    pecific error occurred while establishing a connection to SQL Server. The serve

    r was not found or was not accessible. Verify that the instance name is correct

    and that SQL Server is configured to allow remote connections. (provider: Name

    d Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

    At E:\dbspace\test.ps1:14 char:15

    + [void]$da.fill( <<<< $dt)

    I have tracked this down to the line :

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

    in the Get-SqlData function.

    If I replace @servername with a hardcoded server everything works. The hardcoded value is the same as the value in the lookup table. My powershell isnt good enough to know what to try next.

    For example, I see there is a verbose logging there, but what is the flag to show verbose tracing?

    The odd thing is the function Get-SqlData works fine in the first place to get the list of servers. The only server in the lookup table is the server the script is running on.

Viewing 15 posts - 46 through 60 (of 94 total)

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