Listado de la etiqueta: mssql

Contar días sin Sábado ni Domingo

Como contar cuántos días hay en un período sin contar sábados y domingo

La siguiente es una  función en TSQL de MS SQL Server, la cúal permite saber la cantidad de días que existe en un período determinado sin contar los fines de semana (sábado y domingo).

--Primeramente declaramos que vamos a crear una funcion, en este caso se llama Dif Dias y recibe dos parámetros, la fecha inicial del período y la final*/ 
CREATE FUNCTION [dbo].[DifDias](@StartDate DATETIME,@EndDate DATETIME) 
 RETURNS integer 
 AS 
 Begin 

		DECLARE @DaysBetween INT  --Con esta variable calculamos cuantos dias "normales" hay en el rango de fechas 
		DECLARE @BusinessDays INT  --Con esta variable acumulamos los dias totales 
		DECLARE @Cnt INT --esta variable nos sirve de contador para saber cuando lleguemos al ultimo dia del rango 
		DECLARE @EvalDate DATETIME   --esta variable es la que comparamos para saber si el dia que esta calculando es sábado o domingo*

		/*Esta par de variables sirven para comparar las dos fechas, si son iguales, la funcion nos regresa un 0*/ 
		DECLARE @ini VARCHAR(10) 
		DECLARE @fin VARCHAR(10) 

		--Inicializamos algunas variables 

		 SET @DaysBetween = 0 
		 SET @BusinessDays = 0 
		 SET @Cnt=0 

		--Calculamos cuantos dias normales hay en el rango de fechas  
		SELECT @DaysBetween = DATEDIFF(DAY,@StartDate,@EndDate) + 1 

		/*Ordenamos el formato de las fechas para que no importando como se proporcionen se comparen igual*/ 

		SELECT @ini = (SELECT CAST((CAST(datepart(dd,@StartDate)AS 
						VARCHAR(2))+'/'+ CAST(datepart(mm,@StartDate)AS 
						VARCHAR(2))+'/'+CAST(datepart(yy,@StartDate)AS VARCHAR(4))) as 
						varchar(10))) 
		SELECT @fin = (SELECT CAST((CAST(datepart(dd,@EndDate)AS 
						VARCHAR(2))+'/'+ CAST(datepart(mm,@EndDate)AS VARCHAR(2))+'/'+ 
						CAST(datepart(yy,@EndDate)AS VARCHAR(4)))as varchar(10))) 


		--Se comparan las dos fechas 

		IF @ini <>@fin 
		 BEGIN 

		/*Si la diferencia de fechas es igual a dos, es porque solo ha transcurrido un dia, asi que solo se valida que no vaya a marcar dias de mas*/ 

		IF @DaysBetween = 2 
		 BEGIN 
		 SELECT @BusinessDays = 1 
		 END 
		 ELSE 
		 BEGIN 
		 WHILE @Cnt < @DaysBetween 
		 BEGIN 

		/*Se Iguala la fecha a que vamos a calcular para saber si es sabado o domingo en la variable @EvalDate sumandole los dias que marque el contador, el cual no debe ser mayor que el numero total de dias que hay en el rango de fechas*/ 

		SELECT @EvalDate = @StartDate + @Cnt 

		/*Utilizando la funcion datepart con el parametro dw que calcula que dia de la semana corresponde una fecha determinada, determinados que no sea sabado (7) o domingo (1)*/ 

		IF ((datepart(dw,@EvalDate) <> 1) and 
		 (datepart(dw,@EvalDate) <> 7) ) 
		 BEGIN 

		/*Si no es sabado o domingo, entonces se suma uno al total de dias que queremos desplegar*/ 

		SELECT @BusinessDays = @BusinessDays + 1 
		 END 

		--Se suma un dia mas al contador 

		SELECT @Cnt = @Cnt + 1 
		 END 
		 END 
		 END 
		 ELSE 
		 BEGIN 

		--Si fuese cierto que las fechas eran iguales se despliegue cero 

		SELECT @BusinessDays = 0 
		 END 
		 
		 
		 
		--Al finalizar el ciclo, la funcion regresa el numero total de dias 

		return (@BusinessDays) 
		 END

GO

Ejemplo de Uso:

SELECT  dbo.DifDias('06/01/2015', '06/30/2015')  as DiasAbril

contar dias-sin-sabado-ni-domingo

Truncar Fecha en SQL Server

Porque trabajas todo el día con un motor MS SQL Server, y te toca cambiar los formatos de visualización de las fechas, aquí te dejo una ayuda memoria:

De la siguiente manera es posible quitar las horas y los segundos a una fecha.

select getdate()

-- La manera correcta (desde SQL Server 2008)
select cast(getdate() As Date)

-- La manera correcta(anterior a SQL Server 2008)
select dateadd(dd, datediff(dd,0, getDate()), 0)

truncar-fecha

Reparar una base dañada de MS SQL Server

