Making a more reliable and flexible sp_MSforeachdb

Making a more reliable and flexible sp_MSforeachdb

SQL Server Websites

SQL Server Websites

Sending email from SQL Server Integration Services (SSIS)

Sending email from SQL Server Integration Services (SSIS)

Using the Foreach ADO Enumerator in SSIS - CodeProject

Using the Foreach ADO Enumerator in SSIS - CodeProject

Tempdb Configuration Best Practices in SQL Server

Tempdb Configuration Best Practices in SQL Server

Remove SQL Server database from single-user mode

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

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

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

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

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)

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

SQL Server Integration Services (SSIS) Performance Best Practices

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

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

Microsoft SQL Server vs. MySQL

Microsoft SQL Server vs. MySQL

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

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

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

UPDATE STATISTICS

sp_MSforeachtable'UPDATE STATISTICS ?;'

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

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

Presentation Links: SQL Server Performance Tuning (Quest)

http://blogs.msdn.com/b/buckwoody/archive/2010/07/21/presentation-links-sql-server-performance-tuning-quest.aspx

Grant create table and SP for schema

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go


----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'

DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines

OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string

set @string ='GRANT EXECUTE ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string



FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name
GO

sp_msforeachdb

sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'

Find Currently Running Query

-- ALL Sessions
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

-- SPECIFIC SESSION DETAIL
dbcc inputbuffer(59)

Implementing the OUTPUT Clause in SQL Server 2008

http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/?utm_source=simpletalk&utm_medium=email-main&utm_content=OutputClause-20100726&utm_campaign=SQL

Batch File Commands

From the old days, sometimes batch file becomes very handy

http://academic.evergreen.edu/projects/biophysics/technotes/program/batch.htm

sp_MSforeachtable

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount order by rowcnt desc
select * from #rowcount
drop table #rowcount

Transparent Data Encryption in SQL Server 2008

http://www.sql-server-performance.com/articles/dba/Transparent_Data_Encryption_p1.aspx

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

http://msdn.microsoft.com/en-us/library/bb677274.aspx


You encounter SQL Server error 17308 after you enable TDE on one of your databases in SQL Server

Moving System Databases - A Checklist

Moving System Databases
Moving a database in SQL Server is normally as simple as detaching it from the server, moving it to it's new location and then attaching it again. However, that only counts for user databases. Moving system databases requires quite a bit more work. Since I had to do this a couple of times during the last weeks I wrote down a simple checklist that I could follow to make sure it was done right, so I did not screw up the servers and could speed up the process as much as possible. And, since I guess I will be doing it again sometime in the future and inevitably will lose the notes I scribbled down, I thought I could just as well post them here for safe-keeping. If anyone wants to use this checklist go ahead, but remember to do the steps in the exact order of the list, and make sure you have all the necessary backups before starting. To be completely clear, doing this wrong can completely screw up your databases and I can not take any responsibility if anything does go wrong. Also note that the checklist was written for the specific situation I was encountered with. Your system databases might have more data files and/or other file paths and names than those in the list, so you might need to make some changes. Have fun!

Make sure you have backups of all user databases plus master, model and msdb.
Moving msdb and model
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Add a new parameter "-T3608" (without the quotes)
Stop SQL Server.
Start SQL Server, and make sure that SQL Agent is NOT started.
Run the following command in Query Analyzer:
-------------
use master
go
exec sp_detach_db 'msdb'
go
exec sp_detach_db 'model'
go
-------------

Move the data and log files for both msdb (normally msdbdata.mdf and msdblog.ldf) and model (normally model.mdf and modellog.mdf) to their new locations.
Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'model'
, 'PATH_TO_MODEL_DATAFILE\model.mdf'
, 'PATH_TO_MODEL_LOGFILE\modellog.ldf'
go
-------------

Remove the -T3608 flag in Enterprise Manager/Server/Properties/Startup Parameters.
Stop SQL Server.
Start SQL Server.
Run the following in Query Analyzer and check that the file paths are correct:
-------------
use model
go
exec sp_helpfile
go
-------------

Run the following in Query Analyzer:
-------------
use master
go
exec sp_attach_db 'msdb'
, 'PATH_TO_MSDB_DATAFILE\msdbdata.mdf'
, 'PATH_TO_MSDB_LOGFILE\msdblog.ldf'
go
-------------

Run the following in Query Analyzer and check that the file paths are correct:
-------------
use msdb
go
exec sp_helpfile
go
-------------

