where to find the scheduled jobs

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

General SQL Server Forums

New to SQL Server Administration

where to find the scheduled jobs

Author

Topic

zhuang
Starting Member

6 Posts

Posted - 2014-05-16 : 09:25:59

Hi,

Query through my newly created SQL server instance, I can find that it has been backed up daily, but I couldn't find the scheduled job which does this. I checked SQL Server agent, and maintenance Plans, but not there. The backup location "physical_device_name" is like "{6A00935F-7389-433D-819B-753F237204D8}7".

Could you advise where I could find the scheduled this backup job?

Thanks,

Zhuang

Results:

SQLZHUANGLI\ZLSQLV12 master 2014-05-12 20:38:23.000 2014-05-12 20:38:28.000 NULL Database 3751936 NULL {6A00935F-7389-433D-819B-753F237204D8}7
SQLZHUANGLI\ZLSQLV12 master 2014-05-13 21:44:24.000 2014-05-13 21:44:28.000 NULL Database 3740672 NULL {BBFD068B-C9C6-4A9E-839A-5CD4E666D04C}7
SQLZHUANGLI\ZLSQLV12 master 2014-05-14 21:47:39.000 2014-05-14 21:47:43.000 NULL Database 3760128 NULL {1B1EE999-6D4A-451C-80AC-C279670A1C05}7
SQLZHUANGLI\ZLSQLV12 master 2014-05-15 21:53:55.000 2014-05-15 21:53:59.000 NULL Database 3763712 NULL {B27F935A-CE40-459C-88C1-56C4497C60B1}8
SQLZHUANGLI\ZLSQLV12 model 2014-05-12 20:38:23.000 2014-05-12 20:38:28.000 NULL Database 2622976 NULL {6A00935F-7389-433D-819B-753F237204D8}1
SQLZHUANGLI\ZLSQLV12 model 2014-05-13 21:44:24.000 2014-05-13 21:44:28.000 NULL Database 2622976 NULL {BBFD068B-C9C6-4A9E-839A-5CD4E666D04C}1
SQLZHUANGLI\ZLSQLV12 model 2014-05-14 21:47:39.000 2014-05-14 21:47:43.000 NULL Database 2622976 NULL {1B1EE999-6D4A-451C-80AC-C279670A1C05}1
SQLZHUANGLI\ZLSQLV12 model 2014-05-15 21:53:55.000 2014-05-15 21:53:59.000 NULL Database 2625024 NULL {B27F935A-CE40-459C-88C1-56C4497C60B1}1

Query:

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-16 : 12:03:19

Perhaps NetBackup? Check with the server team to see if Backup Exec is configured to backup the SQL Server databases.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-19 : 01:44:35

if it's running at the same everyday - you could trace the activity.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

zhuang
Starting Member

6 Posts

Posted - 2014-05-19 : 04:22:37

Thanks both for the reply. It is running daily at the same time. Could you give me more advice on how to find out the scheduled job please? I am new to SQL Server and Windown Operting system.

Thanks,

Zhuang

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-19 : 18:52:33

In re: "I couldn't find the scheduled job which does this."
Where are you looking for this information? Also, are you a sysadmin within SQL Server?

Opportunity is missed by most people because it is dressed in overalls and looks like work. - T.A.Edison

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-20 : 01:31:34

@zhuang - if you know this job is running at the same time everyday - use sp_who2 or the sys.dm_exec_requests view to get some clues .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

zhuang
Starting Member

6 Posts

Posted - 2014-05-20 : 04:59:17

Thanks, Checked sp_who2 and sys.dm_exec_requests, still no clue.

The log shows as follows:

