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

Script out Multiple jobs at at time to create on another server

 To script all jobs:

Step1:  ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key.
Step2: Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.

Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. 


This will then create all the selected jobs as a single query. You can script to a query window. 

Add one table to existing publication

use dbname
go
EXEC sp_changepublication
@publication = ' Pub_name',
@property = N'allow_anonymous',
@value = 'false'
GO


EXEC sp_changepublication
@publication = 'pub_name'
@property = N'immediate_sync',
@value = 'false'
GO

EXEC sp_addarticle  @publication = N'Pub_name',
@article = N'Time',
@source_object=N'Time'
GO

 --same above if you another table


--Should run on publisher
EXEC sp_refreshsubscriptions @publication = N'Pub_name'
GO

 ----Should run on publisher
EXEC sp_startpublication_snapshot @publication = N'Pub_name'
go







---take out table from replication
--remove table from replication
 EXEC  sys.sp_dropsubscription
    @publication = N'Pub_name',
    @article = N'Time',
    @subscriber = N'SQL4',
    @destination_db = 'db_name'' ;
GO


EXEC sys.sp_droparticle
    @publication = N'Pub_name',
    @article = N'Time',
    @force_invalidate_snapshot = 1 ;
GO

Grant Read or write access to only 1 table

Follow below steps:

1. Create login, and Add to public to ABC database

2.Grant Select , insert, update , delete on Tablename to Login 

Note: if you add login to db_reader group, the user will have Read access to all Database tables.

Trace Database Activity before dropping

In our company we have decided to drop few databases , but we are not sure which application is being used ? which user is connecting ot working on it ?
So I have followed below steps to decide on dropping Database.

1.Running SQL Server Profiler Trace
Choose,Standard Default Trace:and ADD
1.Database name
2.HOSTNAME
3.NT DOMAIN NAME
4.Session Login name
5.Server Name

 Also,
Choose database name Like:
% ABC%
%XYZ% and EXCLUDE Column which do not match

run the trace for few hours First and  monitor the size of the  files and then run for 24-48 hours.


If no connections found ?
2.OFFLINE the databases  for a week
3.Backup the database and keep in separate folder(DO NOT DELETE)
4.DROP databases after notifying everyone....

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up-Issue in SQL Server Replication

Hi, I have set up Always on Availability Groups on top of Windows Server Failover Cluster(WSFC) with two sql server stand alone installations

Server1--Primary Replica

Server2--Secondary Replica

--Both above configured in Always on Availability Groups( listener name=AGListner)

Server 3-Remote Distributor Server where all sql agent jobs (Log,snapshot,dist jobs)...

Server 4--Subscriber server

I have setup Replication on Server 1 which was primary at the time of configuring replication to server 4(subscriber) 

worked fine, replicated data, when failover happened  Server 2 took over as primary and still replication worked fine...

my Issues are below and i am looking for some assistance 

1.When i shout down Server 1(for testing), Server 2 took over the role as Primary and served the applications, but when i insert data at server 2( which is now primary), server 4 Subscriber did not get the data...once i turn on Server1 , the replication is replicated and subscriber is getting data ...does 2 servers(AG Servers) should be ruining in order to replication work?

2.and when i see the Replication monitor I see the All subscriptions are running, Agents are completed and running (ALL GREEN), also jobs on distributor are still running but server 4 Subscriber did not get the data..

his helped me ..this is a life saviour

DBCC Traceon (1448,-1)
--no sql server restart needed. 

--Enables the replication log reader to move forward even if the async secondaries(AG, MIrror databases) have not acknowledged the reception of a change. 
--Even with this trace flag enabled the log reader always waits for the sync secondaries. 
--The log reader will not go beyond the min ack of the sync secondaries. 
--This trace flag applies to the instance of SQL Server, not just an availability group, an availability database, 
--or a log reader instance. Takes effect immediately without a restart. This trace flag can be activated ahead of
-- time or when an async secondary fails.

SQL Server 2016 Multiple TempDB Database Files

Multiple TempDB Database Files

It has been a best practice for a while to have more than one tempdb data file if you are running on a multi-core machine.  In the past, up through SQL Server 2014, you always had to manually add the additional tempdb data files after you installed SQL Server. With SQL Server 2016 you can now configure the number of tempdb files you need while you are installing SQL Server.  Having this new feature means you will no longer need to manually add additional tempdb files after installing SQL Server.  

Check tempdb Performance

 SELECT files.physical_name, files.name,
  stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
  stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
  ON stats.database_id = files.database_id
  AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'


--Are writes being evenly distributed between data files?
--Are writes finishing in 20ms or less

--IF NO then we have a problem
--8 database files are good ..16 data files ? may be, but try with 8 data files and then if still bad performance try adding 16


declare @milli as int
set @milli = 30251

select @milli/3600000 as hours,
(@milli - ((@milli/3600000)*3600000))/60000 as minutes,
(@milli - (((@milli)/60000)*60000)) /1000 as seconds,
@milli-(((@milli)/1000)*1000) as milliseconds

Convert Milli Seconds to seconds and minutes

declare @milli as int
set @milli = 30251

select @milli/3600000 as hours,
(@milli - ((@milli/3600000)*3600000))/60000 as minutes,
(@milli - (((@milli)/60000)*60000)) /1000 as seconds,
@milli-(((@milli)/1000)*1000) as milliseconds

Easy way to find who dropped table

create table  test1234( id int)
go
insert into test1234 Values(1)

drop table test1234
go
 SELECT
Operation,
[Transaction Id],
[Transaction SID],
[Transaction Name],
 [Begin Time],
   [SPID],
   Description
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO
--get [Transaction Id] and you can nopw find the loginname
SELECT SUSER_SNAME(0xCCE723D8CEDF0244B5963964EC5180ED)

Easy way to find who deleted rows in a table

create table  test1234( id int)
go
insert into test1234 Values(1)
go
delete from test1234

--find deleted rows
SELECT
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
   
FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'
--We found the transaction ID from the above command, now get the transaction SID of the user who has deleted the data check table name also
SELECT
    Operation,
    [Transaction ID],
    [Begin Time],
    [Transaction Name],
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = '0000:0000076b'
AND
    [Operation] = 'LOP_BEGIN_XACT'


---get login name from  [Transaction SID]
SELECT SUSER_SNAME(0xCCE723D8CEDF0244B5963964EC5180ED)

Notify when SQL SERVER and Agent Restarts (works for both)

-- Alert on SQL Server Agent and/or SQL Server service restarts
-- Replace your recipients in line # 14

-- declare the required variables
DECLARE @Message VARCHAR(MAX)
, @Subject VARCHAR(123)
, @Recipients VARCHAR(123)
, @IsDBMailEnabled BIT
, @MailProfile VARCHAR(123)
, @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
, @CurrentNodeName VARCHAR(123)
, @InstanceName VARCHAR(123)
, @RestartTime VARCHAR(123)

set @Recipients = 'naresh.koudagani@xyz.com' -- specify the recipients here, separate with ;

SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
, @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
, @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

-- SQL Server service has been restarted
IF (
SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() )
FROM SYS.DATABASES
WHERE NAME = 'TEMPDB'
) <= 3 -- SQL Server service restarted in the past 3 minutes
BEGIN
SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
FROM SYS.databases
WHERE NAME = 'TEMPDB'

SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'


IF @IsClustered = '1'
BEGIN
SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.'
END
END
ELSE
BEGIN
SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
END

SET @Message = @Message + CHAR(10)
SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'

-- Find the database mail profile name
create table #dbmail_profile
(
profileid int null,
profilename varchar(125) null,
accountid int null,
accountname varchar(125) null,
sequencenumber int null
)
insert #dbmail_profile
EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

select @MailProfile = profilename
from #dbmail_profile
where sequencenumber = 1

drop table #dbmail_profile

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMAIL',
@recipients = @Recipients,
@subject = @Subject,
@body_format = 'TEXT',
@body = @Message;

