SQL Server 7 Tran log will not shrink

Question

SQL Server 7 Tran log will not shrink

Cory E.

SSCertifiable

Points: 7076
More actions
February 6, 2006 at 9:25 am

#167423

Hey all,
I have a SQL Server 7 DB that has the "Truncate log on Checkpoint" and "Select into/ bulk copy" options set, however, the log file is not going away. it is currently at 7595 MB used, and I really do not care about this log file, however, it will not go away. I have tried all sorts of shrinks, backups, checkpoints, and nothing. Any help would be, as usual, greatly appreciated.

Cory
-- Cory

Viewing 5 posts - 1 through 4 (of 4 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

Sometimes, as a last resort, I have detached the database, renamed the offending LDF file, and then reattached the database... omitting the LDF in the attach command. This builds a new LDF with the same name as previous. If it works ok then delete the 7gb LDF.

nb. this works in SQL2000 but not having a SQL7 environment or books on hand I'm just guessing it may work.

Cheers,
- Mark

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

I just answered this question on SQL Server 2005/Administration forum for the topic on DBCC Shrinkfile.

see

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=256050

Detaching /attaching to get a nice new log worked in SQL Server 7 for me with sp_attach_single _file_db when DBCC Shrinkfile did not work.

Regards,Yelena Varsha

dharper3 SSCrazy Points: 2391 More actions · Answer 3

Once the log has grown, checkpointing won't shrink it physically, the inactive portion still occupies space at the filesystem level. Use dbcc shrinkfile.

I would issue a 'backup log with truncate_only' for this database then 'dbcc shrinkfile'. detach/attach seems a very heavy handed approach.

Mark Yelton SSCommitted Points: 1570 More actions · Answer 4

There is another work around that's been out there for a while; you can run a dummy update statement against a non-production table. Here's an excerpt:

STEP1

Run:

DBCC SHRINKFILE ( <logfile> , TRUNCATEONLY )

BACKUP LOG <database> WITH TRUNCATE_ONLY

STEP2

Create a dummy table and insert a record to ReduceLogFile

CREATE TABLE ReduceLogFile (

MyField VARCHAR(10)

, PK INT )

INSERT ReduceLogFile (PK) VALUES (1)

GO

STEP3

Run the following script against ReduceLogFile

SET NOCOUNT ON

DECLARE @index INT

SELECT @index = 0

WHILE (@Index < 30000)

BEGIN

UPDATE ReduceLogFile

SET MyField = MyField

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

SELECT @index = @index + 1

END

SET NOCOUNT OFF

Once the log has been truncated, the earlier portions of the log can be reused. So, once you start running transactions, SQL Server will use space at the beginning of the file, rather than growing the physical file.

This causes the MinLSN with a staus = 2 to wrap around to the unused portion of the physical log file. Virtual log files are then marked as unused and will be deleted when a DBCC SHRINKFILE and BACKUP LOG are run. The deletion of the virtual log files is what causes the reduction of the physical log file size. You can adjust the number of times the loop runs if 30000 doesn’t reduce the log enough.

STEP4

Run the following commands again:

DBCC SHRINKFILE ( <logfile> , truncateonly )

BACKUP LOG <database> WITH TRUNCATE_ONLY

Try this approach and see what happens. You may have to run the update statement more than once to get the MinLSN to wrap around to the unused portion of the log. This has worked for me in the past.

HTH

Mark