Solved

SQL 2000:  Truncate Transaction log

Posted on 2016-11-29
10
28 Views
Last Modified: 2016-12-28
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

813 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

9 Experts available now in Live!

Get 1:1 Help Now