/*
Versao: 0.5
Data:    20110523
Por:    Ricardo Leka
Site:    http://leka.com.br
email:    ricardo@leka.com.br
*/
/*
Versao do SQL
Traz informacoes interessante sobre o nome da maquina, se tem instancia, versao de produto,
SP, collation da instancia, se esta ou nao em cluster
*/

Print ‘Versao de SQL’

SELECT
CAST( SERVERPROPERTY( ‘MachineName’ ) AS varchar( 30 ) ) AS MachineName ,
CAST( SERVERPROPERTY( ‘InstanceName’ ) AS varchar( 30 ) ) AS Instance ,
CAST( SERVERPROPERTY( ‘ProductVersion’ ) AS varchar( 30 ) ) AS ProductVersion ,
CAST( SERVERPROPERTY( ‘ProductLevel’ ) AS varchar( 30 ) ) AS ProductLevel ,
CAST( SERVERPROPERTY( ‘Edition’ ) AS varchar( 30 ) ) AS Edition ,
( CASE SERVERPROPERTY( ‘EngineEdition’)
WHEN 1 THEN ‘Personal or Desktop’
WHEN 2 THEN ‘Standard’
WHEN 3 THEN ‘Enterprise’
END ) AS EngineType ,
CAST( SERVERPROPERTY( ‘LicenseType’ ) AS varchar( 30 ) ) AS LicenseType ,
SERVERPROPERTY( ‘NumLicenses’ ) AS #Licenses,
sERVERPROPERTY(‘Collation’) AS Collation,
( CASE SERVERPROPERTY( ‘IsClustered’)
WHEN 0 THEN ‘Nao’
WHEN 1 THEN ‘Sim’
END ) AS Cluster;

SELECT @@VERSION AS [SQL Server Details];

/*
Pega algumas informacoes interessantes como
nome, owner, data de criação, dbid, Modo de Compatiblidade das bases, recovery model,  versao e status

ATENCAO: Lembre-se que a ideia do script eh de apenas ver o que tem,
caso encontre alguma base com status diferente de online ele nao faz nada
e na teoria ainda nao eh a hora de voce fazer. Apenas reporte.
*/

Print ‘Nome, owner, dbcreate, dbid, cmptlvl, recovery, version, status’

select    name as [NAME]
        ,suser_sname(sid) as [Owner]
        ,convert(nvarchar(11), crdate) as [Data Criacao]
        ,dbid as [DBID]
        ,cmptlevel
        ,databasepropertyex(name, ‘recovery’)
        ,version
        ,databasepropertyex(name, ‘status’)
from master.dbo.sysdatabases

/*
Backup FULL

Uma das informacoes mais importantes, tem backup?
*/

Print ‘Backup FULL’

SELECT d.name, MAX(b.backup_finish_date) AS last_backup_finish_date
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘D’
WHERE d.database_id NOT IN (2, 3)
GROUP BY d.name
ORDER BY 2 DESC

/*
Backup LOG

Outra informacao muito importante,,,,
*/

Print ‘Backup Log’

SELECT d.name, d.recovery_model, d.recovery_model_desc
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = ‘L’
WHERE d.recovery_model IN (1, 2) AND b.type IS NULL AND d.database_id NOT IN (2, 3)

/*
Localizacao dos backups

Se houve backup, pra onde ele foi?
*/

Print ‘Localizacao dos Backups’

SELECT TOP 100 physical_drive, physical_name, *
FROM msdb.dbo.backupfile
ORDER BY 1 DESC

/*
A quanto tempo existe historico de backup?

No SQL 2k, havia um grande problema de performance quando o historico ficava muito grande.
*/

Print ‘Backup History’

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC

/*
Quando foi que o DBCC CHECKDB rodou pela última vez?

Isso eh tao bom quando retorna alguma coisa,,,
*/

Print ‘DBCC CHECKDB’

CREATE TABLE #temp (         
       ParentObject     VARCHAR(255)
       , [Object]       VARCHAR(255)
       , Field          VARCHAR(255)
       , [Value]        VARCHAR(255)  
   )  
  
CREATE TABLE #DBCCResults (
        ServerName           VARCHAR(255)
        , DBName             VARCHAR(255)
        , LastCleanDBCCDate  DATETIME  
    )  
   
EXEC master.dbo.sp_MSforeachdb      
           @command1 = ‘USE ? INSERT INTO #temp EXECUTE (”DBCC DBINFO WITH TABLERESULTS”)’
           , @command2 = ‘INSERT INTO #DBCCResults SELECT @@SERVERNAME, ”?”, Value FROM #temp WHERE Field = ”dbi_dbccLastKnownGood”’
           , @command3 = ‘TRUNCATE TABLE #temp’  
  
   –Delete duplicates due to a bug in SQL Server 2008
  
   ;WITH DBCC_CTE AS
   (
       SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID
       FROM #DBCCResults
   )
   DELETE FROM DBCC_CTE WHERE RowID > 1;
  
    SELECT       
           ServerName      
           , DBName      
           , CASE LastCleanDBCCDate
                   WHEN ’1900-01-01 00:00:00.000′ THEN ‘Never ran DBCC CHECKDB’
                   ELSE CAST(LastCleanDBCCDate AS VARCHAR) END AS LastCleanDBCCDate   
   FROM #DBCCResults  
   ORDER BY 3
  
   DROP TABLE #temp, #DBCCResults;

/*
Quem faz parte do sysadmin ou securityadmin

Quantas outras pessoas podem acessar o SQL e fazer algum tipo de estrago?
*/

SELECT    l.name
        ,l.denylogin
        ,l.isntname
        ,l.isntgroup
        ,l.isntuser
FROM master.dbo.syslogins l
WHERE l.sysadmin = 1 OR l.securityadmin = 1
ORDER BY l.isntgroup, l.isntname, l.isntuser

/*
Configurações da instância

Isso mostra como esta configurado hoje, e nao como estava configurado…
*/
EXEC dbo.sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC dbo.sp_configure

/*
Email

O SQL envia email?
*/

Print ‘Email’

EXEC msdb.dbo.sp_send_dbmail
    @recipients = ‘ricardo@leka.com.br’,
    @body = @@SERVERNAME,
    @subject = ‘Testando SQL Server Database Mail – veja no corpo o nome do servidor’;
GO

/*
Configurando o sistema de email para enviar mensagem de erros

Aqui vai alguma configuracao,,, nada critica,,,
*/

Print ‘Configurando o sistema de emails’

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N’The Database Administrator’,
        @enabled=1,
        @weekday_pager_start_time=0,
        @weekday_pager_end_time=235959,
        @saturday_pager_start_time=0,
        @saturday_pager_end_time=235959,
        @sunday_pager_start_time=0,
        @sunday_pager_end_time=235959,
        @pager_days=127,
        @email_address=N’suporte@asd.com’,
        @pager_address=N’0119999999@operadora.com.br’,
        @category_name=N’[Uncategorized]‘
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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @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’The Database Administrator’, @notification_method = 7
GO

/*
Objetos em bases de sistema

Coisas que nao deveriam estar neste lugar
*/

Print ‘Coisas no lugar errado’

SELECT * FROM master.sys.tables WHERE name NOT IN (‘spt_fallback_db’, ‘spt_fallback_dev’, ‘spt_fallback_usg’, ‘spt_monitor’, ‘spt_values’, ‘MSreplication_options’)
SELECT * FROM master.sys.procedures WHERE name NOT IN (‘sp_MSrepl_startup’, ‘sp_MScleanupmergepublisher’)
SELECT * FROM model.sys.tables
SELECT * FROM model.sys.procedures

/*
Start up Stored procedure

Achou alguma procedure aqui? estranho,,, veja o que ela faz,,,
pode ser o mau esperando um boot,,,
*/

SELECT *
FROM master.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),’ExecIsStartup’) = 1

/*
Quem mais existe em server-level
*/

SELECT * FROM sysservers

/*
Agora mais informacoes sobre as bases

classico,,, nada fora do comum,,,
*/

