Invalid backup mirror specification. All mirrors must have the same number of members.

When we try to take MIRRORED BACKUP the Err occurred in some situation.

Ok. Let us try to take MIRRORED BACKUP...

BACKUP DATABASE SQLServerBuddy
TO DISK='C:\Backup\SQLBuddy_20110123.Bak',
DISK = 'E:\Backup\SQLBuddy_20110123.Bak'
MIRROR TO DISK = 'F:\Backup\SQLBuddy_20110123.Bak' WITH INIT, FORMAT
GO

Error Occurred:
Msg 3010, Level 16, State 1, Line 1
Invalid backup mirror specification. All mirrors must have the same number of members.

Reason:
-"TO DISK" and "MIRROR TO DISK" should have same number of Backup Set.
-But, "TO DISK" have two member(C:\ and E:\) and "MIRROR TO DISK" has only one member(F:\), So the media members not matched..

Solution:
BACKUP DATABASE SQLServerBuddy
TO DISK='C:\Backup\SQLBuddy_20110123.Bak',
DISK = 'E:\Backup\SQLBuddy_20110123.Bak'
MIRROR TO DISK = 'F:\Backup\SQLBuddy_20110123.Bak',
DISK = 'G:\Backup\SQLBuddy_20110123.Bak' WITH INIT, FORMAT
GO

Both the media members TO DISK(C:\ and E:\) and MIRROR TO DISK(F:\ and G:\) can work independently.

Here we have used both Backup Stripping(TO DISK - C:\ and E:\, F:\ and G:\) and Mirrored Backup. So, when we try to restore it, the media should be combined (C:\, E:\) and (F:\, G:\)

Let us try to restore it
RESTORE DATABASE SQLServerBuddy FROM DISK='C:\Backup\SQLBuddy_20110123.Bak', DISK='E:\Backup\SQLBuddy_20110123.Bak' WITH REPLACE
GO

(or)

RESTORE DATABASE SQLServerBuddy FROM DISK='F:\Backup\SQLBuddy_20110123.Bak', DISK='G:\Backup\SQLBuddy_20110123.Bak' WITH REPLACE
GO

Note: This option is available only in SQL Server 2005 Enterprise Edition and later versions

Cannot find either column "dbo" or the user-defined function or aggregate "Function Name", or the name is ambiguous.

The functions is not there in current database.

Here I have created one stored procedure and Inside the procedure, I use one function named "UFN_Test1", But actually the function is not there in current database.

Try to re-produce the Err:
IF OBJECT_ID('USP_Test1','P') IS NOT NULL
DROP PROC USP_Test1
GO
CREATE PROC USP_Test1
AS
BEGIN
SELECT dbo.UFN_Test1()
END
GO

Execute the procedure
EXEC USP_Test1
GO

When executing the procedure, The following Err occurred...

Cannot find either column "dbo" or the user-defined function or aggregate "Function Name", or the name is ambiguous.

How to identify whether the function is there in current database or not ?

IF OBJECT_ID('UFN_Test1') IS NULL
PRINT 'The Object is not there...!'
ELSE
PRINT 'The Object is there...!'

How many SQL Server Instances are there ?

In most of the scenario, We would need to identify that how many instances are there or How many instances installed in current server.

1. Login into SQL Server in which server you want to...
2. Copy & Paste the script given below and hit your 'F5' (or) Execute.

CREATE TABLE #Instances
(
InstanceName VARCHAR(100)
)

INSERT #Instances
EXEC('xp_cmdshell ''SQLCMD -L''')

DECLARE @ServerName VARCHAR(100)
SELECT @ServerName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR)

SELECT LTRIM(InstanceName) [Instance Name] FROM #Instances WHERE LTRIM(InstanceName) LIKE @ServerName + '%'

DROP TABLE #Instances

3. Wow... Now you got the list of Instances installed on the current server.

Date and time SQL Server last started - How to...

1. When was the SQL Server Service Started ?
SELECT sqlserver_start_time [SQL Server Started at] FROM sys.dm_os_sys_info
GO

2. When was the TempDB database Re-Created ?
SELECT create_date [Tempdb Re-created at] FROM sys.databases WHERE [name] ='tempdb'
GO

Both the query have some difference on it's Time, Because, The Tempdb database will be Re-created after the SQL Server service started.

So, The first one tells the actual SQL Server start date & time

Authentication Mode - Tips

We have two possible modes..
1. Windows Authentication Mode
2. Mixed Mode

1. Windows Authentication Mode:
- It enables Windows authentication mode and disables the SQL Server authentication.
- "sa" account for SQL Server authentication also created and disabled.
- When we connect through a Windows user account, SQL Server validates the account name using the Windows principal token in the operating system. The identity validated by the Windows Operating system.
- This is default authentication mode.
- The connection made using the Windows authentication is called as Trusted Connection. So, The Windows authentication is more secure than SQL Server authentication.

2. Mixed Mode:
- It enables both Windows and SQL Server authentication. But, Windows authentication can not be disabled.
- SQL Server authentication logins not based on Windows user account.
- SQL Server logins are stored / validated by the SQL Server.

Advantages of SQL Server authentication:
- It allows to connect with third party application / tools.
- It supports mixed operating system.
- It can be connected with untrusted/unknown domain.

Disadvantages of SQL Server authentication:
- If a user is a Windows domain user who has a login and password for Windows, he must still provide another (SQL Server) login and password to connect.

Object Dependencies - various level

How to identify, What are all the objects being referred/referencing from/to various sources in current database.

1. Schema Bound
2. Non-Schema Bound
3. Cross-Database
4. Cross-Server

SELECT referenced_entity_name [Referenced Objects],
CASE referencing_class WHEN 1 THEN 'Object or Column'
WHEN 12 THEN 'Database DDL Trigger'
WHEN 13 THEN 'Server DDL Trigger'
END [Referencing Type],
CASE is_schema_bound_reference WHEN 1 THEN 'Referenced entity is schema-bound'
ELSE 'Referenced entity is non-schema-bound' END [Schema Bound],
CASE referenced_class WHEN 1 THEN 'Object or Column'
WHEN 12 THEN 'Database DDL Trigger'
WHEN 13 THEN 'Server DDL Trigger'
END [Referenced Type],
referenced_server_name [Object Referred From - Server],
referenced_database_name [Object Referred From - Database],
referenced_schema_name [Object Referred From - Schema]
FROM sys.sql_expression_dependencies
GO

(OR)

What are all the Object(s) being referred by "TEST1" object ?
SELECT referenced_server_name [Object From - Server],
referenced_database_name [Object From - Database],
referenced_schema_name [Object From - Schema],
referenced_entity_name [Object From - Table]
FROM sys.dm_sql_referenced_entities('dbo.TEST1','Object')

What are all the Object(s) referencing the "TEST1" object ?
SELECT * FROM sys.dm_sql_referencing_entities('dbo.Test1','Object')

Blocking / Locking - How to...

What is blocking ?
"When two or more activities permanently block each other by each activity having a lock on a resource which the other activity are trying to lock"

Creating table and Inserting sample record(s)
CREATE TABLE Tb_Sample1
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME
)
GO

INSERT Tb_Sample1(Column1,Column2) VALUES('Sample1',CURRENT_TIMESTAMP)
GO 10

CREATE TABLE Tb_Sample2
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME
)
GO

INSERT Tb_Sample2(Column1,Column2) VALUES('Sample2',CURRENT_TIMESTAMP)
GO 10

Execute the script in a session: 57
BEGIN TRAN
UPDATE Tb_Sample1 SET COLUMN1 ='Sample One' WHERE ID = 2

Execute the script in a session: 56
BEGIN TRAN
SELECT * FROM Tb_Sample1 WHERE ID = 2

Session 57 executed successfully, But the session 56 is waiting...waiting..waiting...

How to identify Blocking Session/ Locking Session ?
IF OBJECT_ID('USP_BlockingSession','P') IS NOT NULL
DROP PROC USP_BlockingSession
GO
CREATE PROC USP_BlockingSession
AS
BEGIN
SET NOCOUNT ON

SELECT DB_NAME(tl.resource_database_id) AS [Database Name],
tl.request_session_id [Blocked Session],
bld.[text] [Blocked Query],
wt.blocking_session_id [Blocking Session],
blk.[text] [Blocking Query],
tl.resource_type [Resource Type],
CASE tl.request_mode WHEN 'S' THEN 'Shared(S)'
WHEN 'U' THEN 'Update(U)'
WHEN 'X' THEN 'Exclusive(X)'
WHEN 'IS' THEN 'Intent Shared(IS)'
WHEN 'IX' THEN 'Intent Exclusive(IX)'
WHEN 'SIX' THEN 'Shared with Intent Exclusive(SIX)'
WHEN 'Sch-M' THEN 'Schema Modification(Sch-M)'
WHEN 'Sch-S' THEN 'Schema Stability(Sch-S)'
WHEN 'BU' THEN 'Bulk Update(BU)'
WHEN 'IU' THEN 'Intent Update(IU)'
WHEN 'SIU' THEN 'Shared Intent Update(SIU)'
WHEN 'UIE' THEN 'Update Intent Exclusive(UIE)'
END [Resource Mode],
(wt.wait_duration_ms /1000) [Waiting Time(Sec)]
FROM sys.dm_tran_locks AS tl
JOIN sys.dm_os_waiting_tasks AS wt
ON (tl.lock_owner_address = wt.resource_address)
JOIN sys.dm_exec_connections AS c
ON (C.session_id = tl.request_session_id)
JOIN sys.dm_exec_connections AS c1
ON (C1.session_id = wt.blocking_session_id)
CROSS APPLY SYS.dm_exec_sql_text(c.most_recent_sql_handle) AS Bld
CROSS APPLY SYS.dm_exec_sql_text(c1.most_recent_sql_handle) AS Blk
END
GO

