Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

El Datafile que no podia eliminarse

leave a comment »

Empezaré diciendo que el eliminar datafiles de una base de datos no es una tarea que sea del todo recomendada ni mucho menos frecuente dentro de las tareas cotidianas de una DBA, pero hay momentos en los que se va a necesitar realizarla y hay que estar preparados para saber qué hacer si esta falla, como me paso a mí.

Primero hay que entender que los datafiles son los archivos físicos de nuestra base de datos. Cuando creamos una base de datos, se crea por defecto sólo uno (basado en lo que tengamos configurado en nuestra base de datos “model”), el cual tiene como extension *.mdf. Sin embargo, nosotros podemos crear más datafiles, conocidos como secundarios y de extensión *.ndf, los cuales podemos distribuirlos entre los diferentes discos de nuestro servidor.

Ahora en mi caso particular yo estaba buscando refrescar uno de mis ambientes de desarrollo con data más reciente de mi base de datos de producción, pero dado que esta es una base de datos de más de 1TB de información y que se tiene una limitación de espacio en el ambiente de desarrollo, antes de refrescar el ambiente, se hace un trabajo de truncado de tablas de log y otras que no son importantes para el funcionamiento de la aplicación en el ambiente de desarrollo. Luego del proceso de truncado se procede a realizar un shrink de los datafiles (lo cual es algo que JAMAS se debe hacer en producción) y la eliminación de los datafiles que sobran y esto venía funcionando bien desde siempre hasta que un día FALLO.

Esta falla evitaba que el datafile se vaciara completamente, por ende, hacía imposible su eliminación, luego de mucho buscar y ver en los primeros resultados de mi búsqueda que uno nunca va a poder hacer un DBCC SHRINK con EMPTYFILE a un archivo primario (recuerdan el archivo *.mdf, ese es el primario), lo cual ya sabía, solo se puede hacer esto en los secundarios, y el archivo que no podía eliminar era efectivamente un secundario. El mensaje de error que aparecía era el siguiente:

Msg 2555, Level 16, State 2, Line 2
Cannot move all contents of file “xxx” to other places to complete the empty

Entonces lo que se me ocurrio fue buscar que objetos eran los que no se habia podido mover del datafile que queria eliminar, para esto use la siguiente consulta, la cual viene de esta pagina:

CREATE TABLE #Object_Search
(File_id          BIGINT,
Page_id          BIGINT,
pg_alloc         BIGINT,
ext_size         BIGINT,
object_id        BIGINT,
index_id         BIGINT,
partition_number BIGINT,
partition_id     BIGINT,
iam_chain_type   VARCHAR(50),
pfs_bytes        VARCHAR(50)
);
GO

INSERT INTO #Object_Search
EXEC ('DBCC EXTENTINFO(''MyDatabase'')');
GO

DECLARE @id AS INT;
SELECT @id = FILE_ID('xxx');
SELECT DISTINCT
[name]
FROM
(
SELECT OBJECT_NAME(object_id) AS name,
*
FROM #Object_Search
WHERE File_id = @id
) x;

Esto me permitio ubicar la tabla “heap” que no se estaba moviendo, lo que hice para resolver el problema fue crearle un indice cluster, y volver a ejecutar el comando DBCC SHRINKFILE con EMPTYFILE y esta vez si funciono y pude eliminar el archivo que me estaba dado problemas.

Espero que este corto articulo les haya sido de utilidad. Hasta la proxima.

 

Written by dbamemories

noviembre 11, 2016 at 4:27 pm

Publicado en SQL Server Database

Cuanto Durará mi Backup o Restore

leave a comment »

Bueno esto es algo que creo que todos se han preguntado en algún momento cuando deciden hacer una operación de backup o restore, especialmente cuando la base de datos de tamaño algo considerable, pues déjenme decirles que si se puede identificar el tiempo que va a tomar una de estas operaciones con un simple script.

Sin embargo hay cosas que deben de considerar cuando ejecuten el script que les dejare líneas más abajo:

  1. Nunca tomen el primer estimado como 100% exacto, hay que dejar que la operación (backup / restore) se asiente, al comienzo comenzara a dar algunos números que cambiaran rápidamente si la base de datos es muy grande. Esperen unos 30 segundos o un minuto luego de iniciada la operación para poder tener un estimado más exacto.
  2. El tráfico de red importa, si están haciendo un backup o restore, desde o hacia una unidad de red, es importante considerar el ratio de transferencia de datos entre estos dos puntos, he visto casos en los que un restore tomaba 20 minutos entre el punto “A” y el punto “B”, sin embargo cuando se hacia el restore desde el punto “A” y el punto “C” demoraba más de 12 horas, y es por el ratio de transferencia.
  3. El estimado es exacto en la mayoría de casos, sin embargo hay veces en las que circunstancias ajenas, tales como sobre carga en alguno de los dos servidores, o cambios en la red pueden hacer que este cambie.

