 |
| Levantamiento |
USE MASTER
GO
USE MASTER
GO
SET NOCOUNT ON -- EVITA QUE SE DEVUELVA EL NÚMERO DE FILAS AFECTADAS
/* DECLARACIÓN DE VARIABLES */
PRINT 'DECLARACIÓN DE VARIABLES'
DECLARE @CRDATE DATETIME, @HR VARCHAR(50), @MIN VARCHAR(5),@TYPEADD VARCHAR(50),@STRSTRING NVARCHAR(4000),@TEMP NVARCHAR(4000),@SALIDA NVARCHAR(4000),@LINEA VARCHAR(200)
DECLARE @ADDSALTO VARCHAR(250),@SQLVERSION VARCHAR(50)
DECLARE @VALUE_CONF CHAR(1)
DECLARE @NAME VARCHAR(255)
DECLARE @PORC INT
DECLARE @COUNT INT
DECLARE @MAYOR90 INT
DECLARE @MAYOR90STR NVARCHAR(4000)
DECLARE @MENOR50 INT
/* INICIALIZACIÓN DE VARIABLES */
PRINT 'INICIALIZACIÓN DE VARIABLES'
SET @MAYOR90 = 0
SET @MENOR50 = 0
SET @MAYOR90STR =''
SET @COUNT = 0
/* COMIENZO DEL SCRIPT */
PRINT 'COMIENZO DEL SCRIPT'
/* FECHA DE EJECUCIÓN DEL SCRIPT */
SELECT 'FECHA DE EJECUCIÓN DEL SCRIPT' = GETDATE()
/* VERSIÓN DE SQL SERVER */
SELECT @SQLVERSION = CASE SUBSTRING(CONVERT(VARCHAR(10),SERVERPROPERTY('PRODUCTVERSION')),1,2) WHEN '11' THEN '2012' WHEN '10' THEN '2008' WHEN '9.' THEN '2005' WHEN '8.' THEN '2000' ELSE '7' END
SELECT 'VERSIÓN DE LA BASE DE DATOS' = SUBSTRING(CONVERT(VARCHAR(26),@@VERSION),1,100)+' ('+CAST(SERVERPROPERTY('PRODUCTVERSION') AS NVARCHAR)+' '+CAST(SERVERPROPERTY('PRODUCTLEVEL') AS NVARCHAR)+') '+CAST(SERVERPROPERTY('EDITION') AS NVARCHAR)
/* UPTIME DEL SERVIDOR */
SELECT MAX(LOGIN_TIME) AS 'FECHA DE SUBIDA', MAX(DATEDIFF(MI,LOGIN_TIME,GETDATE()))/1440 AS 'CANTIDAD DE DÍAS',MAX(DATEDIFF(MI,LOGIN_TIME,GETDATE()))/60 AS 'HORAS', MAX(DATEDIFF(MI,LOGIN_TIME,GETDATE())) AS 'MINUTOS' FROM MASTER..SYSPROCESSES WHERE SPID=1
/* REVISIÓN DEL AGENTE SQL SERVER */
PRINT 'SQL SERVER AGENT'
IF NOT EXISTS (SELECT 1 FROM MASTER.DBO.SYSPROCESSES WHERE PROGRAM_NAME = N'SQLAGENT - GENERIC REFRESHER')
BEGIN
SELECT 'SQL SERVER AGENT' = 'SQL SERVER ESTA CORRIENDO PERO SQL SERVER AGENT NO'
END
ELSE
BEGIN
SELECT 'SQL SERVER AGENT' = 'SQL SERVER Y SQL SERVER AGENT SE ESTÁN EJECUTANDO'
END
/* PROPIEDADES DE SISTEMA OPERATIVO */
PRINT 'PROPIEDADES DE SISTEMA OPERATIVO'
EXEC XP_MSVER
/* PROPIEDADES DE LA INSTANCIA DE BASE DE DATOS */
PRINT 'PROPIEDADES DE LA INSTANCIA DE BASE DE DATOS'
SELECT 'SERVERNAME ' = LOWER(CAST(SERVERPROPERTY('SERVERNAME') AS NVARCHAR))
, 'COMPUTERNAMEPHYSICALNETBIOS' = LOWER(CAST(SERVERPROPERTY('COMPUTERNAMEPHYSICALNETBIOS') AS NVARCHAR))
, 'INSTANCENAME' = ISNULL(LOWER(CAST(SERVERPROPERTY('INSTANCENAME') AS NVARCHAR)),'SIN NOMBRE DE INSTANCIA')
, 'EDITION' = LOWER(CAST(SERVERPROPERTY('EDITION') AS NVARCHAR))
, 'EDITIONID' = LOWER(CAST(SERVERPROPERTY('EDITIONID') AS NVARCHAR))
, 'ENGINEEDITION' = LOWER(CAST(SERVERPROPERTY('ENGINEEDITION') AS NVARCHAR))
, 'PRODUCTVERSION (MAJOR.MINOR.BUILD)' = LOWER(CAST(SERVERPROPERTY('PRODUCTVERSION') AS NVARCHAR))
, 'PRODUCTLEVEL' =
CASE(CAST(SERVERPROPERTY('PRODUCTLEVEL') AS NVARCHAR))
WHEN 'RTM' THEN 'VERSION COMERCIAL ORIGINAL'
WHEN 'CTP' THEN 'VERSION COMMUNITY TECHNOLOGY PREVIEW'
ELSE CAST(SERVERPROPERTY('PRODUCTLEVEL') AS NVARCHAR)
END
, 'BUILDCLRVERSION' = LOWER(CAST(SERVERPROPERTY('BUILDCLRVERSION') AS NVARCHAR))
, 'COLLATION' = LOWER(CAST(SERVERPROPERTY('COLLATION') AS NVARCHAR))
, 'COLLATIONID' = LOWER(CAST(SERVERPROPERTY('COLLATIONID') AS NVARCHAR))
, 'COMPARISONSTYLE' = LOWER(CAST(SERVERPROPERTY('COMPARISONSTYLE') AS NVARCHAR))
, 'LCID' = LOWER(CAST(SERVERPROPERTY('LCID') AS NVARCHAR))
, 'SQLCHARSET' = LOWER(CAST(SERVERPROPERTY('SQLCHARSET') AS NVARCHAR))
, 'SQLCHARSETNAME' = LOWER(CAST(SERVERPROPERTY('SQLCHARSETNAME') AS NVARCHAR))
, 'SQLSORTORDER' = LOWER(CAST(SERVERPROPERTY('SQLSORTORDER') AS NVARCHAR))
, 'SQLSORTORDERNAME' = LOWER(CAST(SERVERPROPERTY('SQLSORTORDERNAME') AS NVARCHAR))
, 'SQLCHARSETNAME' = LOWER(CAST(SERVERPROPERTY('SQLCHARSETNAME') AS NVARCHAR))
, 'FILESTREAMSHARENAME' = LOWER(CAST(SERVERPROPERTY('FILESTREAMSHARENAME') AS NVARCHAR))
, 'FILESTREAMCONFIGUREDLEVEL' = LOWER(CAST(SERVERPROPERTY('FILESTREAMCONFIGUREDLEVEL') AS NVARCHAR))
, 'FILESTREAMEFFECTIVELEVEL' = LOWER(CAST(SERVERPROPERTY('FILESTREAMEFFECTIVELEVEL') AS NVARCHAR))
, 'ISSINGLEUSER' =
CASE(CAST(SERVERPROPERTY('ISSINGLEUSER') AS NVARCHAR))
WHEN '1' THEN 'SÍ'
WHEN '0' THEN 'NO'
ELSE 'NO SE PUEDE DETERMINAR'
END
, 'ISCLUSTERED' =
CASE(CAST(SERVERPROPERTY('ISCLUSTERED') AS NVARCHAR))
WHEN '1' THEN 'SÍ'
WHEN '0' THEN 'NO'
ELSE 'NO SE PUEDE DETERMINAR'
END
, 'LICENSETYPE' =
CASE(CAST(SERVERPROPERTY('LICENSETYPE') AS NVARCHAR))
WHEN 'PER_SEAT' THEN 'POR PUESTO'
WHEN 'PER_PROCESSOR' THEN 'POR PROCESADOR'
WHEN 'DISABLED' THEN 'DESHABILITADA'
ELSE 'NO SE PUEDE DETERMINAR'
END
, 'NUMLICENSES' + CAST(ISNULL(SERVERPROPERTY('NUMLICENSES'),'NO CONFIGURADO') AS NVARCHAR)
, 'ISFULLTEXTINSTALLED: ' =
CASE(CAST(SERVERPROPERTY('ISFULLTEXTINSTALLED') AS NVARCHAR))
WHEN '1' THEN 'ESTÁ INSTALADO'
WHEN '0' THEN 'NO ESTÁ INSTALADO'
ELSE 'NO SE PUEDE DETERMINAR'
END
, 'ISINTEGRATEDSECURITYONLY' =
CASE(CAST(SERVERPROPERTY('ISINTEGRATEDSECURITYONLY') AS NVARCHAR))
WHEN '1' THEN 'ESTÁ ACTIVADA'
WHEN '0' THEN 'ESTÁ DESACTIVADA'
ELSE 'NO SE PUEDE DETERMINAR'
END
, 'RESOURCEVERSIONBD' = LOWER(CAST(SERVERPROPERTY('RESOURCEVERSION') AS NVARCHAR))
, 'RESOURCEBDLASTUPDATEDATETIME' = LOWER(CAST(SERVERPROPERTY('RESOURCELASTUPDATEDATETIME') AS NVARCHAR))
/* PARÁMETROS DE INSTANCIA DE BASE DE DATOS */
PRINT 'PARÁMETROS DE INSTANCIA DE BASE DE DATOS'
EXEC SP_CONFIGURE 'USER OPTIONS','1'
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS','1'
RECONFIGURE WITH OVERRIDE
EXEC SP_CONFIGURE
/* LOGINS DE BASE DE DATOS */
PRINT 'LOGINS DE BASE DE DATOS'
SELECT LOGINNAME 'NOMBRE', DBNAME 'BDD PREDETERMINADA', LANGUAGE 'IDIOMA FROM SYSLOGINS',
CASE (CAST(ISNTNAME AS NVARCHAR))
WHEN '1' THEN 'SO'
WHEN '0' THEN 'SQLSERVER'
END 'TIPO DE USUARIO'
,
CASE (CAST(SYSADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN SYSADMIN'
,
CASE (CAST(SECURITYADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN SECURITYADMIN'
,
CASE (CAST(SERVERADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN SERVERADMIN'
,
CASE (CAST(SETUPADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN SETUPADMIN'
,
CASE (CAST(PROCESSADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN PROCESSADMIN'
,
CASE (CAST(DISKADMIN AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN DISKADMIN'
,
CASE (CAST(DBCREATOR AS NVARCHAR))
WHEN '1' THEN 'SÍ'
ELSE 'NO'
END 'FUNCIÓN DBCREATOR'
FROM MASTER..SYSLOGINS
WHERE CAST(SYSADMIN AS NVARCHAR) = '1'
/* ESPACIO LIBRE */
PRINT 'ESPACIO LIBRE'
EXEC MASTER..XP_FIXEDDRIVES
/* OPTIMIZACIÓN DE CONSULTAS ADHOC */
IF @SQLVERSION = '2008'
BEGIN
PRINT 'ADHOC OPTIMIZER'
SELECT OBJTYPE AS [CACHE STORE TYPE],
COUNT_BIG(*) AS [TOTAL NUM OF PLANS],
SUM(CAST(SIZE_IN_BYTES AS DECIMAL(14,2))) / 1048576 AS [TOTAL SIZE IN MB],
AVG(USECOUNTS) AS [ALL PLANS - AVE USE COUNT],
SUM(CAST((CASE WHEN USECOUNTS = 1 THEN SIZE_IN_BYTES ELSE 0 END) AS DECIMAL(14,2)))/ 1048576 AS [SIZE IN MB OF PLANS WITH A USE COUNT = 1],
SUM(CASE WHEN USECOUNTS = 1 THEN 1 ELSE 0 END) AS [NUMBER OF OF PLANS WITH A USE COUNT = 1]
FROM SYS.DM_EXEC_CACHED_PLANS
GROUP BY OBJTYPE
ORDER BY [SIZE IN MB OF PLANS WITH A USE COUNT = 1] DESC
DECLARE @ADHOCSIZEINMB DECIMAL (14,2), @TOTALSIZEINMB DECIMAL (14,2)
SELECT @ADHOCSIZEINMB = SUM(CAST((CASE WHEN USECOUNTS = 1 AND LOWER(OBJTYPE) = 'ADHOC' THEN SIZE_IN_BYTES ELSE 0 END) AS DECIMAL(14,2))) / 1048576,
@TOTALSIZEINMB = SUM (CAST (SIZE_IN_BYTES AS DECIMAL (14,2))) / 1048576
FROM SYS.DM_EXEC_CACHED_PLANS
SELECT @ADHOCSIZEINMB AS [CURRENT MEMORY OCCUPIED BY ADHOC PLANS ONLY USED ONCE (MB)],
@TOTALSIZEINMB AS [TOTAL CACHE PLAN SIZE (MB)],
CAST((@ADHOCSIZEINMB / @TOTALSIZEINMB) * 100 AS DECIMAL(14,2)) AS [% OF TOTAL CACHE PLAN OCCUPIED BY ADHOC PLANS ONLY USED ONCE]
IF @ADHOCSIZEINMB > 200 OR ((@ADHOCSIZEINMB / @TOTALSIZEINMB) * 100) > 25
SELECT 'SWITCH ON OPTIMIZE FOR AD HOC WORKLOADS AS IT WILL MAKE A SIGNIFICANT DIFFERENCE' AS [RECOMMENDATION]
ELSE
SELECT 'SETTING OPTIMIZE FOR AD HOC WORKLOADS WILL MAKE LITTLE DIFFERENCE' AS [RECOMMENDATION]
END
/* ÚLTIMOS BACKUPS */
PRINT 'ÚLTIMOS BACKUPS'
IF @SQLVERSION = '2000' OR @SQLVERSION = '7' OR @SQLVERSION = ''
BEGIN
SELECT DATABASE_NAME,B.[NAME]AS BACKUP_NAME,MAX(BACKUP_FINISH_DATE)AS ULT_BACK FROM MASTER..SYSDATABASES A INNER JOIN MSDB..BACKUPSET B ON A.NAME = B.DATABASE_NAME INNER JOIN MSDB..BACKUPMEDIAFAMILY C ON C.MEDIA_SET_ID = B.MEDIA_SET_ID GROUP BY DATABASE_NAME,B.NAME
PRINT ''
PRINT 'OPCIONES DE BASES DE DATOS'
SELECT D.DBID,D.NAME,D.FILENAME,CASE (1 & STATUS) WHEN 1 THEN 'SI' ELSE 'NO' END AS AUTO_CLOSE,CASE (16 & STATUS) WHEN 16 THEN 'SI' ELSE 'NO' END AS TORN_PAGE,CASE (4194304 & STATUS) WHEN 4194304 THEN 'SI' ELSE 'NO' END AS SHRINK FROM MASTER..SYSDATABASES D
PRINT ''
PRINT 'ARCHIVOS DE LAS BASES DE DATOS'
SELECT D.NAME,D.CMPTLEVEL,D.SID, F.NAME, F.FILENAME ,CONVERT(VARCHAR(50),F.SIZE)+' KB',CASE F.MAXSIZE WHEN '0' THEN 'FIJO' WHEN '-1' THEN 'ILIMITADO' ELSE '2TB' END AS MAXSIZE FROM SYSALTFILES F INNER JOIN SYSDATABASES D ON (F.DBID = D.DBID) INNER JOIN SYSXLOGINS U ON (D.SID = U.SID) ORDER BY 1,2
PRINT ''
PRINT 'NUMERO DE INDICES PERDIDOS POR BASE DE DATOS'
SELECT 'NO DISPONIBLE PARA ESTA VERSION DE SQLSERVER'
PRINT ''
PRINT 'INDICES SOLICITADOS VALIOSOS'
SELECT 'NO DISPONIBLE PARA ESTA VERSION DE SQLSERVER'
PRINT ''
PRINT 'INDICES MENOS USADOS'
SELECT 'NO DISPONIBLE PARA ESTA VERSION DE SQLSERVER'
PRINT ''
PRINT 'JOBS DE LA INSTANCIA'
EXEC MSDB..SP_HELP_JOB
PRINT ''
PRINT 'MONITOREO TRANSACCIÓN LOG'
DECLARE CURSOR_SP CURSOR FOR SELECT INSTANCE_NAME,CNTR_VALUE FROM SYSPERFINFO WHERE COUNTER_NAME = 'PERCENT LOG USED' AND INSTANCE_NAME != '_TOTAL'
OPEN CURSOR_SP
FETCH NEXT FROM CURSOR_SP INTO @NAME, @PORC
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COUNT = @COUNT + 1
IF(@PORC >= 90)
BEGIN
IF(@MAYOR90STR = '')
SET @MAYOR90STR = @NAME
ELSE
SET @MAYOR90STR = RTRIM(@MAYOR90STR) + ',' + @NAME
SET @MAYOR90 = @MAYOR90 + 1
END
IF(@PORC < 50)
BEGIN
SET @MENOR50 = @MENOR50 + 1
END
FETCH NEXT FROM CURSOR_SP INTO @NAME, @PORC
END
SELECT @COUNT AS NBDD,@MAYOR90 AS PORCMAYOR90 ,@COUNT - (@MAYOR90 +@MENOR50) AS PORCENTRE50Y90,@MENOR50 AS PORCMENOR50,@MAYOR90STR AS BDDMAYOR90
CLOSE CURSOR_SP
DEALLOCATE CURSOR_SP
END
ELSE
BEGIN
SELECT DATABASE_NAME,B.[NAME]AS BACKUP_NAME,MAX(BACKUP_FINISH_DATE)AS ULT_BACK FROM MASTER.SYS.DATABASES A INNER JOIN MSDB..BACKUPSET B ON A.NAME = B.DATABASE_NAME INNER JOIN MSDB..BACKUPMEDIAFAMILY C ON C.MEDIA_SET_ID = B.MEDIA_SET_ID GROUP BY DATABASE_NAME,B.NAME
PRINT ''
PRINT 'DISTRIBUCION Y TAMAÑOS DE BASES DE DATOS'
SELECT D.NAME AS NOMBRE, M.PHYSICAL_NAME AS DATAFILES, M.SIZE AS TAMANO_KB,CASE M.MAX_SIZE WHEN '0' THEN 'FIJO' WHEN '-1' THEN 'ILIMITADO' ELSE '2TB' END AS MAX_SIZE FROM SYS.MASTER_FILES M INNER JOIN SYS.DATABASES D ON M.DATABASE_ID = D.DATABASE_ID ORDER BY 1, 2
PRINT ''
PRINT 'OPCIONES DE BASES DE DATOS'
SELECT DB.DATABASE_ID AS ID, DB.NAME NOMBRE, DB.COLLATION_NAME,CASE DB.IS_AUTO_SHRINK_ON WHEN 1 THEN 'SI' ELSE 'NO' END+''+CASE DB.IS_AUTO_CLOSE_ON WHEN 1 THEN '/SI' ELSE '' END AS SHRINK, CASE DB.PAGE_VERIFY_OPTION WHEN 0 THEN 'NO' ELSE 'SI' END TORN_PAGE,CASE DB.IS_AUTO_CREATE_STATS_ON WHEN 1 THEN 'SI' ELSE 'NO' END AS CREATE_STATS,CASE DB.IS_AUTO_UPDATE_STATS_ON WHEN 1 THEN 'SI' ELSE 'NO' END AS UPDATE_STATS FROM SYS.DATABASES DB ORDER BY 2;
PRINT ''
PRINT 'ARCHIVOS DE LAS BASES DE DATOS'
SELECT D.NAME,ISNULL(U.LOGINNAME,'') AS DUENO,D.LOG_REUSE_WAIT_DESC, CASE D.LOG_REUSE_WAIT WHEN 0 THEN 'HAY ACTUALMENTE UNO O MÁS ARCHIVOS DE REGISTRO VIRTUAL REUTILIZABLES.' WHEN 1 THEN 'NO SE HA PRODUCIDO NINGÚN PUNTO DE COMPROBACIÓN DESDE EL ÚLTIMO AVISO! ÚLTIMO TRUNCAMIENTO O EL ENCABEZADO DEL REGISTRO NO SE HA MOVIDO MÁS ALLÁ DE UN ARCHIVO DE REGISTRO VIRTUAL (TODOS LOS MODELOS DE RECUPERACIÓN). ÉSTE ES UN MOTIVO HABITUAL PARA RETRASAR EL TRUNCAMIENTO.' WHEN 2 THEN 'SE NECESITA UNA COPIA DE SEGURIDAD DEL REGISTRO PARA HACER AVANZAR EL ENCABEZADO DEL REGISTRO (MODELOS DE RECUPERACIÓN COMPLETOS O REGISTRADOS DE FORMA MASIVA SÓLO). CUANDO SE COMPLETA LA COPIA DE SEGURIDAD DEL REGISTRO, SE AVANZA EL ENCABEZADO DEL REGISTRO Y ALGÚN ESPACIO DEL REGISTRO PODRÍA CONVERTIRSE EN REUTILIZABLE.' WHEN 3 THEN 'EXISTE UNA RECUPERACIÓN O COPIA DE SEGURIDAD DE DATOS EN CURSO (TODOS LOS MODELOS DE RECUPERACIÓN). LA COPIA DE SEGURIDAD DE DATOS FUNCIONA COMO UNA TRANSACCIÓN ACTIVA Y, CUANDO SE EJECUTA, LA COPIA DE SEGURIDAD IMPIDE EL TRUNCAMIENTO.' WHEN 4 THEN 'PODRÍA EXISTIR UNA TRANSACCIÓN DE LARGA DURACIÓN EN EL INICIO DE LA COPIA DE SEGURIDAD DEL REGISTRO. EN ESTE CASO, PARA LIBERAR ESPACIO SE PODRÍA REQUERIR OTRA COPIA DE SEGURIDAD DEL REGISTRO. SE DIFIERE UNA TRANSACCIÓN. UNA TRANSACCIÓN DIFERIDA ES EFECTIVAMENTE UNA TRANSACCIÓN ACTIVA CUYA REVERSIÓN SE BLOQUEA DEBIDO A ALGÚN RECURSO NO DISPONIBLE.' WHEN 5 THEN 'SE REALIZA UNA PAUSA EN LA CREACIÓN DE REFLEJO DE LA BASE DE DATOS O, EN EL MODO DE ALTO RENDIMIENTO, LA BASE DE DATOS REFLEJADA ESTÁ NOTABLEMENTE DETRÁS DE LA BASE DE DATOS DE LA ENTIDAD DE SEGURIDAD (SÓLO PARA EL MODELO DE RECUPERACIÓN COMPLETA).' WHEN 6 THEN 'DURANTE LAS REPLICACIONES TRANSACCIONALES, LAS TRANSACCIONES RELEVANTE PARA LAS PUBLICACIONES NO SE HAN ENTREGADO AÚN A LA BASE DE DATOS DE DISTRIBUCIÓN (SÓLO PARA EL MODELO DE RECUPERACIÓN COMPLETA).' WHEN 7 THEN 'SE ESTÁ CREANDO UNA INSTANTÁNEA DE BASE DE DATOS (TODOS LOS MODELOS DE RECUPERACIÓN). ÉSTE ES UN MOTIVO HABITUAL, POR LO GENERAL BREVE, PARA RETRASAR EL TRUNCAMIENTO DEL REGISTRO.' WHEN 8 THEN 'SE ESTÁ PRODUCIENDO UN RECORRIDO DEL REGISTRO (TODOS LOS MODELOS DE RECUPERACIÓN). ÉSTE ES UN MOTIVO HABITUAL, POR LO GENERAL BREVE, PARA RETRASAR EL TRUNCAMIENTO DEL REGISTRO.' WHEN 9 THEN 'NO SE UTILIZA ESTE VALOR ACTUALMENTE.' END AS LOG_REUSE_WAIT_OPT,D.COMPATIBILITY_LEVEL,D.STATE_DESC AS STATUS_DATABASE, D.RECOVERY_MODEL_DESC,D.USER_ACCESS_DESC,D.COLLATION_NAME,D.CREATE_DATE, M.NAME , M.PHYSICAL_NAME, M.STATE_DESC AS STATUS_ARCHIVE, CONVERT(VARCHAR(50),M.SIZE)+' KB',CASE M.MAX_SIZE WHEN '0' THEN 'FIJO' WHEN '-1' THEN 'ILIMITADO' ELSE '2TB' END AS MAX_SIZE FROM SYS.MASTER_FILES M INNER JOIN SYS.DATABASES D ON (M.DATABASE_ID = D.DATABASE_ID) LEFT JOIN SYS.SYSLOGINS U ON (U.SID = D.OWNER_SID) ORDER BY 1, 2
PRINT ''
PRINT 'NUMERO DE INDICES PERDIDOS POR BASE DE DATOS'
SELECT DATABASENAME = DB_NAME(DATABASE_ID),[NUMBER INDEXES MISSING] = COUNT(*) FROM SYS.DM_DB_MISSING_INDEX_DETAILS GROUP BY DB_NAME(DATABASE_ID) ORDER BY 2 DESC;
PRINT ''
PRINT 'INDICES SOLICITADOS VALIOSOS'
SELECT TOP 10 [TOTAL COST] = ROUND(AVG_TOTAL_USER_COST * AVG_USER_IMPACT * (USER_SEEKS + USER_SCANS),0) , AVG_USER_IMPACT, TABLENAME = STATEMENT, [EQUALITYUSAGE] = EQUALITY_COLUMNS, [INEQUALITYUSAGE] = INEQUALITY_COLUMNS, [INCLUDE CLOUMNS] = INCLUDED_COLUMNS FROM SYS.DM_DB_MISSING_INDEX_GROUPS G INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON D.INDEX_HANDLE = G.INDEX_HANDLE ORDER BY [TOTAL COST] DESC;
PRINT ''
PRINT 'INDICES MENOS USADOS'
SELECT TOP 1 DATABASENAME = DB_NAME(),TABLENAME = OBJECT_NAME(S.[OBJECT_ID]),INDEXNAME = I.NAME ,USER_UPDATES ,SYSTEM_UPDATES
INTO #TEMPUNUSEDINDEXES FROM SYS.DM_DB_INDEX_USAGE_STATS S
INNER JOIN SYS.INDEXES I ON S.[OBJECT_ID] = I.[OBJECT_ID]
AND S.INDEX_ID = I.INDEX_ID
WHERE S.DATABASE_ID = DB_ID()
AND OBJECTPROPERTY(S.[OBJECT_ID], 'ISMSSHIPPED') = 0
AND USER_SEEKS = 0
AND USER_SCANS = 0
AND USER_LOOKUPS = 0
AND S.[OBJECT_ID] = -999;
EXEC SP_MSFOREACHDB 'USE [?];
-- TABLE ALREADY EXISTS.
INSERT INTO #TEMPUNUSEDINDEXES
SELECT TOP 10
DATABASENAME = DB_NAME()
,TABLENAME = OBJECT_NAME(S.[OBJECT_ID])
,INDEXNAME = I.NAME
,USER_UPDATES
,SYSTEM_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS S
INNER JOIN SYS.INDEXES I ON S.[OBJECT_ID] = I.[OBJECT_ID]
AND S.INDEX_ID = I.INDEX_ID
WHERE S.DATABASE_ID = DB_ID()
AND OBJECTPROPERTY(S.[OBJECT_ID], ''ISMSSHIPPED'') = 0
AND USER_SEEKS = 0
AND USER_SCANS = 0
AND USER_LOOKUPS = 0
AND I.NAME IS NOT NULL -- IGNORE HEAP INDEXES.
ORDER BY USER_UPDATES DESC;'
SELECT TOP 10 * FROM #TEMPUNUSEDINDEXES ORDER BY [USER_UPDATES] DESC;
DROP TABLE #TEMPUNUSEDINDEXES
/* JOBS DE LA INSTANCIA */
PRINT 'JOBS DE LA INSTANCIA'
EXEC MSDB..SP_HELP_JOB
/*SELECT @VALUE_CONF=CONVERT(CHAR(1),VALUE_IN_USE) FROM SYS.CONFIGURATIONS WHERE NAME='AD HOC DISTRIBUTED QUERIES' IF(@VALUE_CONF='0') BEGIN EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1 RECONFIGURE EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 1 SELECT ORIGINATING_SERVER,NAME,ENABLED,DESCRIPTION,START_STEP_ID,CATEGORY,OWNER,NOTIFY_LEVEL_EMAIL,NOTIFY_EMAIL_OPERATOR,NOTIFY_NETSEND_OPERATOR,NOTIFY_PAGE_OPERATOR,DELETE_LEVEL,DATE_CREATED,DATE_MODIFIED,LAST_RUN_DATE,LAST_RUN_TIME,LAST_RUN_OUTCOME,NEXT_RUN_DATE,NEXT_RUN_TIME FROM OPENROWSET ('SQLOLEDB','SERVER=(LOCAL);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC MSDB.DBO.SP_HELP_JOB') EXEC SP_CONFIGURE 'AD HOC DISTRIBUTED QUERIES', 0 RECONFIGURE ENDELSE BEGIN SELECT ORIGINATING_SERVER,NAME,ENABLED,DESCRIPTION,START_STEP_ID,CATEGORY,OWNER,NOTIFY_LEVEL_EMAIL,NOTIFY_EMAIL_OPERATOR,NOTIFY_NETSEND_OPERATOR,NOTIFY_PAGE_OPERATOR,DELETE_LEVEL,DATE_CREATED,DATE_MODIFIED,LAST_RUN_DATE,LAST_RUN_TIME,LAST_RUN_OUTCOME,NEXT_RUN_DATE,NEXT_RUN_TIME FROM OPENROWSET ('SQLOLEDB','SERVER=(LOCAL);TRUSTED_CONNECTION=YES;','SET FMTONLY OFF EXEC MSDB.DBO.SP_HELP_JOB') END*/
/* CAUSAS DE ESPERA */
PRINT 'CAUSAS DE ESPERAS'
EXEC('SELECT TOP 10
[WAIT TYPE] = WAIT_TYPE,
[WAIT TIME (S)] = WAIT_TIME_MS / 1000,
[% WAITING] = CONVERT(DECIMAL(12,2), WAIT_TIME_MS * 100.0
/ SUM(WAIT_TIME_MS) OVER())
FROM SYS.DM_OS_WAIT_STATS
WHERE WAIT_TYPE NOT LIKE ''%SLEEP%''
ORDER BY WAIT_TIME_MS DESC;')
/* MONITOREO DE TRANSACTION LOG */
PRINT 'MONITOREO TRANSACCIÓN LOG'
EXEC('DECLARE CURSOR_SP CURSOR FOR SELECT INSTANCE_NAME,CNTR_VALUE FROM SYS.DM_OS_PERFORMANCE_COUNTERS WHERE COUNTER_NAME = ''PERCENT LOG USED'' AND INSTANCE_NAME != ''_TOTAL''')
OPEN CURSOR_SP
FETCH NEXT FROM CURSOR_SP INTO @NAME, @PORC
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COUNT = @COUNT + 1
IF(@PORC >= 90)
BEGIN
IF(@MAYOR90STR = '')
SET @MAYOR90STR = @NAME
ELSE
SET @MAYOR90STR = RTRIM(@MAYOR90STR) + ',' + @NAME
SET @MAYOR90 = @MAYOR90 + 1
END
IF(@PORC < 50)
BEGIN
SET @MENOR50 = @MENOR50 + 1
END
FETCH NEXT FROM CURSOR_SP INTO @NAME, @PORC
END
SELECT @COUNT AS NBDD,@MAYOR90 AS PORCMAYOR90 ,@COUNT - (@MAYOR90 +@MENOR50) AS PORCENTRE50Y90,@MENOR50 AS PORCMENOR50,@MAYOR90STR AS BDDMAYOR90
CLOSE CURSOR_SP
DEALLOCATE CURSOR_SP
END
/* MUESTRA LAS QUERYS QUE ESTÁN CORRIENDO ACTUALMENTE */
IF @SQLVERSION = '2005' OR @SQLVERSION = '2008' OR @SQLVERSION = '2012'
BEGIN
SELECT
ER.SESSION_ID "SESSIONID" ,
DB_NAME(ER.DATABASE_ID) "DATABASENAME",
SP.LOGIN_NAME "LOGINNAME",
SP.NT_USER_NAME "NTUSERNAME",
ER.COMMAND "COMMAND",
ER.START_TIME "STARTTIME",
DATEDIFF(SECOND, ER.START_TIME, GETDATE()) "DURATION",
ER.STATUS "STATUS",
ER.WAIT_TYPE "WAITTYPE",
CASE
WHEN ER.BLOCKING_SESSION_ID = -2
THEN 'ORPHANED DISTRIBUTED TRANSACTION'
WHEN ER.BLOCKING_SESSION_ID = -3
THEN 'DEFERRED RECOVERY TRANSACTION'
WHEN ER.BLOCKING_SESSION_ID = -4
THEN 'UNKNOWN'
ELSE
CAST(ER.BLOCKING_SESSION_ID AS VARCHAR(5))
END "BLOCKINGSESSIONID",
SUBSTRING(QT.TEXT, ER.STATEMENT_START_OFFSET/2,
(CASE WHEN ER.STATEMENT_END_OFFSET = -1
THEN LEN(CONVERT(NVARCHAR(MAX), QT.TEXT)) * 2
ELSE ER.STATEMENT_END_OFFSET END
- ER.STATEMENT_START_OFFSET)/2) "SQL",
QT.TEXT "BATCH",
SP.PROGRAM_NAME "PROGRAMNAME",
SP.HOST_NAME "HOSTNAME",
SP.NT_DOMAIN "NTDOMAIN"
FROM
SYS.DM_EXEC_REQUESTS ER WITH (NOLOCK)
INNER JOIN
SYS.DM_EXEC_SESSIONS SP WITH (NOLOCK)
ON ER.SESSION_ID = SP.SESSION_ID
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(ER.SQL_HANDLE) AS QT
WHERE
ER.SESSION_ID > 50
AND
ER.SESSION_ID != @@SPID
ORDER BY 1, 2;
END
/* MUESTRA LOS PRINCIPALES CONSUMIDORES DE IO */
IF @SQLVERSION = '2005' OR @SQLVERSION = '2008' OR @SQLVERSION = '2012'
BEGIN
SELECT TOP 50
(TOTAL_LOGICAL_READS + TOTAL_LOGICAL_WRITES) AS "TOTALLOGICALIO",
(TOTAL_LOGICAL_READS/EXECUTION_COUNT) AS "AVGLOGICALREADS",
(TOTAL_LOGICAL_WRITES/EXECUTION_COUNT) AS "AVGLOGICALWRITES",
(TOTAL_PHYSICAL_READS/EXECUTION_COUNT) AS "AVGPHYSICALREADS",
SUBSTRING(ST.TEXT, (QS.STATEMENT_START_OFFSET/2) + 1,
((CASE QS.STATEMENT_END_OFFSET
WHEN -1 THEN DATALENGTH(ST.TEXT)
ELSE QS.STATEMENT_END_OFFSET
END - QS.STATEMENT_START_OFFSET)/2) + 1) AS "STATEMENTTEXT",
*
FROM
SYS.DM_EXEC_QUERY_STATS AS QS
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS ST
ORDER BY
TOTALLOGICALIO DESC;
END
/* RESTABLECER PARÁMETROS INICIALES */
PRINT 'RESTABLECER PARÁMETROS INICIALES'
EXEC SP_CONFIGURE 'USER OPTIONS','0'
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS','0'
RECONFIGURE WITH OVERRIDE
/* FIN DEL SCRIPT*/
PRINT 'FIN DEL SCRIPT'
Comments
Post a Comment