none
database size issue

    Question

  • What can be the reason for a database to have abnormal size.

     

    for example: i have a database with size 12 GB which is not in according to the amount of data it has (theoratically it should be around 3 GB). To verify this, i create another db and then import all the tables from first db to second, and as expected the second db size is about 2.5 GB.  What can be the reason for additional 9  - 10 GB in first db.

     

     

     

     

    Saturday, June 09, 2007 4:24 PM

All replies

  • It could be that the database data file has a lot of empty space in it. You can check in SSMS by going to Properties, Tasks, Shrink, Files, and check the available free space.

    You could also have heavily fragmented indexes that are taking up extra space.

    Saturday, June 09, 2007 5:09 PM
  • It could be spaced used for indexes, or white space within the files.

     

    Check sp_spaceused and see how much space it shows as being used.

     

    The white space is probably used for rebuilding indexes.

    Monday, June 11, 2007 1:27 AM
  • One of the possible reasons might be that the datafile is allocated to 12GB size check it out

     

    RegarDs,

    Jacx

    Monday, June 11, 2007 5:51 AM
  • Try this to see how much space you have within the database file(s):

     

    -- Individual File Size query

    SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'

    FROM sys.database_files;

    Monday, June 11, 2007 8:55 PM
  • thanks for the info

     

    the result of above query is

     

    filename        physical name      total size   available space

    a_Data         F:\a_Data.MDF   11411       3218.812500
    a_Log             F:\a_Log.LDF      1956        1935.687500

     

    and the result of sp_spaceused

     

     

    db name      db size           unallocated space

    abc               13367 mb         3216 mb

     

    reserved             data                    index_size          unused

    8391672 kb        7487104 kb      862912 kb          41656 kb

     

     

    so the reserved size seems huge: why its so and how to solve it ?

     

     

    Tuesday, June 12, 2007 11:55 AM
  • You probably have a lot of index fragmentation. You can try running this script to defragment all the indexes in that database. It will also call sp_UpdateStats.

     

    This should free up quite a bit of space inside the data file. If you want to, you can run a database shrink after that to make the data file smaller, but unless you are really worried about disk space, I would not bother doing that.

     

    -- Declare variables

    SET NOCOUNT ON

    DECLARE @tablename VARCHAR (128)

    DECLARE @execstr VARCHAR (255)

    DECLARE @objectid INT

    DECLARE @indexid INT

    DECLARE @frag DECIMAL

    DECLARE @maxfrag DECIMAL

    -- Decide on the maximum fragmentation to allow

    SELECT @maxfrag = 5.0

    -- Declare cursor

    DECLARE tables CURSOR FOR

    SELECT TABLE_NAME

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE = 'BASE TABLE'

    -- Create the table

    CREATE TABLE #fraglist (

    ObjectName CHAR (255),

    ObjectId INT,

    IndexName CHAR (255),

    IndexId INT,

    Lvl INT,

    CountPages INT,

    CountRows INT,

    MinRecSize INT,

    MaxRecSize INT,

    AvgRecSize INT,

    ForRecCount INT,

    Extents INT,

    ExtentSwitches INT,

    AvgFreeBytes INT,

    AvgPageDensity INT,

    ScanDensity DECIMAL,

    BestCount INT,

    ActualCount INT,

    LogicalFrag DECIMAL,

    ExtentFrag DECIMAL)

    -- Open the cursor

    OPEN tables

    -- Loop through all the tables in the database

    FETCH NEXT

    FROM tables

    INTO @tablename

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    INSERT INTO #fraglist

    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

    WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT

    FROM tables

    INTO @tablename

    END

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

    -- Declare cursor for list of indexes to be defragged

    DECLARE indexes CURSOR FOR

    SELECT ObjectName, ObjectId, IndexId, LogicalFrag

    FROM #fraglist

    WHERE LogicalFrag >= @maxfrag

    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

    -- Open the cursor

    OPEN indexes

    -- loop through the indexes

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectid, @indexid, @frag

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

    ' + RTRIM(@indexid) + ') - fragmentation currently '

    + RTRIM(CONVERT(varchar(15),@frag)) + '%'

    SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

    ' + RTRIM(@indexid) + ')'

    EXEC (@execstr)

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectid, @indexid, @frag

    END

    -- Close and deallocate the cursor

    CLOSE indexes

    DEALLOCATE indexes

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

    -- Runs UPDATE STATISTICS against all user-defined tables in the current database

    EXEC sp_updatestats

    GO

    Tuesday, June 12, 2007 5:15 PM
  • thanks for the help.

     

    Now my real concern. We have similar stand alone database in each branch of company with the same application and configuration.

     

    Performance of appication in other branches is good, but in this branch its very very slow. since application  / hardware / configuration is same in all branches, one of the possible reason can be a problem in database, and hence i am looking deep into it.

     

    Firstly, why this particular db size has grown so much (and why the empty spaces comes up). Even a size of 12 gb ( with most of it empty) should not be considered big performance blocker. So what can be the reasons for low performance.

     

     

     

    Thursday, June 14, 2007 9:01 AM
  • First, if our answers are helpful, please mark them as "Helpful" so we get credit for them. Second, you should start a fresh thread, with this entirely new question. Otherwise it will not get as much attention. You also need to be more specific about your issue.

     

    In the meantime, what symptoms are you seeing (besides increased database size) at the one branch?

     

    My blog post is a starting point:

    http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!549.entry

     

    This whitepaper has a lot of good performance troubleshooting information:

    http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

    Friday, June 15, 2007 1:10 PM