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
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