Have you backed up the log?? If not you can do this in QA

BACKUP LOG name_of_database WITH NO_LOG

This will remove all the committed transaction from the log and give this space to the free portion of the log. Then if you want to get back some disk space from the T-log, you can do this

1) in QA run this in the database in question
EXEC SP_HELPFILE

This will give the name of the data file and log file

2) then in QA do this
DBCC SHRINKFILE(log_file_name, target_size_in_MB)

If the backup log does not free up space you may have a problem with the position of the active virtual log in the log file. If this is the case let me know and we can fix that too.

Dave

-----Original Message-----
From: Phil Shuttleworth [mailto:Phil.[Email Address Removed] Tuesday, November 19, 2002 9:41 AM
To: LazyDBA.com Discussion
Subject: Transaction log space


Hallo DBA's,

I have a problem with one of our SQL Server 7.0 databases, in that the
Transaction log seems to be growing out of control.

The figures are as follows:-

Data space Used Free Total

Database 416.13 MB 26.88Mb 443.00 MB

Transaction log space 2,652.24 MB 126.50 MB 2,778.74 MB

As you can see, the Transactiion log space (used) is about 6 times the size
of the Data space (used), and the transaction log free space is also out of
proportion to a similar amount. This has been growing steadily over the
past few weeks.

Does anyone know how to reduce this to a reasonable size, please?


Phil Shuttleworth

DBA, IT Services
University of Central England, BIRMINGHAM B42 2SU, UK
Tel: +44(0)121 331 5664 Fax: +44(0)121 356 2875
E-mail: Phil.[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page