June 27, 2012 at 7:32 am
I have a database in SQL 2008. I do Trans log backups every 1 hour. The log backups are double the size of the database, so I did a full backup and then restarted the log backup job and still its the same size. I don't know why my log backups are that big. Any idea??
My solution was as below but didn't work any idea???
some of the databases are in simple recovery and some are in full.
For the one in Full recovery i set it to simple recovery and then ran
Declare @dev_log sysname
select @dev_log = Name from sys.database_files where Type=1
print @dev_log
DBCC Shrinkfile(@dev_log,100)
Then again changed to Full and did a trans log backup and still the log file that I backup is large.
For the one that are in simple recovery I just ran
Declare @dev_log sysname
select @dev_log = Name from sys.database_files where Type=1
print @dev_log
DBCC Shrinkfile(@dev_log,100)
and still log backups are same size. Any idea why they are still big?
June 27, 2012 at 7:39 am
Firstly the size of the log file is unrelated to the size of the log backup. You may want to regrow your logs to a sensible size for the normal usage of the DB.
Long running transactions?
Bulk-logged recovery model?
Replication?
Are you appending multiple backups to a single file?
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
June 27, 2012 at 7:46 am
Right now the log files are around 100 MB, but still the log backups are big in size.
June 27, 2012 at 8:08 am
GilaMonster (6/27/2012)
Long running transactions?Bulk-logged recovery model?
Replication?
Are you appending multiple backups to a single file?
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply