Microsoft SQL Server – Todo sobre la administración y el mantenimiento de Servidores de datos

Listar Tamaño de las Bases de Datos

/1 Comentario/en , /por

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

/0 Comentarios/en , /por

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 THEN ‘SI’
WHEN MAX(B.backup_finish_date) IS 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_date) IS 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

Error al Adjuntar una base de Datos

/2 Comentarios/en , /por

Error al adjuntar las bases de datos. Haga clic en el hipervínculo de la columna de mensajes para obtener más información.

«…Server detectó un error de E/S de coherencia lógico: página rasgada (firma esperada: 0x55555555; firma obtenida: 0xaaaaaaa9)…»

 

Hoy parecía ser un día normal y rutinario, cuando me enfrentó con el inconveniente de que una base se encuentra en modo sospechoso.

Generalmente con desadjuntar (detach) y nuevamente adjuntar (atach) se corrige el problema, pero en esta ocasión luego de volver de separar la misma, ya no se podía adjuntar.

Les paso una serie de instrucciones y pasos que les puede ser útil. En mi caso me dieron resultado:

  • Creamos una base con el mismo nombre, y con los mismos archivos de datos (mdf y ldf) que la base a recuperar
  • Detenemos el motor
  • Reemplazamos los archivos de la base generada con los de la base original dañada
  • Encendemos nuevamente el motor, por lo que la base dañada quedará en modo sospechoso
  • Ejecutamos el siguiente script

     

ALTER
DATABASE MYBASE SET
EMERGENCY
— Colocamos la base en modo «EMERGENCY»

ALTER
DATABASE MYBASE SET
SINGLE_USER
— La ponemos en modo de usuario único.

DBCC CHECKDB
(MYBASE, REPAIR_ALLOW_DATA_LOSS )
— Chequeamos la bd con la opción REPAIR_ALLOW_DATA_LOSS

ALTER
DATABASE MYBASE SET
MULTI_USER
— Por ultimo la ponemos en modo multiusuario

 

 

 


 

Quitar microsegundos a una fecha en SQL Server

/0 Comentarios/en , , /por

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
)


 

Ultimo dia del Mes con SQL Server y otros

/9 Comentarios/en , , /por

Trabajando con funciones TSQL para realizar cálculos de Fechas

A continuación una serie de funciones en TSQL útiles para el cálculo de fechas:

-->Mes Actual:
-----------------------------------
--Primer día del mes actual
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
--Último día del mes actual
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

-->Mes Anterior:
-----------------------------------
--Primer día del mes anterior
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))
--Último día del mes anterior
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

-->Mes Siguiente:
-----------------------------------
----Primer día del mes siguiente
SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
----Último día del mes siguiente
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))


--> Trimestre Actual:
-----------------------------------
----Primer día del trimestre actual
SELECT DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) 
----Último día del trimestre actual
SELECT DATEADD(qq,DATEDIFF(qq,-1,GETDATE()),-1)

Resultados

Con la fecha 10 de Noviembre del 2017 esto serían los resultados

Primer_Y_Ultimo_Dia_Resultados

 

Otra alternativa

Continuando con el trabajo con fechas con TSQL podemos hacer lo mismo, pero con una función nativa: EOMONTH()

Si les interesa les dejo el link, que incluye como siempre un caso práctico:

Obtener el ultimo día del mes con SQL Server


Obtener el número de la Semana

/0 Comentarios/en , , , /por

Una función muy simple en diferentes lenguajes para obtener el número de la semana.

Obtener el número de semana del año con PERL

my $numeroSemana = POSIX::strftime("%V", gmtime time);

Obtener el número de semana del año con PHP

$numeroSemana = date("W");

o date(«W», epoch). Se usa la letra mayúscula ‘W’.

Obtener el número de semana del año con SQL

En MySQL:

 SELECT WEEKOFYEAR(NOW())


 o


 SELECT WEEK(NOW(),3)

 

En MS SQL Server:

SELECT DATEPART( wk, GETDATE() )

Listar todas las bases de datos con su respectiva ubicación y tamaños

/10 Comentarios/en , /por

Listar todas las bases de datos

Una problemática habitual con la que me enfrentaba en las PCs de desarrollo era que me quedaba sin espacio en los discos por logs inmanejables, y más de una vez olvidar la ubicación de los archivos de las bases en proyectos versionados.

El siguiente script muy simple pero muy útil permite listar las bases, sus respectivos tamaños, archivos que la componen(MDF y LDF). Además es posible agregarle más campos a gusto del lector.( complementa a este script que muestra los tamaños )

select d.database_id,d.name, a.name as filename,a.physical_name as ubication,
 a.type_desc,(a.size/128)as sizeMB, recovery_model_desc,d.state_desc,compatibility_level
from sys.master_files a inner join sys.databases d on (a.database_id = d.database_id)
order by a.type,a.size

Espero que les sea útil, y como siempre cualquier sugerencia será tenida en cuenta..

Hasta la próxima…

 

Usando MYSQL varbinary con Linq to Entities

/0 Comentarios/en , , /por

Continuando mi proyecto de realizar un integrador de una base MYSQL con una base MSSQL, me encontré un nuevo problema con los campos VARBINARY.

Básicamente en ellos guardada textos planos, pero al ser VARBINARY, LINQ to ENTITIES los trataba como ARRAYS (binarios). Para convertir los valores use la siguiente función:

system.text.Encoding.ASCII.GetString(tabla.campo).

 

Los tipos BINARY y VARBINARY son similares a CHAR y VARCHAR, excepto que contienen cadenas de caracteres binarias en lugar de cadenas de caracteres no binarias. Esto es, contienen cadenas de bytes en lugar de cadenas de caracteres. Esto significa que no tienen conjunto de caracteres asignado, y la comparación y ordenación se basa en los valores numéricos de los valores de los bytes

The version of SQL in use does not support datatype ‘datetime2’

/0 Comentarios/en , /por

Actualmente estoy trabajando en un proyecto de integrar información de un motor MYSQL 5.0 a MSSQL 2005. Para ello el integrador esta en VB NET 2010 con DotNet 4.0.

El mejor framework para el diagramado y la realización de consultas fue usar LINQ TO ENTITIES.

Luego de un par de dolores de cabeza(*) pude llevar mi primera prueba al servidor de producción.

Al ejecutarlo obtuve mi primer error «The versión of SQL in use does not support datatype ‘datetime2′» .

En este momento recordé que mi servidor de desarrollo era un MSSQL Server 2008, y el de producción un MSSQL 2005. (L)

Antes de poder ponerme a pensar que hacer decidí googlearlo y encontré una solución simple pero efectiva.

Consiste en buscar el .emdx dentro del projecto de Visual Studio. Modificarlo con un editor de XML, o similar. Y buscar el siguiente código.

ProviderManifestToken=«2008« y reemplazarlo por ProviderManifestToken=«2005«

Algo simple pero efectivo… Al final funcionó…

(*) Si al ejecutar su aplicación se cierra sin más aviso, no se olviden de instalar el MYSQL Connector con el que estuvieron trabajando en el servidor de producción.

Reiniciar contador de campo autoincremental

/0 Comentarios/en , , /por

Trabajar con campos INT con el atributo IDENTITY es muy útil, ya que los valores se generan automáticamente de manera secuencial empezando del 1.

El problema surge cuando debemos reiniciar esa cuenta. Para ello podemos utilizar

En SQL Server 2005 o Posterior

DBCC CHECKIDENT (NOMBRE_TABLA, RESEED, 0)

 En MYSQL 

ALTER TABLE NOMBRE_TABLA AUTO_INCREMENT=0