How can you find the stored procedure used by SQL Job

If you have hundreds of sql jobs scheduled  to run everyday in SQLserver.  How quickly you can find the stored procedure used by sql job command.

Here is simple solution to find it.

use msdb
go
select sj.name as job_name, st.command
from  sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like '%uspMyBook%'

RESTORE cannot operate on database because it is configured for database mirroring.

Msg 3104, Level 16, State 1, Line 1
You come to know mirror database not sync while, If you decided to break the mirror and re-setup mirror.

1. ALTER DATABASE AdventureDB SET PARTNER OFF ( You remove the mirror first)
2. Take the full backup from the principal server copy over to mirror
3. You will see mirror database is disconnected

RESTORE DATABASE AdventureDB WITH RECOVERY

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AdventureDB' because it is configured for database mirroring.
Use ALTER DATABASE to remove mirroring if you intend to restore the database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You found this above strange error.

4. You have reboot the SQL Server. Make sure sql server services are running after re-boot.
 -- Mirror Server restarted
5. Now re-run the command on mirror server
RESTORE DATABASE AdventureDB WITH RECOVERY
RESTORE DATABASE successfully processed 0 pages in 20.281 seconds (0.000 MB/sec).
-- Database is now accessible on mirror servers

6. Start restore database backup Full, Logs with norecovery mode
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.BAK' WITH NORECOVERY
 -- Restoring now
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.Log' WITH NORECOVERY, REPLACE, STATS = 5
If you have more than one log backup do the same
7. Ready resetup the mirror again.

Find Page Life Expectancy (PLE) value for default SQL Server instance

SELECT cntr_value AS [Page Life Expectancy], @@servername AS Server
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND [object_name] = 'SQLServer:Buffer Manager'

Note: If you get cntr_value is less than 300 then, that means

An average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure or missing indexes, or a cache flush.

error 3624, status 1, severity 20. Database mirroring has been suspended

The remote mirroring partner for database 'Adventuredb', encountered
error 3624, status 1, severity 20. Database mirroring has been suspended.
Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

when you see this error on sql server logs (or) if you notice your mirror database is suspended.

Solution:

First try to resume mirroring, If mirror become suspended again please use the below steps to resovle

1. Break mirror from Principal server
2. Take a full backup of mirrored database from Principal database server
3. Restored the full backup on mirror server with NORECOVERY
4. Take a Tranactional log backup of mirrored database from Principal database server
5. Restored the Log backup on mirror server with NORECOVERY
6. Re-setup mirroring between principal and mirror server.

Hope this will help

Monitor database mirroring servers

--Simple way to monitor database mirror servers

SELECT d.name as dbname, d.database_id,

@@servername as Principal_Server,
m.mirroring_role_desc,
m.mirroring_state_desc,m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL

Msg 3201, Level 16, State 2, Line 1 Cannot open backup device Operating system error 53

When you are in the situation to restore the database if you wrongly typed path name of backup device you will come accross the following error.

USE master
go
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

--Error
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device '\\SQLSVR001\Backup\Adventureworks.BAK'. Operating system error
53(error not found).

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution:
-- Make sure to include the correct path name of backup file
RESTORE DATABASE Adventureworks
FROM DISK = '\\SQLSVR001\MSSQL\Backup\Adventureworks.BAK'
WITH RECOVERY, REPLACE, STATS = 5

At the end you will see
Msg: RESTORE DATABASE successfully processed 760350 pages in 144.614 seconds (43.071 MB/sec).

Advanced Troubleshooting with Extended Events

Paul S.Randal has written wonderful article on Technet about Troubleshooting SQL Server using extended events in SQL Server 2008.

• Why troubleshooting is necessary
• Troubleshooting tools in SQL Server
• Overview and architecture of Extended Events
• Using Extended Events

Source : http://technet.microsoft.com/en-us/magazine/2009.01.sql2008.aspx

Restore the Database from the Backup file

-- PART 1
-- Find the Backup file details like Machinename, softwareBuild, collation, backupsize, BackupStartDate
USE MASTER
go
RESTORE HEADERONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE FILELISTONLY
FROM DISK = H:\MSSQL\Backup\AdventuresDb.BAK'

-- Find the Backup file orignal File path and Logical filename, Physical filename
RESTORE VERIFYONLY
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'

-- Restore the database from the backup file
-- eg. Development database refresh from production copy
RESTORE DATABASE AdventuresDb
FROM DISK = 'H:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY, REPLACE, STATS = 10

------------------------------------------------------------------ PART 2
-- Find the Backup file orignal File path and Logical filename, Physical filename
USE MASTER
go
RESTORE FILELISTONLY
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
-- Create/Restore the Database from the backup file with move to different locations
-- eg. Development database refresh from production copy

RESTORE DATABASE AdventuresDb
FROM DISK = 'F:\MSSQL\Backup\AdventuresDb.BAK'
WITH RECOVERY,
MOVE 'AdventuresDb_Data' TO 'D:\MSSQL\Data\AdventuresDb.MDF',  MOVE 'AdventuresDb_Log' TO 'E:\MSSQL\Log\AdventuresDb_log.LDF',
STATS = 10

-- sp_helpdb AdventuresDb

Subscription cannot be created because the credentials used to run the report are not stored

When I setup subscription for reports on SQL Server Reporting Services(SSRS) the following error comes up.

Error: Subscription cannot be created because the credentials used to run the report are not stored.

Solution:
1. Click your report (eg: MyInvoiceReport)
2. Click Propertise tab
3. Click DataSource on the left hand side
4.  A custom data source ( If you already associated datasource it will be appear on )
5,. Connect using:  choose option button Credentials stored securely in the report server
     Type username and password
6. Click the check box Use as Windows credentials when connecting to the data source

Find the Report Name and its Exection time from ReportServer database on SSRS

-- Find Report Name from catalog table
Use ReportServer
go

select Type, Name, Path ,ItemId,ParentID
from [Catalog] where name like 'MyReport%'and Type = 2

 -- Find the ExecutionLog history for the Report of ItemId
select * from ExecutionLog where ReportID = '1C380565-ZXF9-9618-3727-28A04652E1B9'


 -- Please note reference between [Catalog] and [ExecutionLog] table is ItemId=ReportID

select e.ReportID, e.InstanceName,e.Format,e.TimeStart,e.TimeEnd
from [Catalog] c, ExecutionLog e
where c.ItemId= e.ReportID
and c.name like 'MyReport%' and c.Type = 2
order by e.TimeStart desc