locked
how to reduce DB size RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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 ?

    thanks

    Thursday, March 19, 2015 10:00 AM

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    Could you please paste the output of below query:

    Use database

    go

    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

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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 ?

    thanks

    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

    http://social.technet.microsoft.com/wiki/contents/articles/22661.sql-server-misleading-database-initial-size-label.aspx

    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
    MVP

    Thursday, March 19, 2015 4:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Size does matter: 10 ways to reduce the database size and improve performance in SQL Server

    http://aboutsqlserver.com/2014/12/02/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