Truncate/Remove extra space in Tr.Log

Question

Truncate/Remove extra space in Tr.Log

barsuk

SSChampion

Points: 12280
More actions
March 2, 2004 at 4:10 pm

#84708

Hello All,
I did that many times before, but somehow in that case it doesn't work.
I just restored a DB from a backup.
6 GB of Data File and 5.5 GB of Tran Log.
I would like to reduce the size of Tran Log. (I am using SQL 7.0)
I run:
Backup LOG MyDB with NO_LOG
and then
I run:
DBCC SHRINKFILE ('MyDB_log', TRUNCATEONLY)
or
DBCC SHRINKFILE ('MyDB_log', EMPTYFILE)
Nothing happen.
What are my options?
Should I detach the DB, remove the Tran Log and then re-attach single Data File
or there is a better way???
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vikrant SSC Veteran Points: 274 More actions · Answer 1

Hi,

If i am not mistaken there is a procedure of running a dummy code to move the active VLF to the begining of log file.once the pointer moves again to the starting point again then running the shrinkfile command will give the desired results.

Regards,

Vikrant

anandc Grasshopper Points: 16 More actions · Answer 2

hello barsuk,

try using the below statement instead

DBCC SHRINKFILE ('MyDB_log', TRUNCATE_ONLY)

use TRUNCATE_ONLY instead of TRUNCATEONLY

anand

perico SSC Rookie Points: 26 More actions · Answer 3

After many attempts, this script worked for me:

use <YourDatabase>

go

DBCC SHRINKFILE ( <YourDatabase>_Log , 5,TRUNCATEONLY )

go

BACKUP LOG <YourDatabase> WITH TRUNCATE_ONLY

go

drop table mytable

go

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )

INSERT Mytable (PK) VALUES (1)

GO

SET NOCOUNT ON

DECLARE @index INT

SELECT @index = 0

WHILE (@Index < 20000)

BEGIN

UPDATE MyTable

SET MyField = MyField

WHERE PK = 1 /* Some criteria to restrict to one row. */

SELECT @index = @index + 1

END

SET NOCOUNT OFF

go

DBCC SHRINKFILE ( <YourDatabase>_Log, 5, truncateonly )

go

BACKUP LOG <YourDatabase> WITH TRUNCATE_ONLY

go

drop table mytable

go

barsuk SSChampion Points: 12280 More actions · Answer 4

Hi All,

Thanks for the replies.

Still Tran Log doesn't want to get truncated.

Any other ideas.

barsuk SSChampion Points: 12280 More actions · Answer 5

Hi Perico,

Thanks!!!!

When I executed your code all together it worked!!!

Can you explain why, because when I was executing that part by part-it didn't.

perico SSC Rookie Points: 26 More actions · Answer 6

Hi Barsuk,

I'm sorry, but I can't explain why it works. I got that script from a script library somewhere in the Net (can't remember where) after a lot of searching because I had your same problem.

I suppose it works because the 20000 updates cause enough data to be inserted in the Log so that it knows it can be cleaned ¿?

Anyway, I think it's just a workaround for a ¿bug? in SQLServer 7.

I'm glad that the script was useful for you, too.

jose.portela Grasshopper Points: 21 More actions · Answer 7

this works for me, hope it does for you too

USE databasename

backup log databasename with truncate_only

dbcc shrinkfile ('databasename_log',1,truncateonly)

hoo-t SSChampion Points: 12713 More actions · Answer 8

I think the key here is that you are using SQL 7.0 . With SQL 7.0 the shrink doesn't happen immediately or all at once. You should see it start to shrink as more activity occurs. That's why some people will run an additional script to create the activity allowing the pages in the log to be reorganized so that the log can shrink.

SQL 2000 reorganizes the log when you issue the shrink command so that the shrink happens right away.

Steve