En todos los casos es importante que si el restore tomara mucho tiempo hay que monitorearlo constantemente. A continuación el script, espero les sea de mucha utilidad como a mí.

select
session_id,
convert(nvarchar(22),db_name(database_id)) as [database],
case command
when 'BACKUP DATABASE' then 'DB'
when 'RESTORE DATABASE' then 'DB RESTORE'
when 'RESTORE VERIFYON' then 'VERIFYING'
when 'RESTORE HEADERON' then 'VERIFYING HEADER'
else 'LOG' end as [type],
start_time as [started],
dateadd(mi,estimated_completion_time/60000,getdate()) as [finishing],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) - wait_time/60000 as [mins left],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) as [total wait mins (est)],
convert(varchar(5),cast((percent_complete) as decimal (4,1))) as [% complete],
getdate() as [current time]
from sys.dm_exec_requests
where command in ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE VERIFYON','RESTORE HEADERON')

Written by dbamemories

agosto 31, 2016 at 11:17 am

Publicado en SQL Server Database

De Regreso

leave a comment »

Hola a todos, que he estado mucho tiempo sin publicar nada y quería decirles lo siento y que no, no he muerto jajaja, simplemente estuve demasiado ocupado con las diversas responsabilidades que tuve y que ahora he decidido regresar a escribir, tratare de hacerlo más seguido para poder compartir todos los conocimientos que he adquirido en todo este tiempo.

Quería también compartir con ustedes que no solo he estado viendo motores SQL Server, sino que también tuve la oportunidad de administrar motores Oracle y MySQL, esta experiencia me ha abierto aún más las posibilidades de entender cómo funciona un motor de base de datos en general, es cierto que todos tienen sus diferencias, pero en el fondo todos se usan para almacenar la datos y obtener información, la diferencia entre datos e información es que esta última si representa una pieza valiosa para el negocio para entender el comportamiento de sus clientes, mientras que la primera de por sí sola no ayuda al negocio a definir tendencias.

Bueno eso es todo por esta introducción, en los próximos días iré publicando más artículos en su mayoría de SQL Server, pero quizás por ahí suelte algunos de MySQL y Oracle. Esto es todo por este post, suerte y bendiciones a todos.

Written by dbamemories

agosto 28, 2016 at 6:15 am

Publicado en SQL Server Database

Historial de Backups y Restores para una Base de Datos

with 5 comments

Hola, en esta ocación quisiera contribuir con un script que he desarrollado ya hace algun tiempo y me ha sido muy util al momento de adminstrar bases de datos, y es que hay veces en las que se requiere saber cuando fue el ultimo backup full o de log de una base de datos, o cuando fue la ultima vez que fue restaurada y que archivo se tomo para poder hacer la restauración. Este tipo de preguntas pueden ser un poco complicadas de responder cuando no se ha manejado la base de datos desde el comienzo (nacimiento) lo cual en muchos casos no pasa asi.

Con respecto a los backups, entre los datos mas relevantes del script se indica cuando fue ejecutado el backup, la duración de la operación de backup, el tamaño del archivo de backup generado, y el tipo de backup (Database, Diferencial y Log) y la ubicación del mismo. Con estos datos sera muy facil identificar la cadena de backups para una base de datos

Con respecto a las operaciones de restore, el scritp entre sus datos mas relevantes tiene: la fecha de la operación de  restore, el tamaño del backup restaurado, el tipo de backup que fue restaurado (Database, Diferencial y Log) y el servidor y la ubicación de donde proviene el backup. Es importante mencionar que para las operaciones de restore se muestra “Unknown” en la columna “Duration” debido a que no se tiene el dato de cuanto tiempo tardo realizar la operacion de restore.

Ahora para poder identificar el tipo de operacion mostrada en el historial es importante ver el valor de la columna “Operation_Type”, los posibles valores son “BACKUP” y “RESTORE”. Adicionalmente es importante mencionar que el script esta hecho para mostrar el historial de backups y restores de una sola base de datos al mismo tiempo, ademas no es necesario modificar el script para colocar el nombre de la base de datos de la cual se desea ver el historial, simplemente se debe de ejecutar sobre la base de datos que desea consultar, el script sacara el historial de la base de datos de la conexion que se uso para ejecutarlo.