Multiple tempdb files per logicalCPUs

 When you create multiple data files they will all be in the primary file group and SQL Server uses a proportional fill algorithm to determine which file to use for each request to create an object. If all the files are exactly the same size, then SQL Server uses the files in a “round robin” fashion, spreading the load equally across the files. This is, of course, exactly what you want.

Microsoft recommends up to a 1:1 mapping between the number of files and logical CPU's because during testing of massive workloads they’ve seen performance benefits, even with hundreds of data files.

select DB_NAME(mf.database_id) database_name

, mf.name logical_name, mf.file_id

, CONVERT (DECIMAL (20,2)

, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]

, CASE mf.is_percent_growth

WHEN 1 THEN 'Yes'

ELSE 'No'

END AS [is_percent_growth]

, CASE mf.is_percent_growth

WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'

WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'

END AS [growth_in_increment_of]

, CASE mf.is_percent_growth

WHEN 1 THEN CONVERT(DECIMAL(20,2)

,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)

WHEN 0 THEN CONVERT(DECIMAL(20,2)

, (CONVERT(DECIMAL, growth)/128))

END AS [next_auto_growth_size_MB]

, physical_name from sys.master_files mf

where database_id =2 and type_desc= 'rows'

go

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

go

--------------------------------Q Drive is SSD-----------------------------------------------------------------------------------------

-----Move tempdb Files, then restart 

ALTER DATABASE TempDB MODIFY FILE

(NAME = tempdev, FILENAME = 'Q:\MSSQL\Tempdb\tempdb.mdf',SIZE =500MB , FILEGROWTH = 500MB)

GO

ALTER DATABASE TempDB MODIFY FILE

(NAME = templog, FILENAME = 'Q:\MSSQL\Tempdb\templog.ldf',SIZE =500MB , FILEGROWTH = 500MB)

GO

---ADD 15 More Tempdb files 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev2',FILENAME = N'Q:\MSSQL\Tempdb\tempdev2.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev3',FILENAME = N'Q:\MSSQL\Tempdb\tempdev3.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE 

 ( NAME = N'tempdev4',FILENAME = N'Q:\MSSQL\Tempdb\tempdev4.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev5',FILENAME = N'Q:\MSSQL\Tempdb\tempdev5.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev6',FILENAME = N'Q:\MSSQL\Tempdb\tempdev6.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb ADD FILE 

( NAME = N'tempdev7',FILENAME = N'Q:\MSSQL\Tempdb\tempdev7.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev8',FILENAME = N'Q:\MSSQL\Tempdb\tempdev8.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev9',FILENAME = N'Q:\MSSQL\Tempdb\tempdev9.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev10',

FILENAME = N'Q:\MSSQL\Tempdb\tempdev10.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev11',FILENAME = N'Q:\MSSQL\Tempdb\tempdev11.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev12',

FILENAME = N'Q:\MSSQL\Tempdb\tempdev12.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev13',FILENAME = N'Q:\MSSQL\Tempdb\tempdev13.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

 ALTER DATABASE tempdb 

 ADD FILE ( NAME = N'tempdev14',FILENAME = N'Q:\MSSQL\Tempdb\tempdev14.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

ALTER DATABASE tempdb 

ADD FILE ( NAME = N'tempdev15',FILENAME = N'Q:\MSSQL\Tempdb\tempdev15.ndf' , SIZE =500MB , FILEGROWTH = 500MB) 

GO

 

Creating copy of existing SQL Server for development or testing purpose

Installation :

Install .NET frame work 3.5

Install SQL Server 2012

Applied latest Service pack 2(Restart)

Copy-Restore the databases from  server X to Server Y 

Changed  all databases compatibility to 110

DB mail:

Configure Database Mail, SQL Operator, Configure Mail profiles to SQL Agent , Restart SQL Agent

Logins/Users:

Create necessary logins,users and check if any orphan users 

sa with strong password 

Jobs:

Created Backup Jobs to run Daily 11am(purge backups older than 2 days)

Rebuild indexes   

Update Statistics 

Created Space alert Job 

Linked Servers:

create if needed any 

.net Framework 3.5 (NetFx3 ) error while installing SQL Server 2012

TITLE: Microsoft SQL Server 2012 Service Pack 1 Setup
------------------------------

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , Please try enabling Windows feature : NetFx3 from Windows management tools and then run setup again. For more information on how to enable Windows features ,
see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.3000.0&EvtType=0x681D636F%25401428%25401

------------------------------
BUTTONS:

OK
------------------------------


Resolution:
Install net Framework 3.5 Features
NOTE: should have the OS CD mounted to CD ROM 

Server Manager -->Add Roles and Features-->Role -based-->.net Framework..
http://sqlblog.com/blogs/sergio_govoni/archive/2013/07/13/how-to-install-netfx3-on-windows-server-2012-required-by-sql-server-2012.aspx

Restore from database snapshot drops FULLTEXT CATALOGS

I am working with Automation team and for the sake of testing i have created the snapshot, the database contains FULL TEXT Catalogs...

so, when i have restored the Snapshots to database  i have lost all FULL TEXT catalogs..

FYI.. must backup of Full text CATALOGS before taking any snapshot backups or restores

fyi...https://technet.microsoft.com/en-us/library/ms179557(v=sql.105).aspx

Advantages of using snapshots:
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

Managing jobs in always on Availability groups

I have Setup Always on Availability Groups between 2 servers and i have a Script that i wanted to put in Job and i want to run the job only on primary at any time..(one active and one secondary)

USE MASTER
GO
DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'



BEGIN
USE TestAG
Insert into CheckAvailabilityRoleJobs(InsertDate,ServerName,RoleDescription)
SELECT GETDATE(),@@SERVERNAME, a.role_desc
FROM      sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b ON b.replica_id = a.replica_id
where   b.replica_server_name=@@SERVERNAME
END


ELSE

BEGIN

      RETURN  
END 

I wanted to keep this job running 24X7 on both servers, but insert happens only on Primary at any time , in secondary the JOB exits with out actually inserting...

the script  is running good on PRIMARY, but when i try to run on SECONDARY it is failing ...

the script should actually exit on SECONDARY after checking Primary or not but it is trying too connect TESTAG database which is not accessible on secondary

Msg 976, Level 14, State 1, Line 16
The target database, 'TestAG', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Answer:

I found out that ...secondary server should be Readable ..it is not possible to run jobs/check in secondary servers  which are not readable 

Failover or Restart Results in Reseed of Identity – FIX ALWAYS ON Availability Groups

I did set up always on between 2 servers 1 active 1 passive(readable not writable)
i had  a table with identity, when failover happens and i try to insert the data , the identity went from current row to 1000, i was surprised and the with little bit of research i found a way to solve the issue...

WHEN EVER SQL restart happens or Failover happens which means a Role is changed , so when a role is changed we need to have a mechanism to notify

1. Configure role with error number =1480
and then in response execute a sql job ,and notify Operator

SQL JOB Code below: on both servers..

USE MASTER
GO

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)


SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'



BEGIN
USE MASTER
EXEC [dbo].[sp_FixIdentityReSeeds]
END


ELSE

BEGIN

      RETURN  
END




------------The sp is below


USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_FixIdentityReSeeds]    Script Date: 10/8/2015 2:24:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_FixIdentityReSeeds]
AS
BEGIN

    --foreach database
    DECLARE @DatabaseName varchar(255)

    DECLARE DatabasesCursor CURSOR READ_ONLY
    FOR
        SELECT name
        FROM sys.databases
        where name not in ('master','tempdb','model','msdb') and
        sys.databases.state_desc = 'online'

    OPEN DatabasesCursor

    FETCH NEXT FROM DatabasesCursor
    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0
    BEGIN

        EXEC ('USE '+@DatabaseName + '

        --foreach identity column
        DECLARE @tableName varchar(255)
        DECLARE @columnName varchar(255)
        DECLARE @schemaName varchar(255)

        DECLARE IdentityColumnCursor CURSOR READ_ONLY
        FOR

            select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA
            from INFORMATION_SCHEMA.COLUMNS
            where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME,
                                 ''IsIdentity'') = 1 and TABLE_NAME not like ''%syncobj_0x%''

        OPEN IdentityColumnCursor

        FETCH NEXT FROM IdentityColumnCursor
        INTO @tableName, @columnName, @schemaName

        WHILE @@FETCH_STATUS = 0
        BEGIN

            print ''['+@DatabaseName+'].[''+@tableName+''].[''+
                            @schemaName+''].[''+@columnName+'']''
            EXEC (''declare @MAX int = 0
                    select @MAX = max(''+@columnName+'')
                    from ['+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
                    if (@MAX IS NULL)
                    BEGIN
                        SET @MAX = 0
                    END
                    DBCC CHECKIDENT(['+@DatabaseName+'.''+
                                    @schemaName+''.''+@tableName+''],RESEED,@MAX)'')

            FETCH NEXT FROM IdentityColumnCursor
            INTO @tableName, @columnName, @schemaName

        END

        CLOSE IdentityColumnCursor
        DEALLOCATE IdentityColumnCursor')

        FETCH NEXT FROM DatabasesCursor
        INTO @DatabaseName

    END

    CLOSE DatabasesCursor
    DEALLOCATE DatabasesCursor
END




EXEC sp_configure 'show advanced options', 1 ;

Create a Database snapshot, Restore from snapshot, Delete the snapshot files

 use Test
go
declare
@Number INT,
@ssdirname varchar(1000),
@targetdb varchar(255),
@Drop Varchar(max)

Set @Number=1
SET @ssdirname = 'D:\DatabaseSnapshots\'
SET @targetdb = DB_NAME() +'Snapshot'+ Convert(Varchar(10),@Number)

---SET @targetdb = DB_NAME() +'Snapshot'+ Convert(Varchar(10),@Number)+Convert(Varchar(100),getdate(),112)

--print @targetDb


-- Unique timestamp for filenames
DECLARE @timestamp varchar(50)
SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(varchar(50),GETDATE(),126),':',''),'.',''),'-','')

DECLARE oncmd CURSOR FOR
select OnCmd = '(NAME=''' + [name] + ''', FILENAME=''' + @ssdirname + [name]  + @timestamp + '.ss'')'
from sys.database_files
where [type] = 0

DECLARE @oncmd varchar(500), @sqlcmd varchar(4000)
SET @sqlcmd = ''

OPEN oncmd
FETCH NEXT FROM oncmd INTO @oncmd
WHILE @@FETCH_STATUS = 0
BEGIN
IF @sqlcmd <> ''
SET @sqlcmd = @sqlcmd + ', ' + CHAR(10)
SET @sqlcmd = @sqlcmd + @oncmd

FETCH NEXT FROM oncmd INTO @oncmd
END
CLOSE oncmd
DEALLOCATE oncmd

SET @sqlcmd = 'CREATE DATABASE ' + @targetdb + ' ON ' + CHAR(10) + @sqlcmd
SET @sqlcmd = @sqlcmd + CHAR(10) + 'AS SNAPSHOT OF ' + DB_NAME()

------------------------------------------------------------------------------------
--STEP1:CREATE Database snapshot
IF EXISTS(select * from sys.databases where name='dbname')
DROP DATABASE dbname

--EXEC (@sqlcmd)
PRINT @SQlCMD
PRINT 'PUT YOUR CODE HERE '--TEST COMPLETE/Automation --- go to step 2


--ALTER DATABASE Test
-- SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--GO

--ALTER DATABASE test
-- SET MULTI_USER
--GO


--STEP2:
---Restore File

 Declare @RestoreCmd Varchar(max)
