How to Restore MSDB Database

How to restore MSDB database

If your msdb goes suspect then you have two choices, if you have backup of database then restore it else you have to recreate it

If you want to recreate it here are the steps to do it

1. Start the server with trace flag 3608.Detach the damaged msdb.
2. Navigating to the directory ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn’ and start sqlservr.exe -c -T3608
3. Move or rename the damaged msdb files
4. Go to the folder ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install’ and run the instmsdb file.
5. Restart SQL Server without the 3608 trace flag

Session Property SQL Server 2005

In sql server we can get session property many ways

1. dbcc useroptions
dbcc useroptions
This will give us information about all the enabled setting for that session.

In SQL Server 2005 two new options are available

2. sessionproperty function
It returns set option of a session

You can get information of following option usig set
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
ARITHABORT
CONCAT_NULL_YIELDS_ NULL
NUMERIC_ROUNDABORT
QUOTED_IDENTIFIER

For ex.
select sessionproperty(‘Quoted_Identifier’)

If result is 1 then property on.

3. Dynamic view dm_exec_sessions
Benefit of this view that you can see property not only of this session but of also other sessions.You can join this view other dynamic system views using session id.
This view also gives much more information than session property.

If the result is 1 then property is on and if its 0 then property is set off.

select session_id,
login_name,
program_name,
date_format,
quoted_identifier,
arithabort,
ansi_null_dflt_on,
ansi_defaults,
ansi_warnings,
ansi_padding,
ansi_nulls,
concat_null_yields_null,
case transaction_isolation_level
when 0 then ‘Unspecified’
when 1 then ‘ReadUncomitted’
when 2 then ‘ReadCommitted’
when 3 then ‘Repeatable’
when 4 then ‘Serializable’
when 5 then ‘Snapshot’
end as transaction_isolation_level,
deadlock_priority
from sys.dm_exec_sessions

New Feature in Named Set – Analysis Service 2008

One of the important feature in SQL Server 2008 is Dynamic Named Sets. Till SQL Server 2005 named sets were static. In SQL Server 2005 once named sets are executed they were remain static during whole session or query or cube. This was giving wrong results during Topcount or Bottomcount like functions.

Lets look at the example to get more details

In Adventureworks cube

First we create a set of top 5 products for of Internet Sales

create set [Adventure Works].[top5products] as

‘topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])’;

First we will run query fro all time period

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

Result

Internet Sales Amount

Road-150 Red, 48 $1,205,876.99

Road-150 Red, 62 $1,202,298.72

Road-150 Red, 52 $1,080,637.54

Road-150 Red, 56 $1,055,589.65

Road-150 Red, 44 $1,005,493.87

Now we run the query for year 2003

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

Result

Internet Sales Amount

Road-150 Red, 48 (null)

Road-150 Red, 62 (null)

Road-150 Red, 52 (null)

Road-150 Red, 56 (null)

Road-150 Red, 44 (null)

Look at here this products have no sales in 2003, because in 2003 top5products are different then top5products for all time period. But even though it appears in the result because the set top5product is evaluated when it was created and it will be static for all sessions and does not reflect the change in where condition

drop set [Adventure Works].[top5products]

Now we will create dynamic set which will be effected from where condition in the query

create dynamic set [Adventure Works].[top5products] as

‘topcount([Product].[Product Categories].[Product].members,5, [Measures].[Internet Sales Amount])’;
First we will run query fro all time period

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

Result

Internet Sales Amount

Road-150 Red, 48 $1,205,876.99

Road-150 Red, 62 $1,202,298.72

Road-150 Red, 52 $1,080,637.54

Road-150 Red, 56 $1,055,589.65

Road-150 Red, 44 $1,005,493.87

Now we will run the query from 2003

select [Measures].[Internet Sales Amount] on 0,

[top5products] on 1 from

[Adventure Works]

where [Date].[Calendar].[Calendar Year].&[2003]

Result

Internet Sales Amount

Mountain-200 Black, 46 $431,458.12

Mountain-200 Black, 42 $403,918.24

Mountain-200 Silver, 38 $389,758.32

Mountain-200 Silver, 42 $373,518.39

Mountain-200 Black, 38 $367,198.40

Here result is different because top5products is different in 2003 than that of 2001 and we are getting right result because of dynamic sets.

drop set [Adventure Works].[top5products]

So because of dyamic set we get right results and better performance which is not possible in static named sets

How to get linked values in one group using CTE

