April 17, 2003 at 8:28 am
Sorry about that. Thought you could search via id
AJ Ahrens
SQL DBA
Custom Billing AT&T Labs
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 17, 2003 at 10:37 am
Here are some solutions 🙂
==============================================================
How can I shrink the transaction log in SQL Server 7 and 2000?
==============================================================
http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=154
http://www.sqlservercentral.com/scripts/contributions/666.asp
Soln1 - Have you tried running DBCC SHRINKFILE with first the NOTRUNCATE option to move all the data to
the front of the file followed by running it again with TRUNCATEONLY to cause the unused space to be released?
Soln2 - I have encounter this problem before. Backup your db first, then turn on following database option
auto close auto shrink truncate log on checkpoint
Soln3 - According to Microsoft, the way to shrink a log file is to use DBCC SHRINKFILE. This process is painfully slow and inefficient.
Even after running the procedure numerous times, you may not get much free space.
Here's another way to shrink your logs that is guaranteed to work every time. First, back up the database and then detach
(sp_detach_db) it (you will have to bring the database down to do this). Next, delete the log file and then re-attach
(sp_attach_db) the database, not providing the old log file location from within the sp_attach_db command. This will create a
new log file with the old log file name in the old location with default size i.e. 512 KB.
To make sure there is no problem during the operation, the old log file can be renamed and kept until the database is reattached
successfully. This provides a backup plan if for some reason SQL server fails to attach the database without the old log file.
This trick won't work if the database has more than one log file, but if you need to, you can alter the database so that it only
has a single log file, while will allow you to perform the above steps. After carrying out the detach and attach database
activity, the database can be again altered to add more log files. [7.0, 2000]
==============================================================
paul
paul
April 17, 2003 at 11:17 am
AER,
SQL Sercer Central posted a script to force shrinking files a while ago. Have you search the site? I grabbed the script and here it is.
It was written for SQL 7 and should be the script you need Good Luck.
___________________________________________
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:
--------
@errHolds 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
____________________________________________
April 17, 2003 at 11:46 am
Thank you guys for all your help,
But you probably didn't read carefully about what I was talking about.
I don't have any problem with shrinking files from the Query Analyzer using 'DBCC ShrinkFile' statement.
I also can create a batch file or DTS package using this statement and schedule it to run at a certain time.
My question was, why the Maintenance Plan created from 'Database Maintenance Plan' would not work? Are there any settings or configuration issues involved?
Why should I make my life more complicated if there is a tool in SQL Server to perform it for me?
Thank you again.
Alex
April 17, 2003 at 11:59 am
Actually,
I take it back. I myself didn't read carefully Paul's response impressed with the amount of code provided by you guys. Sorry about that.
I'll definitely try Paul's 2nd solution: turn on 'Truncate log on checkpoint' & 'Auto Close' options (My 'Auto Shrink' option is on).
Alex
April 17, 2003 at 12:04 pm
Since the database is set to autoshrink, the most the autoshrink will do is about 25% . You need to run dbcc shrinkfile couple of times. If it does not do it the first time it will work after 2 or 3 runs. I ahve had this problem with 7.0. first you need to dump the file you want to shrink and then run shrink command.
Good Luck.
April 24, 2003 at 6:17 am
Just a tip never use auto shrink , cause you will not know when SQL will shrink the database , it is actually recommended by microsoft that auto shrink is disabled .
I have found when I shrink a large datafile somethimes the space is not released back to the system , run sp_updatestats and whoooopa I got me result . Rather setup alerts with job steps then shrink the database throug a maintenance plan ! .....
April 24, 2003 at 6:49 am
Thank you CoetzeeW,
This is a very good advise. I think that what causes the problems.
I will uncheck this option.
Alex
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply