Pause Replication: Publication to Subscriber

--TO Pause Replication from Publication to Subscriber

--EX: if i want to Pause Replication from
  --4 to  01
  --5 to  01


SELECT @@SERVERNAME


exec distribution.dbo.sp_MSstopdistribution_agent
@publisher  = '4',
@publisher_db   = 'TEST',-
@publication    = 'TEST_Pub',--publisher names
@subscriber     = '1',
@subscriber_db  = 'TEST'



---to Start Again
exec distribution.dbo.sp_MSstartdistribution_agent
@publisher  = '4',
@publisher_db   = 'TEST',
@publication    = 'TEST_Pub',
@subscriber     = '1',
@subscriber_db  = 'TEST'



--TO pause Publication: not replicating TO Any Subscriber
--just right click on Replication Publication ,
--View Log reader agtent status
--LOG reader agent and STOP

Restore Database on A Database which is Enabled for Replication

Replication supports restoring replicated databases to the same server and database from which the backup was created. If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. In this case, you must re-create all publications and subscriptions after backups are restored.





https://msdn.microsoft.com/en-us/library/ms152560(v=sql.120).aspx

Find when my SP last executed and find count of how many times the SP got executed after RESTART

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time, d.total_elapsed_time,
    d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
    d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;

How can I track database dependencies across all databases in SQL Server

I had a situation yesterday, I altered Database to offline with purpose, and suddenly some of the hidden part application broke ,and i made the database Online , then Application came online ...

before that I have used SQL Profiler trace and Database Dependency check etc and took care of Dependencies, connection etc .but couldn't figure it out if any other databases are  using the Database tables which i made offline...

google lot of research but u know what ..

RedGate SQL Search

RedGate  Dependency tracker helped me 

RedGate tools Rocks  

Conversion failed when converting the varchar value to data type int

I have a tableA
(ID int , Name Varchar(10), EmpId Varchar(10))


I store all numbers in Empid Column
Ex:
EmpID:
123
3445
4566
35476
etc..



There are few stored procedures which uses the same tableA,empid column data and do some conversions and suddenly the SP was failing 

Conversion failed when converting the nvarchar value '15.       ' to data type int




somehow , some one entered data like below:
EmpID:
123
3445
4566
35476
34123.

YES, there was dot(period) added to the data.

so Resolution for issue was: 

First:Convert the Empid(Varchar) to Float and then to INT
cast(CAST(Empid AS FLOAT) as int

This fixed the issue perfectly instead of modifying the column Datatype, or updating data in table