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
Solved

SQL Server not truncating logs after BACKUP LOG command

Posted on 2004-10-15
17
964 Views
Last Modified: 2008-01-09
I've been reading through a lot of old posts and other resources on the internet, but I'm still having a problem getting my transaction logs to truncate correctly.

I'm trying to set up log shipping for my SQL 2000 standard server and I'm using Microsoft's Simple Log Shipper stored procedures included on the SQL Resource Kit.  According to everything I'm reading from Microsoft and the internet whenever a BACKUP LOG command is issued the transaction logs are supposed to truncate on their own.  However my logs continue to increase in size and the only way I've found to get them to come back down is to manually issue a shrink command against the log files.  

I've read a lot of posts where people are saying run the command with a TRUNCATE_ONLY tag on the end, but others are saying if you do that log shipping won't work.  The command Microsoft uses in their procedure is BACKUP LOG TO DISK but they don't have a TRUNCATE_ONLY tag anywhere.

Can anyone help?
0
Comment
Question by:promap
  • 5
  • 4
  • 3
  • +2
17 Comments
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12322020
Is the recovery model set to FULL ?
0
 

Author Comment

by:promap
ID: 12322091
Yes
0
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12322108
Are you sure that the log shipper procedures contain a "backup log" statement ?  

We use the full log shipping through SQL here, and I know the logs get backed up and truncated when it  happens.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:promap
ID: 12322529
Yeah, I printed out the stored procedure Microsoft is using and it has:

BACKUP LOG @dbname TO DISK = @backupfilename

I've looked and files are moving from server a to server b I just can't figure out why it isn't truncating anything.

0
 
LVL 21

Expert Comment

by:mastoo
ID: 12323151
If you run:

dbcc sqlperf( LOGSPACE )

I presume you are saying the Log Size is growing, but what about the Log Space Used?  Since you say shrink makes the log smaller, I'm assuming you'll see essentially 0 Log Space Used immediately after a transaction log backup.
0
 

Author Comment

by:promap
ID: 12323379
I ran that command and on the databases I recently switched to Full recovery mode the log size is growing (on average my other database log sizes are ~1MB).

For example, my biggest databases are as follows:

Log Size: 2914MB
% Used: 57.79

Log Size: 1109MB
% Used: 74.23

This may be a stupid question, but what exactly does the % used mean?  I thought the log files would just grow as needed with no set limit?
0
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323532
There is an allocated space for both data files and log files.  SQL takes this space from the OS as soon as the log/data files are created.  So if you have a tiny database and specify a 10GB log, SQL will reserve that 10GB, even though the log file may actually only contain 100K of data.

0
 
LVL 21

Expert Comment

by:mastoo
ID: 12323629
and on those two examples you gave, you're saying the log size continues to increase and the %used does or does not drop to near 0 after you do your log backup?
0
 
LVL 6

Expert Comment

by:JaffaKREE
ID: 12323650
The Log size will not increase or decrease, unless the log becomes completely full and is forced to auto-grow.

The % used should become close to zero after a backup log statement is executed.
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12325885
0) Always make a backup before you do this.
1) Put the database in simple recovery model.
2) Issue a DBCC SHRINKFILE or DBCC SHRINKDATABASE command.
3)Once completed look at the log files again. If they have not shrunk issue a a DBCC CheckDB() and depending on your evironment, you should use either estimate or repair options. Once complete issue the shrinkfile command.

This happens frequently with replication. Oh ya, make sure you have your most recent service pack on when you do the backup, to avoid the stuck in loading bug  
0
 
LVL 34

Expert Comment

by:arbert
ID: 12328385
Agree with MichaelSFuller, the logfile usually doesn't reclaim space on its own--use DBCC SHRINKFILE (dbcc shrinkfile(yourdatabase_log,0) ) after your backup.

Brett
0
 

Author Comment

by:promap
ID: 12347084
Well the first couple times I ran the BACKUP LOG statement manually it didn't appear that the % used was dropping that much but I've been watching it and it is now dropping close to 0 after a BACKUP LOG statement.  I was also able to issue a SHRINKFILE command via Query Analyzer but if I want to automate that part how can I put it into a stored procedure?  The log shipping one MS uses runs in the master table and every time I try to tell it to shrink another database it tells me it can't find the file.

0
 
LVL 5

Accepted Solution

by:
MichaelSFuller earned 250 total points
ID: 12347254
1) You could use shrinkdatabase and it will work fine.
2) Have that stored procedure, call a stored procedure in the database your shrinking that executes the shrinkfile command.
3) Make the next step on your job issue the shrinkfile command
0
 
LVL 34

Expert Comment

by:arbert
ID: 12347975
"shrinkdatabase and it will work fine"

I wouldn't shrink the size of the database unless you have a requirement too--slows down when the data files have to regrow--that's why I would recommend DBCC Shrinkfile and just the logfile...
0
 
LVL 5

Expert Comment

by:MichaelSFuller
ID: 12348068
That's very true, it could cause a performance degrade, I was thinking more along the lines of a truncate only.
0
 

Author Comment

by:promap
ID: 12349081
I'm gonna give this one to Michael because adding a shrinkfile as the next step finally got this entire thing to work correctly.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12349226
"I'm gonna give this one to Michael because adding a shrinkfile"

Like I said on my post dated 10/16/2004 10:43AM MDT.....
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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