Solved

SQL 2000:  Truncate Transaction log

Posted on 2016-11-29
10
74 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 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
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.

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Want an individual results display div 8 40
SQL Select Query help 1 34
SQL- GROUP BY 4 21
error in oracle form 11 19
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

685 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