SQL Server DB file Size

http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/

Truncate Mirrored Database Log File

http://sqlserverimpressions.blogspot.com/2009/02/truncate-mirrored-database-log-file.html

Table Partitioning on a varchar field

exec sp_who3
exec sp_whoisactive

drop partition scheme CoreCollectionPartitionScheme
drop PARTITION FUNCTION [SwimlaneRangePartitionFunction]


CREATE PARTITION FUNCTION [SwimlaneRangePartitionFunction] (nvarchar(100))
AS RANGE RIGHT FOR VALUES ('LA4PRDCLT101', 'LA4PRDCLT201', 'LA4PRDCLT301',
               'LA4PRDCLT401', 'LD4PRDCLT101', 'LD4PRDCLT102', 'LD4PRDCLT201');
              
CREATE PARTITION SCHEME CoreCollectionPartitionScheme
AS PARTITION SwimlaneRangePartitionFunction
TO ([primary],sl1,sl2,sl3,sl4,sl5,sl51,sl6);
GO              


drop table [user_demographics_partition_by_swimlane]
go
CREATE TABLE [dbo].[user_demographics_partition_by_swimlane](
    [user_id] [int] NULL,
    [swimlane] [nvarchar](100) NULL,
    [portal] [nvarchar](200) NULL,
    [status_id] [int] NULL,
    [user_type_id] [int] NULL,
    [gender] [varchar](100) NULL,
    [user_country] [varchar](100) NULL,
    [user_state] [nvarchar](50) NULL,
    [user_zipcode] [nvarchar](50) NULL,
    [birth_dt] [datetime] NULL,
    [hire_dt] [datetime] NULL,
    [term_dt] [datetime] NULL,
    [timezone] [int] NULL,
    [date_stamp] [datetime] NULL,
    [user_country_profile_base] [varchar](100) NULL,
    [user_state_profile_base] [nvarchar](50) NULL,
    [user_zipcode_profile_base] [nvarchar](50) NULL
) ON CoreCollectionPartitionScheme ([swimlane])

GO

Database last restore status report

use master
go
drop procedure sp_db_restore_status_report
go

create procedure sp_db_restore_status_report
as
begin

/*
select *
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'
order by a.name

SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
    drop table #tmp
   
Create table #tmp (db nvarchar(1000), restore_Date datetime)

insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'

--select * From #tmp

if(OBJECT_ID('tempdb..#tmp_total')>1)
    drop table #tmp_total
   
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
)  as restored_older_24_hours
into #tmp_total

--select * From #tmp_total

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
    N'

Database Restore Report from '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N'' +
    N' ' +
    CAST ( ( SELECT td = total_dbs,       '',
                    td = restored_last_2_hours,       '',
                    td = restored_last_8_hours, '',
                    td = restored_last_24_hours, '',
                    td = restored_older_24_hours
              FROM #tmp_total
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Total Databases	Restored in last 2 hours	Restored in last 8 hours	Restored in last 24 hours	Restored over 24 hours

' ;
   
print @tableHTML
   
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @subject = @subjectstring;
   
end   

sp_delete_files

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_delete_files]    Script Date: 01/09/2013 07:48:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
exec sp_delete_files @folder = 'B:\SQL_Performance\TraceCollection\TraceFiles\'
    ,@ext  = 'trc'
    ,@minutesgoback  = -60
*/

ALTER proc [dbo].[sp_delete_files]
(
    @folder varchar(100)
    ,@ext varchar(10)
    ,@minutesgoback int = -6000
)
as
begin

    set nocount on;
    declare @run int = 1 , @reccount int = 0
    declare @sql nvarchar(4000), @filename varchar(100), @tablename varchar(100), @param_definition nvarchar(4000), @folderlogs varchar(100)

    --set @folderlogs = @folder +'\logs\'
    set @folderlogs = @folder
   

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

    set @sql = 'DIR ' + '"' + @folderlogs + '\*.'+@ext+'"'
    --print @sql

    if(OBJECT_ID('tempdb..#tmp_tbl')>0)
        drop table #tmp_tbl
       
    CREATE TABLE #tmp_tbl (Name varchar(400), Work int IDENTITY(1,1))

    INSERT #tmp_tbl EXECUTE master.dbo.xp_cmdshell @sql

    DELETE #tmp_tbl WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING(Name,40,1) = '.'
    --select convert(datetime,SUBSTRING(Name,1,20)),SUBSTRING(Name,40,100),dateadd(MINUTE,@minutesgoback,GETDATE()), *
    --From #tmp_tbl
    --where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

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


    declare tmp_cur cursor for
    SELECT SUBSTRING(Name,40,100) AS Files FROM #tmp_tbl where convert(datetime,SUBSTRING(Name,1,20)) < dateadd(MINUTE,@minutesgoback,GETDATE())

    open tmp_cur
    Fetch next from tmp_cur into @filename

    while @@fetch_status = 0
    begin
    print @filename
    set @tablename = REPLACE(@filename,'.'+@ext,'')
    print 'deleteing '+@tablename


    set @filename = ltrim(RTRIM(@filename)) 

    set @sql = 'del '+ @folder+ @filename
    if @run = 0
        print @sql 
    if @run = 1
        EXECUTE master.dbo.xp_cmdshell @sql
     
     


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

    close tmp_cur
    deallocate tmp_cur

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


end

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