SQL Server - Database Backups for all databases

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 ()-2) 

ORDER BY 

   --msdb.dbo.backupset.database_name,

   msdb.dbo.backupset.backup_finish_date desc

Procedure BCP or SQLCMD to Queryout

sqlcmd -S SQL311I01\I01 -h-1 -s, -W -w 65000 -Q "set nocount on; EXEC [DBName].dbo.ProcedureName;" -r1 -m-1 2>E:\Backup\ReportError.txt 1>E:\Backup\Reporter.CSV

sqlcmd -S SQL311I01\I01 -h-1 -s"|" -W -w 65000 -Q "set nocount on; EXEC [DBName].dbo.ProcedureName;" -r1 -m-1 2>E:\Backup\ReportError.txt 1>E:\Backup\Reporter.CSV

Partition Table Filegroup Data Records / Number of Rows

SELECT

      SCHEMA_NAME(t.schema_id) AS SchemaName

     ,OBJECT_NAME(i.object_id) AS ObjectName

     ,p.partition_number AS PartitionNumber

     ,fg.name AS Filegroup_Name

     ,rows AS 'Rows'

     ,au.total_pages AS 'TotalDataPages'

     ,CASE boundary_value_on_right

        WHEN 1 THEN 'less than'

        ELSE 'less than or equal to'

     END AS 'Comparison'

    ,value AS 'ComparisonValue'

    ,p.data_compression_desc AS 'DataCompression'

    ,p.partition_id

FROM sys.partitions p

    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

    JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

    JOIN sys.partition_functions f ON f.function_id = ps.function_id

    LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id

    JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number

    JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

    JOIN (SELECT container_id, sum(total_pages) as total_pages

            FROM sys.allocation_units

            GROUP BY container_id) AS au ON au.container_id = p.partition_id 

    JOIN sys.tables t ON p.object_id = t.object_id

WHERE i.index_id < 2

ORDER BY ObjectName,p.partition_number;

Table Size and Rows

declare @t table (

name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)

)

declare @name nvarchar(100)

declare tt cursor for

Select  QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Name] from 

sys.objects AS sOBJ

 INNER JOIN sys.partitions AS sPTN

 ON sOBJ.object_id = sPTN.object_id WHERE

 sOBJ.type = 'U'AND sOBJ.is_ms_shipped = 0x0

 AND index_id < 2 -- 0:Heap, 1:Clustered

 GROUP BY sOBJ.schema_id, sOBJ.name

---Select name from sys.tables sOBJ

open tt

fetch next from tt into @name

while @@FETCH_STATUS = 0

begin

  insert into @t

  exec sp_spaceused @name

  fetch next from tt into @name

end

close tt

deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size

from (select name,

[rows],

cast (LEFT(data, LEN(data)-3) as int) data,

cast (LEFT(index_size, LEN(index_size)-3) as int) [index]

 from @t

) x

order by 3 desc, 1


OR

SELECT SCH.name AS SchemaName

      ,OBJ.name AS ObjName

      ,OBJ.type_desc AS ObjType

      ,INDX.name AS IndexName

      ,INDX.type_desc AS IndexType

      ,PART.partition_number AS PartitionNumber

      ,PART.rows AS PartitionRows

      ,STAT.row_count AS StatRowCount

      ,STAT.used_page_count * 8 AS UsedSizeKB

      ,STAT.reserved_page_count * 8 AS RevervedSizeKB

FROM sys.partitions AS PART

     INNER JOIN sys.dm_db_partition_stats AS STAT

         ON PART.partition_id = STAT.partition_id

            AND PART.partition_number = STAT.partition_number

     INNER JOIN sys.objects AS OBJ

         ON STAT.object_id = OBJ.object_id

     INNER JOIN sys.schemas AS SCH

         ON OBJ.schema_id = SCH.schema_id

     INNER JOIN sys.indexes AS INDX

         ON STAT.object_id = INDX.object_id

            AND STAT.index_id = INDX.index_id

ORDER BY SCH.name

        ,OBJ.name

        ,INDX.name

        ,PART.partition_number

Table No Primary Key Findings

SELECT DB_name() as DBName,SCHEMA_NAME(schema_id) AS SchemaName

,[Name]

FROM [sys].[objects]

WHERE [Type_Desc] = 'USER_TABLE' AND

      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0

ORDER BY [Name]


Row count for Table which does not have primary key:

--Create Procedure P_PrimaryKeyNotExist

--as

--Begin

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

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

SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName],

SCHEMA_NAME(sOBJ.schema_id) AS SchemaName,

sOBJ.name AS Name

, SUM(sPTN.Rows) AS [RowCount] into #b FROM

