Monday, April 27, 2015

Backup not starting for Database with Full Text Catalog

Today when I was taking backup of a database got below error:
Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0×80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).
When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status. These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed. SQL server backup change status between PAUSE & RESUME before & after backup. 
Than I started Full text Service to resolve the error. And than try to backup and now it worked successfully.

Monday, April 13, 2015

Start, Stop and Disable the job in SQL Server 2008

1) Use below procedure to start the Job in SQL

Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'

Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'


Exec msdb.dbo.sp_start_job  N'Job name'

2) Use below procedure to stop the Job in SQL

Exec msdb.dbo.sp_stop_job @job_name = 'Job_Name'

Exec msdb.dbo.sp_stop_job @job_name = N'Job_Name'


Exec msdb.dbo.sp_stop_job  N'Job name'

3) Use below procedure to enable or disable the Job in SQL

To enable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 1

To disable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 0