SQL Server Performance

Shrink Data File

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256, May 19, 2008.

  1. Trev256 New Member

    Hi - If I shrink a data file (e.g. mdf) whilst the users are online - is there a risk of :
    a) user disruption?
    b) data corruption?
    thanks
    Trev256, May 19, 2008
    #2

  2. eltriki New Member

    a) --> FALSE
    b) --> FALSE
    NOT perform because the Transact log is occupied
    By Triki
    eltriki, May 19, 2008
    #3

  3. moh_hassan20 New Member

    it is not wise to use auto_shrink on for data files. Repeating shrinking and autogrowing the data files will cause file-system level fragmentation, which can slow down performance. plus many disadvantages.
    Turn AUTO_SHRINK off!! - check: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
    moh_hassan20, Jul 2, 2008
    #4

  4. Luis Martin Moderator

    Simple. Don't do that in working time.
    Luis Martin, May 19, 2008
    #5

  5. satya Moderator

    satya, May 21, 2008
    #6

  6. Trev256 New Member

    Hi - How can blocking lead to corruption? do you mean disk contention blocking rather than database blocking (i.e. table blocking)?Thanks
    Trev256, Jun 26, 2008
    #7

  7. satya Moderator

    Ok, I should have been more clear on corruption part which is inconsistency on the data pages where by the DBCC SHRINK operation is trying to remove the page and updates on table (text or non-text columns) will need split the pages, it all depends on the application to database update mechanism.
    You raised a good point too that disk contention is also much important here, which is will have high I/O on the drives where your data/log files are stored.
    satya, Jun 26, 2008
    #8

  8. techbabu303 New Member

    Just to add since shrnk operations are resource intensive, make sure you have adequate growth percentage for log file and max size is atleast has 20% more free space o you do not have to perform regular shrink.
    In SQL 2005 undocumented DBCC LOGINFO command once run you need to check status column to see if there lots of 2 listed, if yes then the log file is eligible for shrinking.

    -Sat
    techbabu303, Jun 26, 2008
    #9

  9. v-manraj@microsoft.com New Member

    Hello,

    If i simply execute DBCC LOGINFO then it lists a lot of files with same file id, better if someone could explain what these rows indicate.

    Also if someone can post the exact syntax for DBCC LOGINFO, it would be great.

    Thanks,
    Mani
    v-manraj@microsoft.com, Jun 26, 2008
    #10

  10. satya Moderator

    How many files exist on transaction log?
    Say if that transaction log consists 2 files, then the LOGINFO output shows that the transaction log consists of file identifiers found in the sysfiles system table for log files are 2 and 7. The transaction log contains 15 virtual log files (total number of rows in the output) of which 5 contain active transactions -- this is true for VLF's that have 2 in the status column of the output.
    Note also that virtual log files containing the active transactions are at the bottom of the output. Until the status of these VLF's changes to 0 you cannot shrink the transaction log. The status will change when transactions are saved to the disk. Once the status of a virtual log file changes to 0 you can run DBCC SHRINKFILE against each physical log file (in this case against files 2 and 7) to reduce their size.
    satya, Jun 29, 2008
    #11

  11. satya Moderator

    satya, Jul 2, 2008
    #12

  12. techbabu303 New Member

    Best way to sum it up Satya.
    Nice operation specific information there.
    -SatN
    techbabu303, Jul 2, 2008
    #13

  13. padam New Member

    Database shrink is only reduce occupied free space from database. and this also Microsoft In-built Tools. So There Is No Risk To Corruption and lost data. but it will effect on performance on running Transaction.
    padam, Dec 4, 2008
    #14

  14. satya Moderator

    There is a risk of data corruption!!
    Say when the backup operation is executing and DBCC SHIRNKFILE operation is running, due to the lock of pages for SHRINK the backup will cause such an issue.
    satya, Dec 7, 2008
    #15

Share This Page