sys.objects AS sOBJ

 INNER JOIN sys.partitions AS sPTN

 ON sOBJ.object_id = sPTN.object_id WHERE

 sOBJ.type = 'U'AND sOBJ.is_ms_shipped = 0x0

 AND index_id < 2 -- 0:Heap, 1:Clustered

 GROUP BY sOBJ.schema_id, sOBJ.name

 ORDER BY [TableName];

 SELECT DB_name() as DBname,SCHEMA_NAME(schema_id) AS SchemaName,[Name] into #a

FROM [sys].[objects]

WHERE [Type_Desc] = 'USER_TABLE' AND

      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0

      ORDER BY [Name];

select a.DBname,b.[TableName] as FullTableName,a.SchemaName,a.name,b.[RowCount]

from #a a inner join #b b on a.name =b.Name and a.SchemaName = b.SchemaName Order by b.[RowCount] desc;

--End

Remove Replication

-- Dropping the transactional subscriptions

use [DBA]

exec sp_dropsubscription @publication = N'ReplicationPublicationName', @subscriber = N'KCRCSTVSQL03', @destination_db = N'DestinationDB', @article = N'all'

GO

 

-- Dropping the transactional articles

use [DBA]

exec sp_dropsubscription @publication = N'ReplicationPublicationName', @article = N'Action', @subscriber = N'all', @destination_db = N'all'

GO

 

-- Dropping the transactional publication

use [DBA]

exec sp_droppublication @publication = N'ReplicationPublicationName'

GO

 

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

SET @publicationDB = N'DBA';

SET @publication = N'ReplicationPublicationName';

 

-- Remove a transactional publication.

USE COMMON

EXEC sp_droppublication @publication = @publication;

 

-- Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

  @dbname = @publicationDB,

  @optname = N'publish',

  @value = N'false';

GO

Long Running Queries Along with Kill Statement of SPID

drop table #a

go

SELECT

      HOST_NAME               AS [System Name],

      program_name                  AS [Application Name],

      DB_NAME(database_id)    AS [DATABASE Name],

      USER_NAME(USER_ID)      AS [USER Name],

      connection_id                 AS [CONNECTION ID],

      sys.dm_exec_requests.session_id AS [CURRENT SESSION ID],

      blocking_session_id           AS [Blocking SESSION ID],

      start_time              AS [Request START TIME],

      sys.dm_exec_requests.status   AS [Status],

      command AS [Command Type],

      (SELECT TEXT FROM sys.dm_exec_sql_text(sql_handle)) AS [Query TEXT],

      wait_type               AS [Waiting Type],

      wait_time               AS [Waiting Duration],

      wait_resource                 AS [Waiting FOR Resource],

      sys.dm_exec_requests.transaction_id AS [TRANSACTION ID],

      percent_complete        AS [PERCENT Completed],

      estimated_completion_time     AS [Estimated COMPLETION TIME (in mili sec)],

      sys.dm_exec_requests.cpu_time       AS [CPU TIME used (in mili sec)],

      (memory_usage * 8)      AS [Memory USAGE (in KB)],

      sys.dm_exec_requests.total_elapsed_time AS [Elapsed TIME (in mili sec)] into #a

FROM sys.dm_exec_requests

            INNER join

      sys.dm_exec_sessions

            ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

WHERE DB_NAME(database_id) = 'DBNAME'

GO

select 'kill ' + cast([CURRENT SESSION ID] as varchar) as Statement, * from #a where [Query TEXT] like '%procedurename%'

go

Schema Creation Dynamically

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Test')

DROP SCHEMA [Test]

GO

SELECT * FROM sys.schemas WHERE name = N'Test'
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Test')

DECLARE @sql NVARCHAR(MAX), @dbname sysname
SET @dbname = 'dba'
SET @sql = 'exec '+ QUOTENAME(@dbname) + '..sp_executesql N''CREATE SCHEMA Test'''

EXEC (@sql)
SELECT (@sql)

GO

SELECT * FROM sys.schemas WHERE name = N'Test'

Monitor TempDB through SQL Server Agent Using Scripts

USE [DBA]  -- Database to collect monitoring / counter information

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--This table is to store information from the SQL Server Agent below Job 

