How to reduce Transaction log file ?

Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.
First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:

> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_s hrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>