Execute the Stored Procedure:
EXEC USP_BlockingSession
GO

Result is:

The Session 56 blocked by the session 57

How to solve this ?
Go to the blocked Session 56 and apply the table hint (NOLOCK)

Statistics - How to ...

What is statistics ?
Microsoft SQL Server collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data.

Creating a Table
IF OBJECT_ID('Tb_Sample1','U') IS NULL
CREATE TABLE Tb_Sample1
(
Id INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME,
Column3 VARCHAR(10)
)
GO

Inserting sample data(10 Records) for testing
INSERT Tb_Sample1(Column1,Column2,Column3) VALUES('Column1',CURRENT_TIMESTAMP,'Column3')
GO 10

Creating Non-Clustered Index on ID
CREATE INDEX IND_ID ON Tb_Sample1(ID)
GO

Creating Non-Clustered Index on Column1
CREATE INDEX IND_Column1 ON Tb_Sample1(Column1)
GO

Creating Non-Clustered Index on Column2
CREATE INDEX IND_Column2 ON Tb_Sample1(Column2)
GO

Creating Non-Clustered Index on Column3
CREATE INDEX IND_Column3 ON Tb_Sample1(Column3)
GO

Fetching the Stratistics information for the Table
SP_AUTOSTATS 'Tb_Sample1'
GO

(OR)

SELECT Name [Index_Name],
CASE no_recompute WHEN 1 THEN 'OFF' ELSE 'ON' END AUTOSTATS,
STATS_DATE([OBJECT_ID],2) [Last Updated]
FROM sys.stats WHERE [Object_Id] = OBJECT_ID('Tb_Sample1')
GO

Fetching the Stratistics information for the particular index on a Table
SP_AUTOSTATS 'Tb_Sample1',NULL,'IND_Column2'
GO

Setting "Auto Update Statistics" OFF for a particular index on a Table
SP_AUTOSTATS 'Tb_Sample1','OFF','IND_Column2'
GO

Setting "Auto Update Statistics" OFF for all the indexes on a Table
SP_AUTOSTATS 'Tb_Sample1','OFF'
GO

Update statistics for a Particular Index on a Table
UPDATE STATISTICS Tb_Sample1 IND_Column2
GO

Update statistics on a particular Table
UPDATE STATISTICS Tb_Sample1
GO

Update statistics on all the User-defined and Internal tables
EXEC SP_UPDATESTATS
GO

Cannot DROP the index 'Table.StatisticsName' because it is not a statistics collection.

Normally, When we try to DELETE the statistics using the following statement, The Error occrred

DROP STATISTICS TableName.StatisticsName
GO

Msg 3739, Level 11, State 1, Line 1
Cannot DROP the index 'Table.StatisticsName' because it is not a statistics collection.

The reason is, The statistics name we going to DELETE should be a Statistics collection.

Ok

What is statistics collection ?
"Creating statistics on multiple column on a table"

How to create ?
CREATE STATISTICS StatisticsName ON TableName(Column1,Column2)

Now the statistics collection can be deleted.

Use column name instead of "*" in SELECT statement - Video

Normally when we use SELECT statements, Most of use simply using "*". The best practice says "Use the column list instead of *".

But, If the table has 50 columns then, It's very hard to enter all the column.

Is there any easiest way to use all the column names... Instead of typing or draging columns one by one ?

Yes. we have ...

sp_spaceused - Not returns correct information about Rows, Reserved Pages, Used Pages

The system stored procedure "sp_spaceused" is used to fetch the Rows, Reserved Page size, Used Data size, Allocated index size,... Etc

Sometimes, It not returns correct statistics.. How to solve this..?

Run the statement

DBCC UPDATEUSAGE(0) --Current Database

If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

It returns the following formation when it correct/update on System tables.

DBCC UPDATEUSAGE: Usage counts updated for table 'TableName' (index 'NCI2', partition 1):
USED pages (In-row Data): changed from (4969) to (4961) pages.
RSVD pages (In-row Data): changed from (4989) to (4985) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.