T-SQL Script to get SQL Server Start time and total Up time.

There may be a situation where you want to know the SQL Server service start date, time and total up time. I have written a simple T-Sql script using DMV to find the same, which is as below:

--======================================================================
SELECT 'Your Microsoft SQL server started On ' + 
CAST( (DATEname (WEEKDAY , sqlserver_start_time ))as varchar(12)) 
+' '+  CONVERT( varchar(20), sqlserver_start_time, 107)+' ' +'At'+ CONVERT( varchar(20), sqlserver_start_time, 108)
+' and is UP and Running From '+
CAST((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))/60 AS VARCHAR(5)) + ' Hours and ' 
+RIGHT('0' + CAST(((DATEDIFF(MINUTE,sqlserver_start_time,GETDATE()))%60) AS VARCHAR(2)),2) + ' Minutes. '  
AS [SQl Server Start Time & Total UpTime] FROM sys.dm_os_sys_info
--==================================================================

You can also check temp DB Creation time for same as every time when we start sql server, temp db is created. 

Thanks!!!

When was SQL Server database Last used??

If we want to remove the databases from the server which are not getting used from a long time. In this case first we need the name of databases which are not used from a long time. We can use below script to find the same.  

SELECT DBName, MAX(LastUsedDate) LastUsedDate

FROM

    (SELECT

        DB_NAME(database_id) DBName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PvtTable

UNPIVOT 

    (LastUsedDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpvtTable

GROUP BY DBName

ORDER BY LastUsedDate

Thanks!!

SOUNDEX and DIFFERENCE Function in SQL Server.

The SOUNDEX function converts a character string to a four-digit code for use in a comparison. Vowels are ignored in the comparison. Non-alphabetic characters are used to end the comparison. This function always returns some value.

Syntax:

SOUNDEX ( character_expression )

The following example displays the results of the SOUNDEX function for the similar character strings of smith and smythe. When character strings are similar, both strings have the same SOUNDEX codes.

SELECT SOUNDEX ('Kumar'), SOUNDEX ('Kumara');
GO

Output:
----- -----
K560  K560

(1 row(s) affected)

DIFFERENCE ()

The DIFFERENCE function compares the SOUNDEX values of two strings and evaluates the similarity between them, returning a value from 0 through 4, where 4 is the best match. 

Syntax:

DIFFERENCE ( character_expression , character_expression )

See the following examples:

SELECT DIFFERENCE('Vimal', 'Vimal');
GO
SELECT DIFFERENCE('Vimal', 'Kamal');
GO
SELECT DIFFERENCE('Roy', 'John');
GO

Thanks!!

Stuff Function In SQL Server.

As per Microsoft BOL 
"The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position."

The syntax of the STUFF string function is as follows:

STUFF ( character_expression , start , length , replaceWith_expression )

Arguments
character_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
start
Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.
length
Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last 
character_expression. length can be of type bigint.
replaceWith_expression
Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

The following example returns a character string created by deleting three characters from the first string,VimtyeumarPrajapati, starting at position 4, at t, and inserting the second string at the deletion point.

select Stuff('VimtyeumarPrajapati',4,3 ,  'alK')
Go

Output:
VimalKumarPrajapati

Thanks!!!

How to Execute Query Against Multiple Servers?

Before executing Multiple Server query, first we need to create a Registered SQL Server Group with all required servers for which we want to execute the Query.

To Create Registered Servers Group follow the below steps in SSMS:

1: In SQL Server Management Studio, on the View menu, click Registered Servers.(Or simply pess ctrl+alt+G)
2: Now Right click on Local Server Groups > New Server Registration

Enter login credentials for same.  

Test the connection and save it. Add all your desired server. Then open a new query editor and execute the query as below:

Use master
Go
Select * from Sys.Databases
Go

The results set includes additional columns for the server name and the login that is used by the query on each server. By default server name is included in result set but for login name we have to enable it from Query options.

So, this is how we can execute Query Against Multiple Servers.

Thanks!!!

Use Of Pausing an MSSQL Server Instance..

When we do right click on any MSSQL server instance we can see the couple of options like Start, Stop, Pause, Reusme and Restart. I have used only three options in my production environment that is Start, Stop and Restart of SQL server instance but never got a chance to pause a SQL Server and frankly speaking I was not having any idea that why there is a pause option in MSSQL server instance until a couple of months ago when I read a nice blog post of Mr. Kenneth on same. So I thought to share this with you guys. 

As per Microsoft BOL,
"When you pause an instance of Microsoft SQL Server, users that are connected to the server can finish tasks, but new connections are not allowed."

It means that the database users which are already connected to database server and are in between process can complete there work but  trying to create a new connection will be refused.

You can think of a scenario  where you need to run maintenance on a server. You let everyone who is currently connected know that they need to be logged out. One of your developer comes to you and said that he has a batch process that is almost finished and will take 10 more minutes. The problem in that scenario is that if you will wait then in that time interval a lot of new users will be connected to your server. So, How you will manage this situation??

So this is where you can Pause the instance!!!

Let see the same in SSMS. Connect to sql server instance and open a new query window.
Execute Waitfor Delay '00:05:00'. This will be in process for 5 minutes. 

Now Right click on Sql server instance and click on pause. A window will appear for confimation. click on Yes.

Now click on new query window. You will get below error window saying that New connection is not allowed.

After this you can resume,Restart or stop the service as per your requirement.

Thanks!!

Reference: Mr. Kenneth Blog Post

Error "Msg 3132, Level 16, State 1, Line 1. The media set has 2 media families but only 1 are provided. All members must be provided."

Sometimes when we try to restore database in Sql server we get below error:

"Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."

In SQL Server 2008 R2 If you are using SSMS then you will get error window as below:

In SQL Server 2008 R2Using TSQL:

In SQL Server 2014 If you are using SSMS then you will get error window as below: In this case the backup was distributed into 3 files: We have provided 2 files but missed to add 3rd backup file.

In SQL Server 2014 TSQL:

This error message appears when the original backup was done as a striped backup where the backup stream was split into multiple destination files. And When you want to restore, you need to specify all of the same backup files which were used to take the backup. If not then you will get the above error.

So in order to solve above problem we have to provide the missing backup file and then restore it. In case we have lost that missing backup file then we'll not be able to restore that database until unless we take a fresh backup of that particular database.

To Restore the above database provide all 3 backup files using TSQL Or SSMS  it will be restored successfully:

E.g. Using TSql:

So this is how we can solve this problem.

How To Take Striped backup Of our Database In SQL Server:

Let see how can we take striped backup of our databases. Suppose, We have a sample database [StripedBackup]. Now we are  going to distribute the backup into 3 different files. We can use SSMS or TSQL code for same.

Usign TSQL you can take Striped backup as:

BACKUP DATABASE STRIPEDBACKUP
TO DISK = 'C:\BACKUP\STRIPEDBACKUP1.BAK',
DISK =  'C:\BACKUP\STRIPEDBACKUP2.BAK',
DISK =  'C:\BACKUP\STRIPEDBACKUP3.BAK'

If you want to take backup using SSMS then you can follow:

Right-click on the name of the database and then select 'Tasks'>'Back Up' Then add files as below screen shot and click ok.

So, this is how we can distribute our backup file into different files.

Thanks For Reading this Blog

Basic Queries to troubleshoot Database Mirroring

Check the state of the DB Mirroring using DMV sys.tcp_endpoints

select name,type_desc,state_desc,port,is_dynamic_port,ip_address 
FROM sys.tcp_endpoints

Check the DB Mirror information using DMV sys.database_mirroring:

select database_id,mirroring_state_desc,mirroring_role_desc,
mirroring_partner_name,mirroring_partner_instance 
from sys.database_mirroring

Thanks!!!