Database Space Capacity Planning

  • graeme.black

    SSC Veteran

    Points: 272

    I have tried

    IEOAK-SQL2005-B & IEOAK-SQL2005-B\MSSQLSERVER

  • cmille19

    SSCertifiable

    Points: 5950

    Correct code for database space, (thanks Birdmaster777):

    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=

    'USE [?]; 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

  • cmille19

    SSCertifiable

    Points: 5950

    graeme.black (10/15/2009)


    I have tried

    IEOAK-SQL2005-B & IEOAK-SQL2005-B\MSSQLSERVER

    What is the Powershell commmand you are running and what are contents of the server_space_lku table?

  • 127.0.0.1

    SSC Enthusiast

    Points: 122

    Hello,

    First, many thanks for your contribution

    I have the same problem than Grasshoper. (free_mb is null)

    But if I execute :

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

    f.filename AS physical_name,

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

    g.groupname,

    CAST (size*8/1024 AS int) AS 'size_mb',

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

    FROM [a database]..sysfiles f

    JOIN [a database]..sysfilegroups g

    ON f.groupid = g.groupid

    it's ok and returns a value.

    It is really strange.

    Another problem. I try to execute Powershell script, on another server which contains databases (who have "-" (minus) in their name and it failed.

    Thanks in advance,

  • cmille19

    SSCertifiable

    Points: 5950

    127.0.0.1 (10/15/2009)


    Hello,

    First, many thanks for your contribution

    I have the same problem than Grasshoper. (free_mb is null)

    But if I execute :

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

    f.filename AS physical_name,

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

    g.groupname,

    CAST (size*8/1024 AS int) AS 'size_mb',

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

    FROM [a database]..sysfiles f

    JOIN [a database]..sysfilegroups g

    ON f.groupid = g.groupid

    it's ok and returns a value.

    It is really strange.

    Another problem. I try to execute Powershell script, on another server which contains databases (who have "-" (minus) in their name and it failed.

    Thanks in advance,

    The problem is the call to FILEPROPERTY system function only works for the database your are currently in (my fault). The minus symbol issue can be addressed by using brackets around the database name. I haven't hit this issue in my environment, but I can see how it can cause a problem. I'll post an update to the Write-DbSpaceToDb.ps1 Powershell for both of these issues, sometime today.

  • 127.0.0.1

    SSC Enthusiast

    Points: 122

    Thank you very much !! ^^ It works perfectly now for "free_mb"

    [/quote]

    The problem is the call to FILEPROPERTY system function only works for the database your are currently in (my fault). The minus symbol issue can be addressed by using brackets around the database name. I haven't hit this issue in my environment, but I can see how it can cause a problem. I'll post an update to the Write-DbSpaceToDb.ps1 Powershell for both of these issues, sometime today.[/quote]

  • graeme.black

    SSC Veteran

    Points: 272

    The command I am running is

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

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

    I have tried different combinations, i have attached a picture of the table

  • cmille19

    SSCertifiable

    Points: 5950

    Attached fixed download. Since the publication of article with your help I've addressed these issues:

    Missing procedure creation statements from download

    SQL Agent job missing parameters for PowerShell script

    Incorrect caluculation of of free mb in PowerShell script

    Special characters in database name causes PowerShell script to fail

    I've republished the download in the article, but it may take several days to show up. The attachment below is the latest as of 10/15/2009.

  • cmille19

    SSCertifiable

    Points: 5950

    graeme.black (10/15/2009)


    The command I am running is

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

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

    I have tried different combinations, i have attached a picture of the table

    Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?

  • 127.0.0.1

    SSC Enthusiast

    Points: 122

    cmille19 (10/15/2009)


    Attached fixed download. Since the publication of article with your help I've addressed these issues:

    Missing procedure creation statements from download

    SQL Agent job missing parameters for PowerShell script

    Incorrect caluculation of of free mb in PowerShell script

    Special characters in database name causes PowerShell script to fail

    I've republished the download in the article, but it may take several days to show up. The attachment below is the latest as of 10/15/2009.

    Thanks for fixed sources !

  • graeme.black

    SSC Veteran

    Points: 272

    graeme.black (10/15/2009)

    --------------------------------------------------------------------------------

    The command I am running is

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

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

    I have tried different combinations, i have attached a picture of the table

    Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?

    Yes IEOAK-2005-B has the backend repository, i have this in IEOAK-SQL2005-B\MSSQLSERVER "server_space_lku " I have also tried IEOAK-SQL2005-B

    Remote connections for windows authentication is also enabled

  • cmille19

    SSCertifiable

    Points: 5950

    graeme.black (10/16/2009)


    graeme.black (10/15/2009)

    --------------------------------------------------------------------------------

    The command I am running is

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

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

    I have tried different combinations, i have attached a picture of the table

    Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?

    Yes IEOAK-2005-B has the backend repository, i have this in IEOAK-SQL2005-B\MSSQLSERVER "server_space_lku " I have also tried IEOAK-SQL2005-B

    Remote connections for windows authentication is also enabled

    I don't see the picture of your table. For a default instance you should definitely use IEOAK-SQL2005-B and not IEOAK-SQL2005-B\MSSQLSERVER. The PowerShell code makes an ADO.NET connection to the SQL Server so the name you specify is not different than what you would use to connect in SSMS or an ADO.NET connection string.

    What's the error you're getting?

  • graeme.black

    SSC Veteran

    Points: 272

    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"

  • cmille19

    SSCertifiable

    Points: 5950

    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

  • graeme.black

    SSC Veteran

    Points: 272

    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

Viewing 15 posts - 31 through 45 (of 93 total)

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