Finished!
Moving tempdb
Run the following in Query Analyzer:
-------------
use master
go
alter database tempdb modify file (name = tempdev
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\tempdb.mdf')
go
alter database tempdb modify file (name = templog
, filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\templog.ldf')
go
-------------

Stop SQL Server
Start SQL Server
Run the following in Query Analyzer and check that the file paths correspond to those stated in step 1:
-------------
use tempdb
go
exec sp_helpfile
go
-------------

Stop SQL Server.
Move the original data and log files for tempdb to some new location, or rename them, just so that you are sure that SQL Server can not be using them.
Start SQL Server.
Run the following in Query Analyzer and check that no error occurs:
-------------
use tempdb
go
create table test (a int)
insert into test (a) values (1)
select * from test
drop table test
go
-------------

Remove the original data and log files for tempdb.
Finished!

NOTE: Steps 5 through 8 are of course not really necessary, they are just included as a quick extra check to really make sure that nothing went wrong. Skip them if you wish.
Moving master

Note: In this scenario we are not only moving the master database, we are also moving all of the files that SQL Server uses in it's 'data location' (as specified when installing SQL Server). The situation I encountered was that SQL Server's data location was specified to be something like D:\ (though with the program files as normal on C:\Program Files\Microsoft SQL Server\), but now the entire D:\ drive needed to be removed, so we needed to move everything SQL Server had stored there plus all references to it to avoid problems in the future. If you are only moving the master database you only need to follow the applicable steps of course.
In Enterprise Manager, right-click the server and choose Properties.
Click Startup Parameters.
Remove all of the three parameters that are already there (if there are more, remove the three that correspond to the three below in step 4).
Add the three following parameters:
-dPATH_TO_NEW_LOCATION_OF_MASTER_MDFFILE\master.mdf
-ePATH_TO_NEW_LOCATION_OF_SQLAGENT_ERRORLOG\ERRORLOG
-lPATH_TO_NEW_LOCATION_OF_MASTER_LOGFILE\mastlog.ldf

In my case the values of these parameters where as follows:
-dE:\MSSQL\Data\master.mdf
-eE:\MSSQL\LOG\ERRORLOG
-lE:\MSSQL\Data\mastlog.ldf
Stop SQL Server.
Move the files as specified below:
OLD_PATH_TO_MASTER_MDFFILE\master.mdf --> NEW_PATH_TO_MASTER_MDFFILE\master.mdf
OLD_PATH_TO_MASTER_LOGFILE\Data\mastlog.ldf --> NEW_PATH_TO_MASTER_LOGFILE\mastlog.ldf
OLD_PATH_TO_SQL_DATA_LOCATION\BACKUP --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\JOBS --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\LOG --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)
OLD_PATH_TO_SQL_DATA_LOCATION\REPLDATA --> NEW_PATH_TO_SQL_DATA_LOCATION
(the entire folder with everything in it)

Make the following changes to the registry (using regedit):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
BackupDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\BACKUP

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\REPLDATA

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
SQLDataRoot = NEW_PATH_TO_SQL_DATA_LOCATION\

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent
ErrorLogFile = NEW_PATH_TO_SQL_DATA_LOCATION\LOG\SQLAGENT.OUT
WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\JOBS

Note: This checklist does not cover servers using full-text search. If your server does use FT, then you will need to expand steps 6 and 7. Just move the FT directory in step 6 and search the registry for any references to it and change them as necessary.
Start SQL Server.
Finished!
If you are using SQL Agent on your server do not forget to check that it is running.
Conclusion
So there we are, all system databases moved. Again, please note that this was mainly meant as a checklist for myself, but feel free to use it as a base for your own checklist when you need to move system databases. I urge you to read through it several times so you are sure what it says and what you are doing. Most of the steps here come from the Microsoft article Moving SQL Server databases to a new location with Detach/Attach, which will probably help you more than this list. I simply compiled them into an easy-to-follow, step-by-step list that I could use to cut the down-time as much as possible.

By Christoffer Hedgate, 2004/11/30

Multi-statement Table Valued Function vs Inline Table Valued Function

remove orphan users

--create proc usp_remove_orphan_users as
set nocount on

-- Section 1: Create temporary table to hold databases to process

-- drop table if it already exists
if (select object_id('tempdb..#dbnames')) is not null
drop table #dbnames

-- Create table to hold databases to process
create table #dbnames (dbname varchar(128))

-- Section 2: Determine what databases have orphan users
exec master.dbo.sp_MSforeachdb 'insert into #dbnames select ''?'' from master..syslogins l right join ?..sysusers u
on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'')
having count(*) > 0'

-- Section 3: Create local variables needed
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @cmd nchar(4000)
declare @c int
declare @hexnum char(100)
declare @db varchar(100)

-- Section 5: Process through each database and remove orphan users
select @cnt=count(*) from #DBNAMES
While @CNT > 0
begin

-- get the name of the top database
select top 1 @db=dbname from #DBNAMES

-- delete top database
delete from #DBNAMES where dbname = @db

-- Build and execute command to determine if DBO is not mapped to login
set @cmd = 'select @cnt = count(*) from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @cmd,N'@cnt int out',@cnt out

-- if DB is not mapped to login that exists map DBO to SA
if @cnt = 1
begin
print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1


-- drop table if it already exists
if (select object_id('tempdb..#orphans')) is not null
drop table #orphans

-- Create table to hold orphan users
create table #orphans (orphan varchar(128))

-- Build and execute command to get list of all orphan users (Windows and SQL Server)
-- for current database being processed
set @cmd = 'insert into #orphans select u.name from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid ' +
'where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +
'and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' ' +
'and u.name <> ''system_function_schema'')'
exec (@cmd)


-- Are there orphans
select @cnt = count(*) from #orphans

WHILE @cnt > 0
BEGIN

-- get top orphan
select top 1 @name= orphan from #orphans

-- delete top orphan
delete from #orphans where orphan = @name

-- Build command to drop user from database.
set @cmd = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
print @cmd
--exec (@cmd)


-- are there orphans left
select @cnt = count(*) from #orphans
end -- WHILE @cnt > 0


-- are the still databases to process
select @cnt=count(*) from #dbnames

end -- while @cnt > 0

-- Remove temporary tables
drop table #dbnames, #orphans

SQL Server DATE/TIME Workbench

Reviewing AutoGrow events from the default trace

DECLARE
@path VARCHAR(255),
@cmd VARCHAR(300);

-- customize this path, of course, if necessary:
SELECT
@path = 'C:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\',
@cmd = 'dir /b "' + @path + '*.trc"';

DECLARE @files TABLE
(
fn VARCHAR(64)
);

INSERT @files
EXEC master..xp_cmdshell @cmd;

DELETE @files
WHERE fn IS NULL;

SELECT
e.DatabaseName,
e.[FileName],
e.SPID,
e.Duration,
e.StartTime,
e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END,
[TraceFile] = f.fn
FROM
@files f
CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE
e.EventClass IN (92,93)
-- AND f.fn LIKE 'log[_][0-9]%.trc'
ORDER BY
e.StartTime DESC;

Excel - if a cell value exist in list on other column list

=IF(ISNA(VLOOKUP(A15,$E$1:$E$600,1,FALSE)),"",A15)

Cleanup duplicate record, another interesting method

SET ROWCOUNT 1
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0

Wildcard search characters in SQL

underscore is a wildcard for single charachter

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '_a_a%' order by TABLE_NAME
ces_barclaysplc dbo data_type BASE TABLE
ces_barclaysplc dbo data_type_group BASE TABLE
ces_barclaysplc dbo data_type_group_operator BASE TABLE
ces_barclaysplc dbo datafeed_questionbank BASE TABLE
ces_barclaysplc dbo datafeed_questionbank_BAKUP BASE TABLE
ces_barclaysplc dbo datafeed_testanswer BASE TABLE
ces_barclaysplc dbo datafeed_testbank BASE TABLE
ces_barclaysplc dbo datafeed_testquestion BASE TABLE


to search for underscore use [_]

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '[___]%' order by TABLE_NAME
ces_barclaysplc dbo ___analytics_report_schedule_08_26_2009 BASE TABLE
ces_barclaysplc dbo ___datafeed_talent_20100210 BASE TABLE
ces_barclaysplc dbo ___datafeed_users_20100210 BASE TABLE
ces_barclaysplc dbo ___dataseg_ucf BASE TABLE
ces_barclaysplc dbo ___lo_options BASE TABLE
ces_barclaysplc dbo ___loadrl BASE TABLE
ces_barclaysplc dbo ___pdOnline_20100210 BASE TABLE

Show statistics and query plans

SET SHOWPLAN_ALL on
go
set statistics IO on
set statistics time on


.....QUERY IS HERE

GO

SET SHOWPLAN_ALL off
go

Resource Governor in SQL Server 2008 - Sample

http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx

VBScripts to manage Text Files

http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/

Fixing SQL Cluster MSDTC

http://support.microsoft.com/kb/890634

filegroup get all DB tables and views info

-- Get all DB tables and views filegroup info
SET NOCOUNT ON

-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo

CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)


-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0


SET NOCOUNT OFF

SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC

-- Cleanup

IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO


----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0

Filegroup space usage and allocation

create table #Data(
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)

create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
FileID int null
)

create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)

INSERT #Data (Fileid , [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.FileId FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.fileid=f.fileid
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log

SQL server performance trouble shooting tools

http://sqlserver-qa.net/blogs/tools/archive/2008/06/05/sql-server-troubleshooting-tools-pssdiag-sqldiag-sqlnexus-rml-utilities-and-readtrace-which-one-to-choose.aspx