Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: Why is SQL DB so big? Can I shrink?

    Hello,

    We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).

    Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.

    I know MS Access has a shrink utility, but don't know where it is in SQL Server.

    Thanks, in advance for your help.

    Rodney

  2. #2
    Join Date
    Jun 2004
    Posts
    7

    Shrink Database

    If you´re using Enterprise Manager,

    right button in the database name,

    there is a Shrink Database command under the All Tasks option.

  3. #3
    Join Date
    Mar 2004
    Posts
    114
    You have the next synthax to shrink a database

    DBCC SHRINKDATABASE
    ( database_name [ , target_percent ]
    [ , { NOTRUNCATE | TRUNCATEONLY } ]
    )

    Or, to shrink a single file:

    DBCC SHRINKFILE
    ( { file_name | file_id }
    { [ , target_size ]
    | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
    }
    )


    This should help you.

  4. #4
    Join Date
    Mar 2004
    Posts
    31

    Shrinking didn't fully work

    Hi,

    I shrunk the DB and it went from 33GB to 30GB. Not much of a difference.

    Are you saying that I need to shrink each file individually? Is there a way to do them all at once without having to run it 200 times?

    Is this the only other option I have to see if I can get it to 11GB?

    Thanks.

  5. #5
    Join Date
    Nov 2002
    Posts
    71
    Rodney,

    Get a bit more of an insight by looking at the figures for usage as well as overall file size. Easiest to use 'task pad' view in EM. What do you see for the data file and the log file? Normally, one would see Tran Log files growing a lot if they aren't being checkpointed enough (eg. via a backup). If you do a select count(*) on the tables and the database looks about the same, I wouldn't expect to see much variation in the sizes of the two db's data files. However, the size of the file might have grown if you've copied tables within the new db at some point. Even if you then deleted the tables you didn't need, the size of the data file would have probably increased if there wasn't adequate unused space in the file. The useage figures seen via task pad should throw some light on this.

    For shrinking a data or a tran log file, I would suggest you use dbcc shrinkfile. However, before you get to that point, have a clear undertanding of the data and tran log size/useage figures to see what needs to be done. If, for example, your tran log is getting bigger by the day, the chances are you're not backing up properly - eg. tran log backups. Just a guess - I can't see your system.

    Regards,

    Clive

  6. #6
    Join Date
    Mar 2004
    Posts
    31

    Task Pad?

    Clive,

    Thanks, I will get on those steps. How do I do a "Task Pad" view in EM?

    Thanks.

  7. #7
    Join Date
    Mar 2004
    Posts
    114
    Quote Originally Posted by rweinstein
    Hi,

    I shrunk the DB and it went from 33GB to 30GB. Not much of a difference.

    Are you saying that I need to shrink each file individually? Is there a way to do them all at once without having to run it 200 times?

    Is this the only other option I have to see if I can get it to 11GB?

    Thanks.
    How did you transfered your database ? Which method (copy, backup/restore, detach ...) ??

  8. #8
    Join Date
    Nov 2002
    Posts
    71
    Highlight the database then select View | Taskpad from the menu.

    Clive

  9. #9
    Join Date
    Mar 2004
    Posts
    31

    File Sizes in Task Pad

    Clive,

    Sorry, I found the Task Pad.

    Here is what it says:

    Data:
    29253.5MB Used 738.56MB Free

    Log:
    .44MB Used .8MB Free

    The table view, I can only see the first 20 or so tables, can I scroll down somehow?

    Thanks.

  10. #10
    Join Date
    Mar 2004
    Posts
    31

    DB Transfer

    We exported the tables over with the export function, but deleted the old tables first.

    Thansk.

  11. #11
    Join Date
    Mar 2004
    Posts
    114
    Quote Originally Posted by rweinstein
    We exported the tables over with the export function, but deleted the old tables first.

    Thansk.
    You should have tried a detach database, then copy the files over the network (or cd), then on the new server, do an attach database giving it the path to the files copied.

  12. #12
    Join Date
    Nov 2002
    Posts
    71
    Us the following to get some useful data on the tables in the db and their sizes. Just copy it into a Query Analzyer window with the required db selected.

    Clive
    --------------------

    declare @id int
    declare @type character(2)
    declare @pages int
    declare @dbname sysname
    declare @dbsize dec(15,0)
    declare @bytesperpage dec(15,0)
    declare @pagesperMB dec(15,0)

    create table #spt_space
    (
    objid int null,
    rows int null,
    reserved dec(15) null,
    data dec(15) null,
    indexp dec(15) null,
    unused dec(15) null
    )

    set nocount on

    -- Create a cursor to loop through the user tables
    declare c_tables cursor for
    select id
    from sysobjects
    where xtype = 'U'

    open c_tables

    fetch next from c_tables
    into @id

    while @@fetch_status = 0
    begin

    /* Code from sp_spaceused */
    insert into #spt_space (objid, reserved)
    select objid = @id, sum(reserved)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id

    select @pages = sum(dpages)
    from sysindexes
    where indid < 2
    and id = @id
    select @pages = @pages + isnull(sum(used), 0)
    from sysindexes
    where indid = 255
    and id = @id
    update #spt_space
    set data = @pages
    where objid = @id


    /* index: sum(used) where indid in (0, 1, 255) - data */
    update #spt_space
    set indexp = (select sum(used)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id)
    - data
    where objid = @id

    /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
    update #spt_space
    set unused = reserved
    - (select sum(used)
    from sysindexes
    where indid in (0, 1, 255)
    and id = @id)
    where objid = @id

    update #spt_space
    set rows = i.rows
    from sysindexes i
    where i.indid < 2
    and i.id = @id
    and objid = @id

    fetch next from c_tables
    into @id
    end


    --select top 25
    select
    Table_Name = (select left(name,25) from sysobjects where id = objid),
    rows = convert(char(11), rows),
    reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
    data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
    index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
    unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

    from #spt_space, master.dbo.spt_values d
    where d.number = 1
    and d.type = 'E'
    order by reserved desc

    drop table #spt_space
    close c_tables
    deallocate c_tables

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Are you doing regular Tlog backups (To backup and truncate the transaction log, if db is in full recovery mode). Keep in mind that you need to truncate your TLogs on a regular basis or they will just grow and grow. I set up jobs to run Backup Log every 1/2 hour (very active database), and it is a necessity too for db recovery. You can shrink your Tlog log once it is backed up to reclaim some space. Once you perform you next full db backup, you can remove the previous backed up tlogs. I have Windows Shell scripts for all this if you would like them.

    -Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •