[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • Last Modified:

SQL 2000: Truncate Transaction log

Hi All,

One of our SQL 2000 servers (running on Windows 2003)  is running low on space.  A couple of months ago I truncated the logs with assistance from experts-exchange link

So I executed the code below again in Query Analyser on the server and got the following...

SQL
But the log file size hasn't changed.  Any ideas what I've done wrong / differently?

Many thanks
D
0
detox1978
Asked:
detox1978
  • 5
  • 5
1 Solution
 
lcohanDatabase AnalystCommented:
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:'
0
 
detox1978Author Commented:
The server is virtual, so we back it up using Veeam (virtual server backup software).  So the transaction logs need to be manually purged.

SQL Log Space
At the moment there isn't enough remove to back it up locally to disk.
0
 
lcohanDatabase AnalystCommented:
"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.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
detox1978Author Commented:
Thanks for the suggestion.

When I run that command I get the following error

truncate error
0
 
lcohanDatabase AnalystCommented:
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
0
 
detox1978Author Commented:
I run

backup log baandb  with truncate_only
 GO 

Open in new window


which returned "The command(s) completed successfully.", but the disk space has not reduced.
0
 
lcohanDatabase AnalystCommented:
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
0
 
lcohanDatabase AnalystCommented:
So if you want to put this in a SQL Job the commands in order would be:

USE baandb
GO
CHECKPOINT
GO
BACKUP LOG baandb  WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE ('baandblog' , 0, TRUNCATEONLY)
GO


Just make sure to schedule the job AFTER the Veeam backup is taken - just in case...
0
 
detox1978Author Commented:
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

Open in new window

0
 
detox1978Author Commented:
Run all the commands in one go and it reduced the size.  Many thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now