dbcc shrinkfile does not shrink mdf

  • Thanks. I think your EMPTYFILE approach is essentially the same as Tim's DTS job. By moving the data pages from one file to another seems to resolve the problem. Stopping and Starting SQL Server service on its own doesn't do it. Thats the first thing I tried. I will try your EMPTYFILE idea and let you know if it works. Won't be able to try for a couple of weeks as I am going on holiday

    Many Thanks,

    Paolo

  • Two Things here.

    First your TRAN log file has to be smaller than the .MDF file.

    Second, Go to your database option and turn of the AUTO GROW on your .MDF file and .LDF file.

    Also turn on the DB option 'TRUNCATE LOG ON CHECKPOINT" ON,

    Give the CHECKPOINT COMMAND FROM YOUR QUERY ANALYZER.

    Now run DBCC SHRINKFILE

    This has worked for me in the past.

  • Can the sp_reattach be run (against distribution dB) while writes to the (distribution)dB are happening?

    Chris

    Edited by - Christine Austin on 08/20/2002 11:04:10 AM


    Aurora

  • Hi Chris

    sp_detach_db

    Detaches a database from a server and, optionally, runs UPDATE STATISTICS on all tables before detaching.

    As far as SQLServer is concerned the database does not exist after running this

    sp_attach_db

    Attaches a database to a server.

    So following the execution of sp_detach_db no writes, or reads can occur on the database until the database has been attached using sp_attach_db.

    Hope this helps

    Tim

    quote:


    Can the sp_reattach be run (against distribution dB) while writes to the (distribution)dB are happening?

    Chris

    Edited by - Christine Austin on 08/20/2002 11:04:10 AM


  • Thanks guys. We just stopped replication and drop the distribution dB (warning*** we were sure we have our data) and started it up again and rebuilt the distibution dB (not necessarily in this order).

    It worked for us. Thanks again

    Christine


    Aurora

  • Hello,

    We are having a similar problem with the Log file, where we have run the SHRINKDATABASE and SHRINKFILE. After running both of these our MDF is down to 600Mb, but our LOG only dropped down to 27Gig from 33Gig. We also tried the script mentioned earlier in this thread, but did not make much of a dent...

    Is there anything else we can try?

    Thanks,

    Dan

  • Dan,

    There is an excellent script in the library that will solve your problem - sp_force_shrink_log'. I have used this on several occations - it works!

    Tim

    Here is a copy:

    use master

    go

    if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log

    go

    create proc sp_force_shrink_log

    /*

    *************************************************************

    Name: sp_force_shrink_log

    Description:

    Shrink transaction log of the current database in SQL Server 7.0.

    Switch context to proper db to execute.

    Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>

    exec pubs..sp_force_shrink_log

    Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000

    zanevsky@azdatabases.com

    Input Params:

    -------------

    @target_percent tinyint. default = 0. Target percentage of remaining shrinkable

    space. Defaults to max possible.

    @target_size_MB int. default = 10. Target size of final log in MB.

    @max_iterations int. default = 1000. Number of loops (max) to run proc through.

    @backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup options.

    Output Params:

    --------------

    Return:

    Results:

    ---------

    Locals:

    --------

    @err Holds error value

    Modifications:

    --------------

    *************************************************************

    */

    @target_percent tinyint = 0,

    @target_size_MB int = 10,

    @max_iterations int = 1000,

    @backup_log_opt nvarchar(1000) = 'with truncate_only'

    as

    set nocount on

    declare @db sysname,

    @last_row int,

    @log_size decimal(15,2),

    @unused1 decimal(15,2),

    @unused decimal(15,2),

    @shrinkable decimal(15,2),

    @iteration int,

    @file_max int,

    @file int,

    @fileid varchar(5)

    select @db = db_name(),

    @iteration = 0

    create table #loginfo (

    id int identity,

    FileId int,

    FileSize numeric(22,0),

    StartOffset numeric(22,0),

    FSeqNo int,

    Status int,

    Parity smallint,

    CreateTime datetime

    )

    create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )

    create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )

    insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40

    select @file_max = @@rowcount

    if object_id( 'table_to_force_shrink_log' ) is null

    exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )

    insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )

    select @last_row = @@rowcount

    select @log_size = sum( FileSize ) / 1048576.00,

    @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,

    @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00

    from #loginfo

    select @unused1 = @unused -- save for later

    select 'iteration' = @iteration,

    'log size, MB' = @log_size,

    'unused log, MB' = @unused,

    'shrinkable log, MB' = @shrinkable,

    'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )

    while @shrinkable * 100 / @log_size > @target_percent

    and @shrinkable > @target_size_MB

    and @iteration < @max_iterations begin

    select @iteration = @iteration + 1 -- this is just a precaution

    exec( 'insert table_to_force_shrink_log select name from sysobjects

    delete table_to_force_shrink_log')

    select @file = 0

    while @file < @file_max begin

    select @file = @file + 1

    select @fileid = fileid from #logfiles where id = @file

    exec( 'dbcc shrinkfile( ' + @fileid + ' )' )

    end

    exec( 'backup log [' + @db + '] ' + @backup_log_opt )

    truncate table #loginfo

    insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )

    select @last_row = @@rowcount

    select @log_size = sum( FileSize ) / 1048576.00,

    @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,

    @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00

    from #loginfo

    select 'iteration' = @iteration,

    'log size, MB' = @log_size,

    'unused log, MB' = @unused,

    'shrinkable log, MB' = @shrinkable,

    'shrinkable %' = convert( decimal(6,2), @shrinkable * 100 / @log_size )

    end

    if @unused1 < @unused

    select 'After ' + convert( varchar, @iteration ) +

    ' iterations the unused portion of the log has grown from ' +

    convert( varchar, @unused1 ) + ' MB to ' +

    convert( varchar, @unused ) + ' MB.'

    union all

    select 'Since the remaining unused portion is larger than 10 MB,' where @unused > 10

    union all

    select 'you may try running this procedure again with a higher number of iterations.' where @unused > 10

    union all

    select 'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10

    else

    select 'It took ' + convert( varchar, @iteration ) +

    ' iterations to shrink the unused portion of the log from ' +

    convert( varchar, @unused1 ) + ' MB to ' +

    convert( varchar, @unused ) + ' MB'

    exec( 'drop table table_to_force_shrink_log' )

    go

    if object_id( 'sp_force_shrink_log') Is Null

    select 'sp_force_shrink_log Not Created'

    else

    select 'sp_force_shrink_log Created'

    go

  • From my experience the main condition is there should be no users connecting to db in order to shrink it successfully.

  • I ahve not run into any issues with shirnking an MDF except for when I setup a filegorup for TEXT. I found by setting the maxsize to the current size I could do a SHRINKFILE and it would be a percentage smaller than before. Then I would set the size again and pull up into itself, the reason is TEXT tryies to use up as much available space as it can so it does not need to compress, I was foring compress to take place by shirnking it's room size.

    As for clients being connected, never found any issue as it will lock the DB during shirnk and only the clients see issues which is the expected outcome based on SHIRNKDB and SHIRNKFILE documentation.

  • what are (index and )datapagestatistics stating ? (imho Shrink is only freeing empty pages)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • what are (index and )datapagestatistics stating ? (imho Shrink is only freeing empty pages)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 11 posts - 16 through 25 (of 25 total)

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