Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Physical size of a database file

green_papergreen_paper Member Posts: 1
I just dropped a table with 10,000+ rows, and the .MDF file size remained unchanged at 106 MB. Is this normal? That table is by far the largest table in my database, and I only have 14 user tables...

I guess the bigger question is: When will the file size increase (when data is added I'm assuming), and when will the file size decrease, if ever?

Comments

  • MasterijnMasterijn Member Posts: 188
    : I just dropped a table with 10,000+ rows, and the .MDF file size remained unchanged at 106 MB. Is this normal? That table is by far the largest table in my database, and I only have 14 user tables...
    :
    : I guess the bigger question is: When will the file size increase (when data is added I'm assuming), and when will the file size decrease, if ever?
    :
    A table consists of pages. Rows as a whole are located on pages. That's why there's is a rowsize limit of 8172 (SQLserver 2000) or 4096 SQLServer 7.0. When deleting rows or dropping a table, unused pages move to the available pages pool for reuse. Because the original allocation of all of those pages is not likely to be at the end of the database file, the database cannot efficiently shink the database file. (And it won't by default. There's a config option auto shrink, but it won't reallocate rows).
    For a good shrink, the syntax is:
    [hr]
    [blue]
    [b]DBCC SHRINKFILE[/b]
    ( { file_name | file_id }
    { [ , target_size ]
    | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
    }
    )

    Arguments
    file_name

    Is the logical name of the file shrunk. File names must conform to the rules for identifiers. For more information, see Using Identifiers.

    file_id

    Is the identification (ID) number of the file to be shrunk. To obtain a file ID, use the FILE_ID function or search sysfiles in the current database.

    target_size

    Is the desired size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size.

    If target_size is specified, DBCC SHRINKFILE attempts to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any available free slots in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

    EMPTYFILE

    Migrates all data from the specified file to other files in the same filegroup. Microsoft SQL Server no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement.

    NOTRUNCATE

    Causes the freed file space to be retained in the files.

    When NOTRUNCATE is specified along with target_size, the space freed is not released to the operating system. The only effect of the DBCC SHRINKFILE is to relocate used pages from above the target_size line to the front of the file. When NOTRUNCATE is not specified, all freed file space is returned to the operating system.

    TRUNCATEONLY

    Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.
    [/blue]
    [hr]
    As you can read this command will reallocate rows to the front of the file. So a good truncate can be done.


Sign In or Register to comment.