SQL Server

SQL Job Scheduler History

Here is an easy way to get list of jobs ran in the past with status. Do you know of a better or simpler way to get the output? If so, please use the comment area to share your code.

 

   1:  CREATE  PROCEDURE [dbo].[pr_check_JobHistory] @dateparam DATETIME = NULL
   2:  
   3:  AS
   4:  
   5:  BEGIN
   6:    IF @dateparam IS NULL
   7:    SET @dateparam = GETDATE();
   8:  
   9:    SELECT sysjobhistory.server,
  10:           sysjobs.name
  11:           AS
  12:           job_name,
  13:           CASE sysjobhistory.run_status
  14:             WHEN 0 THEN 'Failed'
  15:             WHEN 1 THEN 'Succeeded'
  16:             ELSE '???'
  17:           END
  18:           AS
  19:           run_status,
  20:           Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
  21:                         Substring(CONVERT(VARCHAR
  22:                                   (8), run_date), 5, 2) + '-' +
  23:                  Substring(CONVERT(VARCHAR(
  24:                            8), run_date), 7, 2), '')
  25:           AS
  26:           [Run DATE],
  27:           Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
  28:                   +
  29:                         Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
  30:                          )
  31:                  +
  32:                  ':' +
  33:                  Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
  34:           AS
  35:           [Run TIME],
  36:           Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
  37:                   ':' +
  38:                         Substring(CONVERT(VARCHAR(7), run_duration+1000000),
  39:                         4,
  40:                         2)
  41:                  + ':' +
  42:                  Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
  43:           ''
  44:           ) AS
  45:           [Duration],
  46:           sysjobhistory.step_id,
  47:           sysjobhistory.step_name,
  48:           sysjobhistory.MESSAGE
  49:    FROM   msdb.dbo.sysjobhistory
  50:           INNER JOIN msdb.dbo.sysjobs
  51:             ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
  52:    WHERE  sysjobhistory.run_date = Datepart(yyyy, @dateparam) * 10000 +
  53:                                           Datepart(mm, @dateparam) * 100 +
  54:                                    Datepart
  55:                                    (
  56:                                           dd, @dateparam)
  57:           --AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day
  58:    ORDER  BY instance_id DESC
  59:  END

Change column type in SQL Server

There are two ways to change the column type in SQL Server:

1- Is by writing a quick code in query script.

2- Is by using the enterprise manger: SSMS (SQL Server Management Studio).

 

Method 1

Run the following code in SQL server:

ALTER TABLE <table name> ALTER COLUMN <column name> DATA TYPE

Example Code:
ALTER TABLE tblExampleTable ALTER COLUMN ID INT


See Microsoft webpage for full Alter Table syntax details:


http://msdn.microsoft.com/en-us/library/ms190273.aspx






Method 2


To do the changes using the second method follow these steps:


a- Open SSMS


b- Navigate to your table which you want to modify column


c- Find column and Right click the choose Modify


image


d- A window will open in Edit mode so that you can change the type of each column:


image


e- Click on Save once you are done.


Note that when you click Save, you might show the below warning message. Saving changes is not permitted:


image


In this case, you can change some settings to allow this save and avoid the warning. To do this simply follow these steps:


1- In SSMS, click on Tools


2- Choose Options


3- Click on the Designers section from the left side menu


4- Un-check the box for: Prevent saving changes that require table re-creation


image


Now try to save again and this time it will work.


 


			

	
	



			
				


			
		

		
Move database files .mdf & .ldf to different physical location
	


		

		
Here is a brief and simple way to move your Data files from one location to another.


This method involves using SSMS (SQL Server Management Studio)


1- Open SSMS and locate to database you want to change Data file directory


2- Remember/Note the current Data file directory path. You can do this by Right Clicking on the database and choose Properties. Then, from the left side menu, click on Files to see the Path of your current Data file directory path. Note it down or click on Control + C to copy the path name.


image


3- Detach Database by Right clicking on the database and choosing >> Tasks >> Detach


4- A new window will appear. Click on Drop Connections check box and then click OK.


image


5- Using the windows explorer, go to your Data file directory which you copied or wrote down in step 2:


image


6- Copy/Move the Data File (.MDF) and the Transaction Log file (.LDF) to your new physical location. Note that I always recommend copying first instead of moving in case the file gets corrupted during the move, you will still have the original one to go back to.


image


7- Once you copied the two files, click on Database(s) main folder and choose Attach. Then, from the new opened window, click on Add:


image


8- Navigate to the new path where files are copied and choose the .pdf file and click on OK:


image


9- Now you can see that both the Data File (.MDF) and Transaction Log File (.LDF) are shown in the new location. Click OK and you are done (You need to refresh the Databases for the new attached database to appear again).


image


 


To improve the quality of this user-guide, please help me with some feedback or by simply commenting below.