05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: abc<c/> creation date(time): 2014/05/19(16:16:25)<c/> pages dumped: 330<c/> first LSN: 32:275:63<c/> last LSN: 32:303:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}8'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: sysutility_mdw<c/> creation date(time): 2014/05/15(15:59:59)<c/> pages dumped: 2962<c/> first LSN: 127:3632:60<c/> last LSN: 127:3659:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}7'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: AdventureWorks2012<c/> creation date(time): 2014/04/07(14:06:44)<c/> pages dumped: 24225<c/> first LSN: 43:469:1<c/> last LSN: 43:473:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}6'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: SalesDB<c/> creation date(time): 2014/04/03(14:23:30)<c/> pages dumped: 24601<c/> first LSN: 373:10851:1<c/> last LSN: 373:10854:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}5'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: ReportServer$ZLSQLV12TempDB<c/> creation date(time): 2014/03/17(16:30:14)<c/> pages dumped: 305<c/> first LSN: 37:309:1<c/> last LSN: 37:312:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}4'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: ReportServer$ZLSQLV12<c/> creation date(time): 2014/03/17(16:30:14)<c/> pages dumped: 561<c/> first LSN: 71:636:1<c/> last LSN: 71:639:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}3'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: msdb<c/> creation date(time): 2012/02/10(21:02:17)<c/> pages dumped: 3866<c/> first LSN: 1368:16:46<c/> last LSN: 1368:37:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}2'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: master<c/> creation date(time): 2014/05/15(15:13:10)<c/> pages dumped: 459<c/> first LSN: 212:395:106<c/> last LSN: 212:441:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}9'}). This is an informational message only. No user action is required.
05/19/2014 21:48:04,Backup,Unknown,Database backed up. Database: model<c/> creation date(time): 2003/04/08(09:13:36)<c/> pages dumped: 321<c/> first LSN: 31:490:1<c/> last LSN: 31:493:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=VIRTUAL_DEVICE: {'{621D1FB1-6D34-4AB7-AF6D-DD9AD23DAE2B}1'}). This is an informational message only. No user action is required.
05/19/2014 21:48:03,spid68,Unknown,I/O was resumed on database model. No user action is required.
05/19/2014 21:48:03,spid67,Unknown,I/O was resumed on database AdventureWorks2012. No user action is required.
05/19/2014 21:48:02,spid70,Unknown,I/O was resumed on database ReportServer$ZLSQLV12. No user action is required.
05/19/2014 21:48:02,spid62,Unknown,I/O was resumed on database msdb. No user action is required.
05/19/2014 21:48:02,spid69,Unknown,I/O was resumed on database sysutility_mdw. No user action is required.
05/19/2014 21:48:02,spid63,Unknown,I/O was resumed on database ReportServer$ZLSQLV12TempDB. No user action is required.
05/19/2014 21:48:02,spid66,Unknown,I/O was resumed on database master. No user action is required.
05/19/2014 21:48:02,spid65,Unknown,I/O was resumed on database SalesDB. No user action is required.
05/19/2014 21:48:02,spid64,Unknown,I/O was resumed on database abc. No user action is required.
05/19/2014 21:48:01,spid66,Unknown,I/O is frozen on database master. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid64,Unknown,I/O is frozen on database abc. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid69,Unknown,I/O is frozen on database sysutility_mdw. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid67,Unknown,I/O is frozen on database AdventureWorks2012. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid65,Unknown,I/O is frozen on database SalesDB. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid63,Unknown,I/O is frozen on database ReportServer$ZLSQLV12TempDB. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid70,Unknown,I/O is frozen on database ReportServer$ZLSQLV12. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid62,Unknown,I/O is frozen on database msdb. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.
05/19/2014 21:48:01,spid68,Unknown,I/O is frozen on database model. No user action is required. However<c/> if I/O is not resumed promptly<c/> you could cancel the backup.

The above runs everyday at the same time. I am a Oracle DBA, just started to learn to be a SQL server DBA.

Best Regards,

Zhuang

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-20 : 13:56:55

I'm still betting this is Netbackup's Backup Exec software performing the database backups. Check your Netbackup config or check with your server/network administrator.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

zhuang
Starting Member

6 Posts

Posted - 2014-05-21 : 05:43:58

Hi Tara,

Thanks, I shall ask.

Regards,

Zhuang

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-27 : 13:40:00

If not Netbackup - it will be another product that does the same thing. Your system administrators should know what backup software they are using to backup the systems.

zhuang
Starting Member

6 Posts

Posted - 2014-05-30 : 06:27:07

Many many thanks for all who have helped me on this topic. I am still waiting for internal reply.

Best wishes,

Zhuang

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-30 : 19:08:03

Shot in the dark but... Look in the Control Panel->Administrative Tools->Task Scheduler to see if there is a job at the OS level that launches your backup software. HTH

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources