sqlserverpedia.com - powered by Quest

sp_who2 - in detail analysis with temp table

drop table #tmp

create table #tmp
(SPID int,
Status nvarchar(100),
Login nvarchar(100),
HostName nvarchar(100),
BlkBy nvarchar(100),
DBName nvarchar(100),
Command nvarchar(100),
CPUTime int,
DiskIO int,
LastBatch nvarchar(100),
ProgramName nvarchar(100),
SPID2 int,
REQUESTID int)

insert into #tmp
exec sp_who2 active

select * from #tmp order by DiskIO desc
select * from #tmp where Login !='sa' order by LastBatch asc
select * from #tmp where Status !='sleeping' and Login !='sa'

select * from #tmp where DBName = 'ces_flextronics'
--dbcc inputbuffer(492)

Finding the Lead Blocker in a Set of Blocking Process Reports

Script 1:
---------------------------------
SELECT sc.session_id AS UserSPID,
sr.blocking_session_id AS BlockingSPID,
DB_NAME(st.dbid) AS DatabaseNm,
ss.program_name AS AccessingProgram,
ss.login_name AS LoginNm,
OBJECT_NAME(st.objectid, st.dbid) AS ObjectNm,
CAST(st.text AS VARCHAR(MAX)) AS RequestSQL
--INTO #Processes
FROM sys.dm_exec_sessions ss
JOIN sys.dm_exec_connections sc ON ss.session_ID = sc.session_ID
LEFT JOIN sys.dm_exec_requests sr ON sc.connection_ID = sr.connection_ID
CROSS APPLY sys.dm_exec_sql_text (sc.most_recent_sql_handle) AS st
WHERE sc.session_id > 50 --gets us user processes
and isnull(sr.blocking_session_id,0) >0


Script 2:
---------------------------------

/*
--************************************************************************************
-- Description:    This script list the current locking block chain.
--            It will create 1 function: fn_GetLockChainSubTree
--                     1 table: LockChain
--
--
-- Parameters: NONE
--
-- Returns: Locking block chain
--
-- Created:     9/07/2008    John Liu
--
-- Typical Usage:    This script is typically used to find the block chain information
--
-- Comments: It also lists the dead lock (if any) at the end.
--      This script is for SQL 2005 and SQL 2008
--
--
-- Modifications:
--
-- Initials Date Description
-- -------- ---------- -----------------------------------------------------------
--
--************************************************************************************
*/

--***************************************Begin of create functions ********************************
--***************************************** Begin of dbo.fn_GetLockChainSubTree ************************
IF OBJECT_ID('dbo.fn_GetLockChainSubTree') IS NOT NULL DROP FUNCTION dbo.fn_GetLockChainSubTree
go

CREATE FUNCTION dbo.fn_GetLockChainSubTree(@Blocker AS INT)
RETURNS @TREE TABLE
(
Blocker INT NOT NULL,
Blocked INT NOT NULL,
lvl INT NOT NULL
)
AS
BEGIN
WITH Block_Subtree(Blocker,Blocked, lvl)
AS
(
-- Anchor Member (AM)
SELECT
Blocker
,Blocked
,0
FROM
LockChain
WHERE
Blocker = @Blocker

UNION all

-- Recursive Member (RM)
SELECT
l.Blocker
, l.Blocked
, t.lvl+1
FROM
LockChain AS l
INNER JOIN
Block_Subtree AS t
ON
l.Blocker = t.Blocked
)

INSERT INTO
@TREE
SELECT
*
FROM
Block_Subtree

RETURN
END
GO
--***************************************** End of dbo.fn_GetLockChainSubTree ************************
--***************************************End of create functions ********************************

--*************************************************************************************
--**********************************         Main section         **************************
--*************************************************************************************
-- -----------------------------------------------
-- Core processing
-- -----------------------------------------------
IF OBJECT_ID('LockChain') IS NOT NULL DROP TABLE LockChain
go

