Post reply

Honey, I Enlarged the Database!

  • November 3, 2004 at 10:06 am

    #528818

    Adam,

    from BOL (look up DBCC SHRINKFILE):

    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.

  • November 3, 2004 at 10:30 am

    #528824

    Sean,

    I think your suspicions are correct.  The space is taken up by fill factor in the indexes.  I would try again to reduce the fill factor.  You can use DBCC DBREINDEX to do this, but you'll have to run it for each table in the database...

    DBCC DBREINDEX (authors, '', 0)
    "authors" is the table name, the empty quotes are a placeholder for an
index name.

The "0" tells DBREINDEX to use the original fill factor from when the 
index was created.
    When you get the indexes rebuilt, then use dbcc shrinkfile on the data file
to shrink it.  Please do NOT use the EMPTYFILE option! You WILL render the
data file UNUSEABLE, and you'll be faced with creating a new data file, or 
restoring your database from a backup. BOL tells you that you can delete
the file once it is empty, but this is only true of files that are NOT in
the primary file group!  You CAN use TRUNCATEONLY, but if you 
do, take a transaction log backup first, then shrink, then immediately take
a full backup.  As Adam mentioned, these processes can cause the transaction 
log to grow, so you may have to shrink it afterwards as well.
    There is an issue with SHRINKFILE/SHRINKDATABASE that will prevent a 
successful shrink.  The data in the file must be "unfragmented", meaning
it needs to have the free space "pushed" to the end of the file.  On your
data file, the reindex should take care of that if you rebuild clustered
indexes.  If you don't have clustered indexes, create some after you have
successfully changed the fillfactor.  This will reorganize the data.  If
you don't want to keep them, the clustered indexes can be dropped after 
you are finished.  
    The same issue exists with transaction logs, though the fix is different.
A transaction log is made up of multiple "logical files", all of which may
be holding transactions.  Before the log can be shrunk, the logical files
to be deleted must be empty.  This is done by loading a large number of 
transactions, then truncating the log.  This may have to be done many times
to get the log cleared out.  I have a procedure that takes care of that.  
If you need to shrink the log and run into problems, let me know, and I can
provide the procedure for you.
    Steve

  • November 3, 2004 at 10:47 am

    #528827

    Should I run an update query on the sysindexes table to set the OrigFillFactor to 0? As I said above for some reason this is set as 25 in the fat DB but is 0 in the normal DB. I am guessing that this is the value DBCC DBREINDEX looks at when you set the fillfactor to zero.

    Kindest Regards,

    Sean Wyatt
    seanwyatt.com

  • November 3, 2004 at 11:18 am

    #528829

    Don't update the system databases directly!  In your fat database, fillfactor is set to 25, which means each page is only 25% full, leaving 75% open space.  In your other one, in which fillfactor is 0, it is the default.  A 0 fillfactor does not mean 0%.  It is a default value set by Microsoft, and basically means 100%.  So, if you reset your fillfactor to 0, you should take care of your problem.  If you want to provide some blank space, you can specify a lower value for fillfactor, but start in small increments, and watch and optimize gradually.  You may want to start with 95.

    You never want to update system databases directly.  Microsoft does all sorts of weird stuff in there, and you never know what you might break.  To reset the fillfactor, you must rebuild the indexes (that's the goal anyway, rebuild them to regain your space).  See my previous post regarding DBCC DBREINDEX.  Don't forget, you may want to also create clustered indexes, if you don't already have them.  You can only have one clustered index per table, so if there's one there, don't try to create a new one.  The clustered index actually stores the DATA in the order of the index, so you end up de-fragmenting the actual data by creating or rebuilding a clustered index.

    Steve

  • November 3, 2004 at 5:15 pm

    #528858

    Hoo-t,

    It says "data", not "logs" -- note I suggested that the OP use it to shrink the log file.

    I can guarantee that my log files are still 100% operational after I've shrunk them using the EMPTYFILE option, post-backup.

    --
    Adam Machanic
    whoisactive

  • November 3, 2004 at 6:36 pm

    #528862

    Adam,

    I will admit that BOL does not specify that the EMPTYFILE option restricts log files as well as data files.  However, it also does not recommend using it for any purpose other than emptying a file for the purpose of deleting that file.  In fact, nowhere did I find anything that would suggest that it would even be useful in truncating or shrinking a log.

    Why would you want to use it for any other purpose?

    I have shrunk data files and log files alike, and have never found it necessary to use EMPTYFILE to accomplish that.  In fact, the ONLY time I have used EMPTYFILE was when I did want to delete a data file.  Only after I used it did I discover that you can't delete the primary file, even after creating a new one.  So, I was stuck with a database that had a primary file that was only 1MB in size, and couldn't be used, and a secondary file where all the data was stored.

    So, you go on and keep using EMPTYFILE to shrink your databases, and I'll keep recommending that people NOT use it when they're shrinking theirs.

    Steve

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply