script to copy all permissions from a database:

http://dangerousdba.blogspot.com/2012/08/copying-permissions.html




SET NOCOUNT ON
DECLARE    @OldUser sysname, @NewUser sysname
DECLARE PC CURSOR FOR SELECT name FROM sys.database_principals WHERE Principal_ID>4 AND is_fixed_role=0
OPEN PC
FETCH NEXT FROM PC INTO @OldUser
WHILE @@FETCH_STATUS=0
BEGIN
SET    @NewUser = @OldUser
SELECT '-- '+@OldUser AS '-- Current User'
SELECT    'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

SELECT    '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser) AS '--Comment'

SELECT    'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'
FROM    sys.database_role_members AS rm
WHERE    USER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC

SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.objects AS obj
    ON perm.major_id = obj.[object_id]
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
    LEFT JOIN
    sys.columns AS cl
    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE    usr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SELECT    CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
    + SPACE(1) + perm.permission_name + SPACE(1)
    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'
FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
WHERE    usr.name = @OldUser
AND    perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
FETCH NEXT FROM PC INTO @OldUser
END
CLOSE PC
DEALLOCATE PC

Performance Monitor Wizard

Performance Monitor Wizard

Performance Analysis of Logs (PAL) Tool

http://pal.codeplex.com/

Process Trace File

 --- I believe Raymond Peacock is the author



-- variables
declare @trace nvarchar(1000), @filter nvarchar(1000)
select @trace = $(traceFile)
--'E:\Traces\SERVERNAME__sp_trace.trc'
--select @filter = 'p_chat'


-- read raw data
select *
into #trace
from ::fn_trace_gettable(@trace, DEFAULT)
where TextData is not null
and CONVERT(varchar(100), textdata) <> 'exec sp_reset_connection'


-- extract according to filter
select CASE WHEN TextData LIKE '%exec %' THEN CONVERT(varchar(1000), TextData) ELSE NULL END AS ProcName, HostName, cpu, reads, duration, starttime, CONVERT(varchar(1000), TextData) AS TextData
into #procs
from #trace
where textdata like case when @filter is not null then '%' + @filter + '%' else textdata end


-- Deletes
DELETE #procs WHERE textData LIKE '%msdb.dbo.%'
DELETE #procs WHERE TextData = ' if @@trancount > 0 commit tran'
DELETE #procs WHERE TextData = 'begin tran'


-- reformat
update #procs
SET procname = SUBSTRING(procname, CHARINDEX('exec dbo.', ProcName), 1000)
WHERE CHARINDEX('exec dbo.', ProcName) > 0

update #procs
set procname = REPLACE(ProcName, 'exec dbo.', '')

update #procs
set procname = LEFT(procname, charindex('@', ProcName)-1)
where CHARINDEX('@', procname) > 0


-- summary results by proc
select top 10 procname, COUNT(*) as calls, AVG(cpu) AS AvgCPU, AVG(Reads) AS AvgReads, AVG(duration) as duration, MIN(starttime) AS [Start], MAX(starttime) AS [End]
from #procs
group by procname
order by (COUNT(*) * AVG(Reads)) DESC


-- results for top 5 sprocs
select *
from #procs
where procname in (
    select top 1 procname
    from #procs
    where ProcName is not null
    group by procname
    order by (COUNT(*) * AVG(Reads)) DESC
)
order by StartTime


-- cleanup
DROP TABLE #trace
DROP TABLE #procs

Automated Trace job

USE [msdb]
GO

