|
/*
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
|
Ainda bem que existe esse tipo de gente,,, é ainda mais legal quando alguém me liga no meio da madrugada com aquela voz desesperada pedindo ajuda porque o servidor do banco (que era o mesmo do AD, Exchange, FileServer, DHCP) foi pro espaço e eles não estão conseguindo mais recuperar as coisas…
A pior coisa que pode acontecer em um momento de crise é o pânico. Mantenha a calma, com certeza vão ter pessoas desesperadas correndo que nem baratas tontas tentando qualquer coisa que lembrar ou encontrar na internet.