Ahora sin mas preambulos el script:


select
bs.database_name as TargetDatabase
,bs.backup_start_date as Operation_Date
,cast(datediff(minute,bs.backup_start_date,bs.backup_finish_date)/60 as varchar) + ' hours ' +
cast(datediff(minute,bs.backup_start_date,bs.backup_finish_date)%60 as varchar) + ' minutes ' +
cast(datediff(second,bs.backup_start_date,bs.backup_finish_date)%60 as varchar) + ' seconds'
as [Duration]
,cast(bs.backup_size/1024/1024 as decimal(22,2)) as [BackupSize(MB)]
,'BACKUP' as Operation_Type
,case bs.type
when 'D' then 'Database'
when 'L' then 'Log'
when 'I' then 'Differential'
end as BackupType
,bs.user_name as [User]
,bmf.physical_device_name as BackupFile
,bs.server_name as ServerOrigin
,bs.recovery_model
,bs.begins_log_chain
,bs.is_copy_only
,bms.software_name as BackupSoftware
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediaset bms
on bs.media_set_id = bms.media_set_id
inner join msdb.dbo.backupmediafamily bmf
on bms.media_set_id = bmf.media_set_id
where bs.database_name = db_name()
and bs.server_name = serverproperty('servername')

union all

select
rh.destination_database_name
,rh.restore_date as operation_date
,'Unknown' as [Duration]
,cast(bs.backup_size/1024/1024 as decimal(22,2)) as [BackupSize(MB)]
,'RESTORE' as Operation_Type
,case rh.restore_type
when 'D' then 'Database'
when 'L' then 'Log'
when 'I' then 'Differential'
end as BackupType
,rh.user_name as [User]
,bmf.physical_device_name as BackupFile
,bs.server_name as ServerOrigin
,bs.recovery_model
,bs.begins_log_chain
,bs.is_copy_only
,bms.software_name as BackupSoftware
from msdb.dbo.backupset bs
inner join msdb.dbo.backupmediaset bms
on bs.media_set_id = bms.media_set_id
inner join msdb.dbo.backupmediafamily bmf
on bms.media_set_id = bmf.media_set_id
inner join msdb.dbo.restorehistory rh
on bs.backup_set_id = rh.backup_set_id
where rh.destination_database_name = db_name()
order by 2 desc

Espero les sea util. Hasta una proxima oportunidad.

Written by dbamemories

junio 21, 2013 at 11:34 pm

Obtener el DTS de un paso de un job.

leave a comment »

Hola este sera un post pequeño, per me parecio muy util y es que tengo un servidor SQL Server 2000 (si ya se que es bastante antiguo pero es lo que hay), entonces en este servidor hay jobs los cuales dentro de sus steps ejecutan paquetes DTS, sin embargo cuando se abre el step para ver su contenido se ve una sentencia encriptada como la siguiente:

DTSRun /~Z0x6078C236DCDD6A73931306866F9FC179EF750F439603F2CBEB820803744FC6605FF904C2DC6A6F355026A4CF56DEE2CBC7E72D6E7C1C88F17EA6CD17AEA6D7B6D6234D8D743CB0619D8A52006060594AEFEF6EC6582531B5DA1D1F30EDA8B8E2E78099A7869557567EF93557F67265092AF0F4 

Entonces, el problema esta en identificar que DTS es el que ejecuta el step. Luego de buscar puede ver que es tan simple como seguir los siguientes pasos:

  • Abrir una ventana de comandos en el servidor donde se alojan los paquetes.
  • Pegar toda la cadena antes mencionada
  • Agregar al final de esa cadena lo siguiente “/!X /!C”. Entonces quedaria como sigue:

DTSRun /~Z0x6078C236DCDD6A73931306866F9FC179EF750F439603F2CBEB820803744FC6605FF904C2DC6A6F355026A4CF56DEE2CBC7E72D6E7C1C88F17EA6CD17AEA6D7B6D6234D8D743CB0619D8A52006060594AEFEF6EC6582531B5DA1D1F30EDA8B8E2E78099A7869557567EF93557F67265092AF0F4 /!X /!C

Luego de ejecutar la cadena se copiara a la memoria el texto desencriptado de la ejecucion del paquete, entonces solo hay que abrir un notepad y pegar (CTRL + V) y se obtendra algo asi:

DTSRun /S “MiServidor” /N “MiPaqueteDTS_SQL2000” /E /!X /!C 

