October 19, 2004 at 1:14 pm
Running SQL 7.0, I have run DBCC shrinkfile and DBCC shrinkdatabase and my transaction log is still 38 gb. The database itself is only 4 gb. When I backup the database at night the transaction log is part of the maintenance plan. What can I do to make my transaction log smaller before it uses up all my disk space?
October 19, 2004 at 1:19 pm
You need to run a log back up with the truncate_only switch, and then run dbcc shrinkfile to shrink the log file back down. That should get your log file back in check.
October 19, 2004 at 1:59 pm
run following command, just replace device_name with the devicename where you want to back up the logs and DBName with Database name.
Backup log DBName to device_name with Truncate_Only
If you dont want to back up your transactions then run
Backup log DBName with Truncate_Only
And then run DBCC Shrinkfile.
If you dont need logs then you might want to switch your databases to simple recovery mode instead of Full or Bulk_Logged. Which will truncate logs on check points.
October 20, 2004 at 11:39 am
Follow the steps above listed by Deven Fadia for truncating the log and then shrink it to a respectable size - that should do the trick.
You also might want to consider backing up your trans log every so often if you are running in the FULL recovery model, this will keep the trans log in check.
October 20, 2004 at 3:31 pm
Run this in QA for the user database you want to operate. Replace <dbname> with your database name.
dbcc shrinkfile (2, notruncate)
dbcc shrinkfile (2, truncateonly)
create table t1 (char1 char(4000))
go
declare @i int
select @i=0
while(@i < 100)
begin
insert into t1 values ('a')
select @i = @i +1
end
truncate table t1
backup log <dbname> with truncate_only
drop table t1
Go
The trick is transaction log needs some transaction to truncate. This definately works.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply