How to clear my transaction log with out truncating them
-
Hi to all!
I have a problem during transaction log backup, I tried to backup my db (full db backup) and execute transaction log backup, I also tried to restore my latest backup (full db back up and transaction log backup) and it was successfull. The problem is the transaction log is still filling up
. Base on books online, the transaction log must be clear after the transaction log back up. What should i do? whats wrong with this transaction log?Please HELP me......
Thanks in advance.
-
Can you please provide what version of SQL you are using ? i.e. SQL 7 or 2K?
There is a problem with SQL 7 where an active or "stuck" transaction could be in the middle of completed transactions and would prevent the log from releasing to where it should. Cycling the server should clear that up.
IF SQL 2K Try this AFTER making a backup and ensuring no one is in system
BACKUP LOG [DBNAME here] WITH NO_LOG and then DBCC SHRINKFILE [t_log name here], 5
Good Hunting!AJ Ahrens
webmaster@kritter.net -
sir,
Im using MS SQL Server 2000 Standard Edition, and I frequently used this comman(please see below), just to clear my transaction logs, and execute full database backup and start another sequence of transction logs.
backup log sfa with truncate_only
dbcc shrinkdatabase (sfa, truncateonly)
Please correct me if im wrong, I guess this is not the best practice. Please advice......
I dont want to use this command, bacause I think it will brake my transaction log sequence. Do you think this is normal? Please Please help me. my transaction logs is getting biger........ even though i backup it in hourly basis...
Thanks in advance.
Best regards,
Arnold
-
You must put yourself one question. How precious is your data? What is your recovery strategy in the event of failure? Is it ok to return to last full backup or is it necessary to recover all commited transactions to the point of failure? If the first is the case, you can go with SIMPLE recovery mode and you don't have to bother with growing transaction log or tr. log backups at all. If the latter is the case, you have to be in FULL/BULK-LOGGED recovery mode, perform regular full backups and transaction log backups and keep sequence of these. Currently, you are somewhere in the middle and you don't enjoy the benefits of either of the models. In my opinion, shrinking databases is not a good practice in the production. Start with searching Books Online for description of recovery models and for sample backup strategies.
-
Here is the answer to your question
"Dynamically shrink the transaction log with a stored procedure"
http://codetempest.com/node/10
Also: I concur with the above. Unless you have some explicit need for the full-logged recovery model, bulk-logged will do just fine and will definitely help keep your tranaction log size smaller.
Shrinking the transaction log on a regular basis (say, once a week with the above mention stored procedure in a job) is OK as long as you backup the database immediately afterward and save at least one days more backups and 2 days more tranaction logs for recovery if you need it.
G. Milner
-
What is your database size ? Is it a 24*7 application ? What is the frequency of the backups you are currently taking ? What time in the day is your database at the minimal access ?
Provide me with the following information and I would suggest a good backup/recovery strategy.
Thanks,
Kishore
-
Hi to all,
Thank you for all the suggestions and supports.
Actually my database is used by ecommerce web application, and its a 24x7 production operation. currently my database has 2.5 GB datafile only since this not to big, and I don’t have any problem in datafiles, but some times my transaction log files is twice of the size of my datafile, but some times my transaction log file used all the free space of the storage, this was happed if i forgot to check it. basically my solution for this is to truncate my transaction logs,
currently the minimal access of my db is from 11PM to 1AM and 3AM to 7AM, because there is a download program of master data from SAP Informix database to MSSQL database that scheduled every 1AM to 3AM.
My backup strategy are as follows:
Complete database backup - Daily (6PM) to Disk
Transaction log Backup - Hourly to Disk
****After complete database backup
backup all the DB and tlogs to Tape (daily)
Since this is a 24x7 operation I cant afford of loosing any data, that why i backup the transaction log every hour. By the way, my database is in full recovery model, and a unrestricted file growth of transaction logs. And i planning to change my backup strategy, please see my propose backup strategy:
Complete database backup - weekly
Differential backup - daily (6PM)
Transaction log backup - hourly
What can you suggest or comments to my current backup strategy and to my propose backup strategy?
And why does my transaction logs continue to grow after all the transaction logs backup? Please I need your advice.Thank you in advance.
Your suggestions and comments are highly appreciated
Regards,
Arnold
-
Dynamically shrink the transaction log with a stored procedure
One of the things I see often on SQL Server forums is newbies having trouble with ever-growing transaction files. They are often at a loss on how to shrink them, really shrink them, without having to, for example, detach them, delete the transaction log file (.ldf) and reattach them, thereby taking their database off line and denying access for their users.
A few years ago I found a great script that forcibly shrinks the transaction log file on a given database on the Microsoft support site (see: INF: How to Shrink the SQL Server 7.0 Transaction Log. I have used this on my production databases for a long time. Recently I got around to converting it to a stored procedure and it is that stored procedure I am presenting here.
The only real caveat associated with this procedure is that as soon as you run it on a given database you should immediately do a full backup of that database because, as the Microsoft page explains "DBCC SHRINKFILE and DBCC SHRINKDATABASE are not logged operations, and running them invalidates further transaction log backups. You must make a full database backup after you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands." For this reason, I usually schedule this as a job to run right before the nightly backups of my production databases.
You should, however, read the entire MS KB article associated with this before you use it.
I have found this works best when run twice in succession. Here's an example of how to run it. This example will run for (up to) 2 minutes and will shrink the database transaction log to 50 MB:
USE MyDatabase
GO
EXEC sp_trx_log_shrink 2, 50
GO
EXEC sp_trx_log_shrink 2, 50
GO
Incidentally, this also works on the TempDB database. I have read many instances of advice and documentation saying the only way to shrink the TempDB database is to stop the SQL Server service and restart it. That's not strickly true. You can use this procedure (along with DBCC SHRINKFILE for the .mdf data file) to get the desired shrinking of tempdb. Just put it in your master database and go.
And now, for the code:
Create Procedure sp_trx_log_shrink (@MaxMinutes INT,
@NewSize INT )
/*=========================================================================
Title: Shrink SQL Server Transaction Log Stored procedure
Script sp_trx_log_shrink.sql
Purpose: system proc based on
INF: How to Shrink the SQL Server 7.0 Transaction Log
Force shrink transaction log of current database to
specific size.
Params: @MaxMinutes = Max number minutes to run before stoppint
(recommend 2 at least)
@NewSize = New size in MBs of the log file after shrinking
(recommend at least 10 MB in most DBs)
Create/Update History:
3/9/2005 3:33:44 PM - GMilner: Converted to procedure.
Notes:
Assumes only 2 physical database files and that _Data file
is file id 1 in sysfiles table and that log file is file id 2.
Original Source:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650
Microsoft Knowledge Base Article - 256650 ;
=========================================================================*/
AS
SET NOCOUNT ON
DECLARE @err int
DECLARE @LogicalFileName sysname
--DECLARE @SSQL as VARCHAR(255)
DECLARE @DBN as nVarchar(50)
-- Setup / initialize
DECLARE @OriginalSize int
set @DBN = (select db_name())
PRINT 'Database: ' + @DBN
SET @LogicalFileName = (SELECT FILE_NAME (2))
PRINT 'Log logical filename: ' + @LogicalFileName
PRINT ''
EXEC sp_helpdb @DBN
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles
WHERE name = @LogicalFileName) -- the log has not shrunk
-- IF thhe value passed in for new size is smaller than the current size...
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
-- Because it is a char field it inserts 8000 bytes...
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
IF @err <> 0
BEGIN
RETURN(@err)
PRINT '*** SHRINK FAILED!!! ***'
END
IF @err = 0
BEGIN
PRINT '*** Perform a full database backup ***'
END
G. Milner
-
Not to throw too big a monkey-wrench at your ideas....but... What is your (the mangement) expectation for recovery time.
With a 2.5GB database backups should take less than 5 minutes unless you have a real crappy SCSI controller.
I would setup a Daily Full
Differentials every 3 hours
Trans every hour (or maybe every 1/2 hr depending on how paranoid I feel).
As for why the log continues to grow after trans log backups - I would look at the Apps using it. It may not shrink, but it should have been truncated and not really expand. My suspicion is that something in the application is not committing transactions, and therefore leaving an open checkpoint. So after a period of time those multiple open checkpoints aren't allowing the log to truncate at the right LSN.
Is the app a homegrown or vendor. If its homegrown that means you can probably find the offending programmer and slap him/her silly.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Personally, I agree with Jim P. (maybe I would skip diff backups). If you perform log backups every hour and log doesn't get truncated, check for the last open transactions with DBCC OPENTRAN command and inspect sysprocesses table for processes with open transactions.
As to log shrinking, do you need to do this at all? If your server is dedicated and there's sufficient space on disks, there's no need to perform shrinking (very resource-intensive operation). Just set transaction log size to a size that's sufficient to hold transactions between tran. log backups and you don't need to worry about log shrinking (periodically check physical file(s) size)
-
hi all
i do have same issue at sql 2000server
having 60 database
logfiles keep on growing i am doing backup as follows
daily full
trn log at 12 am , pm , 4 am 4 pm
but apart from this the physical log size doesnt truncate
also i have scheduled this using database maintenanace plan and havent writtine any script or changed any paratmetr.
pls help what shall be optimumu solution to truncate logs.
and if we have to trace open transactio how shall we do that if i watch open processes on any database there are none most of times.
REgards
Deeps
-
Deeps, If your log continues to grow when you are doing 4 transaction log backups per day, increase the frequency of your log backups until they quit growing. 4 transaction log backups per HOUR is not unreasonable (every 15 minutes). A transaction log backup NEVER reduces the size of the physical file. Its not designed to. It will remove the inactive portion of the log after backing it up, freeing that space WITHIN the log file for new transactions.
Terminology -
TRUNCATE - empty inactive transactions from the transaction log file.
SHRINK - physically reduce the size of the transaction log file.
Please read Books Online, transaction logs - active portion, truncating, shrinking to understand how the transaction log is managed.
Steve
-
Hi to all!
Thanks for all the inputs and suggestions.
I reviewed the SPs used by the system, and I noticed that the begin transaction and commit transaction are not included and missing in the SPs, and I think the reason why my transaction logs are not truncated after the transaction log backup is because of uncommitted transaction to the database, it might be the committed logs are truncated. Do you think this is right? And what happen to active portion? 
May be I will consider also the SPs used by the system, and I will talk to the developers to discuss that. Please advice. I will inform you all as soon as my problem in transaction logs is solved.
Thank you again in advance.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply