scripts-10 Tips ‘n’ Tricks for DBAs

El día 8 de Julio de 2017 participamos en el evento SQL Tips ‘n’ Tricks, organizado por Pass Lima. En esta ocación presentamos 10 Tips ‘n’ Tricks for DBAs, en el que mostramos recomendaciones en base a las mejores prácticas de SQL Server, explicando la razón de por qué implementarlas y los scripts necesarios para hacerlo. Aquí compartimos tanto la presentación como los scripts utilizados:

1.Controlar del espacio disponible.

--Obtener información de tamaño y espacio libre de los archivos
SELECT 
  DB_NAME()		AS BaseDatos,
  file_id, 
  type_desc					AS TipoArchivo, 
  name						AS NombreArchivo, 
  physical_name				AS ArchivoFisico, 
  --size, 
  size/128.0	AS TamañoMB, 
  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS EspacioLibreMB, 
  ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/(size/128.0))*100 AS PctLibre
FROM sys.database_files;
GO

--Obtener información de tamaño y espacio libre de los archivos

SELECT

DB_NAME() AS BaseDatos,

file_id,

type_desc AS TipoArchivo,

name AS NombreArchivo,

physical_name AS ArchivoFisico,

--size,

size/128.0 AS TamañoMB,

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS EspacioLibreMB,

((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)/(size/128.0))*100 AS PctLibre

FROM sys.database_files;

2.Configurar del espacio de crecimiento automático.

--Obtener información de autocrecimiento de los archivos
SELECT 
  DB_NAME()		AS BaseDatos,
  file_id, 
  type_desc					AS TipoArchivo, 
  name						AS NombreArchivo, 
  physical_name				AS ArchivoFisico, 
  size/128.0	AS TamañoMB, 
  CASE is_percent_growth
    WHEN 1 THEN RTRIM(CAST(growth AS VARCHAR(30)))+'%'
    ELSE CAST(ROUND((growth*8.)/1024.00,0) AS VARCHAR(30))
  END CrecimientoMB 
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO

--Obtener sentencias de configuración de autocrecimiento, en base a 1/4 del tamaño actual de los archivos
SELECT CONCAT('ALTER DATABASE ', DB_NAME(),
               ' MODIFY FILE ( NAME = ''',name,''',',
			   ' FILEGROWTH = ', CAST((size/128)/4 AS varchar(100)),'MB);')
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO

--Obtener información de autocrecimiento de los archivos

SELECT

DB_NAME() AS BaseDatos,

file_id,

type_desc AS TipoArchivo,

name AS NombreArchivo,

physical_name AS ArchivoFisico,

size/128.0 AS TamañoMB,

CASE is_percent_growth

WHEN 1 THEN RTRIM(CAST(growth AS VARCHAR(30)))+'%'

ELSE CAST(ROUND((growth*8.)/1024.00,0) AS VARCHAR(30))

END CrecimientoMB

FROM sys.database_files

WHERE type_desc = 'ROWS';

--Obtener sentencias de configuración de autocrecimiento, en base a 1/4 del tamaño actual de los archivos

SELECT CONCAT('ALTER DATABASE ', DB_NAME(),

' MODIFY FILE ( NAME = ''',name,''',',

' FILEGROWTH = ', CAST((size/128)/4 AS varchar(100)),'MB);')

FROM sys.database_files

WHERE type_desc = 'ROWS';

3.No activar la característica de auto-encogido.

--Identificar configuración de autoencogido
SELECT 
   name AS BaseDatos, 
   CASE is_auto_shrink_on 
	   WHEN 1 THEN '******* Prendido - FATAL *******'
	   ELSE 'Apagado - OK'
   END AutoEncogidoON,
   CASE is_auto_shrink_on 
	   WHEN 1 THEN CONCAT('ALTER DATABASE ',name,' SET AUTO_SHRINK OFF WITH NO_WAIT;')
	   ELSE NULL
   END ScriptCorreccion
FROM sys.databases;
GO

--Identificar configuración de autoencogido

SELECT

name AS BaseDatos,

CASE is_auto_shrink_on

WHEN 1 THEN '******* Prendido - FATAL *******'

ELSE 'Apagado - OK'

END AutoEncogidoON,

CASE is_auto_shrink_on

WHEN 1 THEN CONCAT('ALTER DATABASE ',name,' SET AUTO_SHRINK OFF WITH NO_WAIT;')

ELSE NULL

END ScriptCorreccion

FROM sys.databases;

4.Ubicar los archivos en arreglos físicos separados.

--Identifica Ubicación de archivos
SELECT 
  DB_NAME()		AS BaseDatos,
  file_id, 
  type_desc					AS TipoArchivo, 
  name						AS NombreArchivo, 
  LEFT(physical_name,2)		AS Unidad, 
  physical_name				AS ArchivoFisico
FROM sys.database_files
GO

--Cambia la ubicación de los archivos
USE master;
GO
ALTER DATABASE TestFiles SET OFFLINE; 
GO
ALTER DATABASE TestFiles MODIFY FILE ( NAME = TestFiles, FILENAME = 'D:\SQLData\SQL130\TestFiles.mdf' );
GO
ALTER DATABASE TestFiles SET ONLINE;
GO

--Identifica Ubicación de archivos

SELECT

DB_NAME() AS BaseDatos,

file_id,

type_desc AS TipoArchivo,

name AS NombreArchivo,

LEFT(physical_name,2) AS Unidad,

physical_name AS ArchivoFisico

FROM sys.database_files

--Cambia la ubicación de los archivos

USE master;

ALTER DATABASE TestFiles SET OFFLINE;

ALTER DATABASE TestFiles MODIFY FILE ( NAME = TestFiles, FILENAME = 'D:\SQLData\SQL130\TestFiles.mdf' );

ALTER DATABASE TestFiles SET ONLINE;

5.Distribuir la base de datos tempdb en varios archivos de datos.

--Identificamos configuración actual
USE tempdb
SELECT * from sys.database_files

--Determinamos en numero de CPUs
DECLARE 
   @Contador tinyint,			--Control del loop
   @ConteoCPU tinyint,			--Cantidad de CPUS para el numero de archivos. Máximo 8
   @TamañoArchivoMB smallint,	--Tamaño de los nuevos archivos
   @Query nvarchar(1000),		--Variable para armar la consulta de creación de los archivos
   @Ruta varchar(1000)			--Ruta de ubicación del archivo

--Obtenemos la cantidad de CPUS, aprovechamos el query para definir demás variables
SELECT @Contador =1, @ConteoCPU=cpu_count,@TamañoArchivoMB=10 
FROM sys.dm_os_sys_info

--Obtenemos la ruta actual de los archivos de datos
SELECT TOP 1 @Ruta=LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
FROM sys.database_files
WHERE Type=0

WHILE @Contador<=@ConteoCPU-1 AND @Contador<=8
BEGIN
  SET @Query = CONCAT('ALTER DATABASE tempdb ADD FILE (NAME = ''tempdev',CAST(@Contador+1 AS char(1)),'''',
					  ', SIZE = ', CAST(@TamañoArchivoMB AS varchar(10)),
					  ', FILENAME = ''',@Ruta,'tempdev',CAST(@Contador+1 AS char(1)),'.ndf''',
					  ')')
  PRINT @Query
  EXEC sys.sp_executesql @command1 = @Query
  SET @Contador += 1

END

--Identificamos configuración actual

USE tempdb

SELECT * from sys.database_files

--Determinamos en numero de CPUs

DECLARE

@Contador tinyint, --Control del loop

@ConteoCPU tinyint, --Cantidad de CPUS para el numero de archivos. Máximo 8

@TamañoArchivoMB smallint, --Tamaño de los nuevos archivos

@Query nvarchar(1000), --Variable para armar la consulta de creación de los archivos

@Ruta varchar(1000) --Ruta de ubicación del archivo

--Obtenemos la cantidad de CPUS, aprovechamos el query para definir demás variables

SELECT @Contador =1, @ConteoCPU=cpu_count,@TamañoArchivoMB=10

FROM sys.dm_os_sys_info

--Obtenemos la ruta actual de los archivos de datos

SELECT TOP 1 @Ruta=LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)

FROM sys.database_files

WHERE Type=0

WHILE @Contador<=@ConteoCPU-1 AND @Contador<=8

BEGIN

SET @Query = CONCAT('ALTER DATABASE tempdb ADD FILE (NAME = ''tempdev',CAST(@Contador+1 AS char(1)),'''',

', SIZE = ', CAST(@TamañoArchivoMB AS varchar(10)),

', FILENAME = ''',@Ruta,'tempdev',CAST(@Contador+1 AS char(1)),'.ndf''',

')')

PRINT @Query

EXEC sys.sp_executesql @command1 = @Query

SET @Contador += 1

END

6.Verificar los trabajos (JOBS) programados.

--Obtener detalle de los Trabajos (Jobs)
SELECT 
 j.name AS Job, j.description AS Descripcion, 
 CASE 
  WHEN j.enabled = 1 THEN 'Habilitado'
  ELSE 'Deshabilitado'
 END Estado,   
 CASE 
  WHEN ss.freq_type = 1 THEN 'Una vez'
  WHEN ss.freq_type = 4 THEN 'Diario'
  WHEN ss.freq_type = 8 THEN 'Semanal'
  WHEN ss.freq_type = 16 THEN 'Mensual'
  WHEN ss.freq_type = 32 THEN 'Relativo mensual'
  WHEN ss.freq_type = 64 THEN 'Cuando se inicia el Agente SQL Server'
 END Frecuencia,
 CASE 
 WHEN next_run_date = 0 THEN 
   CONVERT(SMALLDATETIME, CONVERT(char(10), GETDATE() + 1, 20) + 
   ' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) +
   ':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) + 
   ':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2))
 WHEN active_start_time = 0 THEN 
   CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) + 
   SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) + 
   RIGHT(CAST(next_run_date AS char(8)), 2) +
   ' 00:00:00')
 WHEN active_start_time <> 0 THEN 
   CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) + 
   SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) + 
   RIGHT(CAST(next_run_date AS char(8)), 2) +
   ' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) +
   ':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) + 
   ':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2))
 END SiguienteEjecucion,
 jh.run_date,
 STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'Duración',
 CASE jh.run_status
  WHEN 0 THEN 'Fallo'
  WHEN 1 THEN 'Éxito'
  WHEN 2 THEN 'Reintento'
  WHEN 3 THEN 'Cancelado'
 END Resultado
from msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobschedules s 
  ON j.job_id = s.job_id
 INNER JOIN msdb.dbo.sysschedules ss
  ON s.schedule_id=ss.schedule_id 
 INNER JOIN msdb.dbo.sysjobhistory jh
  ON j.job_id=jh.job_id AND jh.step_id=0;
GO

--Obtener detalle de los Trabajos (Jobs)

SELECT

j.name AS Job, j.description AS Descripcion,

CASE

WHEN j.enabled = 1 THEN 'Habilitado'

ELSE 'Deshabilitado'

END Estado,

CASE

WHEN ss.freq_type = 1 THEN 'Una vez'

WHEN ss.freq_type = 4 THEN 'Diario'

WHEN ss.freq_type = 8 THEN 'Semanal'

WHEN ss.freq_type = 16 THEN 'Mensual'

WHEN ss.freq_type = 32 THEN 'Relativo mensual'

WHEN ss.freq_type = 64 THEN 'Cuando se inicia el Agente SQL Server'

END Frecuencia,

CASE

WHEN next_run_date = 0 THEN