/****** Object:  Job [Automated Trace - 1 Minute (ad-hoc)]    Script Date: 5/28/2014 1:13:23 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [SQL Vision]    Script Date: 5/28/2014 1:13:23 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'SQL Vision' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'SQL Vision'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Automated Trace - 1 Minute (ad-hoc)',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'SQL Vision',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Team_DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Trace]    Script Date: 5/28/2014 1:13:24 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run Trace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'-- Variables
declare @rc int, @TraceID int, @filename NVARCHAR(256), @maxsize BIGINT, @on BIT
DECLARE @Folder VARCHAR(1000)


-- Check for other running traces
SELECT @rc = COUNT(DISTINCT traceid) FROM ::fn_trace_getinfo(default)
IF (@rc > 1) goto error


-- Create the holding folder
SELECT TOP 1 @Folder = RTRIM(RootFolderPath) FROM SysAdmin.dbo.tbl_AutoTrace
IF RIGHT(@Folder, 1) <> ''\''
SET @Folder = @Folder + ''\''

SELECT @Folder = @Folder +
                @@SERVERNAME + ''\'' +
''output_'' +
CONVERT(VARCHAR(8), GETDATE(), 112) + ''_'' +
LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), '':'', ''''), 4)

exec master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + ''\'' + REPLACE(@@SERVERNAME, ''\'', ''_'') + ''__sp_trace''
SELECT @maxsize=50, @on=1


-- Create a Queue
exec @rc = sp_trace_create @TraceID output, 2, @filename, @maxsize, NULL
if (@rc != 0) goto error


-- Set the events
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 10, 2, @on  -- Needed for 2008 RML
exec sp_trace_setevent @TraceID, 12, 2, @on  --  Needed for 2008 RML

--exec sp_trace_setfilter @TraceID, 10, 0, 7, N''SQL Server Profiler - 8deeb35d-8e51-401e-bc3c-2574de5948eb''


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1


-- Wait 10 min
WAITFOR DELAY ''00:01:00''

-- Stop/Delete the trace

exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2

goto finish

error:
select ErrorCode=@rc

finish:',
@database_name=N'SysAdmin',
@output_file_name=N'G:\Traces\1mintrace.log',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Will produce candidate shrink/grow commands based on given

USE tempdb
GO

SET NOCOUNT ON
SET   QUOTED_IDENTIFIER ON

DECLARE @target DECIMAL(5,2)
SET         @target = 20      -- Modify to desired value of free space %

DECLARE @DriveLetter nvarchar(4)
SET @DriveLetter  ='H'


/***************************************************************************
Candidate Commands Utility                     
                                         
Written by: David Paul Giroux                                    
Date: Fall 2008                                                                                      
Purpose: Assist in file size management.  Will produce candidate shrink/grow commands based on given Target                                
Calls: xp_fixeddrives        
Data Modifications: None

User Modifications:
* Modify @target to desired value of free space percent

Known Issue: There is an issue when dealing with smaller values because the
candidate commands use int data type. The candidate command may round to a value equal to the current file size.
Thus a MODIFY FILE statement will fail. Workaround: Add 1 to the candidate command 
                                                     
***************************************************************************/

DECLARE     @cmd varchar(1000)          -- various commands
DECLARE     @counter tinyint            -- Number of databases
DECLARE     @crlf char(2)               -- carriage return line feed
DECLARE     @db sysname                 -- Database Name

SET         @crlf = CHAR(13) + CHAR(10)
SET         @target = (100.0 - @target) * .01

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
      DBName sysname,
      [FileName] sysname,
      FileType sysname,
      Drive char(1),
      UsedData varchar(25),
      TotalDataSize varchar(25),
      Smallest decimal(10,2)
      )

-- Databases to examine
DECLARE @Databases TABLE  (
      DID tinyint IDENTITY(1,1) primary key,
      db sysname NULL
      )

-- Hold values from xp_fixeddrives
DECLARE @DiskInfo TABLE(
      Drive char(1) primary key,
      MBFree int
      )

-- Gather databases
INSERT @Databases
SELECT      DISTINCT sd.[name]
FROM  sys.master_files mf WITH (NOLOCK)
JOIN  sys.databases sd WITH (NOLOCK)
ON          mf.database_id = sd.database_id
WHERE sd.[state] = 0
AND         sd.is_read_only = 0
AND         sd.is_in_standby = 0
AND         sd.[name] NOT IN ('model', 'tempdb')
AND         mf.[type] = 0
-- to exclude databases that have a full text-catalog offline
AND         sd.database_id NOT IN (
                            SELECT DISTINCT database_id
                            FROM  sys.master_files WITH (NOLOCK)
                            WHERE [state] <> 0)