Últimamente me ha tocado quemarme las pestañas por casos de bases de datos dañadas. Una mala infraestructura, y/o un mal plan de backups termina provocando este tipo de post luego de sufrir por un rato…

Reparar una base dañada

El siguiente es un error común cuando se daña una base:

Msg 824, Level 24, State 2, Line 11
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:49876; actual 17253:-991032068). It occurred during a read of page (1:49876) in database ID 15 at offset 0x000000185a8000 in file ‘E:\Bases\SBDAXXXX.MDF’.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

A continuación los pasos que se debería seguir:

  1. Intentar hacer un backup de la base
  2. Obtener la cantidad de registros que hay en ese momento en cada tabla (script adjunto al final)
  3. Colocar la base en modo de usuario único
    ALTER DATABASE SBDAXXXX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

     

  4. Intentar reparar la base
    DBCC CHECKDB(SBDAXXXX,REPAIR_ALLOW_DATA_LOSS)

     

  5. Verificar la cantidad de registros actual (script adjunto al final)
DECLARE @Tabla VARCHAR(250)
DECLARE @SQL VARCHAR(2000)
CREATE TABLE #TablaTemp(ID INT IDENTITY(1,1),Tabla_Nombre VARCHAR(200),Cant_Registros INT)

DECLARE TINFO_CUR CURSOR FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'