CONVERT(SMALLDATETIME, CONVERT(char(10), GETDATE() + 1, 20) +

' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) +

':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) +

':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2))

WHEN active_start_time = 0 THEN

CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) +

SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) +

RIGHT(CAST(next_run_date AS char(8)), 2) +

' 00:00:00')

WHEN active_start_time <> 0 THEN

CONVERT(SMALLDATETIME, LEFT(CAST(next_run_date AS char(8)), 4) +

SUBSTRING(CAST(next_run_date AS char(8)), 5, 2) +

RIGHT(CAST(next_run_date AS char(8)), 2) +

' ' + LEFT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2) +

':' + SUBSTRING(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 3, 2) +

':' + RIGHT(RIGHT(RTRIM('00' + CAST(active_start_time AS char(6))), 6), 2))

END SiguienteEjecucion,

jh.run_date,

STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'Duración',

CASE jh.run_status

WHEN 0 THEN 'Fallo'

WHEN 1 THEN 'Éxito'

WHEN 2 THEN 'Reintento'

WHEN 3 THEN 'Cancelado'

END Resultado

from msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobschedules s

ON j.job_id = s.job_id

INNER JOIN msdb.dbo.sysschedules ss

ON s.schedule_id=ss.schedule_id

INNER JOIN msdb.dbo.sysjobhistory jh

ON j.job_id=jh.job_id AND jh.step_id=0;

7.Confirmar las copias de seguridad.

USE msdb;
GO

--Historial de copias de seguridad
SELECT DISTINCT
   DB_ID(s.database_name) AS IDBaseDatos, s.database_name AS BaseDatos,  
   s.backup_start_date AS Inicio, s.backup_finish_date AS Fin, 
   CAST(s.backup_finish_date-s.backup_start_date AS TIME) AS Tiempo,
   CASE s.type   
	 WHEN 'D' THEN 'D - Full'
     WHEN 'I' THEN 'I - Diferencial'
     WHEN 'L' THEN 'L - Log'
     WHEN 'F' THEN 'F - Archivo/Grupo archivo'
     WHEN 'G' THEN 'G - Archivo diferencial'
     WHEN 'P' THEN 'P - Parcial'
     WHEN 'Q' THEN 'Q - Parcial diferencial'
   END AS Tipo, 
   CASE s.is_copy_only
	   WHEN 1 THEN 'Si'
	   ELSE 'No'
   END SoloCopia,
   s.backup_size/1024.0/1024.0/1024.0 AS TamañoGB,  
   ISNULL(mf.logical_device_name,'') AS DispositivoLogico,  
   mf.physical_device_name AS ArchivoFísico, 
   LEFT(mf.physical_device_name,2) AS Unidad,   
   s.name AS BackupSet, 
   ISNULL(s.description,'') AS Descripción
FROM   msdb.dbo.backupmediafamily  mf
   INNER JOIN msdb.dbo.backupset s
     ON mf.media_set_id = s.media_set_id  
WHERE  (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - 180)  
ORDER BY  
   s.database_name, 
   s.backup_finish_date;
GO

--Backup más reciente por base de datos
SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, 
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY msdb.dbo.backupset.database_name  
ORDER BY msdb.dbo.backupset.database_name;
GO

USE msdb;

--Historial de copias de seguridad

SELECT DISTINCT

DB_ID(s.database_name) AS IDBaseDatos, s.database_name AS BaseDatos,

s.backup_start_date AS Inicio, s.backup_finish_date AS Fin,

CAST(s.backup_finish_date-s.backup_start_date AS TIME) AS Tiempo,

CASE s.type

WHEN 'D' THEN 'D - Full'

WHEN 'I' THEN 'I - Diferencial'

WHEN 'L' THEN 'L - Log'

WHEN 'F' THEN 'F - Archivo/Grupo archivo'

WHEN 'G' THEN 'G - Archivo diferencial'

WHEN 'P' THEN 'P - Parcial'

WHEN 'Q' THEN 'Q - Parcial diferencial'

