Thursday, August 25, 2016

Remove SQL Replication in Publisher and Subscriber after changing server names

Remove SQL Replication even when the publisher or Subscriber Server is not available

Existing server/computer are renamed and given new names. If you are trying to update the Instance name of  SQL Server. it wont allow you to change the name.  And if you try to remove subscriber and publishers Forcefully  also will through below error.


OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "repl_distributor" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 65535, Level 16, State 1, Line 0

SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. 




Solution to drop subscriber and publisher are as below (As per MS This procedure should be used only if other methods of removing replication objects have failed)



Declare @repdb as sysname
Set @repdb = 'MyPublisherDatabase' 

EXEC sp_removedbreplication @repdb
Go 

Monday, August 1, 2016

Grant VIEW DEFINITION to all databases stored procedure in SQL Server




/*
Change the user name name in 2nd line and
Replace print @sqlstmt  with exec @sqlstmt to grant VIEW DEFINITION to all databases stored procedure in SQL Server
*/



DECLARE @userName varchar(200)
SET @userName = 'corp\domainuser'

DECLARE @name varchar(50)
DECLARE @spname varchar(50)
DECLARE @schemaname varchar(50)
DECLARE @sqlstmt nvarchar(300)
DECLARE @sqlsel varchar(300)


DECLARE db_cursor CURSOR FOR
SELECT
  name
FROM MASTER.sys.databases
WHERE state_desc = 'ONLINE'
AND is_in_standby = 0
AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
  --Print @name  
  SET @sqlsel = 'DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from [' + @name + '].information_schema.routines where routine_type = ''PROCEDURE'' and routine_name not like ''dt%'' and routine_name not like ''ms%'' and routine_name not like ''xp%'' '
  -- print @sqlsel
  --DECLARE @spro_cursor CURSOR    
  --  DECLARE spro_cursor CURSOR FOR SELECT  SPECIFIC_NAME, SPECIFIC_SCHEMA  from information_schema.routines where routine_type = 'PROCEDURE'
  EXEC (@sqlsel)
  --DECLARE spro_cursor CURSOR FOR @sqlsel
  OPEN spro_cursor
  FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sqlstmt = 'use [' + @name + ']' + CHAR(13) + CHAR(10) + '  GRANT VIEW DEFINITION ON [' + @schemaname + '].[' + @spname + '] TO [' + @userName + ']'
    PRINT @sqlstmt
    EXECUTE sp_executesql @sqlstmt
    FETCH NEXT FROM spro_cursor INTO @spname, @schemaname
  END
  CLOSE spro_cursor
  DEALLOCATE spro_cursor

  FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Thursday, July 7, 2016

Blocking notification alert job


Step  1:  : Create below procedure in msdb database

use msdb
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Blocking_Notification]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Blocking_Notification]
GO
/*

exec usp_Blocking_Notification 'venkateswara.sangu@gmail.com',500000

change DBMail profile & From address in below procedure before creating
*/

CREATE PROCEDURE usp_Blocking_Notification   @Receipiant nvarchar(30),@waittime int = 100000
AS
SELECT s.session_id
    ,r.STATUS
    ,r.blocking_session_id
    ,r.wait_type
    ,wait_resource
    ,r.wait_time / (1000.0) 'WaitSec'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,r.total_elapsed_time / (1000.0) 'ElapsSec'
    ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
            (
                CASE r.statement_end_offset
                    WHEN - 1
                        THEN Datalength(st.TEXT)
                    ELSE r.statement_end_offset
                    END - r.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
    ,r.command
    ,s.login_name
    ,s.host_name
    ,s.program_name
    ,s.host_process_id
    ,s.last_request_end_time
    ,s.login_time
    ,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID and
r.wait_time > @waittime
ORDER BY r.cpu_time DESC
    ,r.STATUS
    ,r.blocking_session_id
    ,s.session_id

IF (
        SELECT count(*)
        FROM #temp_requests
        WHERE blocking_session_id > 50
        ) <> 0
BEGIN
    -- blocking found, sent email.
    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = N'<H1>Blocking Report</H1>' + N'<table border="1">' + N'<tr>' + N'<th>session_id</th>' + N'<th>Status</th>' +
                     N'<th>blocking_session_id</th><th>wait_type</th><th>wait_resource</th>' +
                     N'<th>WaitSec</th>' + N'<th>cpu_time</th>' +
                     N'<th>logical_reads</th>' + N'<th>reads</th>' +
                     N'<th>writes</th>' + N'<th>ElapsSec</th>' + N'<th>statement_text</th>' + N'<th>command_text</th>' +
                     N'<th>command</th>' + N'<th>login_name</th>' + N'<th>host_name</th>' + N'<th>program_name</th>' +
                     N'<th>host_process_id</th>' + N'<th>last_request_end_time</th>' + N'<th>login_time</th>' +
                     N'<th>open_transaction_count</th>' + '</tr>' + CAST((
                SELECT td = s.session_id
                    ,''
                    ,td = r.STATUS
                    ,''
                    ,td = r.blocking_session_id
                    ,''
                    ,td = r.wait_type
                    ,''
                    ,td = wait_resource
                    ,''
                    ,td = r.wait_time / (1000.0)
                    ,''
                    ,td = r.cpu_time
                    ,''
                    ,td = r.logical_reads
                    ,''
                    ,td = r.reads
                    ,''
                    ,td = r.writes
                    ,''
                    ,td = r.total_elapsed_time / (1000.0)
                    ,''
                    ,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                            (
                                CASE r.statement_end_offset
                                    WHEN - 1
                                        THEN Datalength(st.TEXT)
                                    ELSE r.statement_end_offset
                                    END - r.statement_start_offset
                                ) / 2
                            ) + 1)
                    ,''
                    ,td = Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) +
                        N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')
                    ,''
                    ,td = r.command
                    ,''
                    ,td = s.login_name
                    ,''
                    ,td = s.host_name
                    ,''
                    ,td = s.program_name
                    ,''
                    ,td = s.host_process_id
                    ,''
                    ,td = s.last_request_end_time
                    ,''
                    ,td = s.login_time
                    ,''
                    ,td = r.open_transaction_count
                FROM sys.dm_exec_sessions AS s
                INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
                CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
                WHERE r.session_id != @@SPID
                    AND blocking_session_id > 0
                ORDER BY r.cpu_time DESC
                    ,r.STATUS
                    ,r.blocking_session_id
                    ,s.session_id
                FOR XML PATH('tr')
                    ,TYPE
                ) AS NVARCHAR(MAX)) + N'</table>';

    EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
,@from_address  = 'DevDBAAdmin@techridesol.com'
        ,@body_format = 'HTML'
        ,@profile_name = N'TechRideSolDBMail'
        ,@recipients = @Receipiant
        ,@Subject = N'Blocking Detected'
END

DROP TABLE #temp_requests


Step  2 : 



Connect to SQL Server using SSMS and Expand SQL Server Agent and right click on Jobs and select New Job


In New Job window provide the job name and owner as SA and select Steps Tab in left side pane 


In Job Steps Window click on NEW button to create a job step





Tuesday, June 28, 2016

Transactional Replication Without the Snapshot Agent

When we configuring subscriber in transnational replication it is initialised with snapshot is generated by Snapshot Agent and applied by the Distribution agent. In couple of scenarios like replicating large databases generating & applying Snapshot will time consuming process. Here we will "initialise subscription using backup". In this method we can initialise subscriber database quickly. 

In this method you need to restore the recent full backup taken after the publication was enabled with option of initialise replication with backup below are the steps to configure replication without snapshot using GUI 

Step 1: Configure Distribution 

  • Start SSMS and connect to publisher server 
  • Right click on Replication folder and select Configure Distribution .. as shown in below fig






  • In configure Distribution wizard select the distribution server information & Click Next 






  • Provide the Snapshot folder and click on Next 






    • In Distribution Database properties page please provide and name and location of distribution database data & log files. Click on NEXT

        • In Next page select the Publishers to use this distribution 

        • In final screen of wizard check Configure distribution check box and click Next and in next screen click on as shown in below two screens 
                            

        • it will create a distribution and enable the publisher for this distributer.


        Step 2: Configure Publisher 

        • Open SSMS and connect to publisher server 
        • Expand the server, Navigate to Replication folder and expand in Object explorer and right click on Local Publisher and select New Publication ...


        • in New Publication Wizard Select the database which you want to publish & click Next 



        • Select the publication type as Transnational Publication & Click on Next



        • Select the Articles (Tables & other objects) which you want to publish and also select columns to filter tables




        • In Snapshot Agent wizard leave the check boxes un-ticked (In this you are configuring replication without snapshot) 



        • In Agent Security Dialog box Click on Security Settings button to provide credentials to connect Snapshot agent and LogReader Agent 



        • In below image i selected SQL Server Agent Service account but as per MS it is best practice it should have separate windows credentials  



        • After provide appropriate credentials it looks like below image 


        • Click on next. In next wizard Create the publication check box must need to select 
        • Provide Publication name in Final Wizard. and Click on Finish
        • It creates and Publication. 

        • After publication created open properties of publication by right click on the publication 

        • In publication properties window goto Subscriber Option 



        • In Subscriber options screen Change "Allow initialization from backup files" value to "true" from false




        Step 3: Backup Published Database

        • In this table full backup of Published database 



        • Right click on database goto Task and select Backup ...


        • In Backup database wizard select the Database, Backup type to FULL & backup location in Destination part 



        Step 4: Restore Published Database in Subscriber server

        • Copy the backup file from Publisher server to Subscriber server and restore it in Subscriber server. Right click on Databases and select Restore Database ... 


        • In Restore Database wizard select to restore database from device option and provide the backup file name along with location 


        • Provide the database name and click on Restore option 



        • It restores the database with given name in subscriber server 


        Step 5: Add Subscriber information 

        Connect to publisher server goto New Query and run below SQL Statement on Publisher Database with appropriate values 

        sp_addsubscription 
        @publication ='PublicationName', -- Provide Publisher name 
        @subscriber='Subscriber Server', -- Provide Subscriber Server instance name 
        @destination_db='TranRepl_WOSnapShotTest',  -- Provide Destination Server 
        @sync_type = 'initialize with backup', -- With this it recognise that subscriber will initiate using backup file 
        @backupdevicetype = 'disk', -- 
        @backupdevicename = 'd:\TranRepl_WOSnapShotTest.bak' -- Provide backup file location 
        go






        http://sql2005ted.blogspot.in/2010/12/setting-up-transactional-replication.html


        Wednesday, April 13, 2016

        What is – DML, DDL, DCL,TCL and DQL





        What is DDL Command  : (Data Definition Language)

        It is used to create and modify the structure of database objects in database.

        Command
        Description
        CREATE
        Creates a new table, a view of a table, or other object in database
        ALTER
        Modifies an existing database object, such as a table
        DROP
        Deletes an entire table, a view of a table or other object in the database



        What is DML Command  : (Data Manipulation Language)

        It is used to retrieve, store, modify, delete, insert and update data in database.

        Command
        Description
        INSERT
        Creates a record
        UPDATE
        Modifies records
        DELETE
        Deletes records


        What is DCL Command  : (Data Control Language)

        It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

        Command
        Description
        GRANT
        Gives a privilege to user
        REVOKE
        Takes back privileges granted from user


        What is DQL Command  : (Data Query Language)

        It is used to fetch the records from Physical files


        Command
        Description
        SELECT
        Retrieves certain records from one or more tables

        What is TCL Command  : (Transaction Control Language)

        It is used to manage different transactions occurring within a database.

        Command
        Description
        COMMIT
        To commit the bunch of SQL Statements
        ROLLBACK 
        To revert a transaction


        Tuesday, April 5, 2016

        Issue in expanding MSDB packages in SSIS


        When you are trying to expand MSDB folder in Integration services. if you are facing below error.




        TITLE: Import Package
        ------------------------------

        The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in %SQL_PRODUCT_SHORT_NAME% Books Online.

        Login timeout expired
        A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
        Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

        ------------------------------
        ADDITIONAL INFORMATION:

        Login timeout expired
        A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
        Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 11.0)


        Solution : you need to configure Integration Services service 

        you will have to modify the configuration file if your packages are stored in a named instance or a remote instance of Database Engine.

        Configuration file : MsDtsSrvr.ini.xml

        Location :  %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn




        Monday, April 4, 2016

        which version of Windows you are running



        To know which version of windows os you are using enter the following commands in the Command Prompt

        wmic os get caption

        wmic os get osarchitecture


        Friday, March 18, 2016

        to get the information of used/free space of all SQL Server databases in an instance.


        To get the information of used/free space of all SQL Server databases in an instance.


        EXEC sp_msforeachdb
        'USE [?];
        SELECT DB_NAME() AS [Database Name],
            CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) /128.0 AS DECIMAL(18,2)) AS [Data file size(MB)],
            CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) / 128.0 AS DECIMAL(18,2)) AS [Log file size(MB)],
            CAST(SUM(FILEPROPERTY(name, ''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Used space(MB)],
            CAST(SUM(size)/128.0 AS DECIMAL(18,2)) - CAST(SUM(FILEPROPERTY(name,''SpaceUsed''))/128.0 AS decimal(18,2)) AS [Free space(MB)]  
        FROM sys.database_files
        GROUP BY type_desc'



        Thursday, February 18, 2016

        Enabling CLR Integration


        Enabling CLR Integration


        Msg 6263, Level 16, State 1, Line 1
        Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.



        The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure



        sp_configure 'show advanced options', 1;
        GO
        RECONFIGURE;
        GO
        sp_configure 'clr enabled', 1;
        GO
        RECONFIGURE;
        GO

        Friday, January 22, 2016

        VB Script Conversion functions

        VB Script Conversion functions

        CBool() - Convert to Boolean Type 
        Converts an expression to a Boolean variable type that is either True or False.
        CByte() - Convert to Byte Value 
        Converts an expression to a Byte type (i.e. an integer value in the range 0 to 255).
        CCur() - Convert to Currency Type 
        Converts an expression to a Currency type (i.e. a floating-point number with up to four decimal places).
        CDate() - Convert to Date Value 
        Converts a formatted date string to a date variable type.
        CDbl() - Convert to a Double Value 
        Converts an expression to a double-precision floating point number.
        CInt() - Convert to Integer Value 
        Converts an expression into an integer value in the range -32,768 to 32,767.
        CLng() - Convert to Long Integer Value 
        Converts an expression to a long integer value.
        CSng() - Convert to a Single Value 
        Converts an expression into a single-precision floating-point number.
        CStr() - Convert to String Value 
        Converts an expression into a string value.

        Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

        If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...