How to find allocated space and used space

  • Hello Team,

    How to find allocated space and used space for all databases in sqlserver instance ?

    Is there any query.

    Thanks in advance

  • Google it sp_spaceused.

  • You can see this in Management Studio.

    1. Click on "Databases" in Object Explorer

    2. Select menu item "View -> Object Explorer Details" - a table is displayed showing all databases and few properties, including Size (MB)

    3. Right click on one of the column headings and make sure "Space Available (KB)" is checked. You can uncheck other columns if you don't need them

    Voila

  • DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = REPLACE(

    CAST(

    (

    SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

    'SELECT ' + CHAR(13) + CHAR(10) +

    'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +

    'a.FILEID, ' + CHAR(13) + CHAR(10) +

    '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +

    'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)

    FROM sys.databases

    FOR XML PATH('')

    )

    AS NVARCHAR(MAX)

    ),

    '& # x 0 D ;',CHAR(13) + CHAR(10)

    )

    --SELECT @SQL

    EXECUTE sp_executesql @SQL

    This loops through all DB's pulling out the free space, used space, total space per file.

    Just remove the spaces between '& # x 0 D ;'

  • Thanks anthony

    I have removed '& # x 0 D ;' in script it is not working

    Can you please give me modified script

  • RK2012 (12/18/2012)


    Can you please give me modified script

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = REPLACE(

    CAST(

    (

    SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

    'SELECT ' + CHAR(13) + CHAR(10) +

    'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +

    'a.FILEID, ' + CHAR(13) + CHAR(10) +

    '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +

    'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)

    FROM sys.databases

    FOR XML PATH('')

    )

    AS NVARCHAR(MAX)

    ),

    ' ',CHAR(13) + CHAR(10)

    )

    print @SQL

    EXECUTE sp_executesql @SQL

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • don't remove the whole thing, just remove the spaces.

    the string of characters is special and they are removed from posts hence why I need to put spaces in so that you can see the full script

    '& # x 0 D ;'

    '&#x 0D;'

    Just remove the space between the x and the 0

  • For database log file space stats,you can run " dbcc sqlperf ".

    dbcc sqlperf('LOGSPACE')

  • Hello Team,

    I am using below query to get size_mb and unused space in database

    Is there any way to get instance level or report ?

    Query:

    select

    a.FILEID,

    [FILE_SIZE_MB] =

    convert(decimal(12,2),round(a.size/128.000,2)),

    [SPACE_USED_MB] =

    convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),

    [FREE_SPACE_MB] =

    convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,

    NAME = left(a.NAME,15),

    FILENAME = left(a.FILENAME,30)

    from

    dbo.sysfiles a

    Result

    ---------

    Field id FILE_SIZE_MB Space_used_mb free_space_mb name filename

    14.00 2.94 1.06 masterC:\Program Files (x86)\Microso

    21.25 0.64 0.61 mastlogC:\Program Files (x86)\Microso

    Kindly help me

    Thanks in advance

  • There is a script here to help you with that.

    http://jasonbrimhall.info/2012/07/17/a-trio-of-eachdb/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 10 posts - 1 through 9 (of 9 total)

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