END AS Tipo,

CASE s.is_copy_only

WHEN 1 THEN 'Si'

ELSE 'No'

END SoloCopia,

s.backup_size/1024.0/1024.0/1024.0 AS TamañoGB,

ISNULL(mf.logical_device_name,'') AS DispositivoLogico,

mf.physical_device_name AS ArchivoFísico,

LEFT(mf.physical_device_name,2) AS Unidad,

s.name AS BackupSet,

ISNULL(s.description,'') AS Descripción

FROM msdb.dbo.backupmediafamily mf

INNER JOIN msdb.dbo.backupset s

ON mf.media_set_id = s.media_set_id

WHERE (CONVERT(datetime, s.backup_start_date, 102) >= GETDATE() - 180)

ORDER BY

s.database_name,

s.backup_finish_date;

--Backup más reciente por base de datos

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,

DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

GROUP BY msdb.dbo.backupset.database_name

ORDER BY msdb.dbo.backupset.database_name;

8.Asignar Password fuerte a la cuenta SA.

--Modificar el password del SA
ALTER LOGIN SA
WITH PASSWORD = '1mTheStr0ngerDBAOnE@rt2017';
GO

--Modificar el password del SA

ALTER LOGIN SA

WITH PASSWORD = '1mTheStr0ngerDBAOnE@rt2017';

9.Crear usuarios con privilegios mínimos indispensables.

--Creamos un inicio de sesión con un password fuerte
CREATE LOGIN AdminApp
WITH PASSWORD = 'Cl@veSuperSegura2017';
GO

USE WideWorldImporters;
GO

--Creamos un usuario en la base de datos
CREATE USER AdminApp FOR LOGIN AdminApp;
GO

--Añadimos el usuario a los roles de lectura y escritura
ALTER ROLE db_datareader ADD MEMBER AdminApp;
GO
ALTER ROLE db_datawriter ADD MEMBER AdminApp;
GO

--Creamos un rol para ejecución de código
CREATE ROLE dbu_execute AUTHORIZATION dbo;
GO
GRANT EXECUTE TO dbu_execute;
GO

--Añadimos al usuario al rol de ejecución de código
ALTER ROLE dbu_execute ADD MEMBER AdminApp;
GO

--Creamos un inicio de sesión con un password fuerte

CREATE LOGIN AdminApp

WITH PASSWORD = 'Cl@veSuperSegura2017';

USE WideWorldImporters;

--Creamos un usuario en la base de datos

CREATE USER AdminApp FOR LOGIN AdminApp;

--Añadimos el usuario a los roles de lectura y escritura

ALTER ROLE db_datareader ADD MEMBER AdminApp;

ALTER ROLE db_datawriter ADD MEMBER AdminApp;

--Creamos un rol para ejecución de código

CREATE ROLE dbu_execute AUTHORIZATION dbo;

GRANT EXECUTE TO dbu_execute;

--Añadimos al usuario al rol de ejecución de código

ALTER ROLE dbu_execute ADD MEMBER AdminApp;

10.Ejecutar DBCC CHECKDB.

DBCC CHECKDB('CorruptDB') WITH NO_INFOMSGS;
GO

--Cambiar la BD a usuario único
ALTER DATABASE CorruptDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

--Corrección permitiendo perdida de datos
DBCC CHECKDB('CorruptDB', REPAIR_ALLOW_DATA_LOSS);
GO

--Cambiar la BD a usuario multiple
ALTER DATABASE CorruptDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB('CorruptDB') WITH NO_INFOMSGS;

--Cambiar la BD a usuario único

ALTER DATABASE CorruptDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--Corrección permitiendo perdida de datos

DBCC CHECKDB('CorruptDB', REPAIR_ALLOW_DATA_LOSS);

--Cambiar la BD a usuario multiple

ALTER DATABASE CorruptDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Comparte Este artículo:

Deja una respuesta Cancelar la respuesta