/* How to get linked values in one group using CTE*/
create table table_a
(col1 varchar(100),
col2 varchar(100))
go
insert into table_a values (‘item1′,’item2’)
insert into table_a values (‘item2′,’item3’)
insert into table_a values (‘item3′,’item4’)
insert into table_a values (‘item5′,’item6’)
insert into table_a values (‘item6′,’item7’)
insert into table_a values (‘item8′,’item9’)
go
select * from table_a
go
/*
As per the above data
item1 is linked with item2
and item2 is linked with item3
and item3 is linked with item4
so all item1 , item2 , item3 and item4 are in same group, lets give it number 1.
item5 is linked with item6
and item6 is linked with item7
so item5 , item6 and item7 are in same group, lets give it number 2
item8 and item9 will be in group 3.
We can achieve this result using follwing query.
*/
WITH temp (Col1, Col2, id)
AS (SELECT Col1, Col2,
ROW_NUMBER() OVER (ORDER BY Col1, Col2) AS num
FROM TABLE_A) ,
t as
(select col1 , col2,id,1 as linkid from temp where id= 1
union all
select temp.col1 , temp.col2,temp.id, case when
(temp.col1 in (t.col1, t.col2) or
temp.col2 in (t.col1, t.col2) ) then linkid
else linkid+ 1 end
from temp , t
where temp.id= t.id+1
)
select col1, col2 , linkid from t
Regards
Amish Shah

How to monitor Index Usage SQL Server 2005

For every Developer and DBA index maintenance is very important task. Since the changing requirements of customers Developers has to regularly create new indexes for new reports and queries. As the system becomes old and database becomes large and Developers changed time to time due to different reasons, its become hard to maintain track of all index usages. Some old index which may have been irrelevant after change in business requirements.

In SQL 2000 it was not possible easily to find which indexes are used regularly and which are not used regularly. Since each update , delete and insert also do changes in index and index play important part in transaction and locking proper index maintenance requires. So for every DBA its required to get report which index are being used by application or system and which are not.

In SQL Server 2005 Dynamic Management View sys.dm_db_index_usage_stats contains counts of different types of index operations and the time each type of operation was last performed.

Every operation like seek, scan, lookup, or update on the specified index is counted as a use of that index and increments the corresponding counter in this view.

The counters are resert everytime when the SQL Server service is started.

I have tried some code as below to find index usage and find unused indexes or indexes which are updated only but not used in index seek or update or lookup operation.

declare @dbid int
–To get Datbase ID
set @dbid = db_id()

–This query will order result according to index usage, which can help you to find which indexes are highly used and requires more attention
— I am not using index upadate count over here since we requires details of index usage for search operations
— Join this view to Sys.indexes to get name of indexes. Index name will be null where there is no index exist and table scan happens.

–Here index_name and index_column may come NULL, where no index exists and SQL Server refers table directly which is called heap.

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and objectproperty(d.object_id, ‘IsIndexable’) = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc

— To find index scans

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and objectproperty(d.object_id, ‘IsIndexable’) = 1
and ( d.user_scans +d.system_scans ) > 0
order by
d.user_scans +d.system_scans

–To find unused indexes
— This query will give you details of unused indexes with details like object_name index_name,index_type , index columns .
— We are considering only indexes so we are omitting index_type heap, clustered since it represents tables

select object_name(i.object_id) object_name,
i.name index_name,
i.index_id index_id,
i.type_desc type_desc,
c.index_columns
from sys.indexes i left outer join sys.dm_db_index_usage_stats d
on d.object_id=i.object_id and i.index_id=d.index_id
and d.database_id = @dbid
left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = i.index_id and c.object_id = i.object_id
where objectproperty(i.object_id, ‘IsIndexable’) = 1
and d.index_id is null and i.type_desc not in (‘heap’,’clustered’)

— To find only indexes which are only updated but not used in index seek, lookup or scan.

select object_name(i.object_id) object_name,
i.name index_name,
i.index_id index_id,
i.type_desc type_desc,
c.index_columns,
d.user_updates,
d.user_seeks,
d.user_scans,
d.user_lookups,
d.system_updates,
d.system_seeks,
d.system_scans,
d.system_lookups
from sys.indexes i inner join sys.dm_db_index_usage_stats d
on d.object_id=i.object_id and i.index_id=d.index_id
and d.database_id = @dbid left outer join
(select distinct object_id, index_id,
stuff((SELECT ‘,’+col_name(object_id,column_id ) as ‘data()’ FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH (”)),1,1,”)
as ‘index_columns’ FROM sys.index_columns t1 ) c on
c.index_id = i.index_id and c.object_id = i.object_id
where objectproperty(i.object_id, ‘IsIndexable’) = 1
and i.type_desc not in (‘heap’,’clustered’)
and (d.user_updates > 0 or d.system_updates > 0) and d.user_seeks = 0
and d.user_scans = 0 and d.user_lookups = 0
and d.system_seeks=0
and d.system_scans = 0 and d.system_lookups = 0

Regards

Amish Shah