-
shrinking log files after backup
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's
All db set for full recovery
and autoshrink
Backup done with Tivoli
full backup once a week
log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
Thank you
Alex
-
Do a checkpoint before shrinking.
-
Hi Mak,
what is the script for checkpoint ?
Thank you
Alex
-
Run
CHECKPOINT
from query analyzer, in the context of database
-
Hi
I tried
CHECKPOINT
DBCC SHRINKFILE(Invoice_Log)
----
Cannot shrink log file 2 (Invoice_Log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ------ ----------- ----------- ----------- --------------
8 2 142544 3185 142544 3184
---
dbcc sqlref shows
Database Name = 'Invoice'; Log Size ( MB) = 1113.6172 ; Log Space Used (%) = 7.017683 ; Status = 0;
-
try this.
use test
go
checkpoint
go
backup database test to disk = 'd:\test.bak'
go
BACKUP LOG test WITH TRUNCATE_ONLY
go
dbcc shrinkfile ('test_log',TRUNCATEONLY)
go
dbcc shrinkfile ('test_log')
go
if it still dont work, send us the result of the following query on that database.
use databasename
go
DBCC LOGINFO
Last edited by MAK; 07-23-2003 at 02:12 AM.
-
Hi Mak,
I tried you code
it works only with
BACKUP LOG test WITH TRUNCATE_ONLY
Tivoli keeps logs till next full backup
and with truncate will break log chain
Here is my stor proc trying to
shrink logs
ALTER procedure generate_dummy_trn
as
Declare
@Sql varchar(300),
@name varchar(50),
@Sql2 varchar(300)
set @sql = ' if object_id(''shrink'') is not Null ' + char(13)+
' drop table shrink ' + char(13) +
' create table shrink (i smallint)' + char(13) +
-- ' GO '+ char(13) +
' checkpoint'+ char(13)+
-- ' GO '+ char(13) +
' declare @x sysname ' + char(13) +
' select @x = rtrim(name) from sysfiles where fileid = 2 '+ char(13)+
' select @x ' + char(13)+
' DBCC SHRINKFILE (@x,TRUNCATEONLY) '
declare
C1 cursor for
select --top 2
rtrim(name)
from sysdatabases
open c1
fetch c1 into @name
while @@fetch_status = 0
begin
if (select convert(char(9),DatabasePropertyEx(@name,'Updateab ility'))) <>'READ_ONLY'
begin
set @sql2 = ' use ' + @name + char(13) + @sql
select @sql2
exec (@sql2)
end
-- select @sql2
fetch c1 into @name
end
close c1
deallocate c1
Thank you
Alex
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|