Useful SQL Server scripts

SELECT
OBJECT_NAME(i.ID) as tablename
,ISNULL(SYSCOLUMNS.NAME,'') as columnname
,i.name as indexname
, i.indid as isclustered
,systypes.name as columndatatype
--,*
FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
inner join systypes on systypes.xtype = SYSCOLUMNS.xtype
inner join sysobjects on sysobjects.id = i.id
WHERE I.INDID =1
AND I.INDID < 255 AND (I.STATUS & 64)=0 and SYSCOLUMNS.xtype = 36 and i.id > 255
and sysobjects.xtype = 'U'
order by
OBJECT_NAME(i.ID)

Leaving a SQL Server DBA Job Gracefully

Tuesday, January 25, 2011

drop indexes for all tables in DB

sp_msforeachtable
'

declare @name varchar(50) ,@errorsave int, @tab_name varchar(100) = ''?''

if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0) begin declare ind_cursor cursor for select name from sysindexes where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0

open ind_cursor
fetch next from ind_cursor into @name
while (@@fetch_status = 0)
begin
print ''drop index '' + @tab_name + ''.'' + @name
print ''go''
fetch next from ind_cursor into @name
end
close ind_cursor
deallocate ind_cursor
end

'

Monday, January 24, 2011

sp_alert_jobs

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_alert_jobs] Script Date: 01/24/2011 07:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[sp_alert_jobs]
(
@maxrundurationhour int = 6
)
as
begin

--CAN NOT EXECUTE THIS PROC BY QUERY , ONLY RUNS FROM JOB

if(object_id('msdb..#tmp')>1)
drop table #tmp
if(object_id('msdb..__tmp_final')>1)
drop table __tmp_final

declare @maxrunduration int = 0
--set @maxrunduration = 60 * 60 * 6 -- 6 hours
set @maxrunduration = 60 * 60 * @maxrundurationhour

create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);

insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLD4SW2';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);

--select * From #tmp

SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration
into __tmp_final
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where --j.name like 'csod%'
h.run_status != 1
or h.run_duration > @maxrunduration

--select * from __tmp_final
--select 'Job = '+jobname+' : Status = '+lastrunoutcome+ ' : Run Duration = '+ convert(nvarchar(10),lastrunduration)+' min' from __tmp_final
if(select COUNT(*) from __tmp_final) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'SQL JOB ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'ayegudkin@csod.com;palexander@csod.com;Defcon4@csod.com'

exec msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'default',
@Subject = @subject,
--@body = 'test',
@query = 'set nocount on; select ''Job = ''+jobname+'' : Status = ''+lastrunoutcome+ '' : Run Duration = ''+ convert(nvarchar(10),lastrunduration)+'' min
'' from __tmp_final;set nocount off; ',
--@attach_query_result_as_file = 0,
@query_result_header = 0,
--@query_result_width = 1000,
--@exclude_query_output = 1,
--@query_result_separator = ';',
@execute_query_database = 'msdb',
@recipients = @recipientslist
end
--EXEC sys.xp_logininfo @acctname = 'CSODMGMT\palexander', @option ='members'
--EXEC sys.xp_logininfo @acctname = 'CSODProd\SQLClusterLA4SW4'

end

Tuesday, January 11, 2011

Removing Unused Indexes | SQL performance | SQL Server Performance Blog

Monday, January 10, 2011

Run an SSIS Package Under a Different Account - SQLServerCentral

Run an SSIS Package Under a Different Account - SQLServerCentral

Run an SSIS Package Under a Different Account
By Polar Bear, 2010/03/25
Total article views: 6535 | Views in the last 30 days: 88
Rate this | Join the discussion | Briefcase | Print
Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.

Here are the steps to setup a proxy by using an existing application user account:

Create a credential using the account that having right access to the application database

Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
Go to Security - > Credentials, and click on 'New Credential...' to create a new credential

Zoom in | Open in new window
Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
Click 'ok' and the new credential should be listed.

Zoom in | Open in new window
Create a proxy using the credential created in the previous step

Right click SQL Server Agent -> Proxies, and select 'New proxy...'

Zoom in | Open in new window
Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.

Zoom in | Open in new window
Click 'OK' and the new proxy should be listed.
Setup a job to run the SSIS package using the proxy

