Monday, June 27, 2016

To check the last successful run date and time of the sql job

Declare @jobId as uniqueidentifier

select

j.job_id,j.Name as "Job Name", j.description as "Job Description", CONVERT(DATETIME, RTRIM(h.run_date))

+ ((h.run_time / 10000 * 3600)

+ ((h.run_time % 10000) / 100 * 60)

+ (h.run_time % 10000) % 100) / (86399.9964) AS run_datetime,

case h.run_status

when 0 then 'Failed'

when 1 then 'Successful'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as JobStatus

from sysJobHistory h, sysJobs j

where j.job_id = h.job_id and h.run_date =

(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id ) and h.run_status= 1

and cast(cast(h.run_date as varchar(8)) as date) = cast('06/27/2016' as date)

order by 2

Friday, June 24, 2016

Move a user databases

To move a data or log file as part of a planned relocation, follow these steps:
  1. Run the following statement.
     
    ALTER DATABASE database_name SET OFFLINE;  
    
    
  2. Move the file or files to the new location.
  3. For each file moved, run the following statement.
     
    USE master
    GO
    ALTER DATABASE AdventureWorks2012
    MODIFY FILE 
    ( NAME = AdventureWorks2012_Data, 
    FILENAME = 'C:\Disk2\AdventureWorks2012_Data.mdf'); -- New file path
    
    USE master
    GO
    ALTER DATABASE AdventureWorks2012 
    MODIFY FILE 
    ( NAME = AdventureWorks2012_Log, 
    FILENAME = 'C:\Disk2\AdventureWorks2012_log.ldf'); -- New file path
    
  4. Run the following statement.
     
    ALTER DATABASE database_name SET ONLINE;  
    
    
  5. Verify the file change by running the following query.
     
    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  
    WHERE database_id = DB_ID(N'<database_name>');  
    
    

Thursday, June 23, 2016

Start the SQL Server instance in single user mode.




Click on SQL Configuration Manager
Click on SQL Services
Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box. ( make sure that the semicolon exists after the m.


-m;dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf


Once the desired work is done, make sure to remove the newly added parameter.



Friday, June 17, 2016

SQL Agent Job: Update the notification operator

I found many SQL Agent job were enabled but no any  notifications setup in case of failure.

I ran the following

use msdb
GO
select * from dbo.sysjobs

where notify_level_email = 0

and enabled = 1

--- gives the list of jobs enabled but no notification set up.

SELECT [ID], [Name], [Enabled]

FROM MSDB.dbo.sysoperators

WHERE [Enabled] = 1

ORDER BY [Name];
Go

-- Gives the list of operators. Pick the Id of the one you want to notify to use in the below script








UPDATE S

SET S.[notify_level_email] = 2,

S.[notify_email_operator_id] = 2   ---my operator

FROM MSDB.dbo.sysjobs S

WHERE S.[Notify_Level_Email] = 0

AND S.[Enabled] = 1;




GO