The media set has 2 media families but only 1 are provided.

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

VERIFY DATABASE is terminating abnormally.

This is very common error, which generally comes with Restore and Backup. To dig into the problem lets stimulate this error.

Here I have a bakup file stored in D:\ drive, I want to use RESOTE common to verify the bak file.

RESTORE VERIFYONLY FROM

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

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

VERIFY DATABASE is terminating abnormally.

As I hit F5, I got this error, the error clearly specify, that the backup of the database wrote its data to two files, not one.   Therefore, you need both files in order to do a successful restore.

To locate the missing part you can use the following query.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = '<Media_UUID>'

Now Media_UUID you can get from

RESTORE LABELONLY FROM

disk = 'D:\SQL Server\myBakup.bak'

MediaName MediaSetId                          

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

NULL      A335C850-4788-4A6B-BAE0-984AF5F4E6CC

Now here we have MediaSetId, we can use this mediaSetId to the above query to find the missing bak file location.

SELECT b.physical_device_name

FROM msdb..backupmediaset a

INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id

WHERE a.media_uuid = 'A335C850-4788-4A6B-BAE0-984AF5F4E6CC''

physical_device_name

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

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak

D:\SQL Server\myBakup.bak

So here we found that the bakup was splited into two different file.  Now we can use both the RESTORE Command to use both the

RESTORE VERIFYONLY FROM

disk = 'D:\SQL Server\myBakup.bak',

Disk ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Backup\TestDB.bak'

The backup set on file 1 is valid.

Conclusion: You can msdb..backupmediaset and msdb..backupmediafamily table to find the missing file location.

The only gears that loom in coders dream are their love to code. Love you dear SQL Server  :)

Post Reference: Vikram Aristocratic Elfin Share

Fantastic DMV in SQL Server 2014, sys.dm_exec_query_profiles

You can check the progress of query execution using this DMV, you can get information of each operator used in Actual Query Plan in detail through this dmv.

Lets play around with dm_exec_query_profiles.

Test 1

I opened a SSMS and run my query in session 1

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

At the same time I opened another query window(another session, session 2) and run my dm_exec_query_profiles

select * from sys.dm_exec_query_profiles

(0 row(s) affected)

What I found is zero record though a heavy query is running in session1 but it didn’t capture the query progress.

Test 2

I found that to get the result from this DMV you need to trigger this. And to trigger this DMV you need to SET STATISTICS PROFILE ON

Again in session1

SET STATISTICS PROFILE ON

-- My Long query in session 1

select * from sys.all_columns

CROSS JOIN sys.objects

GO

Session 2

select * from sys.dm_exec_query_profiles

select session_id,physical_operator_name,node_id,row_count,

estimate_row_count,first_row_time,last_active_time,cpu_time_ms

from sys.dm_exec_query_profiles

So here lot of interesting information we captured through this DMV, all the information and progress of running query including the time taken by each physical operator, row count etc.

But only point of disappointment is it need either actual Query plan on or Set profiler on the running query for which we want to capture the progress of query.

My code is my fire. Fire and individual cannot sleep, when I will get prospect of sleep!!!

 

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching VI - Forced Parameterization in Adhoc Queries

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1
• Plan Caching IV - Simple Parameterization in Adhoc Query Processing
• Plan Caching V - Disadvantage of Simple Parameterization

In the last article we saw that the datatype of constant is selected on fly depending upon the constant value by SQL Server, due to which the cached Prepared Parameterized plan is not been used by a similar query with constant little different.

Just check this, below example.  

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

select * from emp where emp_id = 1

go

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

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

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

From the output we can see that the constant used in query where predict is being treated as tinyint. So the datatype is of the constant is being selected on fly by SQL Server while making a prepared Parameterized cache plan.

Now let’s query the same select with contact value ranging out of tiny data type.

select * from emp where emp_id = 700

go

 

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

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

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see from the output that it has not used the Prepared plan which gets generated when we executed query with predicate constant value as 1 and generate a new prepared plan and cached in memory; this time the constant is replaced by parameter and the type for the parameter is choosed as smallInt unlike tinyint in previous case.

So we get two prepared queries plan with two different parameter data type. So SQL Server is not making use of existing Parameterized cached plan.

The only way to force SQL Server to use the same data type  for both the queries is to enable PARAMETERIZED FORCED for the database.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

alter database sparsh set parameterization forced;

go

Here we have set PARAMETERIZATION FORCED enabled. Once this is done SQL Server treats every kind constants as just a set of parameters.

Now since we have enabled PARAMETERIZATION FORCED. Lets run both the select query and query the dm_exec_cached_plans to check the output.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 700

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go   

usecounts cacheobjtype  objtype  text

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

2         Compiled Plan Prepared (@0 int)SELECT * FROM emp WHERE emp_id=@0

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see that no new Prepared plan gets generated, instead the same prepared plan is referred by the second query with emp_id = 700. And the data type chosen by SQL Server for constant is INT which satisfy both the constant.

Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.

If my girl friend judge my love to code is like putting them in a strait jacket and kicking them down a flight, then yes.. I have deep love to my code K   

Post Reference: Vikram Aristocratic Elfin Share