Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Question Unanswered: SHRINKING? Is it possible to shrink the mdf

    file?

    I know how to shrink the log file but I am not sure if I can shrink the MDF file
    and how. I am using SQL 2000.

    I don;t think my database is that big but the complete backup file is up
    to 6G.

    this is what is displayed in Taskpad

    MDF-------------- 5941M Used 589M Free
    Log -------------- 3.58 M Used 12.5M Free

    Any suggestion is greatly appreciated.
    Thank you

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    It is possible to shrink the mdf file (DBCC SHRINKFILE), but in your case, I don't think that it will help very much and it may impact performance.

    You would use DBCC SHRINKFILE if you noted that the Free portion was significantly higher than what you currently have (589 MB free out of 6530 total is only 10% free space).

    Your choices are to truncate data from tables, drop some tables, drop some unused indexes. There are some other things you could do (create a new filegroup on a separate physical disk, move the log file to a separate physical disk), but I don't think that you will gain much by trying to shrink the mdf to recapture that 1/2 GB of unused storage. If you do reclaim that free space, you may well find that SQL will only grab it back. While SQL is allocating that space to the db, you may find a performance degradation during the extend process.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Posts
    52
    Thank you for your response. I understand a little more now. I have
    another question: Can I find out how big each table in my database
    and how?

    Thanks again

  4. #4
    Join Date
    Aug 2002
    Location
    Prague
    Posts
    77
    run this query against your database, it takes data from sysindexes system table, which might be in some rare cases inaccurate.
    Look up DBCC UPDATEUSAGE topic in Books Online for details. Hope that helps. mojza

    use your_db

    --reports size of tables in db
    SELECT
    OBJECT_NAME(id) AS ObjectName,
    SUM(CASE WHEN indid in (0,1,255) THEN rows ELSE 0 END) AS Rows,
    ((SUM(CASE WHEN indid in (0,1,255) THEN Reserved ELSE 0 END) * Cast(8192 as BigInt)) / 1024) As Reserved,
    ((SUM(CASE WHEN indid in (0,1,255) THEN dpages ELSE 0 END) * Cast(8192 as BigInt)) / 1024) AS Data,
    (((SUM(CASE WHEN indid in (0,1,255) THEN used ELSE 0 END) - SUM(CASE WHEN indid in (0,1,255) THEN dpages ELSE 0 END)) * Cast(8192 as BigInt) ) / 1024) as Index_Size,
    (((SUM(CASE WHEN indid in (0,1,255) THEN Reserved ELSE 0 END) - SUM(CASE WHEN indid in (0,1,255) THEN Used ELSE 0 END)) * Cast(8192 as BigInt)) / 1024) as Unused
    FROM
    sysindexes
    WHERE
    OBJECTPROPERTY(id,N'IsMSShipped') = 0
    GROUP BY
    OBJECT_NAME(id)

  5. #5
    Join Date
    Feb 2004
    Posts
    52
    Thank you for the sample codes. I ran it and this is some of the result. Can you please explain what is the difference between reserved, data, and unused?
    Thank you



    Row reserverd Data Index_size Unused
    adpropcd 7779 1032 992 8 32
    APN_RSU 74361 10080 936 6968 176
    CNTY_ASSR_PRCL 852059 488072 486904 1168 0
    CNTY_PRCL_BLDG 859134 87048 87008 8 32
    fnParseArray 0 0 0 0 0
    HSENO 976200 147088 147040 16 32
    HSENO_delta 4619 912 816 16 80
    HSENO_DELTA2 4622 464 400 16 48

Posting Permissions

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