none
how to check the exact size of DB in sql server 2008

    Question

  • I have deleted the all the data's from the DB but when I check the size still it is showing 13GB.

     

    can u please help me on this


    fuzailrashid
    Monday, April 18, 2011 5:36 AM

Answers

  • use [youtdatabase name]
    Go
    
    --if you don't want to count the log size then use:
    
    SELECT Sum(size/128) As CurrentDBSizeWithSpace_MB
    , Sum(size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) AS AvailableSpaceDB_MB
    , Sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) As ActualDataSizeINDB_MB
    FROM sys.database_files
    Where type_desc != 'LOG'
    
    
    --if you want to incluse log size then use:
    SELECT Sum(size/128) As CurrentDBSizeWithSpace_MB
    , Sum(size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) AS AvailableSpaceDB_MB
    , Sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) As ActualDataSizeINDB_MB
    FROM sys.database_files
    
    
    • Marked as answer by fuzailrashid Monday, April 18, 2011 6:06 AM
    Monday, April 18, 2011 5:46 AM

All replies

  • use [youtdatabase name]
    Go
    
    --if you don't want to count the log size then use:
    
    SELECT Sum(size/128) As CurrentDBSizeWithSpace_MB
    , Sum(size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) AS AvailableSpaceDB_MB
    , Sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) As ActualDataSizeINDB_MB
    FROM sys.database_files
    Where type_desc != 'LOG'
    
    
    --if you want to incluse log size then use:
    SELECT Sum(size/128) As CurrentDBSizeWithSpace_MB
    , Sum(size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) AS AvailableSpaceDB_MB
    , Sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) As ActualDataSizeINDB_MB
    FROM sys.database_files
    
    
    • Marked as answer by fuzailrashid Monday, April 18, 2011 6:06 AM
    Monday, April 18, 2011 5:46 AM
  • Did you perform shrink operation? SQL won't release the space to OS operating system. Also what is the recovery model of database?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 5:47 AM
    Moderator
  • I have not performed any shrink operation.Full recovery model is used.

    can u help me in how to DB shrink


    fuzailrashid
    Monday, April 18, 2011 6:06 AM