Open the job step properties for the step that run the SSIS package
Select the proxy that created in the previous step from the 'Run as' dropdown list

Zoom in | Open in new window
Click 'OK' to save the change
Now you can run the package using the application account.

By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.

Wednesday, January 05, 2011

SQL Server Security Audit Report

Tuesday, January 04, 2011

Appendix A: Monitoring SQL Server health

Wednesday, December 29, 2010

Making a more reliable and flexible sp_MSforeachdb

SQL Server Websites

Friday, December 10, 2010

Sending email from SQL Server Integration Services (SSIS)

Thursday, December 09, 2010

Using the Foreach ADO Enumerator in SSIS - CodeProject

Tempdb Configuration Best Practices in SQL Server

Wednesday, December 08, 2010

Remove SQL Server database from single-user mode

SQL Server Security Audit Report

USE master
GO
SET nocount ON

-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole

-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))

DECLARE @ServerRole VARCHAR(128)

DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END

CLOSE srv_role
DEALLOCATE srv_role

SELECT ServerRole, MemberName FROM #temp_memberrole

-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'

SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END

DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole

-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1

-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)

DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin

PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

SET ROWCOUNT 8
DELETE FROM #temp_groupadmin

SET ROWCOUNT 0

INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')

FETCH NEXT FROM grp_role INTO @grpname
END

CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2

PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''

-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))

DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)

DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)

INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember

FETCH NEXT FROM grp_role INTO @dbname
END

CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_rolemember_final

DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final

Monday, December 06, 2010

SSIS - Dynamically set Flat File Connection Manager - Sudeep's Domain

Thursday, December 02, 2010

SQL Server Encryption - Encrypt a database

USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go

USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO

USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO

SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)

SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys

Tuesday, November 30, 2010

SQL 2008 Server Audit - Server and Database Specification

--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO

--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)

--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)

--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)

Thursday, November 18, 2010

Jobs on Server and detail information

IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]

--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1

Monday, November 15, 2010

SQL Server Integration Services (SSIS) Performance Best Practices

http://www.mssqltips.com/tip.asp?tip=1840

http://www.mssqltips.com/tip.asp?tip=1867

Friday, November 12, 2010

Microsoft SQL Server vs. MySQL

Thursday, November 04, 2010

Apply a service pack or hotfix SQL Server 2008 to a failover cluster instance

Thursday, October 28, 2010

Getting a SQL Server RowCount Without doing a Table Scan

SELECT [name]
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO

----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO

CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN

DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID

SET @nObjectID = OBJECT_ID(@sTableName)

-- Object might not be found
IF @nObjectID is null RETURN NULL

SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2

RETURN @nRowCount
END
GO

Friday, October 22, 2010

SQL 2008 Data Collection

Data Collection

Manually enable data collection
Managing Data Collection Using Transact-SQL

SQL Server 2008 Data Collector – Video Tutorial

Data Collector: Remove Data Collector to Remove Associated Objects

SQL server CPU Allocation and usage

--general sql configuration
EXEC sys.sp_configure

--server available CPU
select cpu_count from sys.dm_os_sys_info

--server used cpu by sql
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'

--server physical memory
select physical_memory_in_bytes from sys.dm_os_sys_info

Friday, October 15, 2010

UPDATE STATISTICS

sp_MSforeachtable'UPDATE STATISTICS ?;'

Friday, October 01, 2010

Custom Reports Library - SQL Server

SQL Server Manageability Team Blog
http://blogs.msdn.com/b/sqlrem/

http://blogs.msdn.com/b/sqlrem/archive/2010/06/06/mdw-overview-report-for-data-collector-mdw-report-series-part-1.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/19/mdw-overview-report-for-data-collector-server-activity-mdw-report-series-part-2.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/21/server-activity-history-report-mdw-report-series-part-3.aspx

http://blogs.msdn.com/b/sqlrem/archive/2010/06/28/server-activity-history-report-mdw-report-series-part-4.aspx

Thursday, September 30, 2010

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 **********************************

Wednesday, September 29, 2010

SQL Server Performance Blog - Idera

Thursday, September 23, 2010

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'

Tuesday, September 14, 2010

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