Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SQL Server Administration (2005)  Backup file growing fast

Author  Topic  Shawn73 Starting Member 1 Post Posted - 2011-05-31 : 14:12:04 Hi all, New to the site and first time poster. It's a great site for information. My problem is that my database backup file (writing .BAK file to disk) has started growing rapidly. At the end of April the .BAK file was around 4GB, now it's 25GB. This started happening at the end of April/beginning of May. There are multiple administrators and I don't know what changed. Examining the backup logs, I see the following for backup information: Date, Pages, First LSN, Last LSN 05/14/2011 00:09:18, 1813123, first LSN: 1289:575078:1, last LSN: 1665:41954:1 05/13/2011 21:57:56, 1781530, first LSN: 1289:575078:1, last LSN: 1651:88243:1 05/13/2011 18:04:53, 1772657, first LSN: 1289:575078:1, last LSN: 1650:41910:1 05/13/2011 12:04:43, 1735425, first LSN: 1289:575078:1, last LSN: 1643:85767:1, 04/29/2011 22:39:37, 584278, first LSN: 1289:575078:1, last LSN: 1289:3154765:1 04/28/2011 23:33:28, 527526, first LSN: 1289:575078:1, last LSN: 1289:2240536:1 04/28/2011 06:01:33, 446534, first LSN: 1289:575078:1, last LSN: 1289:938557:1 04/28/2011 00:01:35, 442534, first LSN: 1289:575078:1, last LSN: 1289:874566:1 04/27/2011 18:01:43, 424798, first LSN: 1289:377390:94, last LSN: 1289:378724:1 04/27/2011 12:01:50, 424854, first LSN: 1288:4242991:28, last LSN: 1288:4245181:1 04/27/2011 06:01:26, 424614, first LSN: 1288:3679518:216, last LSN: 1288:3679985:1 The pages have grown by 5 times and the first LSN looks like it's "stuck". Database Details: - SQL Server 2005 SP3 with latest patches (going to apply SP4 soon) - Server is a publisher doing merge and snapshot replication to multiple subscribers - Main database is 3.5 GB and hasn't changed considerably over the last year - Backup plan was recently changed from all databases to the specific user database with no change in .BAK size. - Backup is configured to overwrite previous - At the end of April, we had some issues with replication agreements that had been failing for quite some time. - I also switched the recovery model to simple in early May thinking it might be related to transaction logging. I'm not sure what else to add. I'm fairly new to SQL admin. Could anyone provide any help? It seems like there are old transactions somewhere in the database but I have no idea how to clear them. I'm hoping to not have to break replication as this is an HA environment. Thanks for the help, Shawn