SET         @counter = SCOPE_IDENTITY()

WHILE @counter > 0
BEGIN
            SELECT      @db = db
            FROM  @Databases
            WHERE DID = @counter   

            SELECT @cmd =

            N'USE [' + @db + N']' +  @crlf +
            N'SET NOCOUNT ON' + @crlf +
            N'SELECT     '+ QUOTENAME(@db, '''') + N',' + @crlf +
            N'[name],' + @crlf +
            N'CASE type ' + @crlf +
            N'     WHEN 0 THEN ''DATA''' + @crlf +
            N'     WHEN 1 THEN ''LOG'''  + @crlf +
            N'     ELSE ''Other''' + @crlf +
            N'END,' + @crlf +
            N'LEFT(physical_name, 1), ' + @crlf +
            N'CAST(FILEPROPERTY ([name], ''SpaceUsed'')/128.0 as varchar(15)),' + @crlf +
            N'CAST([size]/128.0 as varchar(15))' + @crlf +
            N'FROM sys.database_files WITH (NOLOCK)' + @crlf +
            N'WHERE      [state] = 0' + @crlf +
            N'AND        [type] IN (0,1)'

            -- Preliminary results
            INSERT #Results
            (DBName, [FileName], FileType, Drive, UsedData, TotalDataSize)
            EXEC (@cmd)

            SET   @counter = @counter - 1
END

ALTER TABLE #Results
ALTER COLUMN TotalDataSize decimal(10,2)

ALTER TABLE #Results
ALTER COLUMN UsedData decimal(10,2)

UPDATE      #Results
SET         Smallest = UsedData / @target

-- Command determines free space in MB
INSERT INTO @DiskInfo
EXEC master..xp_fixeddrives

---- Final Query
SELECT      upper(DBName) as dbname,
            upper([FileName]) as filename,
            upper(FileType) as filetype,
            upper(r.Drive) as Drive,
            CAST((TotalDataSize)/1024 as decimal(5,2)) AS [Total Size GB],
            CAST((UsedData /1024) as decimal(5,2))  [Used Space GB],
            CAST((TotalDataSize - UsedData)/1024 as decimal(5,2))  [Free Space GB],
            UsedData,
            TotalDataSize - UsedData N'FreeData',
            TotalDataSize,
            CAST(((TotalDataSize - UsedData) / TotalDataSize) * 100 as decimal(5,2)) [%DataFeeSpace],
            d.MBFree N'DiskFreeSpace',
            Smallest N'SmallestForTarget',
            CASE
                    WHEN TotalDataSize > Smallest THEN CAST(TotalDataSize - Smallest as varchar(10)) + N' Decrease'
                    ELSE CAST(Smallest - TotalDataSize as varchar(10)) + N' Increase'
            END N'CandidateResult',
            CASE
                  WHEN Smallest - TotalDataSize > d.MBFree THEN N'Insufficient Disk Space'
                    WHEN TotalDataSize > Smallest
                              THEN N'USE [' + DBName + N'] DBCC SHRINKFILE(' + QUOTENAME([FileName], '''') + N', ' + CAST(CAST(Smallest as int) as varchar(10)) + N')'
                    ELSE  N'ALTER DATABASE [' + DBName + N']' + @crlf +
                                      N'MODIFY FILE (' + @crlf +
                                      N'     NAME = ' + [FileName] + N',' + @crlf +
                                      N'     SIZE = ' + CAST(CAST(Smallest as int) as varchar(10)) + @crlf +
                                      N'     )'
            END N'CandidateCommand'
FROM  #Results r
JOIN  @DiskInfo d
ON          r.Drive = d.Drive
and     r.drive = @DriveLetter
--ORDER BY (TotalDataSize - UsedData) / TotalDataSize
order by CAST((TotalDataSize - UsedData)/1024 as decimal(5,2)) desc

DROP TABLE #Results

Partitioning: Three useful query

http://weblogs.sqlteam.com/dmauri/archive/2006/04/20/9646.aspx

DECLARE @PartTables Table (DBName sysname, TableName sysname, PartitionScheme sysname)


INSERT INTO @PartTables
EXEC sp_msforeachdb '    select DISTINCT ''[?]'' AS DBName, t.name As TableName, s.name AS PartitionScheme
                        from [?].sys.indexes i
                        join [?].sys.partition_schemes s
                            on i.data_space_id = s.data_space_id
                        join [?].sys.tables t
                            on i.object_id = t.object_id'

SELECT * FROM @PartTables


DECLARE @OBJECTNAME sysname = 'XXXXX'

select distinct
   p.object_id,
   index_name = i.name,
   index_type_desc = i.type_desc,
   partition_scheme = ps.name,
   data_space_id = ps.data_space_id,
   function_name = pf.name,
   function_id = ps.function_id
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
where p.object_id = object_id(@OBJECTNAME)




DECLARE @OBJECTNAME sysname = 'XXXX'


select
   p.object_id,
   p.index_id,
   p.partition_number,
   p.rows,
   index_name = i.name,
   index_type_desc = i.type_desc,
   i.data_space_id,
   pf.function_id,
   pf.type_desc,
   pf.boundary_value_on_right,
   destination_data_space_id = dds.destination_id,
   prv.parameter_id,
   prv.value
from
   sys.partitions p
inner join
   sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
inner join
   sys.data_spaces ds on i.data_space_id = ds.data_space_id
inner join
   sys.partition_schemes ps on ds.data_space_id = ps.data_space_id
inner join
   sys.partition_functions pf on ps.function_id = pf.function_id
inner join
   sys.destination_data_spaces dds on dds.partition_scheme_id = ds.data_space_id and p.partition_number = dds.destination_id
left outer join
   sys.partition_range_values prv on prv.function_id = ps.function_id and p.partition_number = prv.boundary_id
where p.object_id = object_id(@OBJECTNAME)

Collect Lock information

USE [msdb]
GO

/****** Object:  Job [Collect Lock information]    Script Date: 08/16/2012 11:30:41 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 08/16/2012 11:30:41 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Collect Lock information',
        @enabled=0,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'DBA_ONLY', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [run tsql]    Script Date: 08/16/2012 11:30:42 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'run tsql',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE [SysAdmin]
GO

/****** Object:  Table [dbo].[locks]    Script Date: 10/22/2010 11:23:14 ******/
IF  not EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[locks]'') AND type in (N''U''))


CREATE TABLE [dbo].[locks](
    [databasename] [nvarchar](128) NULL,
    [resource_type] [nvarchar](60) NOT NULL,
    [requested_object_name] [nvarchar](128) NULL,
    [lock_type] [nvarchar](60) NOT NULL,
    [request_status] [nvarchar](60) NOT NULL,
    [request_session_id] [int] NOT NULL,
    [lock_owner_address] [varbinary](8) NOT NULL,
    [request_owner_id] [bigint] NULL,
    [TimeofOccurence] [datetime] NOT NULL
) ON [PRIMARY]


use master
go

DECLARE @COUNTER int
, @RUN_TIME_MIN int
, @STOPAT int
   
SET @COUNTER=0;


truncate table SysAdmin..locks

WHILE 1=1

BEGIN

with all_locks (databasename,resource_type, requested_object_name,lock_type,request_status,request_session_id,
          lock_owner_address,request_owner_id,TimeofOccurence)
as
(
select Db_name(resource_database_id) databasename,
       resource_type,
       ( case
           when resource_type = ''OBJECT'' then Object_name(resource_associated_entity_id, resource_database_id)
           when resource_type in ( ''DATABASE'', ''FILE'', ''METADATA'' ) then ''N/A''
           when resource_type in ( ''KEY'', ''PAGE'', ''RID'' ) then (select Object_name(object_id)
                                                                from   sys.partitions
                                                                where  hobt_id = resource_associated_entity_id)
           else ''Undefined''
         end )                       as requested_object_name,
       request_mode                  as lock_type,
       request_status,
       request_session_id,
       lock_owner_address,
       request_owner_id              as transaction_id,
       getdate() as TimeofOccurence
from   sys.dm_tran_locks
where  resource_type not in ( ''DATABASE'', ''METADATA'', ''APPLICATION'' )
      
)


insert into sysadmin..locks
select * from all_locks
where lock_type =''X''



SET @COUNTER=@COUNTER+1;
WAITFOR DELAY ''00:00:01'';

IF @COUNTER = -1 BREAK;

END
',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Sp_spaceused

  declare @Space TABLE  (
   tablename    sysname,
   [rows]       bigint,
   reserved_S   varchar(32),
   data_S       varchar(32),
   index_size_S varchar(32),
   unused_S     varchar(32)
   )


  INSERT @Space(tablename,[rows],reserved_s,data_s,index_size_s,unused_s)
  EXEC sp_msforeachtable 'exec sp_spaceused [?]'


select
DB_NAME() as DatabaseName,
tablename    ,
   [rows]       ,
   type_desc,
   cast(replace(reserved_S, 'KB','') as bigint)/1024 as reserved_MB,
    cast(replace(data_S, 'KB', '') as bigint)/1024 as datasize_MB ,
    cast(replace(index_size_S, 'KB', '') as bigint)/1024 as index_size_MB  ,
    cast(replace(unused_S, 'KB', '') as bigint)/1024 as unused_MB
from @Space spa
inner join sys.indexes idx on idx.[object_id]=object_id(spa.tablename) and index_id in (0,1)
where (cast(replace(reserved_S, 'KB', '') as bigint)/1024) > 50000
order by unused_MB desc

Blocking Chains

-- http://thesqlguy.wordpress.com/2010/11/15/sql-2005-blocking-chains-a-friendly-display-using-cte-and-recursion/
--Best to output results to text rather than grid -- SQL 2005 and above only

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
    DROP TABLE #Processes

DECLARE @results TABLE (
    id INT IDENTITY(1,1),
    DB_Name VARCHAR(500),
    BlockingSPID INT, SPID INT,
    BlockingStatement VARCHAR(MAX),
    RowNo INT, LevelRow INT,
    hostname sysname
)

SELECT
    s.spid,
    BlockingSPID = s.blocked,
    DatabaseName = DB_NAME(s.dbid),
    s.program_name,
    s.loginame,
    ObjectName = OBJECT_NAME(objectid, s.dbid),
    Definition = CAST(text AS VARCHAR(MAX)),
    hostname
INTO #Processes
FROM sys.sysprocesses s
    CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;


WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, DB_Name, Hostname)
AS
(
    SELECT
        s.SPID,
        s.BlockingSPID,
        s.Definition,
        ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
        0 AS LevelRow,
        s.DatabaseName AS DB_Name,
        s1.hostname
    FROM #Processes s
        INNER JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
    WHERE s.BlockingSPID = 0
        UNION ALL
    SELECT
        r.SPID,
        r.BlockingSPID,
        r.Definition,
        d.RowNo,
        d.LevelRow + 1,
        r.DatabaseName AS DB_Name,
        d.Hostname
    FROM #Processes r
        INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
    WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
    [DB_Name],
    [BlockingSPID],
    [SPID],
    [BlockingStatement],
    [RowNo],
    [LevelRow],
    [Hostname]
)
SELECT
    MIN(DB_NAME) AS DB_Name,
    BlockingSPID,
    SPID,
    BlockingStatement,
    MIN(RowNo),
    LevelRow,
    [Hostname]
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow,  [Hostname]
ORDER BY MIN(RowNo), LevelRow
 
SELECT
    CASE
        WHEN [BlockingSPID] = 0
        THEN '
****Head of Blocking Chain SPID hostname:' + rtrim(hostname) + ' Spid:' + CAST([SPID] AS VARCHAR(5)) + '...
'
            + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
                + '...is blocking the following SPID(s):'
        WHEN [LevelRow] > 1
        THEN '
'
            + SPACE(LevelRow * 5) + 'SPID '
            + CAST(BlockingSPID AS VARCHAR(50))
            + ' is, in turn, blocking the following SPID:
'
            + SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')

        ELSE '
'
            + SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
    END
FROM @results

IF NOT EXISTS (SELECT TOP 1 * FROM @results)
    PRINT 'No Blocking Chains Were Found'

BEGIN TRY
    DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH

dm_exec_query_stats

SELECT TOP 20
        SUBSTRING(text, statement_start_offset/2 + 1,
             (CASE WHEN statement_end_offset = -1
                             THEN LEN(CONVERT(nvarchar(max), text)) * 2
                             ELSE statement_end_offset
                END - statement_start_offset)/2) as SQL,
        db_name(query_text.DBID) as DBNAME, Object_name(query_text.objectid,query_text.DBID) as OBJECTNAME
        ,
        cast(c.query_plan as XML) as queryplan, cast(c.query_plan as XML).exist(
'declare default element namespace ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'';
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan//MissingIndexes') HASMISSINGINDEX,
        --s.min_worker_time / 1000 AS min_cpu_time_ms,
        --s.max_worker_time / 1000 AS max_cpu_time_ms,
        ((s.total_worker_time * 1.00)/ s.execution_count)/ 1000.00 AS avg_cpu_time_ms,
       
       
        --s.total_elapsed_time / 1000.00 AS total_execution_time_ms,
        --s.min_elapsed_time / 1000.00 AS min_execution_time_ms,
        --s.max_elapsed_time / 1000.00 AS max_execution_time_ms,
          ((s.total_elapsed_time)/ s.execution_count)/ 1000.00 AS avg_execution_time_ms,
       
        --s.total_logical_reads,
        --s.min_logical_reads,
        --s.max_logical_reads,
         ((s.total_logical_reads * 1.00)/ s.execution_count) AS avg_logical_reads,
        
        --s.total_logical_writes,
        --s.min_logical_writes,
        --s.max_logical_writes,
        ((s.total_logical_writes * 1.00)/ s.execution_count) AS avg_logical_writes,
       
       
        --s.total_physical_reads,
        --s.min_physical_reads,
        --s.max_physical_reads,
        ((s.total_physical_reads * 1.00)/ s.execution_count) AS avg_physical_reads,
       
       s.query_hash,
        s.query_plan_hash,
                s.execution_count,
        -- Convert the DMV's ambiguous DATETIME to DATETIMEOFFSET
        TODATETIMEOFFSET (s.creation_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS creation_time,
        s.plan_generation_num,
        TODATETIMEOFFSET (s.last_execution_time, DATENAME (TZoffset, SYSDATETIMEOFFSET())) AS last_execution_time
        --s.total_worker_time / 1000 AS total_cpu_time_ms,
FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS query_text
    cross apply sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) c
    --where Object_name(query_text.objectid,query_text.DBID) = 'p_Folders_sel_All_Active_with_Hierarchy'
ORDER BY total_worker_time DESC
--ORDER By (s.total_elapsed_time/ s.execution_count) DESC

SP_INDEXINFO

http://www.karaszi.com/sqlserver/util_sp_indexinfo.asp


/*
Parameters:
@tblPat sysname = '%'
Name of table. Default is all tables in current database.

@missing_ix tinyint = 1
Whether or not to include missing indexes information. Default is "yes".

Usage examples
USE Adventureworks
EXEC sp_indexinfo
EXEC sp_indexinfo 'Product'
EXEC sp_indexinfo 'Product', 0
EXEC sp_indexinfo 'Product%'
EXEC sp_indexinfo DEFAULT, 0
EXEC pubs..sp_indexinfo


WARNING:
In order for this to be treated as a regular system procedure we need to create it in the master database and also mark it as a system procedure.

JEOB: Added information for compression and filtered index - 2008 ONLY
*/



USE master
GO
IF OBJECT_ID('sp_indexinfo') IS NOT NULL DROP PROC sp_indexinfo
GO

CREATE PROCEDURE sp_indexinfo
 @tblPat sysname = '%'
,@missing_ix tinyint = 0
AS
--Written by Tibor Karaszi 2008-07-07
--Last modified by Tibor Karaszi 2008-07-09
WITH key_columns AS
(
SELECT c.OBJECT_ID, c.name AS column_name, ic.key_ordinal, ic.is_included_column, ic.index_id, ic.is_descending_key
FROM sys.columns  AS c with (nolock)
 INNER JOIN sys.index_columns  AS ic with (nolock) ON c.OBJECT_ID = ic.OBJECT_ID AND ic.column_id = c.column_id
)
, physical_info AS
(
SELECT p.OBJECT_ID, p.index_id, ds.name AS location, SUM(p.rows) AS rows, SUM(a.total_pages) AS pages, p.data_compression_desc as Data_Compression
FROM sys.partitions  AS p with (nolock)
 INNER JOIN sys.allocation_units AS a with (nolock) ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces  AS ds with (nolock) ON a.data_space_id = ds.data_space_id
GROUP BY OBJECT_ID, index_id, ds.name, p.data_compression_desc
)
SELECT
 OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS schema_name
,OBJECT_NAME(i.OBJECT_ID) AS table_name
,i.name AS index_name
,CASE i.type WHEN 0 THEN 'heap' WHEN 1 THEN 'cl' WHEN 2 THEN 'nc' WHEN 3 THEN 'xml' ELSE CAST(i.type AS VARCHAR(2)) END AS type
,i.is_unique
,CASE
  WHEN is_primary_key = 0 AND is_unique_constraint = 0 THEN 'no'
  WHEN is_primary_key = 1 AND is_unique_constraint = 0 THEN 'PK'
  WHEN is_primary_key = 0 AND is_unique_constraint = 1 THEN 'UQ'
 END
 AS cnstr

,(SELECT
  CAST(kc.column_name + CASE kc.is_descending_key WHEN 0 THEN '' ELSE ' DESC' END AS VARCHAR(MAX)) + ', '  AS [text()]
  FROM key_columns AS kc
  WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0
  ORDER BY key_ordinal
  FOR XML PATH('')
 ) AS key_columns
,(SELECT CAST(column_name AS VARCHAR(MAX)) + ', '  AS [text()]
  FROM key_columns AS kc
  WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1
  ORDER BY key_ordinal
  FOR XML PATH('')
 ) AS included_columns
 ,i.filter_definition
,p.location
,p.rows
,p.pages
,CAST((p.pages * 8.00) / 1024 AS decimal(9,2)) AS MB
,p.Data_Compression
,s.user_seeks
,s.user_scans
,s.user_lookups
,s.user_updates
,STATS_DATE(i.object_id, i.index_id) as Statistics_last_collected
FROM sys.indexes AS i with (nolock)
INNER JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s with (nolock) ON s.OBJECT_ID = i.OBJECT_ID AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY table_name, index_name

IF @missing_ix = 1
BEGIN
SELECT
 OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
,OBJECT_NAME(d.OBJECT_ID) AS table_name
,'CREATE INDEX ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' '
 + '(' + COALESCE(d.equality_columns, '') + COALESCE(d.inequality_columns, '') + ')'
 + COALESCE(' INCLUDE(' + d.included_columns + ')', '')
 AS ddl
,s.user_seeks
,s.user_scans
,s.avg_user_impact
FROM sys.dm_db_missing_index_details  AS d with (nolock)
INNER JOIN  sys.dm_db_missing_index_groups  AS g with (nolock) ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats  AS s with (nolock) ON g.index_group_handle = s.group_handle
WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
AND d.database_id = DB_ID()
ORDER BY avg_user_impact DESC
END
GO

EXEC sp_MS_marksystemobject sp_indexinfo

-- CALL WHO IS ACTIVE

------select * from sys.traces
--MASTER..XP_readerrorlog

--SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'Physical Name',
--SERVERPROPERTY('Servername') AS 'Server Name',
--SERVERPROPERTY('MachineName') AS 'Machine Name',
--SERVERPROPERTY('Edition') AS 'Edition',
-- SERVERPROPERTY('ProductVersion') AS 'Version'


----select wait_type, count(*) waiting_tasks
----from sys.dm_os_waiting_tasks
----group by wait_type
----order by count (*) desc

exec sysadmin.dbo.usp_WhoIsActive

--~
    --Filters--Both inclusive and exclusive
    --Set either filter to '' to disable
    --Valid filter types are: session, program, database, login, and host
    --Session is a session ID, and either 0 or '' can be used to indicate "all" sessions
    --All other filter types support % or _ as wildcards
    --@filter  = '',
    --@filter_type  = 'session',
    --@not_filter  = '',
    --@not_filter_type  = 'session',

    --Retrieve data about the calling session?
    --@show_own_spid  = 0,

    --Retrieve data about system sessions?
    --@show_system_spids  = 1,

    --Controls how sleeping SPIDs are handled, based on the idea of levels of interest
    --0 does not pull any sleeping SPIDs
    --1 pulls only those sleeping SPIDs that also have an open transaction
    --2 pulls all sleeping SPIDs
    @show_sleeping_spids  = 0,

    --If 1, gets the full stored procedure or running batch, when available
    --If 0, gets only the actual statement that is currently running in the batch or procedure
    @get_full_inner_text  = 0,

    --Get associated query plans for running tasks, if available
    --If @get_plans = 1, gets the plan based on the request's statement offset
    --If @get_plans = 2, gets the entire plan based on the request's plan_handle
    @get_plans  = 1,

    --Get the associated outer ad hoc query or stored procedure call, if available
    @get_outer_command  = 1,

    --Enables pulling transaction log write info and transaction duration
    @get_transaction_info  = 1,

    --Get information on active tasks, based on three interest levels
    --Level 0 does not pull any task-related information
    --Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers
    --Level 2 pulls all available task-based metrics, including:
    --number of active tasks, current wait stats, physical I/O, context switches, and blocker information
    @get_task_info  = 1,

    --Gets associated locks for each request, aggregated in an XML format
    @get_locks  = 0,

    --Get average time for past runs of an active query
    --(based on the combination of plan handle, sql handle, and offset)
    --@get_avg_time  = 0,

    --Walk the blocking chain and count the number of
    --total SPIDs blocked all the way down by a given session
    --Also enables task_info Level 1, if @get_task_info is set to 0
    @find_block_leaders  = 1,

    --Pull deltas on various metrics
    --Interval in seconds to wait before doing the second data pull
    --@delta_interval  = 0,

    --List of desired output columns, in desired order
    --Note that the final output will be the intersection of all enabled features and all
    --columns in the list. Therefore, only columns associated with enabled features will
    --actually appear in the output. Likewise, removing columns from this list may effectively
    --disable features, even if they are turned on
    --
    --Each element in this list must be one of the valid output column names. Names must be
    --delimited by square brackets. White space, formatting, and additional characters are
    --allowed, as long as the list contains exact matches of delimited valid column names.
    --@output_column_list  = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',

    --Column(s) by which to sort output, optionally with sort directions.
        --Valid column choices:
        --session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,
        --tempdb_current, CPU, context_switches, used_memory, physical_io_delta,
        --reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,
        --tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,
        --tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,
        --percent_complete, host_name, login_name, database_name, start_time
        --
        --Note that column names in the list must be bracket-delimited. Commas and/or white
        --space are not required.
    @sort_order = '[start_time] DESC'

    --Formats some of the output columns in a more "human readable" form
    --0 disables output format
    --1 formats the output for variable-width fonts
    --2 formats the output for fixed-width fonts
    --@format_output  = 1

    --If set to a non-blank value, the script will attempt to insert into the specified
    --destination table. Please note that the script will not verify that the table exists,
    --or that it has the correct schema, before doing the insert.
    --Table can be specified in one, two, or three-part format
    --@destination_table  = '',

    --If set to 1, no data collection will happen and no result set will be returned; instead,
    --a CREATE TABLE statement will be returned via the @schema parameter, which will match
    --the schema of the result set that would be returned by using the same collection of the
    --rest of the parameters. The CREATE TABLE statement will have a placeholder token of
    -- in place of an actual table name.
    --@return_schema  = 0,
    --@schema VARCHAR(MAX) = NULL OUTPUT,

    --Help! What do I do?
    --@help  = 0