Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: DBCC SHRINKFILE isn't working

  1. #1
    Join Date
    Nov 2004
    Posts
    13

    DBCC SHRINKFILE isn't working

    When I execute a DBCC SHRINKFILE or try shrinking database files through enterprise manager it works fine, except when I reboot the server the files return to the original size. Here is the statement I used:

    DBCC SHRINKFILE (filename, filesize)

    I have also tried using the TRUNCATEONLY option.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    What's original file size used when create db? Didn't see that on dbs other than tempdb.

  3. #3
    Join Date
    Nov 2004
    Posts
    13
    I am not sure, as I wasn't here when it was set up. No documentation of course. Is it stuck at the original size? I thought by specifying the size in the DBCC SHRINKFILE statement you are resetting the minimum file size.

    Books Online says:
    "Use DBCC SHRINKFILE to reduce the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size."

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  5. #5
    Join Date
    Nov 2004
    Posts
    13
    No...I am trying to shrink just regular database files.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Yes, should reset file size. In fact, I never saw that on my servers. Did you check free space in the db?

  7. #7
    Join Date
    Nov 2004
    Posts
    13
    The size of the database is 141,762.38 MB and the space available is 44,262.82 MB.

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Free space in data file or log file? Possible file expanded before rebooting?

  9. #9
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    What kind of file growth setting you have for the database? Is it allocating fixed number of MBs?

  10. #10
    Join Date
    Nov 2004
    Posts
    13
    The data file free space is 28,974.07 MB and the log file free space is 15,989.5 MB. I am only trying to shrink the data file, not the log file. I'm pretty sure the file growth occurs when SQL Server starts. I've tried shrinking a few times and every time the file stays at the new size until the server is rebooted (SQL Server restarts).

    The data file is set to automatically grow at a fixed size of 500 MB.

  11. #11
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    --Any startup procedure that you have on the server. Run this following command

    use master
    go
    select name from sysobjects where type ='p' and OBJECTPROPERTY(id,'ExecIsStartup')=1
    go

    if you find any procedure listed, then execute the following command

    sp_helptext procedurename

  12. #12
    Join Date
    Nov 2004
    Posts
    13
    These are the only two start up procedures:
    sp_MSrepl_startup (used for replication)
    sp_LEServer_Start (used for Log Explorer)
    Just to make sure I looked at both stored procs and they don't do anything to database size.

  13. #13
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Can you see anything on the sql server errorlog regarding, database expanding or any stored procedure fired?

  14. #14
    Join Date
    Nov 2004
    Posts
    13
    It's been a couple months since I last tried to shrink the files. So I don't have log files dating that far back. As you could imagine, it is a rather lengthy after hours operation, so I haven't had a chance to try again since. The log files that I do have, which include several reboots (we do one scheduled reboot every weekend) do not contain anything suspicious.

  15. #15
    Join Date
    Nov 2004
    Posts
    13
    I found some new info. at msdn2. It says:

    Consider the following information when you plan to run a shrink operation:

    A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

    Most databases require some space to be available for regular day-to-day operations. If you repeatedly shrink a database and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

    A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.




    I think what might be happening is when I shrink the file it causes some fragmentation. Then after the server reboots I have a job that runs DBCC REINDEX against any index with excesive fragmentation. This reindexing causes the file sizes to grow.

Posting Permissions

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