re-size tempdb file counts and sizes

USE master
GO
DECLARE @BASEPATH VARCHAR(200)
DECLARE @PATH VARCHAR(200)
DECLARE @SQL_SCRIPT VARCHAR(500)
DECLARE @NUM_OF_FILES INT
DECLARE @FILECOUNT INT
DECLARE @SIZE INT
DECLARE @GROWTH INT
DECLARE @ISPERCENT INT

SELECT @NUM_OF_FILES = 12 -- Number of tempdb files you want to have.
SELECT @SIZE = 10240      -- Size in MB
SELECT @GROWTH = 10      
SELECT @ISPERCENT = 1

PRINT @NUM_OF_FILES

SET @BASEPATH = (select SUBSTRING(physical_name, 1, CHARINDEX(N'tempdb.mdf', LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 2 and FILE_ID = 1)
PRINT @BASEPATH

SET @FILECOUNT = (SELECT COUNT(*)
FROM master.sys.master_files
WHERE database_id = 2 AND TYPE_DESC = 'ROWS')

WHILE @FILECOUNT > @NUM_OF_FILES
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    REMOVE FILE tempdev' + RTRIM(CAST(@FILECOUNT as CHAR))
    EXEC(@SQL_SCRIPT)
    PRINT 'Removed ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@FILECOUNT as CHAR)) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END

WHILE @NUM_OF_FILES > @FILECOUNT
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    ADD FILE
    (
    FILENAME = ''' + @BASEPATH + 'tempdb' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'',
    NAME = tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'

    EXEC(@SQL_SCRIPT)
    PRINT 'Created ' + @BASEPATH + 'tempdev' + RTRIM(CAST(@NUM_OF_FILES as CHAR)) + '.ndf'
    SET @NUM_OF_FILES = @NUM_OF_FILES - 1
END

WHILE @FILECOUNT > 0
BEGIN
    SET @SQL_SCRIPT = 'ALTER DATABASE tempdb
    MODIFY FILE ( NAME = tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + ',
    SIZE = ' + RTRIM(CAST(@SIZE as CHAR)) + 'MB ,
    FILEGROWTH = ' + RTRIM(CAST(@GROWTH as CHAR))
    IF @ISPERCENT > 0
    SET @SQL_SCRIPT = @SQL_SCRIPT + '%'
    SET @SQL_SCRIPT = @SQL_SCRIPT + ')'
    EXEC(@SQL_SCRIPT)
    PRINT 'Modified ' + @BASEPATH + 'tempdev' + RTRIM(CASE WHEN CAST(@FILECOUNT as CHAR) = '1' THEN '' ELSE CAST(@FILECOUNT as CHAR) END) + '.ndf'
    SET @FILECOUNT = @FILECOUNT - 1
END
GO

isuniqueidentifier

CREATE FUNCTION dbo.isuniqueidentifier (@ui varchar(50))  
RETURNS bit AS  
BEGIN

RETURN case when
    substring(@ui,9,1)='-' and
    substring(@ui,14,1)='-' and
    substring(@ui,19,1)='-' and
    substring(@ui,24,1)='-' and
    len(@ui) = 36 then 1 else 0 end

END
GO

SQL DB mail

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default',
        @from_address = 'palexander@csod.com',
        @recipients= 'palexander@csod.com',
        @subject = 'test',
        @Body_Format = 'HTML',
        @body = 'test reply to me if you get this email'



use msdb
go
SELECT * FROM sysmail_allitems order by 1 desc
SELECT * FROM sysmail_faileditems order by 1 desc
SELECT * FROM sysmail_sentitems order by 1 desc


EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole' --- check members of the DatabaseMailUserRole

EXEC msdb.dbo.sysmail_help_principalprofile_sp

EXECUTE dbo.sysmail_help_status_sp ; --- check status of Database Mail

EXECUTE dbo.sysmail_start_sp ---start Database Mail in a mail host database
EXECUTE dbo.sysmail_stop_sp ---start Database Mail in a mail host database

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp
    @principal_name = 'guest',
    @profile_name = 'Default' ;
   

EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Default' ;

Tracking database recovery progress using information from DMV

This script is a modified script from the this article :


USE dbacollection
GO

DROP TABLE [dbo].[tbl_recovery_tracking]
GO

DROP TABLE [dbo].[tbl_dm_tran_database_transactions]
GO

CREATE TABLE [dbo].[tbl_recovery_tracking](
      [runtime] [datetime] NOT NULL,
      [command] [nvarchar](256) NOT NULL,
      [session_id] [smallint] NOT NULL,
      [database_id] [smallint] NOT NULL,
      [total_elapsed_time] [int] NOT NULL,
      [percent_complete] [real] NOT NULL,
      [estimated_completion_time] [bigint] NOT NULL,
      [wait_resource] [nvarchar](256) NOT NULL,
      [wait_time] [int] NOT NULL,
      [wait_type] [nvarchar](60) NULL,
      [blocking_session_id] [smallint] NULL,
      [reads] [bigint] NOT NULL,
      [writes] [bigint] NOT NULL,
      [cpu_time] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_dm_tran_database_transactions](
      [runtime] [datetime] NOT NULL,
      [transaction_id] [bigint] NOT NULL,
      [database_id] [int] NOT NULL,
      [database_transaction_log_record_count] [bigint] NOT NULL,
      [database_transaction_log_bytes_used] [bigint] NOT NULL,
      [database_transaction_log_bytes_reserved] [bigint] NOT NULL,
      [database_transaction_next_undo_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO

-- collect the information in a loop
declare @exittime datetime = dateadd(second, 60, getdate())

WHILE 1 = 1
BEGIN
      INSERT INTO [dbo].[tbl_recovery_tracking]
      SELECT GETDATE() as runtime, command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
      FROM sys.dm_exec_requests
      WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore

      INSERT INTO tbl_dm_tran_database_transactions
      SELECT GETDATE() as runtime,
      transaction_id, database_id,
      database_transaction_log_record_count, database_transaction_log_bytes_used,
      database_transaction_log_bytes_reserved, database_transaction_next_undo_lsn
      FROM sys.dm_tran_database_transactions
    
      WAITFOR DELAY '00:00:01'            -- change this capture interval
      if @exittime < getdate()
        break
       
END
GO
-- after you collect information for some time, you can analyze the information to understand the progress of recovery
SELECT runtime,   command,
      session_id, database_id, total_elapsed_time,
      percent_complete, estimated_completion_time,
      wait_resource, wait_time, wait_type, blocking_session_id,
      reads, writes, cpu_time
FROM [dbo].[tbl_recovery_tracking]
WHERE session_id = 50         -- change this
ORDER BY runtime
GO

SELECT
runtime, transaction_id, database_id,
database_transaction_log_record_count,
database_transaction_log_bytes_used, database_transaction_log_bytes_reserved,
database_transaction_next_undo_lsn
FROM tbl_dm_tran_database_transactions
WHERE database_id = 49 and transaction_id = 428607070         -- change this
ORDER BY runtime

GO

parameterized output valus with dynamic query

declare @sql nvarchar(4000), @tablename nvarchar(100), @reccount int , @param_definition nvarchar(4000) , @param_value int
set @tablename  = 'LA4-PRD-WWW103_W3SVC2_ex120917'

set @sql = 'select @reccount_out = COUNT(*) from ['+@tablename+']'
set @param_definition = N'@reccount_out int OUTPUT';
print @sql

exec sp_executesql @sql , @param_definition, @reccount_out = @reccount output;
print @reccount

Change Schema owner of objects in DB

use ces_AUTOMATION_LD4PILOT
go

declare @name nvarchar(1000), @sql nvarchar(4000)
------------------------
--rename schem onwer for users
declare tmp_cur2 cursor for
SELECT 'ALTER SCHEMA [DBO] TRANSFER [' + s.Name + '].' + o.Name
    FROM sys.Objects o
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
    WHERE s.name = 'ToBeDeleted'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
   
open tmp_cur2
Fetch next from tmp_cur2 into @sql

while @@fetch_status = 0
begin
print @sql
exec sp_executesql @sql
Fetch next from tmp_cur2 into @sql
print '---'
end

close tmp_cur2
deallocate tmp_cur2          

drop orphan users and rename current objects schema

CREATE SCHEMA [ToBeDeleted]
go


declare @name nvarchar(1000), @sql nvarchar(4000)
declare tmp_cur cursor for
SELECT     u.name --u.*--, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name


open tmp_cur
Fetch next from tmp_cur into @name

while @@fetch_status = 0
begin
    print @name
    set @sql = ''

    ------------------------
    --rename schem onwer for users
    declare tmp_cur2 cursor for
    SELECT 'ALTER SCHEMA [ToBeDeleted] TRANSFER [' + s.Name + '].' + o.Name
        FROM sys.Objects o
        INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
        WHERE s.Name = @name
        And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')
       
    open tmp_cur2
    Fetch next from tmp_cur2 into @sql

    while @@fetch_status = 0
    begin
    print @sql
    exec sp_executesql @sql
    Fetch next from tmp_cur2 into @sql
    print '---'
    end

    close tmp_cur2
    deallocate tmp_cur2   


    ------------------------
    --drop users and schema

    set @sql = 'DROP SCHEMA ['+@name+']'
    print @sql
    exec sp_executesql @sql

    set @sql = 'DROP USER ['+@name+']'
    print @sql
    exec sp_executesql @sql
    ------------------------

    Fetch next from tmp_cur into @name
    print '---'
end

close tmp_cur
deallocate tmp_cur

-------------------------------------------------------------------------

List of inactive users in a DB

SELECT     u.*, l.*
FROM    sysusers U
left outer join master..syslogins L on U.sid = L.sid
where l.sid is null and u.altuid is null and u.status <> 4
and u.name not in ('sys','guest','INFORMATION_SCHEMA')
order by u.name

In SSIS how to continue a for each loop container even if its inner tasks fails (Usage of Propogate Property in SSIS)

http://www.codeproject.com/Articles/384690/In-SSIS-how-to-continue-a-for-each-loop-container

Recompile Stuck Procs : sp_recompile_stuck_procs

USE [master]
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
    SET NOCOUNT ON;


declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr

set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress  varchar(100),Authentication  varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql   

set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql   

set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by  dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql   


---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)

set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'

insert into @tbl
exec sp_executesql @sql

declare tmp_cur cursor for
select db, sp From @tbl

open tmp_cur
Fetch next from tmp_cur into @db, @sp

while @@fetch_status = 0
begin
    --print  @db
    --print @sp
    set @sql = 'use ' +@db +'
    exec sp_recompile '+replace(@sp, 'dbo.','')

    print @sql
    --exec sp_executesql @sql


Fetch next from tmp_cur into  @db, @sp
print '---'
end

close tmp_cur
deallocate tmp_cur

-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin

DECLARE @tableHTML  NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'

SET @tableHTML =
    N'

Proc Re-compile on '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N' ' +
    CAST ( ( SELECT td = db,       '',
                    td = sp,       '',
                    td = cc
              FROM @tbl
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Database	Proc	Counts

' ;
   
    print @tableHTML
   
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'default',
            @recipients ='patrick@csod.com',
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subjectstring;
           
end

---------------------------------------

set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql   



set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql   


END