SQL 2000: Truncate Transaction log

Can you run this command to see what's in the SQL T-log first?

DBCC SQLPERF(logspace);

Maybe yo need to back it up before yo can truncate. Please remember that the steps in order to recycle T-log space back into use for a database in FULL recovery mode is to:
1. take periodic(daily) FULL BACKUPS
2. take periodic T-Log backups

Then you can shrink the T-Log file(s)


In an emergency situation when disk is full OR you don't care/need about the T-log backups for recovery purpose you can run a command like below before the shrink:

backup log baandblog with truncate_only

-- or:
BACKUP LOG baandblog TO DISK='NUL:'

The server is virtual, so we back it up using Veeam (virtual server backup software).  So the transaction logs need to be manually purged.


At the moment there isn't enough remove to back it up locally to disk.

"So the transaction logs need to be manually purged." - there is a difference between virtual "image" backup and SQL Server database backup and you can read more details here but as I said - in order to recycle T-log space back into use for a database in FULL recovery mode is to:
1. take periodic(daily) FULL BACKUPS
2. take periodic T-Log backups

right now before yo can shrink it you will need to empty the T-log by running either

backup log baandblog with truncate_only
GO
-- or:
BACKUP LOG baandblog TO DISK='NUL:'
GO

Maybe the best is to add a SQL Job to run this T-log backup command every few times a day for that T-log file to not grow and fill the drive again.

Thanks for the suggestion.

When I run that command I get the following error

Argh...I took the t-log file name not db name sorry so you can try this generic set of commands for SQL 2000 should work on any database

declare @logfileid as bigint, @sstr as nvarchar(1000), @datab as nvarchar(200)
set @datab=db_name()

backup log @datab  with truncate_only



--OR simply just

backup log baandb  with truncate_only
GO

I run

backup log baandb  with truncate_only
 GO 

Select allOpen in new window

which returned "The command(s) completed successfully.", but the disk space has not reduced.

Well now if you run the command again

DBCC SQLPERF(logspace);


the T-log should be empty or barely used comparing to 99% before so now you can shrink it by running


USE baandb
GO
CHECKPOINT
GO
DBCC SHRINKFILE ('baandblog' , 0, TRUNCATEONLY)
GO

I cant seem to get the log file to reduce in size.  Running the command below doesn't work.  Any suggestions?

USE baandb
GO
CHECKPOINT
GO
DBCC SHRINKFILE ('baandblog' , 0, TRUNCATEONLY)
GO

Select allOpen in new window

Run all the commands in one go and it reduced the size.  Many thanks.