Thursday, 7 April 2016

Check Articles on a Publication Database

This needs to be run against the Publication Database:

This script came from Stack Overflow User Davmos:

http://stackoverflow.com/users/493680/davmos

SELECT 
  msp.publication AS PublicationName,
  msa.publisher_db AS DatabaseName,
  msa.article AS ArticleName,
  msa.source_owner AS SchemaName,
  msa.source_object AS TableName
FROM distribution.dbo.MSarticles msa
JOIN distribution.dbo.MSpublications msp ON msa.publication_id = msp.publication_id
ORDER BY 
  msp.publication, 
  msa.article

Friday, 4 March 2016

List users with roles on a Database



SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name]
FROM
--Role/member associations
  sys.database_role_members AS members
--Roles
JOIN sys.database_principals AS roleprinc 
ON roleprinc.[principal_id] = members.[role_principal_id]
--Role members (database users)
JOIN sys.database_principals AS membprinc 
ON membprinc.[principal_id] = members.[member_principal_id]
--Login accounts
LEFT JOIN sys.server_principals AS ulogin    
ON ulogin.[sid] = membprinc.[sid]
WHERE membprinc.[type] IN ('S','U','G')
-- No need for these system accounts
AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

Wednesday, 18 November 2015

Remove Backup History for databases no longer on the server

USE [msdb]
;
DECLARE @DBName NVARCHAR(128)
DECLARE @Delete nvarchar(max)

CREATE TABLE #DatabaseList
    (
    databasename nvarchar(128),
    deletestatus bit
    );
   
INSERT INTO #DatabaseList
SELECT DISTINCT database_name,
                0
FROM msdb.dbo.backupset
WHERE database_name NOT IN(
    SELECT name
    FROM sys.databases)
   
WHILE (SELECT COUNT(*) FROM #databaseList WHERE deleteStatus = 0) > 0
    BEGIN

        SELECT TOP 1 @DBName = databasename
        FROM #databaseList
        WHERE DeleteStatus = 0;

            SET @Delete = 'EXEC [msdb].[dbo].[sp_delete_database_backuphistory] @database_name = N''' +@DBName + ''''
   
            EXEC (@Delete)
   
            IF @@ERROR<>0 PRINT @Delete
   
        UPDATE #databaseList
        SET deletestatus = 1
        WHERE databaseName = @DBName;

    END

DROP TABLE #databaseList;

Database Deletion Taking a long time

This is caused because there are missing indexes on the backupset table within the system database msdb:

After using the Display Estimated Query Plan Wizard a few times for the system stored procedure

exec dbo.sp_delete_database_backuphistory @database_name =  N'DBName'

I found the follwoing indexes needed to be added to the backupset table:

USE [msdb]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_database_name_backup_set_id] ON [dbo].[backupset]
(
    [database_name] ASC
)
INCLUDE ( [backup_set_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_database_name_media_set_id] ON [dbo].[backupset]
(
    [database_name] ASC
)
INCLUDE ( [media_set_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_backupset_media_set_id] ON [dbo].[backupset]
(
    [media_set_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Friday, 1 May 2015

Getting Column Descriptions of a Table in MS SQL

--Run in the Context of the Database where the table resides...

DECLARE @Schema VARCHAR(50)
DECLARE @Table VARCHAR(50)

SET @Schema = 'Your Schema Here'
SET @Table = 'Your Table Here'


SELECT sch.name AS [Schema],
        st.name AS [Table],
        sept.value AS [TableDescription],
        sc.name AS [Column],
        sep.value AS [ColumnDescription],
        ty.name AS [DataType],
        sc.max_length AS [DataLength],
        sc.precision AS [DatePrecision],
        sc.scale AS [DateScale],
        sc.collation_name AS [ColumnCollation]
FROM sys.tables st
INNER JOIN sys.schemas sch ON st.schema_id = sch.schema_id
LEFT JOIN sys.extended_properties sept ON st.object_id = sept.major_id
                                        AND st.parent_object_id = sept.minor_id
                                        AND sept.name = 'MS_Description'
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id
                                        AND sc.column_id = sep.minor_id
                                        AND sep.name = 'MS_Description'
INNER JOIN sys.types ty ON sc.user_type_id = ty.user_type_id
WHERE st.name = @Table
AND sch.name = @Schema
;

Friday, 31 October 2014

Pad leading Zero's in SQL

This example would pad a 3 character string with a leading zero:

SELECT RIGHT('000'+ISNULL(field,''),3)

NULL values will show as 000

If you have an Integar Value that you want to add leading zero's then:

 
SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

Find Duplicate Fields in a table

select field1,field2,field3, count(*)
  from table_name
  group by field1,field2,field3
  having count(*) > 1

Wednesday, 30 July 2014

Create SQL Server Agent Alerts

There are a number of different resources about this. Brent Ozar is usually my first Port of Call for anything SQL Related.

http://www.brentozar.com/blitz/configure-sql-server-alerts/

and this is also quite a useful link that explains what some of the error codes mean by Robyn Page and another SQL guru:

https://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

Below is the SQL Script for Setting up alerts with severity 16-25 and SQL Server Alerts 823-825 and 832. I added the 832 alert to the script, but the main body of the script is from Brent Ozar at the first link above. When you run the script you need to do a Find and Replace on 'DBA Team' and substitute it for whichever SQL Server Agent Operator that have configured and want to be notified.



USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
@message_id=823,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
 @message_id=824,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
 @message_id=825,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'DBA Team', @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 832',
 @message_id=832,
 @severity=0,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 832', @operator_name=N'DBA Team', @notification_method = 7;
GO