CREATE TABLE [dbo].[T_TempDBMonitor](

      [System Name] [nvarchar](128) NULL,

      [Application Name] [nvarchar](128) NULL,

      [Database Name] [nvarchar](128) NULL,

      [User Name] [nvarchar](128) NULL,

      [Connection ID] [uniqueidentifier] NULL,

      [Current Session ID] [smallint] NOT NULL,

      [Blocking Session ID] [smallint] NULL,

      [Request Start Time] [datetime] NOT NULL,

      [Status] [nvarchar](30) NOT NULL,

      [Command Type] [nvarchar](16) NOT NULL,

      [Query Text] [nvarchar](max) NULL,

      [Waiting Type] [nvarchar](60) NULL,

      [Waiting Duration] [int] NOT NULL,

      [Waiting for Resource] [nvarchar](256) NOT NULL,

      [Transaction ID] [bigint] NOT NULL,

      [Percent Completed] [real] NOT NULL,

      [Estimated Completion Time (in mili sec)] [bigint] NOT NULL,

      [CPU time used (in mili sec)] [int] NOT NULL,

      [Memory Usage (in KB)] [int] NULL,

      [Elapsed Time (in mili sec)] [int] NOT NULL,

      [TempDB Size] [xml] NULL,

      [ExecutedAT] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[KE Perf Testing]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[KE Perf Testing]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Montor-TempDB',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'No description available.',

            @category_name=N'[KE Perf Testing]',

            @owner_login_name=NDomain\Username', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TEMPDB',

            @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'SET NOCOUNT ON;

select COUNT(*) as Counts

from sys.dm_exec_requests

            inner join

      sys.dm_exec_sessions

            on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

where DB_NAME(database_id) = ''tempdb'' and sys.dm_exec_requests.session_id >50

if @@ROWCOUNT = 0

return

declare @script nvarchar(max)

set @script = ''SELECT

      [Name] as   [Logical_Name],

      [size_on_disk_bytes] / 1024 /1024   as    [Total_Size_MB],

      [num_of_bytes_written] / 1024/1024 as    [Space_Used_MB]

FROM sys.dm_io_virtual_file_stats(2, NULL)

            inner join

      sys.sysfiles

            on sys.dm_io_virtual_file_stats.file_id = sys.sysfiles.fileid

            ORDER BY [Name]

for xml raw''

declare @ReportResultXml table (RXml XML)

declare @ResultXml xml

declare @sql nvarchar(max)

set @sql = ''set @ResultXml = ('' + @script + '')''

execute sp_ExecuteSQL @sql, N''@ResultXml xml output'', @ResultXml output

--select @ResultXml;

INSERT INTO [DBA].[dbo].[T_TempDBMonitor]

           ([System Name]

           ,[Application Name]

           ,[Database Name]

           ,[User Name]

           ,[Connection ID]

           ,[Current Session ID]

           ,[Blocking Session ID]

           ,[Request Start Time]

           ,[Status]

           ,[Command Type]

           ,[Query Text]

           ,[Waiting Type]

           ,[Waiting Duration]

           ,[Waiting for Resource]

           ,[Transaction ID]

           ,[Percent Completed]

           ,[Estimated Completion Time (in mili sec)]

           ,[CPU time used (in mili sec)]

           ,[Memory Usage (in KB)]

           ,[Elapsed Time (in mili sec)]

           ,[TempDB Size]

           ,[ExecutedAT])

select

      host_name               as [System Name],

      program_name                  as [Application Name],

      DB_NAME(database_id)          as [Database Name],

      User_Name(user_id)                  as [User Name],

      connection_id                 as [Connection ID],

      sys.dm_exec_requests.session_id as [Current Session ID],

      blocking_session_id           as [Blocking Session ID],

      start_time              as [Request Start Time],

      sys.dm_exec_requests.status   as [Status],

      command                         as [Command Type],

      (select text from sys.dm_exec_sql_text(sql_handle)) AS [Query Text],

      wait_type               as [Waiting Type],

      wait_time               as [Waiting Duration],

      wait_resource                 as [Waiting for Resource],

      sys.dm_exec_requests.transaction_id as [Transaction ID],

      percent_complete        as [Percent Completed],

      estimated_completion_time     as [Estimated Completion Time (in mili sec)],

      sys.dm_exec_requests.cpu_time       as [CPU time used (in mili sec)],

      (memory_usage * 8)                  as [Memory Usage (in KB)],

      sys.dm_exec_requests.total_elapsed_time as [Elapsed Time (in mili sec)] ,

      @ResultXml,

      GETDATE()

from sys.dm_exec_requests

            inner join

      sys.dm_exec_sessions

            on sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id

where DB_NAME(database_id) = ''tempdb'' and sys.dm_exec_requests.session_id >50

SET NOCOUNT OFF;',

            @database_name=N'tempdb',

            @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_jobschedule @job_id=@jobId, @name=N'TempDB',

            @enabled=1,

            @freq_type=4,

            @freq_interval=1,

            @freq_subday_type=4,

            @freq_subday_interval=3,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20140827,

            @active_end_date=99991231,

            @active_start_time=0,

            @active_end_time=235959,

            @schedule_uid=N'224d01bf-fc46-4e1c-bc27-90a0b4ac7ef1'

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