March 16, 2009 at 11:06 am
One of the disks on a reporting server filled up this morning. As I investigated why the disk filled up I noticed the the MSDB database was much larger than normal, to the tune of a 2.3G data file. Naturally I wanted to know why, so I ran a report of the Disk Usage by Top Tables. I found that the BackupFile, BackupFileGroup and BackupSet tables where the largest in the DB, with the largest of those having over 2.9M records. I looked deeper and found that somehting is logging a backup of the ReportServerTempDB multiple times a second, even though backups are only scheduled for once and evening, at 23:00.
I can find no jobs or maintenance plans on this box that are running any sort of backup on ReportServerTempDB, or any other DB on the server, that often. I do not know what process is causing this and any suggestions will be appreciated.
Sample of the BackupFile and BackupSet data (there are millions of these):
(BackupType = D = Full Backup)
BackupStartTime, BackupEndtime, BackupType, Database Name
2009-03-16 10:14:52.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:52.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:53.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:53.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:54.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:54.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:55.0002009-03-16 10:14:55.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:56.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:56.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:57.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:57.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:58.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:58.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:14:59.0002009-03-16 10:14:59.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:00.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:00.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:01.0002009-03-16 10:15:01.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:02.0002009-03-16 10:15:02.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:03.0002009-03-16 10:15:03.000DReportServerTempDB
2009-03-16 10:15:04.0002009-03-16 10:15:04.000DReportServerTempDB
2009-03-16 10:15:04.0002009-03-16 10:15:04.000DReportServerTempDB
2009-03-16 10:15:05.0002009-03-16 10:15:06.000DReportServerTempDB
2009-03-16 10:15:05.0002009-03-16 10:15:06.000DReportServerTempDB
2009-03-16 10:15:06.0002009-03-16 10:15:06.000DReportServerTempDB
2009-03-16 10:15:06.0002009-03-16 10:15:06.000DReportServerTempDB
2009-03-16 10:15:06.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:06.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:07.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:07.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:07.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:07.0002009-03-16 10:15:07.000DReportServerTempDB
2009-03-16 10:15:08.0002009-03-16 10:15:08.000DReportServerTempDB
2009-03-16 10:15:08.0002009-03-16 10:15:08.000DReportServerTempDB
2009-03-16 10:15:08.0002009-03-16 10:15:08.000DReportServerTempDB
2009-03-16 10:15:08.0002009-03-16 10:15:08.000DReportServerTempDB
2009-03-16 10:15:09.0002009-03-16 10:15:09.000DReportServerTempDB
2009-03-16 10:15:09.0002009-03-16 10:15:09.000DReportServerTempDB
2009-03-16 10:15:09.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:09.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:10.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:10.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:11.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:11.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:12.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:13.000DReportServerTempDB
2009-03-16 10:15:12.0002009-03-16 10:15:13.000DReportServerTempDB
I see these in the SQL Log file:
Date3/16/2009 10:14:30 AM
LogSQL Server (Archive #1 - 3/16/2009 10:15:00 AM)
SourceBackup
Message
Database backed up. Database: ReportServerTempDB, creation date(time): 2008/08/07(14:46:13), pages dumped: 186, first LSN: 75320:179:37, last LSN: 75320:195:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL\Backup\ReportServerTempDB.bak'}). This is an informational message only. No user action is required.
Date3/16/2009 10:14:29 AM
LogSQL Server (Archive #1 - 3/16/2009 10:15:00 AM)
SourceBackup
Message
Database backed up. Database: ReportServerTempDB, creation date(time): 2008/08/07(14:46:13), pages dumped: 186, first LSN: 75320:110:37, last LSN: 75320:126:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL\Backup\ReportServerTempDB.bak'}). This is an informational message only. No user action is required.
This behavior seems to have stopped as of 10:15, when we rebooted the server because of the disk space issue. Once the server rebooted I suddenly had 1.25G free on C:, which is odd, but I'll take it.
Thanks,
Chris
Up to 3 per second.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
March 16, 2009 at 11:23 am
Crazy, if you're getting that many, fire up Profiler and get a check on what might be kicking it off. I haven't heard of this happening, but you never know.
March 16, 2009 at 1:12 pm
How are the backups set up to run (third party tool, SQL Server job / maintenance plan, homegrown app, something else?)? Have you checked there?
March 17, 2009 at 5:57 am
I am a contractor here and do not have much knowledge being passed to me. I have to hunt most o fit down. I see no maintenance plans or third-party backup products on this server. I have put one backup job in place, which backs up all DBs once a day, and I check but see that job is not running at the time when I see the supposed backups occurring.
I just saw in the SQL Log that the backup has been repeating itself since the job firead at 23:00 last night . This tells me that it must me my backup script causing this, but if that were the case I would think I would see that job as being active in the Job Activity Monitor, yet I do not. In fact, job history says the job failed last night. Unfortunately I cannot tell why because the job step output exceeds the available space in the history log and I forgot to set up an output file for the job step. I just fixed that.
I gotta figure it is a logic problem in my script, but it is surprizing that SQL Server would just drop the job status even though the job is still running.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply