BLOGS

ByDr. SubraMANI Paramasivam

Create a new job from already existing job

You can create a brand new job by duplicating already existing job within the SQL Server instance. Open SSMS and then go down to SQL Server Agent and expand to see the JOBS and again expand to see all the available Jobs. From the below screen, I have chosen a maintenance plan job and then right click on it to see the available options on it and mouse over “Script Job as” and then further mouse over to “CREATE To” and then choose the relevant options for you. I would suggest to choose “New Query Editor Window” and this gives all the settings that is available for this job. You can choose this and rename wherever required and you can execute the same by creating a new job accordingly.

CreateScript_fm_Job

ByDr. SubraMANI Paramasivam

Automation of taking backup of all databases within single SQL Server instance

Below scripts will enable you to take backup of every single database except Model & TempDB databases, by using cursors on it. Strongle advise not to run this with very large databases or in OLTP environments where the systems are heavily used.

 

DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)

SET @backupPath =
‘F:\’

DECLARE All_Backup_Cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN (‘Model’,’TempDB’);
OPEN All_Backup_Cursor
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
END
CLOSE All_Backup_Cursor
DEALLOCATE All_Backup_Cursor

ByDr. SubraMANI Paramasivam

Job Activity Monitor

After you have created your new job following this article, you can view all the new created jobs and monitor the health of the jobs like whether it succeeded or failed, how long it took to run, reasons if it has failed, what time it started and what time it completed, when is the next run scheduled, etc.,

Once you have opened your SSMS follow the JOBS section and you can see JOB ACTIVITY MONITOR once you expand it. Double click on this and you can find below screen, which gives overall status.

JobActivityMonitor

If you want to take further action on each job you can right click on any of the listed jobs and you will find more options on this like below screen. In this you have options to Start the job from any step level, stop the job, enable/disable the job, refresh to see the current job running status, delete the job, view the whole history of job run  and properties to further edit by opening the job itself.

JobOptions

ByDr. SubraMANI Paramasivam

Create new SQL Server Job

Whatever developments you have done, may require a job to get it scheduled to ensure the data is always up to date for your reporting purpose. The schedule to update the job is achieved using SQL SERVER AGENT JOBS. Below is the screenshot that shows how you can create a new job. Open SSMS and connect to your server and navigate down to SQL Server Agent and expand it. Here you will find jobs and right click on it to get a new menu and choose “New Job…”

Create_Job_01

After this a new pop-up screen appears and you need to key in your new job name. I am using below screenshot from one my colleague’s computer and hence you see a different name on it.

Create_Job_02

In the same pop-up you have to go step by step tab in the left side. Go to steps and then provide the path to your SSIS package, but in the below example, I am giving an option to run SQL Script. And then go to schedules tab and choose how frequent you want this job to run, like hourly / daily / weekly / monthly / quarterly / yearly.

Create_Job_03

ByDr. SubraMANI Paramasivam

Explore DMVs

You can explore DMV’s within SQL Server Management Studio. A sample picture is provided below.

Explore_DMV

Also you can right click on any DMV’s and then select top 1000 rows how you do like every other table in your local databases. Below image provides information on selecting DM_OS_BUFFER_DESCRIPTORS DMV.

DMV_OS_Buffer_01