SELECT * FROM sysdatabases

/*
Onde estao os arquivos

onde eles estao? quantos sao?
*/

CREATE TABLE #DatabaseFiles(
    [database_name] [sysname] NOT NULL,
    [file_id] [int] NOT NULL,
    [file_guid] [smallint] NULL,
    [size] [int] NOT NULL,
    [max_size] [int] NOT NULL,
    [growth] [int] NOT NULL,
    [status] [int] NULL,
    [perf] [int] NOT NULL,
    [name] [sysname] NOT NULL,
    [physical_name] [nvarchar](260) NOT NULL,
)
EXEC dbo.sp_MSforeachdb ‘INSERT INTO #DatabaseFiles SELECT ”[?]” AS database_name, * FROM [?].dbo.sysfiles’
SELECT * FROM #DatabaseFiles ORDER BY database_name
DROP TABLE #DatabaseFiles

/*
Triggers

isso pode demorar um pouco,,,
e mais ainda pra ler,,,,
*/

Print ‘Triggers’

EXEC dbo.sp_MSforeachdb ‘SELECT ”[?]” AS database_name, o.name AS table_name, t.* FROM [?].sys.triggers t INNER JOIN [?].sys.objects o ON t.parent_id = o.object_id’

/*
Alguns Waits

Nao zere o contador, apenas veja o que tem,,,
*/

Print ‘Alguns Waits’

SELECT *, (wait_time_ms – signal_wait_time_ms) AS real_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY (wait_time_ms – signal_wait_time_ms) DESC

/*
Frangmentacao de indices

se voce achou que o de Triggers demorou,,, imagina esse,,,,
*/

Print ‘Fragmentacao de indices’

SELECT
      db.name AS databaseName
    , ps.OBJECT_ID AS objectID
    , ps.index_id AS indexID
    , ps.partition_number AS partitionNumber
    , ps.avg_fragmentation_in_percent AS fragmentation
    , ps.page_count
FROM sys.databases db
  INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N’Limited’) ps
      ON db.database_id = ps.database_id
WHERE ps.index_id > 0
   AND ps.page_count > 100
   AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1);

/*
Jobs

Simples,,, quais sao, a quem pertence e se estao ativos,,,
NAO tente corrigir-los agora,,, entenda o que eles fazem ou deveriam fazer,,,
*/

Print ‘Jobs’

select     Name as [Name]
    ,SUSER_SNAME(owner_sid) as [Owner]
    ,(    Case enabled
        WHEN 0 THEN ‘Nao’
        WHEN 1 THEN ‘Sim’
        END
    ) AS [Enable]
    , description as [Description]
from msdb.dbo.sysjobs_view
order by name

/*
Users

Quais sao os usuarios de Windows e os de SQL
Preste atencao quais sao os usuarios, podem tem mais do que precisa,,,,
*/

Print ‘Usuarios windows e SQL’

select     Name as [Name]
    ,(    Case isntname
        WHEN 0 THEN ‘SQL Server Standard’
        WHEN 1 THEN ‘Windows Authentication’
        END
    ) AS [Type]
from master.sys.syslogins

/*
Usuarios x Bases

Esse aqui nao deve demorar muito,,,
apenas gera um relacionamento dos usuarios que estao nas bases
compare com a quantidade de usuarios que voce pegou na lista acima
*/

Print ‘Usuarios por Bases’

create table #UsrDataMapping(
[database_name] [sysname] NULL,
[name] [sysname] NULL,
[schema] [sysname] NULL
)
EXEC sp_MSforeachdb ‘insert into #UsrDataMapping SELECT ”?” as DBNAME,
–u.name AS [Name],
SUSER_SNAME(sid) AS [Name],
ISNULL(u.default_schema_name,N””) AS [DefaultSchema]
FROM
[?].sys.database_principals AS u
LEFT OUTER JOIN [?].sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id
WHERE
(u.type in (”U”, ”S”, ”G”, ”C”, ”K”))
and dp.state = ”G”
‘
select * from #UsrDataMapping order by database_name
drop table #UsrDataMapping