OPEN TINFO_CUR
FETCH NEXT FROM TINFO_CUR INTO @Tabla
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL='INSERT INTO #TablaTemp(Tabla_Nombre,Cant_Registros) SELECT '''+@Tabla+''',COUNT(*) FROM '+@Tabla+''
EXECUTE (@SQL)
FETCH NEXT FROM TINFO_CUR INTO @Tabla
END
CLOSE TINFO_CUR
DEALLOCATE TINFO_CUR


SELECT * FROM #TablaTemp ORDER BY Tabla_Nombre

DROP TABLE #TablaTemp

 

Si la base está muy dañada posiblemente no se repara, o se pierde información. En ese caso se puede recurrir a un software de 3ro para intentar reparar el .MDF.

Listar Triggers

Como identificar las triggers que hay en tu base de datos

Siempre es útil poder listar las triggers que tienes funcionando en tu server de producción.

Para ellos es que surge el siguente script:

SELECT O.name AS [Trigger]
,OBJECT_NAME(parent_obj) AS Tabla
,OBJECTPROPERTY( id,'ExecIsUpdateTrigger') AS Es_Update
,OBJECTPROPERTY( id,'ExecIsDeleteTrigger') AS Es_Delete
,OBJECTPROPERTY( id,'ExecIsInsertTrigger') AS Es_Insert
,OBJECTPROPERTY( id,'ExecIsAfterTrigger') AS Es_After
,OBJECTPROPERTY( id,'ExecIsInsteadOfTrigger') AS Es_insteadof
 ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS Habilitado
FROM sysobjects O INNER JOIN sysusers U ON O.uid = U.uid
INNER JOIN sys.tables T ON O.parent_obj = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE O.type = 'TR'

 

Resultado en un Microsoft SQL Server 2008 R2 x64 con el Microsoft SQL Server Management Studio:

Listar Triggers con TSQL

Directorio de Backup de MS SQL predeterminado

Restaurar backup es un proceso, que en algunos casos se debe desarrollar diariamente. Por ello es bueno optimizar los tiempos.

A continuación un Tips para que al buscar el backup con el Microsoft
SQL Server Management Studio les abra en un directorio que ustedes elijan.

En este caso estoy trabajando con SQL Server 2008 R2 x64 Enterprise. Al intentar levantar un backup siempre abre el “examinar” en el mismo lugar:
“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup”

 

Para modificar dicha ruta predeterminada realizamos lo siguiente:

  1. Ingresamos al Editor del Registro de Windows (regedit)
  2. Navegamos dentro del registro por MIPC\HKLM\Software\Microsoft\Microsoft Sql Server\(Motor + Instancia)\MSSQLServer:
  3. Buscamos y modificamos la clave “BackupDirectory” por el directorio que nosotros queramos:

 

Listo! Ahora la próxima vez que intentemos recuperar un backup, abrirá directamente en dicha carpeta:

Averiguar la intercalación de una base de datos

De la siguiente manera podrán averiguar la intercalación (collation) de una base de datos en MS SQL Server:

TSQL:
SELECT
DATABASEPROPERTYEX(‘MASTER’, ‘Collation’)
as Intercalacion;

SQL Server Management Studio:

Obtener Base Actual

Cuando trabajamos con campos calculados a veces es necesario incluir el nombre de la base sobre la que está realizando la consulta. De esta manera si realizamos esta misma consulta en varias veces, y unimos el resultado tendremos siempre un dato agrupador que nos identificará la base origen.

Esta función es DB_NAME().

Ejemplo:
select db_name() as BaseActual

Resultado:

BaseActual
master

 

Listar Tamaño de las Bases de Datos

El siguiente script en TSQL lista todas las bases de datos con sus respectivos tamaños en Megas y Gigas.

SELECT DB_NAME(db.database_id) DatabaseName,
round((CAST(mfrows.RowSize AS FLOAT)*8)/1024,2) RowSizeMB,
round((CAST(mflog.LogSize AS  FLOAT)*8)/1024,2) LogSizeMB,
round((CAST(mfrows.RowSize AS FLOAT)*8)/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024,2) DBSizeMB,
round((CAST(mfrows.RowSize AS FLOAT)*8)/1024/1024+(CAST(mflog.LogSize AS FLOAT)*8)/1024/1024,2) DBSizeGB
FROM sys.databases db LEFT JOIN
(SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0
  GROUP BY database_id, type) mfrows
ON mfrows.database_id = db.database_id LEFT JOIN
(SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1
  GROUP BY database_id, type) mflog
ON mflog.database_id = db.database_id LEFT JOIN
(SELECT database_id,SUM(size) StreamSize FROM sys.master_files WHERE type = 2
 GROUP BY database_id, type) mfstream
ON mfstream.database_id = db.database_id LEFT JOIN
(SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files  WHERE type = 4
  GROUP BY database_id, type) mftext
ON mftext.database_id = db.database_id
ORDER BY 4 DESC

Complementa al siguiente script para listar las bases de datos con su ubicación:
https://maurobernal.com.ar/blog/t-sql/listar-todas-las-bases-de-datos-con-su-respectiva-ubicacion-y-tamanos/

Verificar Backups por TSQL

Verificar que se hayan realizado correctamente los backups es tarea fundamental de un DBA.

Para ello contamos con una serie de tablas provistar por Microsoft SQL Server para almacenar dicha información:

  • msdb.dbo.backupfile
  • msdb.dbo.backupmediafamily
  • msdb.dbo.backupmediaset
  • msdb.dbo.backupset

 

Los siguientes scripts pueden facilitar dicha tarea.

Más adelante trabajaremos con más detalle.

 

Atención:   Si copian y pegan puede que la comilla simple (‘) se convierta a comilla doble (“”).

/*******************************************************************************
Último Backup de la Base
******************************************************************************/
SELECT    S.Name AS DB, MAX(B.backup_finish_date) Fecha,DATEDIFF(hh,MAX(B.backup_finish_date),GETDATE()) Antiguedad_HS ,
CASE
  WHEN DATEDIFF(hh , MAX(B.backup_finish_date, GETDATE()) > 24 THENSI’
WHEN MAX(B.backup_finish_dateIS NULL THEN ‘SI’ ELSE ”
END AS SinBackupUltimas24hs
FROM sys.sysdatabases S LEFT OUTER JOIN msdb.dbo.backupset B ON B.database_name = S.name
WHERE B.type NOT LIKE ‘L’ AND S.name NOT LIKE  ‘tempdb’
GROUP BY S.Name ORDER BY S.Name

/*******************************************************************************
Ultimo Backup del Log
******************************************************************************/
SELECT S.Name AS DB,MAX(B.backup_finish_date) Fecha ,
CASE WHEN DATEDIFF(hh , MAX(B.backup_finish_date, GETDATE()) > 4 THEN ‘SI’
WHEN MAX(B.backup_finish_dateIS NULL THEN ‘SI’ ELSE
END AS SinBackupUltimas24hs
FROM sys.sysdatabases S LEFT OUTER JOIN msdb.dbo.backupset B ON B.database_name = S.name
WHERE B.type LIKE ‘L’ AND S.name NOT LIKE ‘tempdb’
GROUP BY S.Name ORDER BY S.Name

/*******************************************************************************
Detalle por Backups de los últimos 7 días
******************************************************************************/
SELECT    database_name as DB, name AS Backup_Name, backup_finish_date AS LastBackup,
CASE [type] WHEN ‘D’ THEN ‘Full’ 
WHEN ‘L’ THEN ‘Log’
WHEN ‘I’ THEN ‘Differential’ ELSE ‘Other’
END AS Tipo_Backup
FROM    msdb.dbo.backupset B
WHERE backup_finish_date DATEADD(dd,-7,GETDATE())
ORDER BY database_name, backup_finish_date desc

Quitar microsegundos a una fecha en SQL Server

De la siguiente manera podrán quitar (truncar) la fechas para quitarles los microsegundos.

–Como Quedaria sin los microsegundos

SELECT
CampoFecha
,DATEADD(MILLISECOND,
DATEPART(MILLISECOND ,
CampoFecha

)
,
CampoFecha

)
as Resultado From Tabla

 

–Update de la Tabla

update Tabla set
CampoFecha=DATEADD(MILLISECOND,
DATEPART(MILLISECOND ,
CampoFecha

)
,
CampoFecha

)