View recently run queries

Use below query to get recently run queries

SELECT DEQS.last_execution_time as [Time], DEST.text as [Query] 
FROM 
    sys.dm_exec_query_stats as DEQS cross apply  sys.dm_exec_sql_text(DEQS.sql_handle) as DEST 
order by DEQS.last_execution_time DESC

Generating user instances in SQL Server is disabled. User sp_configure ‘user instances enabled’ to generate user instances.

To solve this issue, please follow the following steps:

1. Start the SQL Server Management Studio Express.
2. Connect to the default instance of that server (Your Machine Name\SQLEXPRESS)
3. Then open a New Query Window. Please check that you are connecting to the master database
4. Type: exec sp_configure ‘user instances enabled’, 1
5. Type: RECONFIGURE
6. Restart the SQL Server

Output time in days, hours and minutes

Use below query that will format a time span by days, hours and minutes.


DECLARE @FromTime DateTime
SET @FromTime = '2009-07-11 14:19:40.000'


DECLARE @ToTime DateTime
SET @ToTime = getUtcDate()


SELECT 
    CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + 'd ' 
  + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
  + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm' AS Format1
  , CASE 
      WHEN (((DateDiff(mi, @FromTime, @ToTime)/(24*60))) > 0) THEN
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + 'd ' 
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
      WHEN (((DateDiff(mi, @FromTime, @ToTime)%(24*60)/60)) > 0) THEN
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60) + 'h '
        + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
      ELSE
          CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60) + 'm'
    END AS Format2
  , CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)/(24*60)) + ':' 
  + RIGHT('00' + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%(24*60)/60), 2) + ':'
  + RIGHT('00' + CONVERT(VarChar(40), DateDiff(mi, @FromTime, @ToTime)%60), 2) AS Format3

Error 3154 - The backup set holds a backup of a database other than the existing database.

Today when I was restoring database on SQL Server Management Studio, I got an error 
Error 3154- "The backup set holds a backup of a database other than the existing database".

Solution


After selecting the database backup, goto option Tab from the Left Hand Side Menu and click on First Restore Option i.e Overwrite the existing database(WITH REPLACE) as shown in screen below.

* Make sure that there is no database file exist at the path mentioned in RESTORE the database file as section with same file name otherwise it will replace those files.

Search a table used in all SP

You can search for a table used in all the stored procedure. It is a very basic information but makes it easy while making changes to a table name and like to change the procedures using the table.


SELECT '[' + SPECIFIC_CATALOG + '].[' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']', ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%STRING%'