Work adventure : MoveSQL Agent Jobs to another instanse

It is very easy to move (recreate) the sql agent job that you want to export to another instance of SQL server

1. Highlight the job you want o export
2. Right click
3. Choose Scrip Job as CREATE TO

The script is created and you can run that in the new instance that will create the job.

To move multiple jobs
You can also script multiple jobs to a single file. In SSMS

Click on Jobs,
hit F7 to get the 'Object Explorer Details' tab.
Highlight the jobs you want,
Script job as...

CPU consumption

--from SQLAuthority website
--SQL SERVER – Who is consuming CPU on my SQL Server?

WITH DB_CPU_Statistics




AS


(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)

CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N'dbid') AS pa

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Statistics

WHERE DatabaseID <> 32767 -- ResourceDB

ORDER BY [CPU Ranking] OPTION (RECOMPILE);

Add linked servers

--view linked servers



exec sp_linkedservers




--OR



select * from sys.servers where is_linked = 1



　

--add a linked server


EXEC sp_addlinkedserver

@server=N'STAGINGCONNECT',

@srvproduct=N'SQL Sever',

@provider=N'SQLNCLI',

@datasrc=N'DB7830F';



　

　

--view the linked servers and the logins to the linked server



select s.name, p.principal_id, l.remote_name

from sys.servers s

join sys.linked_logins l

on s.server_id = l.server_id

left join sys.server_principals p

on l.local_principal_id = p.principal_id

where s.is_linked = 1

Work Adventure: Fix Orphaned user

When I restored a database to a new server, the users were imported but the login were not. The database users were  imported as the orphan users in SQL 2012

Here is how I found the orphaned users in a database

EXEC sp_change_users_login @Action='Report'

--OR we can run the following in the current database context

select * from sysusers where issqluser = 1 and (sid <> 0x0

and sid is not null)

and (LEN(sid) <= 16 ) AND suser_sname(sid) is null 





-----------------------------------------

The in order to link the orphan user to the existing login  I did the following


sp_change_users_login 'Update_One', 'OrphanUser, 'Logintolinkto'

 

Configuring Dbmail

--check for operators
use msdb
Go
select * from msdb.dbo.sysoperators

--add operators
use msdb
Go
exec dbo.sp_add_operator @name= User1, @enabled = 1, @email_address = 'user1e@abc.com'
exec dbo.sp_add_operator @name= 'User2, @enabled = 1, @email_address = 'user2@abc.com'

--enable the database mail
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

--Set up account and profile.


--One last thing but most important thing left is Letting the SQL Server Agent Talk to Database Mail. If we do not perform this step we get the following error
Avoiding 'Failed to notify <operator> via email' errors

1. -Highlight the "SQL Server Agent" in the object Explorer
2. -Go to Properties
3. -Go to Alert system options
4. - Check the enable profile on and select the profile

 

Error: Failed to notify via email

The above error occurs when we forget to perform the step to let the SQL Server Agent Talk to Database Mail

To perform that , follow the following steps -

-Highlight the "SQL Server Agent" in the object Explorer
-Go to Properties
-Go to Alert System options
- Check the enable profile on and select the profile  in the mail Session section.

Work Adeventure: Backup database to network

I had a situation where I had to perform a backup of the database on a network. I performed the following

Mapped the  nwMachine\L share to Y drive.
Thus I had (\\nwMachine\L) Y:

If I do a backup at this point the SQL server will not see the mapped drive (Y:) yet .
I had to run few scripts to make SQL server see the mapped Y drive.

EXEC sp_configure 'advanced', 1

RECONFIGURE WITH override

GO

--to enable the xp_cmdshell
--once the job is done make sure this is turned off

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE WITH override

GO


now make sql server see the mapped drive
EXEC xp_cmdshell 'net use Y: \\nwMachine\L
GO



--EXEC xp_cmdshell 'net use <local mapped drive> (space)  <shared path>
 
--Once we get result as "command ran successfully" we are successful.

--Now in the backup wizard the Y drive should appear along with the other local drives.

--now turn off the xp_cmdshell

EXEC sp_configure 'xp_cmdshell',0

RECONFIGURE WITH override

GO