Solved

SQL 2000:  Truncate Transaction log

Posted on 2016-11-29
10
13 Views
Last Modified: 2016-11-29
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
Comment
Question by:detox1978
  • 5
  • 5
10 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 41906382
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
 
LVL 2

Author Comment

by:detox1978
ID: 41906389
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
 
LVL 39

Expert Comment

by:lcohan
ID: 41906413
"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
 
LVL 2

Author Comment

by:detox1978
ID: 41906419
Thanks for the suggestion.

When I run that command I get the following error

truncate error
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41906424
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Author Comment

by:detox1978
ID: 41906431
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
 
LVL 39

Expert Comment

by:lcohan
ID: 41906463
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
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41906467
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
 
LVL 2

Author Comment

by:detox1978
ID: 41906599
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
 
LVL 2

Author Closing Comment

by:detox1978
ID: 41906601
Run all the commands in one go and it reduced the size.  Many thanks.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Moving applications to the cloud or switching services to cloud-based ones, is a stressful job.  Here's how you can make it easier.
A Short Story about the Best File Recovery Software – Acronis True Image 2017
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now