Y eso es todo, con eso se puede saber con seguridad cual es el paquete que ejecuta un paso de un job. Espero que les sea de utilidad.

Written by dbamemories

enero 29, 2013 at 12:17 pm

Publicado en SQL Server Database

Tagged with , , ,

Diseño de una Base de Datos

with one comment

Las Bases de Datos están compuestas por elementos lógicos y físicos como se puede detallar en el siguiente post. Sin embargo para poder tener un mayor control sobre los objetos que guardamos en ella es necesario realizar un planeamiento previo del diseño ésta, este paso previo a la creación de la base de datos es muy importante ya que si no tenemos un buen diseño inicial, mas adelante cuando encontremos problemas en el diseño, el corregirlo será mucho mas complicado y costoso.

 Comenzaremos viendo desde donde sale la estructura inicial de una base de datos en SQL Server. Cuando se crea una base de datos, internamente SQL Server toma como plantilla la base de datos de sistema llamada “model”, heredando todas sus propiedades y objetos, también copiando los filegroups y datafiles para luego cambiarles de nombre por los de la nueva base de datos, sin embargo este diseño lógico y físico inicial se puede personalizar añadiendo opciones adicionales a la creación de la base de datos, estas opciones adicionales se pueden agregar a través de código o a través del wizard del SSMS (SQL Server Management Studio).

El problema con esto es que normalmente la base de datos model esta configurada sólo con el filegroup “PRIMARY” con un tamaño de 8 MB y un Log de transacciones de 2 MB, ambos configurados con un auto-creacimiento de 10% para ambos archivos. Adicionalmente tiene un modelo de recuperación Full, lo cual ocasiona problemas con el crecimiento del log de transacciones si es que éste no tiene el tratamiento debido, es decir considerar dentro de la estrategia de backup los backups de log de transacciones, lo cual normalmente.

Ahora, personalmente considero que toda base de datos debe tener por lo menos dos filegroups, uno de ellos siempre sera el “PRIMARY” el cual albergara a todos los objetos de sistema como las tablas y vistas de metadata, los subsiguientes filegroups serán dedicados a albergar la data del usuario. Adicionalmente se pueden agregar mas filegroups que sirvan para albergar distintos tipos de data como indices, BLOB data, o tablas de módulos específicos. De esta manera,al tener filegroups destinados a un uso especifico es mucho mas ordenado e incluso permite traer a la base de datos en linea por partes en caso de algún desastre reduciendo el tiempo de espera de los sistemas para poder acceder a la data.

Otra razón importante para poder tener este diseño es para tener mayor flexibilidad al momento de mover los datafiles de un disco a otro mientras estos se encuentran en linea, si es que la data de usuario se encontrara en el filegroup ”PRIMARY” y se quisiera mover el data file con extensión “MDF”, no se podría mover ya que este datafile en especial no puede ser vaciado por completo. Mas adelante tendremos un articulo en el cual se explicara a mas detalle como se debe hacer este movimiento.

Finalmente el realizar este diseño permitirá que la data se distribuya entre varios archivos de datos los cuales según las buenas practicas deberían estar alojados en diferentes discos físicos los cuales permitirán compartir las operaciones de lectura \ escritura de disco, lo cual permitirá el más rápido acceso a los datos. Así que ya saben la próxima vez que se cree una base de datos piense bien el mejor diseño que esta debe tener.

Written by dbamemories

enero 15, 2013 at 5:00 pm

Publicado en SQL Server Database

Tagged with ,

Los números de 2012

leave a comment »

Hola,

A pesar de que este año he estado muy ocupado con el trabajo y otras cosas, y no he podido escribir como hubiera querido, los numeros que muestra este reporte anual del blog son muy buenos. Solo quiero agradecerles a todos las personas que visitan este sitio para consulta, ademas hacer el firme compromiso de que este 2013 voy a escribir mas para no tenerlos tan abandonados. Un saludo fuerte y a continuacion los numeros de este 2012.

Los duendes de las estadísticas de WordPress.com prepararon un informe sobre el año 2012 de este blog.

Aquí hay un extracto:

600 personas llegaron a la cima del monte Everest in 2012. Este blog tiene 7.300 visitas en 2012. Si cada persona que ha llegado a la cima del monte Everest visitara este blog, se habría tardado 12 años en obtener esas visitas.

Haz click para ver el reporte completo.

Written by dbamemories

diciembre 31, 2012 at 8:06 am

Publicado en SQL Server Database