Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    75

    Unanswered: Data file will not shrink

    First off, I'm normally not one to shrink the hell out of data files (for obvious reasons), but this is a special case. We are setting up a mini test environment in preparation for migrating one of our systems to SQL Server 2005 (among other things).

    The problem I have is that the test SQL Server I have to use has only about 50GB of disk space that I can allocate to databases, when the databases that I need to fit in there sit at around 130GB total. I've got 13 total databases that I have to fit in there. There are several logging tables in each, and lots of binary data that's really not needed for our testing. The first thing I did on my testing copies was to change the recovery model to simple, which chopped the log file.

    So I've been truncating the logging tables and have been stripping out the unneeded data. I then have been running a script that I wrote to reindex, then shrink the individual files, then the database as a whole. Probably overkill, but it has worked on all of the DBs with the exception of one.

    This particular DB is approx 21GB in total size (it's already come down from about 55GB), but when you look at the free space, it's showing 75% free inside the mdf file. I don't really care much about performance at this point, I just need to get the file size down and can't figure out how.

    Any ideas?

    BTW - this is the script that I wrote:
    Code:
    declare @tablename varchar(255) 
    declare @logfilename nvarchar(200)
    declare @datafilename nvarchar(200)
    declare @dbname nvarchar(200)
    declare @sql nvarchar(1000)
    
    set @dbname = ltrim(rtrim(db_name()))
    set @logfilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.ldf%')))
    set @datafilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.mdf%')))
    
    /* Reindex Tables */
    declare tablecursor cursor for 
    select '[' + table_schema + '].[' + table_name + ']' from information_schema.tables 
    where table_type = 'base table' 
    
    open tablecursor 
    fetch next from tablecursor into @tablename 
    while @@fetch_status = 0 
    begin 
    print 'Reindexing ' + @tablename 
    dbcc dbreindex(@tablename,' ',90) 
    fetch next from tablecursor into @tablename 
    end 
    close tablecursor 
    deallocate tablecursor
    
    /*Shrink the crap out of the DB*/
    set @sql = 'BACKUP LOG [' + @dbname + '] WITH TRUNCATE_ONLY'
    print @sql
    exec sp_executesql @sql
    
    set @sql = 'DBCC SHRINKFILE([' + @logfilename + '], 1)'
    print @sql
    exec sp_executesql @sql
    
    set @sql = 'DBCC SHRINKFILE([' + @datafilename + '], 1)'
    print @sql
    exec sp_executesql @sql
    
    set @sql = 'DBCC SHRINKDATABASE([' + @dbname + '], 1)'
    print @sql
    exec sp_executesql @sql

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Thoughts:
    1. How many tables are heaps? Your script grabs and reindexes all tables, but you can't move data around in heaps because there is no clustered index. If you have lots of heaps, or maybe 1 really huge heap, add a clustered index (forces a data rewrite to get it in ordered sequence) then drop the index and shrink.
    2. How heavily indexed are the tables? Maybe try and drop some indexes, then shrink.
    3. You are still leaving 10% free space on your reindex. Be merciless ... make that sucker a 100% fill factor!

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Try Michael's script from SQLTeam:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

    BTW - NO point defragging first (other than to fill up pages) since shrinking fragments the indexes anyway.

    HTH

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Slow, slow fingers

  5. #5
    Join Date
    Aug 2005
    Posts
    75
    thanks for the suggestions guys.

    Looking at the one largest table, there was no clustered index, so I created one. I knew this was the problem table, because that's where all the binary data was stored. Previously I just went thru and set the binary columns (ntext fields) as "". (This worked fine for the other DBs - so I'm not sure why it didn't work here) I also deleted 3-4 of the non-clustered indexes on it. I reindexed the DB at a 100% fill factor and tried it again - The DB size didn't change one byte.

    I also tried that script you linked to, pootle. It just went in a perpetual loop, trying to shrink the same amount over and over, so after about 5-6 minutes, I stopped.

    In my playing, I decided to just truncate the one offending table (was taking up approximately 90% of the db space). It still wouldn't shrink. I made a test backup of the DB and the bak file came out to be around 2GB (out of 20).

Posting Permissions

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