August 27, 2008 at 3:28 am
I have a database set to simple recovery mode and there is a backup job doing a full db backup every hour. The mdf is 1gb but the ldf is 7gb and growing rapidly. I have tried issueing a checkpoint, db shrink, file shrink, log backup, nothing I have done has reduced the log file size.
The strange thing is that the DB backups are about 7gb, now to my knowledge the logs shouldnt be included in a simple recovery mode database backup.
Anyone got any suggestions on how I shrink the log file?
Thanks.
August 27, 2008 at 3:57 am
Same problem with mine, the rapid increasing of database backup ( differential)....I have set Full backup occurrence once a day and Differential backup occurrence every 3 hours daily. The question is " how to remove the old backup from Differential with retention of 3 days". I am using SQL Server Management Studio 2005 with customize setup ( Maintenance Plan not available)....Only SQL Server agent should work for the jobs.
Anyone can give some idea to resolve this puzzle. Many thanks!:)
August 27, 2008 at 6:18 am
To Stuart:
Perhaps you have an old transaction that's still open, that's preventing the transaction log from being truncated. Run DBCC OPENTRAN to check if this is the case.
As to why the backup is so large, same reason as above, and/or you are continuously appending backup sets to the same file.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 28, 2008 at 3:03 am
Hi Ray,
Thanks for your help, there is something going on there but some more help would be appreciated to resolve it. I ran the command and it came back with the output below, all of our other databases came back with "No active open queries". In case it helps this database was a transactional replication subscriber. Transactional replication was removed and replaced with merge replication. The merge publisher log file size is tiny 80mb. Another database was also trans then moved to merge but that isnt exhibiting the same behavior with regards to log file size.
Transaction information for database 'xyz'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (123745:318:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any further help you can give would be great.
Regards,
Stuart
August 28, 2008 at 3:44 am
Sorry, replication isn't a subject I'm familiar with.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 28, 2008 at 4:33 am
Far shot here but is it possible that this database still sees itself as a publisher and has trasactions waiting to be replicated to the distributor? Have you disabled publishing for the database (assuming that this is no longer a published database for transactional replication).
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 28, 2008 at 4:40 am
Thanks Guys,
To clarify, the problem DB was a transactional replication subscriber it is now a merge replication subscriber.
Stuart
August 28, 2008 at 6:23 am
Have you looked at the activity on this database to determine what minimally logged operations are being run and expanding the logs through either a trace or looking at the process activity (sp_who2)?
Very curious. Let me know what you see.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply