June 4, 2008 at 7:56 am
log file upnormally increases up to 80 gb , but no transactions to be done in SQL
recovery model is full, log backup running on. so we unable to change the recovery model also
any body help
June 4, 2008 at 2:16 pm
How often do you run log backups?
How often do you rebuild indexes?
Are there any ETL type processes that run on a scheduled basis?
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 4, 2008 at 9:37 pm
Check for any open transactions using DBCC OPENTRAN('yourdbname')
Also check the log_reuse_wait column in sys.databases using the below command,
Use master
go
Select name,log_reuse_wait from sys.databases
go
[font="Verdana"]- Deepak[/font]
October 16, 2008 at 1:40 am
Why dont you shrink the log file by using the following command
USE DatabaseName
GO
DBCC SHRINKFILE( , 1)
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE( , 1)
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 16, 2008 at 1:55 am
krayknot (10/16/2008)
BACKUP LOG WITH TRUNCATE_ONLY
Noting, of course, that Backup log will break the log chain. It will no be possible to restore to a point-in-time after that, unless another full/diff backup is taken.
If the database fails after running that and before doing another full backup, it will only be possible to restore toe the time of the last log backup before the log was truncated.
Also note that it's deprecated in SQL 2005 and gone in SQL 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply