June 17, 2010 at 1:56 am
Hello,
I have some unusual behavior from my MSSQL2008.
Just this few days.. I have found my Drive C:\ is flooding with backup from unknown job. All my databases is backup-ed into c:\Backup (By all databases I meant if I add a new database, then it's automatically followed being backup the next day)
The backup (.bak) is created every night at 9pm.
I have checked all my scheduled job, and I found no job that points to that folder. I have also monitor the files was created by sqlserver.exe (So I think the culprit must be MSSQL himself).
I suspected some bugs prevented me from seing this "unseen" backup job,
but I tried to list-up all my scheduled job using msdb.dbo.sysjobs, but still can't find it.
It surely is iritating coz it's eating up my C:\ dataspace
Any help is welcomed..
Thanks a lot from any comments here..
June 17, 2010 at 3:22 am
Check the Windows Scheduled tasks.
Also run Profiler to see from which host BACKUP command is issued.
June 17, 2010 at 7:54 am
Thx for the suggestion
I have checked Windows Scheduler and it's clean.
I tried to make a profiler like ur saying, but I'm not sure how to make a backup profiler,
anyone care to tell me how to do it ?
Thx for any comments here..
June 17, 2010 at 8:37 am
Perhaps your Netwrok Admin is running BackupExec from another server ?
Run this and look at the user_name colums for the various backups
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, user_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -60, (getdate())) -- Last 60 days
AND sysdb.name = 'My_DB_Name'
ORDER BY sysdb.name, bkup.backup_finish_date desc
June 17, 2010 at 8:55 am
Here's one I've used, similar to homebrew01's just pull from few more tables:SELECT distinct t1.name AS 'DatabaseName'
,(datediff( ss, t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'
,t3.user_name AS 'UserResponsible'
,t3.name AS backup_name
,t3.description
,t3.backup_start_date
,t3.backup_finish_date
,CASE
WHEN t3.type = 'D' THEN 'Database'
WHEN t3.type = 'L' THEN 'Log'
WHEN t3.type = 'F' THEN 'FileOrFilegroup'
WHEN t3.type = 'G' THEN 'DifferentialFile'
WHEN t3.type = 'P' THEN 'Partial'
WHEN t3.type = 'Q' THEN 'DifferentialPartial'
END AS 'BackupType'
,floor(((t3.backup_size/1024)/1024)) AS 'BackupSizeMB'
,t6.physical_device_name
,CASE
WHEN t6.device_type = 2 THEN 'Disk'
WHEN t6.device_type = 102 THEN 'Disk'
WHEN t6.device_type = 5 THEN 'Tape'
WHEN t6.device_type = 105 THEN 'Tape'
END AS 'DeviceType'
,t3.recovery_model
FROM sys.databases t1
INNER JOIN msdb.dbo.backupset t3 ON (t3.database_name = t1.name )
LEFT OUTER JOIN msdb.dbo.backupmediaset t5 ON ( t3.media_set_id = t5.media_set_id )
LEFT OUTER JOIN msdb.dbo.backupmediafamily t6 ON ( t6.media_set_id = t5.media_set_id )
--WHERE t1.name = ''
--WHERE t3.type = 'D'
ORDER BY backup_start_date DESC
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply