SQL data base in Restoring mode after successfully restored

        Most of the people facing an issue stuck database in restoring mode, after successful restore of  the database. Why this is happening ? This is likely caused by  WITH No RECOVERY, missing of adding with recovery option will causing the issue. 

This is the situation database is looking for latest transaction log after restoring.

        To resolve this issue restore the database again is a time consuming thing, So we can force the database out of restoring mode by executing below script.

RESTORE DATABASE YourDbName WITH RECOVERY

ALTER DATABASE YourDbName SET ONLINE

How to get next business day after excluding weekends in SQL

    Below query is helpful to find next business date by excluding weekends. We can use  same logic to find out the weekend.

1. Find next business day excluding weekend holiday.

   DECLARE @Current_date DATETIME = '14 Nov 2020'

   SELECT CASE

   WHEN (((DATEPART(DW,  @Current_date) - 1 ) + @@DATEFIRST ) % 7) IN (6)

           THEN @Current_date + 2

   WHEN (((DATEPART(DW,  @Current_date) - 1 ) + @@DATEFIRST ) % 7) IN (5)

           THEN @Current_date + 3

   ELSE @Current_date + 1 END AS next_business_date

2. Check today is business day or not in SQL server.

    SELECT (CASE

        WHEN (((DATEPART(DW,  GETDATE()) - 1 ) + @@DATEFIRST ) % 7) IN (0,6)

        THEN 0

        ELSE 1

      END) AS is_business_day

Different query method in SQL Server

 

    Here we are discussing about simple query technique used in sql server . Please go through the below example.

1. Use below query to crated temp Data set in SQL.

CREATE TABLE #TempTable (UserName VARCHAR(20),Age INT,Gender VARCHAR(10),Amount MONEY)

INSERT INTO #TempTable

SELECT 'Clark',27,'Male',500

UNION ALL

SELECT 'Jack_dani',29,'Male',100

UNION ALL

SELECT 'Libsa',18,'Female',1000

UNION ALL

SELECT 'Libsa',18,'Female',500

UNION ALL

SELECT 'Jp-dani',30,'Male',100

2. Query to select data contain underscore in User Name.

SELECT  * FROM #TempTable WHERE CHARINDEX('_',UserName) > 0

3. Query to filter column gender is Male.

SELECT  * FROM #TempTable WHERE Gender = 'Male'

4. Query to find sum of total amount grouping age column.

SELECT Age,SUM(Amount) As Total

FROM #TempTable

GROUP BY Age

5. Query to find the users whose age is greater than 27. 

SELECT  * FROM #TempTable WHERE age > 27

Find the SQL server Version

             We can find out the version of SQL server using query. The @@version query will returns all about the SQL server versions. Once you run the query you will get the following information about SQL server.

• SQL server Version.
• Processor Architecture.
• SQL server Build Date.
• Copyright Details.
• SQL server Edition Details.
• OS version.

SELECT @@VERSION

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

Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) 

Mar 12 2019 19:29:19 

Copyright (C) 2017 Microsoft Corporation

Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Where are Temp Table and Table Variable Stored

         It is a confusing thing where temp tables and table variables are stored, Most of the people think that table variables are stored in memory and temp tables stored in tempdb. However both are saved and executed from tempdb. Lets check the below example.

CREATE TABLE #Temptable(Cust_Name VARCHAR(50))

DECLARE @TABLE_VARIABLE AS TABLE(Cust_Name VARCHAR(50))

SELECT *

FROM tempdb.sys.objects

WHERE CAST(create_date AS DATE) = CAST(GETDATE() AS DATE)

            

            From the example its clear that both are created in temp db or disk, the first object is created for temp table and second one is for table variable. In terms of performance, I choose table variable, because its more efficient than temp table and also it is automatically dropped after execution.

Install and configure Master Data Services

Master Data Services

Master Data Management (MDM) defines a process of collecting enterprise data from various sources, applying standard rules and business processes, building a single view of the data, and finally distributing this ‘golden’ version of data to various systems in the enterprise, thereby making it accessible to all consumers.

Master Data Services is a Microsoft product for developing MDM solutions that is built on top of SQL Server database technology for back end processing. It provides service-oriented architecture endpoints using Windows Communication Foundation (WCF). We can implement a hub architecture using MDS to create centralized and synchronized data sources to reduce data redundancies across systems.

Install & configure Master Data Services

1. Double-click SQL Setup.exe, and follow the steps in the installation wizard.

2. Select Master Data Services on the Feature Selection page under Shared Features

3. Configure IIS on windows server.

4. Open Master data services configuration manager

5. Create MDS database by specifying SQL Instance and windows administrative account

6. Move to Web configuration tab and set up application pool for IIS

7. Open MDS web portal by entering IIS server name and application pool

http://servername/MDS

Finding blocking/locking queries in SQL Server

     To resolve blocking issue in sql server we have to find out which process is causing the issue, and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are as follows.

Activity Monitor – Right click on SQL server and open tool Activity Monitor, You can use Activity Monitor to view information about the current processes. You can find out and kill blocked process using Blocked By drop down.

sp_who2 – It is the System SP help to find out running process in sql server. Once we execute procedure, we can find out blocked process from BlkBY column. But using sp_who2 we can find out which session is causing the issue but we couldn’t get the information about which Query part is causing the block or other information

Find SQL block using query – Using below query we can find out the SQL blocks, which process is causing the block, from that in which query part taking time to execute everything we can find out. I think this query part is very help full to identify or rectifying SQL blocks. 

SELECT 

       s.session_id

       ,r.STATUS

       ,r.blocking_session_id AS 'blocked_by_SQL_Feathers'

,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' +

QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' +

   QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS'stored_proc'

       ,s.program_name

       ,s.last_request_end_time

       ,s.login_time

       ,s.login_name

       ,r.wait_type

       ,r.open_transaction_count

       ,r.command

    ,r.wait_resource

    ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time'

    ,r.cpu_time

    ,r.logical_reads

    ,r.reads

    ,r.writes

    ,CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time'

    ,CAST((

            '<?Running -->  ' + CHAR(13) + CHAR(13) + 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) + CHAR(13) + CHAR(13) + '--?>'

            ) AS XML) AS 'query_text'

    --,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan

    ,s.host_name   

    ,s.host_process_id

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

--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp --  (2) if you want to see plan

WHERE r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%'

    OR r.session_id != @@SPID

ORDER BY

     s.session_id,

         r.cpu_time DESC