SET @RestoreCmd= 'RESTORE DATABASE Test FROM DATABASE_SNAPSHOT='''+  @targetdb    +  ''''
--EXEC (@sqlcmd)
print @RestoreCmd



USE MASTER
GO
/****** Object:  StoredProcedure [dbo].[SPRestoreSnapshot]    Script Date: 9/28/2015 5:21:04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTEr PROCEDURE [dbo].[SPRestoreSnapshot]
AS

ALTER DATABASE dbname
SET SINGLE_USER WITH ROLLBACK IMMEDIATE


    DECLARE @Number INT ,
        @ssdirname VARCHAR(1000) ,
        @targetdb VARCHAR(255) ,
        @Drop VARCHAR(MAX);


    SET @Number = 1;
    SET @ssdirname = 'D:\DatabaseSnapshots\';
    SET @targetdb = 'dbname' + 'Snapshot' + CONVERT(VARCHAR(10), @Number);


    DECLARE @RestoreCmd VARCHAR(MAX);
    SET @RestoreCmd = '
USE MASTER

    RESTORE DATABASE dbname FROM DATABASE_SNAPSHOT='''
        + @targetdb + '''';
    EXEC (@RestoreCmd)
ALTER DATABASE DB
SET MULTI_USER
;
--print (@RestoreCmd)








 PRINT 'WANTS TO delete, insert ,DROP '--GO TO STEP3


 --STEP3:
--DELETE THE Database SnapShot and the FILES associated with snapshot in the Folder
SET @DROP = 'DROP DATABASE ' + @targetdb + ';' + CHAR(10)
--EXEC (@DROP)
PRINT @DROP

Create Snapshot of databases

USE master;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE Test
    (
      id INT ,
      Name VARCHAR(10) NULL
    );

INSERT  INTO Test
VALUES  ( 1, 'Naresh' ),
        ( 2, 'Kumar' ),
        ( 3, 'Koudagani' );


CREATE DATABASE TestDB_Snapshot ON
( NAME = TestDB, FILENAME = 'C:\MSSQL\Data\myDb_snapshot.ss' )
AS SNAPSHOT OF TestDB;

select file_id, name, type_desc from sys.database_files
delete from test
select * from test

SELECT * from TestDB_Snapshot.dbo.test
RESTORE DATABASE TestDB FROM DATABASE_SNAPSHOT ='TestDB_Snapshot'
go
select * from TestDB.dbo.Test

SQL Server Configuration Manager

How to open sql server configuration manager in Windows Server 2012 or 2012 R2 ?

1. RUN--type 
SQLServerManager12.msc  for sql server 2014 
SQLServerManager10.msc  for sql server 2008


2. From Search options type above as required 



Because SQL Server Configuration Manager is a snap-in for the Microsoft Management Console program and not a stand-alone program, SQL Server Configuration Manager not does not appear as an application when running Windows 8. To open SQL Server Configuration Manager, in the Search charm, under Apps, type SQLServerManager12.msc for SQL Server 2014 (SQLServerManager11.msc for SQL Server 2012 or SQLServerManager10.msc for SQL Server 2008), and then press Enter.

HOW to Find Unused databases

Step1:
---Create a table to keep track of connectios:
USE AuditDB;
GO
CREATE TABLE [dbo].[TrackDBConnections]
    (
      ServerName VARCHAR(50) NOT NULL ,
      DatabaseName VARCHAR(30) NOT NULL ,
      NumberOfConnections INT NOT NULL ,
      LoginTime DATETIME NOT NULL ,
      LastBatch DATETIME NOT NULL ,
      DateTimeRecordInsertd DATETIME NULL ,
      Status VARCHAR(100) NULL ,
      HostName VARCHAR(100) NULL ,
      ProgramName VARCHAR(1000) NULL ,
      NTusername VARCHAR(100) NULL ,
      Loginame VARCHAR(100) NULL
    )
ON  [PRIMARY];
GO





Step2:--Main Step
---create a SP to insert the Data into the [TrackDBConnections] Table:
USE AuditDB
GO
CREATE PROCEDURE usp_TrackDBConnections
AS
BEGIN

 /*    The main purpose of the SP is to keep track of Connections which get connect to Databases
                and decide on which database is being used or not being used by any applications and then make OFFLINE for fewdays, backup and then drop..
 */


 SET NOCOUNT ON;
INSERT INTO [TrackDBConnections](ServerName, DatabaseName,LoginTime,LastBatch,DateTimeRecordInsertd,NumberOfConnections,Status,HostName,ProgramName,NTusername,Loginame)
 
  SELECT  @@ServerName AS SERVER ,
        name ,
        login_time ,
        last_batch ,
        GETDATE() AS DATE ,
        COUNT(status) AS number_of_connections ,
        status ,
        hostname ,
        program_name ,
        nt_username ,
        loginame
FROM    sys.databases d
        LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE   database_id NOT BETWEEN 0 AND 4
        AND loginame IS NOT NULL
GROUP BY name,status,login_time ,
        last_batch,hostname ,
        program_name ,
        nt_username ,
        loginame;

END


--Step3:-Create a Job to schedule to run every 10 Minutes
       --Let it run for Few days/1 week and see the results with below query,Detailed SQL Server Connection Information



--Step4: CHECK results
----count
SELECT  DatabaseName ,
COUNT(status) AS number_of_connections,
ProgramName
FROM    TrackDBConnections
 GROUP BY DatabaseName,ProgramName,Status
 GO

--Details
 SELECT *  from TrackDBConnections
GO

 
   

How licensing works with AlwaysOn Availability groups

I would like to share some knowledge on how licensing works with AlwaysOn Availability groups.
It took 1 week to figure it out, Having calls with Microsoft and help from forums


Min Requirements for having AlwaysOn Availability groups.:

• Two Windows Servers 2012 R2 Standard Edition
• Ensure that each computer is a Node in a Windows Server Fail over Clustering (WSFC)
• All two Windows Servers should reside on one Cluster and One Domain
• Each SQL Server Instance involved in AlwaysOn Availability Groups must be running Enterprise Edition of SQL Server 2012 or later (2014)
• Must use same network links for communications between WSFC members and availability Replicas
• All Databases should be in Full recovery model, only User databases can participate in AlwaysOn Availability Groups 
• Recommended to keep all configurations/Settings/Drives identical between two nodes

Advantages of using AlwaysOn Availability Groups:

• AlwaysOn Availability Groups build on Top of Windows Server Fail over Clustering (WSFC)
• Automatics Failover(Synchronous mode), No witness server needed for automatic failover, uses Windows Server Failover Clustering to do Automatic Failover
• Can have multiple copies of secondary, but here at FrontPoint we are not going to use secondary
• Backups and DBCC Check DB operations cane be done on secondary( but need to pay for License)
• Applications connect to Virtual Server Name any given time, and we do not need to change any in connection strings even after Failover happens 
• Secondary Replica can be same or different location 
• More than one secondary and cane be readable and run synchronous mode 
• Multiple database failover -AlwaysOn Availability Groups ensures all databases are connected when failover happen to secondary Replica
• No shared Storage is required in AlwaysOn Availability Groups 

AlwaysOn Availability Groups Support Fail over In any of the following  Events

• Operating System goes down
• SQL Server Instance, Database goes offline/Shutdown
• Disk Failures(hardware failures) in which databases cannot be accessed 
• Network goes down or someone unplug Network cables
• Manual failover whenever needed in emergency situations
• Upgrade/Patch Failed/Restarting SQL services 

Manage Jobs and Replication:

• Jobs should be created on both primary and secondary Replicas with some logic.
• Jobs run on both servers all times, but which ever is primary actually execute the job and secondary will not pass validation Skip the job to execute the actual code
• Replication Jobs will be Created on Both server and have to create publications on both servers and configure Distribution database to use remote distributor

IF ( SELECT ars.role_desc

 FROM   sys.dm_hadr_availability_replica_states ars

           INNER JOIN sys.availability_groups ag ON ars.group_id = ag.group_id

         WHERE  ag.name = 'YourAvailabilityGroupName'

         AND ars.is_local = 1

   ) = 'PRIMARY‘

BEGIN

---this server is the primary replica, Put all Job code here 

 END;

ELSE

BEGIN

PRINT 'no';    -- this server is not the primary replica, (optional) do something here

    END;

Finally, How licensing works with AlwaysOn Availability groups ?

 

I am going to explain how it works when we have like below environment

SQL Server 2014 Always ON Availability Groups between 2 nodes(servers) and using SQL server Enterprise edition on top of WSFC, Node1 is primary replica(read/Write)  and Node2 is secondary replica(NON READABLE)  and databases are Restoring mode only and will not use or read/write  data until disaster (failover)happens.

As per documentation , SQL server  Services, Agent  services should run on both servers at anytime but only Primary Replica's  jobs executes at anytime using Some checks like i provided above t SQL script, so we should have JOBS running at both servers, we will have to put a logic inside the jobs , if this is primary execute else come out of the job..--this code or logic make sure the jobs run on PRIMARY REPLICA (now, or after failover)

so we need only 1 SQL SERVER ENTERPRISE Licence to setup ENVIRONMENT like above.....

Note:

1.You probably shouldn't take my word for it, since I don't work for Microsoft, but I'm pretty sure that isn't a problem, You need to have the server ready to take over for any failure. Having your jobs already configured and scheduled is an important part of that.

2.Passive fail-over rights permit a customer to run passive instances of SQL Server in a separate operating system environment (OSE) without requiring the passive server to be licensed for SQL Server. A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads.e over for any failure. Having your jobs already configured and scheduled is an important part of that.

Some Useful Information:

For each server licensed with SQL Server 2014 and covered by active SA, customers can run up to the same number of passive failover instances in a separate, on-premises OSE to support failover events.

A passive SQL Server instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The passive failover instances can run on a separate server. These may only be used to synchronize with the primary server and otherwise maintain the passive database instance in a warm standby state in order to minimize downtime due to hardware or software failure.

The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work”, such as additional backups being made from secondary servers, then it must be licensed for SQL Server. 

http://download.microsoft.com/download/B/4/E/B4E604D9-9D38-4BBA-A927-56E4C872E41C/SQL_Server_2014_Licensing_Guide.pdf

So long as the agent jobs only actually perform "work" on the primary, and the other conditions are met, it would be passive. 

Thanks

Naresh DBA

Difference between NORecovery vs STANDBY

 
RECOVERY is the normal and usual status of the database where users can connect and access the database (given that they have the proper permissions set up).

NORECOVERY allows the Database Administrator to restore additional backup files such as Differential or Transactional backups. While the database is in this state then users are not able to connect or access this database.

STANDBY is pretty much the same as NORECOVERY status however it allows users to connect or access database in a READONLY access. So the users are able to run only SELECT command against the database. This is used in Log Shipping quite often for reporting purposes. The only drawback is that while there are users in the database running queries SQL Server or a DBA is not able to restore additional backup files. Therefore if you have many users accessing the database all the time then the replication could fall behind.

SQL Server Find What Jobs Are Running a Procedure

SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  WHERE EXISTS
  (
    SELECT 1 FROM msdb.dbo.sysjobsteps AS s
      WHERE s.job_id = j.job_id
      AND s.command LIKE '%SPNAME%'
  );

or to get more details:
SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%SPNAME%'
ORDER BY [JobName], [StepNo]

why Distribution on SQL Express Edition is not possible

SQL server Distribution which act as intermediate and ruin jobs cannot be on SQL Express edition as SQL Express edition cannot have SQL Agent.

decrypt Stored procedure

Create SP with encryption:
CREATE PROCEDURE Test
WITH ENCRYPTIONAS
SELECT TOP 10 CityFROM Person.Address
GO


Now we cannot view the Definition of SP 



To view the definition of the view:I have downloaded below one and it worked excellent...

https://www.devart.com/dbforge/sql/sqldecryptor/download.html



Thanks to devart