June 18, 2003 at 1:05 am
Hi,
I get a Database which wasn't administrate before. The logfile aute grows and now when I restore the DB the size of the logfile is 20Go and 1% used.
How can I reduce the space used on the disk by thi file ?
June 18, 2003 at 1:29 am
Hi,
You can either do
dbcc shrinkfile (dbid, n) --where dbid is the fileid in sysfiles and n is the target MB size
Or
backup log yourdbname with truncate_only
Ritch
Edited by - Ritch on 06/18/2003 03:09:00 AM
Edited by - Ritch on 06/18/2003 03:30:42 AM
"I didn't do anything it just got complicated" - M Edwards
June 18, 2003 at 3:05 am
OK dbcc shrinkfile (2, 100,TRUNCATEONLY)
June 18, 2003 at 3:30 am
Sorry for the incomplete syntax tsc1, the caffiene has just kicked in
Also I have tried shrinking logs in EM via taskpad, but it never works. Anyone else found this?
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards
June 18, 2003 at 8:34 pm
I already encounter the same problem and what I did is I shrink the file in a small chunk. On my case, I shrink it by 1GB at a time.
hope that helps.
June 18, 2003 at 9:42 pm
Ritch,
I use SQL7 and SQL2000 and i find the SQL7 to be a little buggy in this instance, it could be the setup i have here but i find quitting EM and starting again / or a refresh of the taskpad will give a more accurate result, plus it depends on the type of backup mode you are in - simple / bulk / full. As i understand transactions are happening when you shrink a database as well. HTH. Remember you *might* need to backup before shrinking too and truncating logs.
------------------------------
Life is far too important to be taken seriously
June 19, 2003 at 1:36 am
Thanks.
*I didn't do anything it just got complicated*
"I didn't do anything it just got complicated" - M Edwards
June 19, 2003 at 2:22 am
The script below will solve your problem,it runs for bot sql 7.0 an sql 2000.
-- Caution : database compatibility level must be 7.0 or higher ..
dbcc shrinkfile ( [Logical Log File Name], truncateonly )
backup log [Logical Database Name] with truncate_only
GO
if exists ( select * from sysobjects where name ='tmp_00000000000001a')
drop table tmp_00000000000001a
create table tmp_00000000000001a (cola varchar(10), colb int )
set nocount on
insert tmp_00000000000001a (colb) values (1)
declare @index int
select @index = 0
while (@index < 4000)
begin
update tmp_00000000000001aset cola = cola where colb = 1
end
dbcc shrinkfile ( [Logical Log File Name], truncateonly )
backup log [Logical Database Name] with truncate_only
GO
drop table tmp_00000000000001a
GO
June 19, 2003 at 2:23 am
The script below will solve your problem,it runs for bot sql 7.0 an sql 2000.
-- Caution : database compatibility level must be 7.0 or higher ..
dbcc shrinkfile ( [Logical Log File Name], truncateonly )
backup log [Logical Database Name] with truncate_only
GO
if exists ( select * from sysobjects where name ='tmp_00000000000001a')
drop table tmp_00000000000001a
create table tmp_00000000000001a (cola varchar(10), colb int )
set nocount on
insert tmp_00000000000001a (colb) values (1)
declare @index int
select @index = 0
while (@index < 4000)
begin
update tmp_00000000000001aset cola = cola where colb = 1
end
dbcc shrinkfile ( [Logical Log File Name], truncateonly )
backup log [Logical Database Name] with truncate_only
GO
drop table tmp_00000000000001a
GO
June 19, 2003 at 5:46 am
Hi !
/*
shrink a log file
WARNING: change the database name and the logical name of the log file.
database: MyDB
log file: MyDB_Log
*/
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'TRUE'
USE MyDB
go
DBCC SHRINKFILE ('MyDB_Log', TRUNCATEONLY )
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'FALSE'
USE MyDB
June 19, 2003 at 6:32 am
Here's a link to the script that I used OFTEN.
http://www.sqlservercentral.com/scripts/savescript.asp?scriptid=26
It forces the transaction log to shrink by moving the last active transaction back to the top of the transaction log.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy