Database Space Capacity Planning

  • vinodkn

    SSC Journeyman

    Points: 95

    Look like very usefull stuff, thanks but cant try with out spacedm.procedures.sql

    can you please copy paste the procedures in the article itself.

    I tried downloading it from following link too but cant find spacedm.procedures.sql there also 🙁

    http://ddi59g.blu.livefilestore.com/y1p-lPWTbCLIn7R0-1VuM3g9iuCYpwwooYuPY8QPoGLu-hUq99CpAqc0CrHP53p_NjyRU8ZXBb2vtuY8lIfu7Oecg/SpaceAnalysis.zip?

  • graeme.black

    SSC Veteran

    Points: 272

    Hi

    I am stepping thorugh the various steps, but when I try to launch the powershell scripts, I get the following error

    C:\BIN>WRITE-DBSPACETODB.PS1

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

    ce_lku'."

    At C:\BIN\Write-DbSpaceToDb.ps1:13 char:15

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

    I have the following entry in 'Server_space)lku'

    servname\instance

  • cmille19

    SSCertifiable

    Points: 5920

    vinodkn (10/14/2009)


    Look like very usefull stuff, thanks but cant try with out spacedm.procedures.sql

    can you please copy paste the procedures in the article itself.

    I tried downloading it from following link too but cant find spacedm.procedures.sql there also 🙁

    http://ddi59g.blu.livefilestore.com/y1p-lPWTbCLIn7R0-1VuM3g9iuCYpwwooYuPY8QPoGLu-hUq99CpAqc0CrHP53p_NjyRU8ZXBb2vtuY8lIfu7Oecg/SpaceAnalysis.zip?

    My apologies, somehow I've managed not to include the stored procedure creation statements yet again. I'll fix the download in the article and provide a script just for the procedure creation statements later this evening.

  • cmille19

    SSCertifiable

    Points: 5920

    graeme.black (10/14/2009)


    Hi

    I am stepping thorugh the various steps, but when I try to launch the powershell scripts, I get the following error

    C:\BIN>WRITE-DBSPACETODB.PS1

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

    ce_lku'."

    At C:\BIN\Write-DbSpaceToDb.ps1:13 char:15

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

    I have the following entry in 'Server_space)lku'

    servname\instance

    You need specifiy both a server name and database name, where server name is the SQL Server instance where your spacedm database is located and database is your space dm database. The script should be called like this:

    Write-dbspacetodb.ps1 Z002\SQL2K8 spacedm

  • cmille19

    SSCertifiable

    Points: 5920

    Link to the stored procedure creation statements. I will update the zip file included with the article, but this may take a few days to show up.

  • vinodkn

    SSC Journeyman

    Points: 95

    thank you very much !!!

  • Birdmaster777

    Valued Member

    Points: 68

    Hello

    I have strange results from the powershell scripts.

    The scripts run successfully, however the Free_MB column only displays Null values in exception of the master database.

    The powershell script is executed with an windows account with full DB access so I am not sure why this is happening? Any ideas?

    😀

  • vinodkn

    SSC Journeyman

    Points: 95

    need help, Sorry not asking you to troubleshoot the issue but just tell me where i am going wrong, ( I am very much interested in this spacedm solution )

    How will you connect If the DB is installed on default instance ?

    for example

    Server name is REP

    used default instance and installed DB name called XXX

    created spacedm DB

    if i run the following

    Write-DbSpaceToDb.ps1 rep\XXX spacedm

    PS E:\SpaceAnalysis\SpaceAnalysis> .\Write-DbSpaceToDb.ps1 rep\XXX spacedm

    Exception calling "Fill" with "1" argument(s): "An error has occurred while establishing a connection to the server. W

    hen connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server do

    es not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specifie

    d)"

    At E:\SpaceAnalysis\SpaceAnalysis\Write-DbSpaceToDb.ps1:27 char:19

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

    I also tried following but nothing worked. 🙁

    PS E:\SpaceAnalysis\SpaceAnalysis> .\Write-DbSpaceToDb.ps1 XXX spacedm

    PS E:\SpaceAnalysis\SpaceAnalysis> .\Write-DbSpaceToDb.ps1 .\XXX spacedm

    PS E:\SpaceAnalysis\SpaceAnalysis> .\Write-DbSpaceToDb.ps1 rep\. spacedm

    remote connections already enabled

  • Birdmaster777

    Valued Member

    Points: 68

    you just need to do the following

    Write-DbSpaceToDb.ps1 'rep' spacedm

  • graeme.black

    SSC Veteran

    Points: 272

    My server name is IEOAK-2005-B, and I have a default instance installed

    when i run

    C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B' spacedm

    I 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)

    in the field

    server_space_lku I have and IEOAK-SQL2005-B

  • vinodkn

    SSC Journeyman

    Points: 95

    Birdmaster777, It was quick, thank you.

    But some have it didn't work 🙁

  • Birdmaster777

    Valued Member

    Points: 68

    what is the contents of you table server_space_lku ?

  • vinodkn

    SSC Journeyman

    Points: 95

    looks like following command completed without error

    PS E:\SpaceAnalysis\SpaceAnalysis> .\Write-DbSpaceToDb.ps1 "rep" "spacedm"

  • cmille19

    SSCertifiable

    Points: 5920

    Birdmaster777 (10/15/2009)


    Hello

    I have strange results from the powershell scripts.

    The scripts run successfully, however the Free_MB column only displays Null values in exception of the master database.

    The powershell script is executed with an windows account with full DB access so I am not sure why this is happening? Any ideas?

    😀

    What if you run the query executed by the PowerShell script in SSMS. What results do you see?

    CREATE TABLE #output(

    server_name varchar(128),

    dbname varchar(128),

    physical_name varchar(260),

    dt datetime,

    file_group_name varchar(128),

    size_mb int,

    free_mb int)

    exec sp_MSforeachdb @command1=

    'INSERT #output

    SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server_name,

    ''?'' AS dbname,

    f.filename AS physical_name,

    CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,

    g.groupname,

    CAST (size*8.0/1024.0 AS int) AS ''size_mb'',

    CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS ''free_mb''

    FROM ?..sysfiles f

    JOIN ?..sysfilegroups g

    ON f.groupid = g.groupid'

    SELECT * FROM #output

  • Birdmaster777

    Valued Member

    Points: 68

    ive just ran it on Query An using the master database.

    The output displays all the databases and values execept the last column which it has set to null, the only exception is the master database which is poplulated correctly

Viewing 15 posts - 16 through 30 (of 93 total)

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