September 20, 2004 at 7:06 am
I have read in some forums that when the trans. Logs grows too much, it is possible to backup the transaction logs every 15mn and the logs wont grows too much.
I did it but they still grows. What are the right things to do.
September 20, 2004 at 1:41 pm
If you are not concerned about point in time recovery, you can change the recovery model to Simple. But with this change you would be able to restore your database to the last good full/differential backup only.
What version are you running ?
September 21, 2004 at 2:19 am
on sql server 2K the backups are full backups, i dont want to switch to simple or differential
September 21, 2004 at 6:45 am
The time interval between the transaction log backups depends on database server workload and / or users activity. Monitor the transaction log grows and adjust time interval accordingly (create transaction log backups more frequently)
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
September 21, 2004 at 6:48 am
also take a look at this article: http://www.myitforum.techtarget.com/articles/18/view.asp?id=7411
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
September 21, 2004 at 6:50 am
i am trying now a proc, i found at sqlservercentral, and i have created a dts which is running every 3 hours. I am watching the results. here is the proc, if you like:
reate proc ShrinkLog @mb int = 10
as
begin
declare @log varchar(20)
declare @log1 varchar(20)
declare @db varchar(10)
set @db=db_name()
backup log @db with truncate_only
declare clog cursor
for select name from sysfiles where filename like '%.ldf%'
open clog
fetch next from clog into @log
while (@@fetch_status<>-1)
begin
set @log1=rtrim(@log)
dbcc shrinkfile(@log1,@mb)
fetch next from clog into @log
end
deallocate clog
end
GO
September 21, 2004 at 7:29 am
Fred,
Are you running FULL backups every 15 minutes to try to keep the logs from growing? If so, that won't work. Full backups don't truncate the logs. Only a transaction log backup will do that. You probably would want to do a full backup once a day, and transaction log backups as frequently as required to keep your logs small.
Also, if you don't want to change to Simple recovery model, you should never do a BACKUP LOG WITH TRUNCATE_ONLY. It defeats the purpose of having the full recovery model.
You really should study recovery models in Books Online to familiarize yourself with the models, how they work, and how they fit into your environment.
Steve
September 21, 2004 at 7:41 am
Steve,
you are right, i only backup the transaction Logs every 15mn and run now the proc every 3 hours. And it seems to be ok. But i am waiting until tomorrow, then that' s the d day for transactions.
September 21, 2004 at 8:02 am
Fred,
Your running the ShrinkLog proc every three hours? Its doing a BACKUP LOG WITH TRUNCATE_ONLY. If you do a truncate_only, your NOT backing up your transaction log, and therefore, cannot use the transaction logs for recovery! If you're going to do this, just change your recovery model to simple, and forget about your transaction logs.
But it sounds like you have an oltp process, in which transaction log backups are important. If so, you MUST stop using the ShrinkLog stored procedure. If you ever have to recover the database from backups, you will discover that you cannot use your transaction log backups!
Steve
September 21, 2004 at 8:14 am
Steve,
i delete from the proc the option with truncate only. I run a tansactions log backup every 15mn and the proc every 3 hours, i think that must be enough for recovery.
You dont think so?
September 21, 2004 at 12:00 pm
OK, if you've removed the truncate_only from the proc, we have the critical issue taken care of. Yes, with the truncate_only removed, you now have full recoverability.
There is, however, another issue. The ShrinkLog procedure is shrinking the transaction log on every one of your databases on that server, every 3 hours, or 8 times a day. This is a HUGE performance drain! You are truncating your transaction log (by backing it up every 15 minutes), then shrinking it, causing the database to have to expand the log whenever a transaction is applied to your database. You are constantly shrinking/expanding. This is not good! I believe that you should remove the ShrinkLog procedure completely! As long as you are taking transaction log backups every 15 minutes, your log should stay fairly small. You said tomorrow is the "d day" for transactions. After tomorrow, your transaction log size should pretty well stabilize, and I would leave it at that size. That will allow for nearly all of your processing to "fit" into the existing log size without further expansion. The ONLY time I would ever run the ShrinkLog procedure, is if you have a process that causes unusual and sizeable growth to your log, and you don't expect it to happen again in the near future, like a year end process. And even then, don't use the truncate_only unless you immediately take a full backup afterwards.
Steve
September 22, 2004 at 6:24 am
Steve
i have removed the proc, and let now the full trans. logs backup working
Thanks
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply