how to reduce DB size RRS feed

  • Question

  • Hi,

    I am the happy owner of a SQL express 2008 R2 holding my DB.

    I have my DB primary data file at 7 GB

    In DB properties, I have initial size of primary db at 7022 MB, autogrowth = by 1 MB, unlimited.

    I query table sizes with :

    select object_name(id) [Table Name],
    [Table Size] = convert (varchar, dpages * 8 / 1024) + 'MB'
    from sysindexes where indid in (0,1)
    order by dpages desc

    total size of all my tables is then 1171 MB

    I do shrink / files / file type = data => available free space is 0.25 MB

    I'm afraid it will hit the 10 GB size.

    How do I reduce the DB size ? it is possible to change the  initial size of primary db ?


    Thursday, March 19, 2015 10:00 AM


All replies

  • I query table sizes with :

    No, you don't, you query only the size of heaps (indid=0) and clustered Indexes(indid=1), everything else is missing, see Detailed list of all tables and their size

    You have 2 options
    - Move old data to a second database
    - Delete old data

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Edited by Olaf HelperMVP Thursday, March 19, 2015 10:27 AM
    • Proposed as answer by Michelle Li Thursday, March 19, 2015 2:12 PM
    • Marked as answer by Michelle Li Friday, March 27, 2015 12:56 PM
    Thursday, March 19, 2015 10:09 AM
  • Could you please paste the output of below query:

    Use database


    [FILE_SIZE_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)
    dbo.sysfiles a

    If available free space is 0.25 MB then you cant reduce anymore. You will have to purge data or move to other database or go for edition upgrade when you hit 10GB. Initial size is not a problem you can shrink only if you have free space.

    Please note shrink db is not advisable.

    • Edited by dave_gona Thursday, March 19, 2015 10:50 AM
    Thursday, March 19, 2015 10:13 AM

  • I'm afraid it will hit the 10 GB size.

    How do I reduce the DB size ? it is possible to change the  initial size of primary db ?


    The initial size of database is not actually the  *initial size* it does not gives correct information. Below article would help you in understanding more


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article


    Thursday, March 19, 2015 4:37 PM
  • Size does matter: 10 ways to reduce the database size and improve performance in SQL Server


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, March 21, 2015 5:47 AM