Post reply

How to get the Un Allocated Space in Sql 2005 ?

  • September 29, 2011 at 2:47 am

    #257999

    I want to get the Unallocated Space in Sql 2005 ?

    Thanks

    Kumar

  • September 29, 2011 at 10:37 am

    #1388506

    sp_spaceused can help

  • September 30, 2011 at 8:55 pm

    #1389070

    You could refer sys.database_files or sysfiles.

    for current db(2000):

    select RTRIM(CONVERT(VARCHAR(50),sf.name)) [filname],

    [filesize]=(sf.size/128),

    [maxsize]=(sf.maxsize/128),

    fileproperty(sf.name,'SpaceUsed')/128 AS SpaceusedMB

    ,(fileproperty(sf.name,'SpaceUsed')*100)/(sf .size) as PercernUtilization

    from sysfiles sf

    for current db(2005,2008):

    select RTRIM(CONVERT(VARCHAR(50),sf.name)) [filename],

    [filesize]=(sf.size/128),

    [maxsizeMB]=(sf.max_size/128),

    fileproperty(sf.name,'SpaceUsed')/128 AS SpaceusedMB

    ,(fileproperty(sf.name,'SpaceUsed')*100)/(sf .size) as PercernUtilization

    from sys.database_files sf

    for all dbs on a single sql instance:

    DECLARE @databasespace TABLE

    ( SQLServerName VARCHAR(100), DatabaseName VARCHAR(100)

    , FileSizeMB INT, FreeSpaceMB INT, FreeSpacePct VARCHAR(7)

    , CaptureDate datetime, MaxSizeMB INT, LogicalFileName sysname

    , PhysicalFileName NVARCHAR(520), Status sysname

    , Updateability sysname, RecoveryMode sysname )

    DECLARE @space_query VARCHAR(5000)

    SELECT @space_query = 'Use [' + '?' + '] SELECT @@servername as SQLServerName, '

    + '''' + '?' + '''' + ' AS DatabaseName, CAST(sys.database_files.size/128.0 AS int) AS FileSize, CAST(sys.database_files.size/128.0 - CAST(FILEPROPERTY(sys.database_files.name, ' + '''' + 'SpaceUsed' + ''''

    + ' ) AS int)/128.0 AS int) AS FreeSpaceMB, CAST(100 * (CAST (((sys.database_files.size/128.0 -CAST(FILEPROPERTY(sys.database_files.name, ' + '''' + 'SpaceUsed' + ''''

    + ' ) AS int)/128.0)/(sys.database_files.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + ''''

    + '%' + '''' + ' AS FreeSpacePct, GETDATE() as CaptureDate, CAST(sys.database_files.max_size/128.0 AS int) AS MaxSizeMB, sys.database_files.name AS LogicalFileName, sys.database_files.physical_name AS PhysicalFileName, CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode FROM sys.database_files'

    INSERT INTO @databasespace (SQLServerName, DatabaseName, FileSizeMB, FreeSpaceMB, FreeSpacePct, CaptureDate, MaxSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode )

    EXEC sp_MSForEachDB @space_query

    SELECT SQLServerName, DatabaseName, FileSizeMB, FreeSpaceMB, FreeSpacePct, CaptureDate, MaxSizeMB, LogicalFileName, PhysicalFileName, Status, Updateability, RecoveryMode FROM @databasespace ORDER BY SQLServerName, DatabaseName

Viewing 3 posts - 1 through 2 (of 2 total)

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