Why does msdb.dbo.backupset table have entries for each database every 15 minutes?

%3CLINGO-SUB%20id%3D%22lingo-sub-304808%22%20slang%3D%22en-US%22%3EWhy%20does%20msdb.dbo.backupset%20table%20have%20entries%20for%20each%20database%20every%2015%20minutes%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-304808%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20SQL%20Sever%202008%20R2%20with%20134%20separate%20databases.%20The%20MSDB%20database%20is%20huge.%26nbsp%3BWhen%20I%20query%20the%20msdb.dbo.backupset%20table%20I%20see%20that%20every%2015%20minutes%20there%20are%20134%20new%20rows%20being%20added%2C%20one%20for%20each%20database.%20This%20happens%20every%20fifteen%20minutes%2C%20all%20day%20long.%26nbsp%3B%20This%20amounts%20to%2012%2C864%20new%20rows%20every%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20posted%20on%20stack%20exchange%20(%3CA%20href%3D%22https%3A%2F%2Fdba.stackexchange.com%2Fq%2F225401%2F283%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdba.stackexchange.com%2Fq%2F225401%2F283%3C%2FA%3E)%20and%20I'm%20getting%20responses%20from%20people%20saying%20it%20is%20most%20likely%20transaction%20logs%20being%20backed%20up%20every%2015%20minutes.%20I%20do%20not%20belive%20this%20to%20be%20the%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20far%20as%20I%20can%20tell%20these%20records%20are%20type%20%22D%22%20databases%20and%20not%20type%20%22L%22%20for%20logs.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20all%20these%20databases%20are%20not%20being%20backed%20every%20day.%20Most%20of%20them%20are%20static%20snapshots%20strictly%20used%20for%20reporting.%20I%20have%20maintenance%20plans%20for%20backing%20up%2012%20of%20these%20databases%20on%20a%20daily%20basis.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%26nbsp%3Bis%20the%26nbsp%3B%3CSPAN%3Emsdb.dbo.backupset%20table%20getting%20written%20to%20every%2015%20minutes%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDo%20any%26nbsp%3Bof%20you%20see%20this%20activity%20on%20your%20MSDB%20database%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-304808%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMSDB%20Huge%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Established Member

I have a SQL Sever 2008 R2 with 134 separate databases. The MSDB database is huge. When I query the msdb.dbo.backupset table I see that every 15 minutes there are 134 new rows being added, one for each database. This happens every fifteen minutes, all day long.  This amounts to 12,864 new rows every day.

 

I've posted on stack exchange (https://dba.stackexchange.com/q/225401/283) and I'm getting responses from people saying it is most likely transaction logs being backed up every 15 minutes. I do not belive this to be the case.

 

As far as I can tell these records are type "D" databases and not type "L" for logs. 

 

I know that all these databases are not being backed every day. Most of them are static snapshots strictly used for reporting. I have maintenance plans for backing up 12 of these databases on a daily basis.

 

Why is the msdb.dbo.backupset table getting written to every 15 minutes?

Do any of you see this activity on your MSDB database?

 

0 Replies