Shrinking SQL Server 6.5 log file

[PROPAR]

About :
Microsoft SQL Server 6.50 - 6.50.479 (Intel X86)
Jun 14 2000 16:12:29
Copyright (c) 1988-1997 Microsoft Corporation

My databases are created in 2 files : 60MB for datas and 500 MB for log.
The 500 MB log file is far too large and consumes too much disk space (as I happily dump and truncate log frequently).

How can I shrink the log file to 150 MB ?

I work with SQL 6.5, no DBCC SHRINKFILE allowed...

Thank you very much for your help.

 

[nigelrivett]

Think there's a dbcc shrinkdb.
It will shrink both data and log then you can increase the size of the data file.

After spending a lot of time getting the unused part of the log to the end of the file.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[PROPAR]

I can't understand the way "dbcc shrinkdb" works.
For example, for a database with a half-full data file (20.8 free MB over 60MB) and an EMPTY log file (500 MB free over 500 MB !), "dbcc shrinkdb" allows me to shrink the database of only a meager 3 MB.

dbcc shrinkdb result :

Current size of database Size database can be shrunk to
------------------------ ------------------------------
286720 285184

Objects pvnt further shrink Index
-------------------------------- --------------------------------
syslogs data

The difference between (286720-285184)*2K tells me I can expect shrinking the files of only 3 MB ! And what about the empty unused 500 MB of the log file ?

Of course I can't detach/attach the database to correct the files size since this is 6.5...

Anyone still toiling with the old stuff anywhere ?

Thanks.

 

[nigelrivett]

Sorry - didn't notice the log file was created that size - you can't reduce it to less than the created size.

Think your best option would be to create a new database and transfer everything to it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[PROPAR]

This is not very much of an option, since I can't restore a saved database of 60MB data/500 MB log into a 60MB data/ 150 MB log database; SQL Server doesn't allow restoring a base into a smaller one.
May be the database transfer tool of Enterprise Manager can do it ?

 

[nigelrivett]

You can script the database structure - crate the tables then use bcp to get the data out and in.
Create the bcp statements from the sysobjects using a query.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[PROPAR]

Whatever happens with SQL 6.5 , it always falls to the BCP command...