--initial the LockChain table
SELECT
Blocker = Blocking_Session_ID
,Blocked = Session_ID
INTO
LockChain
FROM
sys.dm_exec_requests
WHERE
Blocking_Session_ID <> 0

--list the locking chain
SELECT
t.*,s.host_name,s.program_name,s.login_name,s.nt_user_name
FROM
LockChain l
CROSS APPLY
fn_GetLockChainSubTree(l.Blocker) as t
INNER JOIN
sys.dm_exec_sessions s
ON
l.blocker = s.session_id
WHERE
l.blocker NOT IN (
SELECT
blocked
FROM
LockChain
)

--report any dead lock
SELECT
'Dead Lock' = 'Dead Lock'
,*
FROM
LockChain
WHERE
blocker IN (
SELECT DISTINCT
blocked
FROM
LockChain
)
AND
blocked IN (
SELECT DISTINCT
blocker
FROM
LockChain
)

--clean up    
--DROP TABLE LockChain
--DROP FUNCTION dbo.fn_GetLockChainSubTree

-- -----------------------------------------------
-- End of Core processing
-- -----------------------------------------------
--***************************************** End of Main section **********************************

SQL Server Performance Blog - Idera

Full Text Search

--Create FTS
Use ClientDB
GO
EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG [FTC_LO_TEXT] WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[lo_text] KEY INDEX [PK_lo_text] ON ([FTC_LO_TEXT]) WITH CHANGE_TRACKING AUTO)
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([descr])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([keywords])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ADD ([title])
GO
ALTER FULLTEXT INDEX ON [dbo].[lo_text] ENABLE
GO

--Drop FTS
EXEC sp_fulltext_database 'disable'
GO
DROP FULLTEXT INDEX ON lo_text
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'FTC_LO_TEXT')
DROP FULLTEXT CATALOG [FTC_LO_TEXT]''
GO

--Disable Stopwords and Stoplists on FTS
ALTER FULLTEXT INDEX ON lo_text
SET STOPLIST OFF;
GO

EXEC sp_fulltext_catalog 'FTC_LO_TEXT', 'rebuild'
GO

--Rebuild FTS Catalog
ALTER FULLTEXT CATALOG FTC_LO_TEXT REBUILD


--Sample of FTS search
declare @str_decr nvarchar(50), @str nvarchar(50) , @str1 nvarchar(50) , @str2 nvarchar(50)
set @str1 = 'New'
set @str2 = 'Document'
set @str_decr = '%'+@str1+'%'

select * From lo_text lt where lt.title LIKE @str_decr-- or lt.descr LIKE @str_decr

set @str = @str1 + ' OR ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)

set @str = @str1 + ' AND ' +@str2
select * from lo_text WHERE CONTAINS(title, @str)

set @str = @str1 + ' ' +@str2
select * from lo_text WHERE FREETEXT(*, @str)

set @str = '"'+@str1 + ' ' +@str2+'*"'
select * from lo_text WHERE FREETEXT(descr, @str)


NOTES:
1)If you cannot start the fdhost, please check the permission of FDLauncher Service logon account. Do you specify a user account for FDLauncher service during setup? If you didn't specify user account for FDLauncher Service, by default, FDLauncher is configured using the Local Service account. This account is not added to the SQLServerFDHostUser$$MSSQLSERVER group. To work around the issue, you need to assign Local Service account (or logon account of FDLauncher Service) to SQLServerFDHostUser$$MSSQLSERVER group. Then restart fdhost using sp_fulltext_service 'restart_all_fdhosts' procedure.

EXEC sp_fulltext_service 'restart_all_fdhosts'

Restore db from backup file with dynamic build

-----------------------------------------------------------------------
--RESTORE DB
-----------------------------------------------------------------------
use master
go

declare @backupfile nvarchar(200) , @sql nvarchar(1000), @restorepath nvarchar(100), @restoredbname nvarchar(100) ,@logicalname nvarchar(100), @ttype nvarchar(10)
set @backupfile = 'e:\ces_uws_backup_2010_09_13_182228_1716321.bak'
set @restorepath= 'E:\DATA\'

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp

create table #tmp
(LogicalName nvarchar(100),
PhysicalName nvarchar(500),
Type nvarchar(2),
FileGroupName nvarchar(20),
Size bigint ,
MaxSize bigint ,
FileId int ,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes bigint,
SourceBlockSize bigint,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly int,
IsPresent int,
TDEThumbprint nvarchar(100))


set @sql = 'RESTORE FILELISTONLY from disk = '''+@backupfile+''''
print @sql
insert into #tmp
exec sp_executesql @sql

select * from #tmp


if(OBJECT_ID('tempdb..#tmp1')>1)
drop table #tmp1

create table #tmp1
(BackupName nvarchar(500),
BackupDescription nvarchar(500),
BackupType int,
ExpirationDate datetime,
Compressed int,
Position int ,
DeviceType int,
UserName nvarchar(100),
ServerName nvarchar(100),
DatabaseName nvarchar(100),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize bigint,
FirstLSN nvarchar(100),
LastLSN nvarchar(100),
CheckpointLSN nvarchar(100),
DatabaseBackupLSN nvarchar(100),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder int,
CodePage int,
UnicodeLocaleId int ,
UnicodeComparisonStyle int,
CompatibilityLevel int,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int ,
SoftwareVersionBuild int ,
MachineName nvarchar(100),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(100),
FamilyGUID uniqueidentifier,
HasBulkLoggedData datetime,
IsSnapshot int,
IsReadOnly int ,
IsSingleUser int,
HasBackupChecksums int,
IsDamaged int,
BeginsLogChain int,
HasIncompleteMetaData int,
IsForceOffline int,
IsCopyOnly int,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN nvarchar(100),
RecoveryModel nvarchar(100),
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(500),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint )


set @sql = 'restore headeronly from disk = '''+@backupfile+''' with nounload'
print @sql
insert into #tmp1
exec sp_executesql @sql

select * from #tmp1

select @restoredbname = replace(DatabaseName,'ces_','')+'_'+convert(nvarchar(4),year(BackupFinishDate))+'_'+convert(nvarchar(4),MONTH(BackupFinishDate))+'_'+convert(nvarchar(4),DAY(BackupFinishDate))
from #tmp1

select @restoredbname

set @sql = 'RESTORE DATABASE '+@restoredbname+' from disk = '''+@backupfile+'''
WITH FILE = 1, '


declare tmp_cur cursor for
select logicalname, type from #tmp order by fileid

open tmp_cur
Fetch next from tmp_cur into @logicalname,@ttype

while @@fetch_status = 0
begin
--print @logicalname
--print @ttype
set @sql = @sql + 'MOVE '''+@logicalname+''' TO '''+@restorepath+@restoredbname+(case when @ttype = 'D' then '.mdf' when @ttype = 'L' then '.ldf' else '.ndf' end )+''','
Fetch next from tmp_cur into @logicalname, @ttype
--print '---'
end

close tmp_cur
deallocate tmp_cur

set @sql = @sql + ' NOUNLOAD, REPLACE, STATS = 10'
print @sql
exec sp_executesql @sql

set @sql = 'ALTER DATABASE '+@restoredbname+' SET RECOVERY SIMPLE'
print @sql
exec sp_executesql @sql

set @sql = 'USE '+@restoredbname+'
DBCC SHRINKFILE (2,0)
'
print @sql
exec sp_executesql @sql

set @sql = 'update '+@restoredbname+'.dbo.corp_setting set [value] = ''deadbox@cyberu.com'' where [key] = ''OverrideEmailAddress'''
print @sql
exec sp_executesql @sql

View Database Backup History

---------------------------------------------------------------------------------
--Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date


-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database
-------------------------------------------------------------------------------------------
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name

-------------------------------------------------------------------------------------------
--Most Recent Database Backup for Each Database - Detailed
-------------------------------------------------------------------------------------------
SELECT
A.[Server],
A.database_name,
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name

Free Space on Drives with T-SQL

EXEC master.sys.xp_fixeddrives