?
Solved

SQL 2000:  Truncate Transaction log

Posted on 2016-11-29
10
Medium Priority
?
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 40

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 40

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 40

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 40

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 40

Accepted Solution

by:
lcohan earned 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

752 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