Truncar y reducir el fichero de transacciones de SQL Server

El propósito de este post es saber cómo y cuándo podemos reducir el fichero de transacciones.

Seguro que más de una vez has visto como tu fichero .ldf crece y crece sin medida… y tú en el bar tomándote una caña. Fuera de broma, este problema ha sido recurrente en toda mi vida como “aprendiz” de DBA.

Pues hasta hoy hemos llegado, de aquí no pasa que sepamos todos resolver este problema con cierto criterio.

Para este post asumo que disponemos de una base de datos llamada EJEMPLO con modelo de recuperación SIMPLE.

Si quieres un pequeño script para que tu fichero .ldf crezca, ahí van un millón de registros:

CREATE TABLE [dbo].[Clientes]

    (

      [IdCliente] [int] NOT NULL ,

      [Nombre] [nvarchar](50) NOT NULL ,

      CONSTRAINT [PK_Clientes] PRIMARY KEY CLUSTERED ( [IdCliente] ASC )

        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,

               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,

               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

    )

ON  [PRIMARY]

 

GO

 

DECLARE @i INT

SELECT  @i = 0

BEGIN TRAN

WHILE @i < 100000

    BEGIN

        INSERT  INTO dbo.Clientes

                ( IdCliente, Nombre )

        VALUES  ( @i, CAST(@i AS NVARCHAR) )

        SELECT  @i = @i + 1

    END

COMMIT TRAN

Si hablamos de modelos de recuperación (que es algo muy importante si queremos reducir nuestro fichero de log), el SIMPLE es aquel en el que no tenemos previsto hacer copias de seguridad del registro de transacciones, sino simplemente copias de seguridad completas o diferenciales de nuestros archivos de datos (.mdf). En el modelo SIMPLE, cada vez que una transacción es confirmada (por confirmar nos referimos a grabar en disco la transacción, ya sea con commit o rollback, eso da igual), la misma se elimina del fichero de log y el espacio utilizado por la misma se marca como reutilizable (esto es radicalmente distinto en el modelo de recuperación COMPLETO donde, y puesto que podrías querer hacer copias de seguridad del registro de transacciones, las transacciones confirmadas no se eliminan y hasta que no hagas una copia de seguridad del registro, las transacciones no se marcarán como eliminadas y el espacio se reclamará como reutilizable).

Si quieres una breve pero concisa explicación del modelo de recuperación SIMPLE vs COMPLETA, visita este post donde se explica muy bien el concepto y ventajas y desventajas de ambos modelos de recuperación.

Yo por mi parte, tengo claro que mi modelo de recuperación es SIMPLE porque es sencillo de administrar y además no requiere una especial vigilancia del fichero .ldf. Lógicamente, esto no significa que igualmente crezca más de lo debido, pero te aseguro que con el modelo de recuperación COMPLETO, tu fichero .ldf terminará siendo gigante (siempre y cuando no pongas los medios necesarios para evitarlo, claro).

Una forma de consultar el actual modelo de recuperación podría ser la siguiente:

SELECT DATABASEPROPERTYEX('EJEMPLO', 'RECOVERY')

Para cambiar el modelo de recuperación:

ALTER DATABASE EJEMPLO SET RECOVERY {FULL | SIMPLE}

Si quieres saber más formas de consultar el modelo de recuperación de las base de datos de tu servidor, visita el siguiente enlace.

Habiendo ya debatido sobre los distintos modelos de recuperación, pasemos a la acción.

Con SP_SPACEUSED podemos ver el espacio que ocupa nuestra base de datos.

Resulta una buena práctica, ejecutar primero el comando DBCC UPDATEUSAGE para que el resultado ofrecido por SP_SPACEUSED sea el más correcto posible.

SP_SPACEUSED devolverá los siguientes resultados:

La columna database_size es la suma de los ficheros .mdf y .ldf en disco.

Como verás, esta información es útil pero no sabemos, ni el tamaño de nuestro fichero .ldf, ni tampoco su espacio libre.

Para saber el tamaño de nuestros ficheros .mdf y .ldf, podemos ejecutar el comando SP_HELPDB. De este modo, el comando SP_HELPDB ‘EJEMPLO’, mostrará los siguientes resultados:

Ahora ya sabemos que nuestro fichero .ldf ocupa 24 MB, pero seguimos sin saber si dispone o no de espacio libre.

Para saber el espacio libre del que dispone el fichero de registro .ldf, podemos ejecutar el comando DBCC SQLPERF(LOGSPACE).

En este momento, ya sabemos que aunque el tamaño del fichero de log es de 23 MB, sólo se está utilizando el 10%, luego queremos reducir el fichero .ldf y recuperar el espacio marcado como reutilizable.

Si además quieres saber cuanto espacio ocupa en disco una tabla o índice concreto, puedes ejecutar el script que está en este otro post http://panicoenlaxbox.blogspot.com.es/2010/11/espacio-en-tablas-e-indices-en-sql.html

Aunque parezca que nuestro fichero de transacciones ya es un serie candidato a ser reducido (como vemos sólo un 10% del fichero está en uso), es probable que aunque lo intentemos, no lo logremos. Para obtener más información sobre el fichero de transacciones, ejecutaremos el comando DBCC LOGINFO.

DBCC LOGINFO(EJEMPLO) nos devuelve la segmentación del propio fichero de log, es decir nos devuelve  los distintos ficheros virtuales VLF (Virtual Log Files) que componen el registro. La información importante es la columna Status, que indica:
0. La información está truncada (luego se podrá reducir).
2. La información está activa (no está truncada, luego no se podrá reducir).

De esta información se extrae que sólo para los VLF con estado 0 se podrán reclamar el espacio en disco usado.

Antes de comenzar con la reducción del fichero de log, podemos ejecutar el comando DBCC OPENTRAN que nos informa si hay o no transacciones abiertas.

Un ejemplo del comando DBCC OPENTRAN con transacciones abiertas (porque estoy ejecutando un comando con BEGIN TRAN y COMMIT TRAN).

Como puedes ver, la información incluye el SPID que podríamos utilizar junto a SP_WHO2acimdf).acer copias de seguridad del registro de transacciones, sino simplemente copias de seguridad c para obtener información de la transacción.

Si por el contrario no hay ninguna transacción abierta:

Cómo último paso antes de reducir el fichero de log, ejecutaremos el comando CHECKPOINT (si nuestro modelo de recuperación es SIMPLE), para asegurarnos de que se escriben en disco todas las páginas pendientes de escribir en disco y se produce el truncamiento del registro de transacciones.

Si te fijas, en este post hemos hablado tanto de “reducir” como de “truncar”.  

El truncamiento se produce automáticamente siempre que una transacción es confirmada y el modelo de recuperación de la base de datos sea SIMPLE. Es decir, “truncar” significa que las transacciones son eliminadas del fichero de registro y el espacio que estaban utilizando se marca como reutilizable. Además, un truncamiento también se produce con la instrucción CHECKPOINT (de nuevo si el modelo de recuperación es SIMPLE), y también cuando en el modelo de recuperación COMPLETO hacemos una copia de seguridad del registro.

En cualquiera de los casos y al hilo de la información obtenida, en este momento ya podemos reducir el fichero .ldf, vamos a ello!

Para reducir el tamaño de los ficheros (tanto el de datos como el de registro) podemos utilizar DBCC SHRINKFILE o DBCC SHRINKDATABASE. En general, ambos comandos son similares. Quizás la gran deferencia entre ambos es que DBCC SHRINKFILE permite reducir el fichero más allá de su tamaño inicial, mientras que SHRINKDATABASE no. Por otro lado, SHRINKDATABASE reducirá tanto el fichero de datos como el fichero de registro.

Si estamos en SQL Server 2005:

Si el modelo de recuperación es SIMPLE:

DBCC SHRINKFILE(EJEMPLO_Log)

Si el modelo de recuperación es COMPLETO:

BACKUP LOG EJEMPLO WITH TRUNCATE_ONLY

DBCC SHRINKFILE(EJEMPLO_Log)

Si estamos en SQL Server 2008:

Si el modelo de recuperación es SIMPLE:

DBCC SHRINKFILE(EJEMPLO_Log)

Si el modelo de recuperación es COMPLETO:

BACKUP LOG EJEMPLO TO DISK 'C:/BORRAR.BAK'

DBCC SHRINKFILE(EJEMPLO_Log)

Si te fijas, DBCC SHIRNKFILE reduce el fichero pero antes tiene que estar truncado. Es por ello que si el modelo de recuperación es COMPLETO, necesitamos hacer un backup previo del fichero de log para que se lleve a cabo el truncamiento. En este punto, SQL Server 2005 es más flexible y permite simplemente “desechar” el fichero de log con TRUNCATE_ONLY, sin embargo, en SQL Server 2008 tendremos que llevar a cabo un backup del registro a disco aunque acto seguido borremos el fichero.

Si además también quieres reducir el fichero de datos, puedes utilizar DBCC SHRINKDATABASE en vez de DBCC SHRINKFILE y todo funcionará igualmente.

Bueno, espero no dar muchas vueltas sobre esto porque ello supondrá, que de ahora en adelante el fichero .ldf no será una de mis peores pesadillas.

Un saludo!

MySql GUI Tools

Esta semana he tenido que instalar MySql en mi Windows.

Para ello, he utilizado Web Platform Installer y me ha instalado la versión 5.1.

Mi sorpresa ha sido cuando he visto que además del motor de base de datos (al que por cierto también se puede conectar con Web Matrix), me ha instalado ciertas herramientas gráficas para la administración del servidor.

Yo estaba esperando la herramienta WorkBench que es ahora mismo la herramienta gráfica oficial de MySql, tanto para administradores como para desarrolladores, pero sin embargo me ha instalado otras herramientas como: MySql Administrator, MySql Query Browser, etc.

A partir de aquí he comenzado a dudar que herramientas son las que debería instalar, es decir, ¿WorkBench o las que instaló Web Platform Installer?

Tirando del hilo, he visto que MySql Administrator, MySql Query Browser y el resto de herramientas que instaló Web Platform Installer son parte de una suite de herramientas que ya están en desuso y para las que MySql ya no ofrece soporte ni evolución alguna. De hecho, MySql las considera EOL (End-Of-Line), es decir, que van a morir y nadie lo impedirá.

Aún puedes descargarlas desde el sitio de MySql desde la dirección http://dev.mysql.com/downloads/gui-tools/5.0.html, pero yo que soy un super-novato de MySql, preferiré utilizar WorkBench que es la apuesta oficial y presente de MySql.

Un saludo!

Linq To DataSet: Convirtiendo nuestra consulta a DataTable

Hoy he estado trabajando con Linq To DataSet y tenía la necesidad de devolver las consultas Linq como objetos DataTable.

Para ello he visto que hay disponibles distintas opciones.

Partiendo del siguiente código:

        Dim q =

            From r In customersDataTable.AsEnumerable

            Where r.Field(Of String)("CustomerID") = "4004008"

 

        Dim table1 As DataTable = q.AsDataView.Table

        Dim table2 As DataTable = q.AsDataView.ToTable

        Dim table3 As DataTable = q.CopyToDataTable

 

table1 devolverá el objeto DataTable llamado “customersDataTable”, así que el filtro especificado en la consulta de Linq no será efectivo, luego no utilizaré esta forma.

table2 convierte el resultado de la consulta a un objeto DataTable donde sí se aplica el filtro.

table3 copia el resultado de la consulta a un objeto DataTable donde de nuevo, sí se aplica el filtro.

En este punto, está claro que excepto la primera forma, las 2 siguientes son válidas. CopyToDataTable tiene la ventaja de que trabaja con tipos anónimos (Ni CopyToDataTable ni AsDataView soportan tipos anónimos).

Sin embargo, me he encontrado con algunas situaciones delicadas.

Si el número de filas devueltas por la consulta Linq es 0, entonces CopyToDataTable no funcionará. Sin embargo, AsDataView no tiene problemas si la consulta no devolvió ningún registro.

Por otro lado, AsDataView, además de no llevarse bien con los tipos anónimos, tampoco se lleva bien con las proyecciones. Sin embargo, CopyToDataTable no tiene problemas con las proyecciones y funciona correctamente.

Por hoy nada más

Un saludo!

Linq To DataSet: Comparándolo con su antecesor y enlazando a datos

En el anterior post Linq To DataSet, contamos las bondades que nos ofrece este proveedor de Linq para la búsqueda y ordenación de filas en un objeto DataTable. Sin embargo, me gustaría enfrentar las posibilidades actuales de filtrado y ordenación de las que disponemos actualmente en un objeto DataTable contra las nuevas y mejoradas opciones que nos ofrece Linq To DataSet.

Siendo así, haremos un breve repaso sobre las propiedades y métodos más relevantes que, de forma nativa, ofrece un DataTable para la búsqueda, filtrado y ordenación de filas, para finalmente enfrentarlos a sus equivalentes en Linq To DataSet, y así convencernos plenamente de la potencia y superioridad que ofrece el proveedor de Linq para DataSet.

En un objeto DataTable, la única operación de búsqueda de filas disponible es el método Select.

DataTable.Select(filterExpression[, sort])

El método Select devuelve un array de objetos DataRow que cumple el filtro especificado y, opcionalmente, ordenado según el criterio de ordenación especificado.

Existe otra sobrecarga de Select que permite devolver filas a partir de un criterio de búsqueda y según el estado de las filas a partir del enumerado DataViewRowState.

La sintaxis para la expresión de filtro y los operadores disponibles puedes encontrarla aquí.

Cualquier objeto DataTable tiene automáticamente disponible un objeto DataView asociado en la propiedad DataTable.DefaultView. En cualquier caso, somos libres de crear tantos objetos DataView asociados a un DataTable como creamos oportuno.

Un objeto DataView es simplemente una lista de punteros a objetos DataRow del DataTable asociado, con un filtro y ordenación específico. Buscando una equivalencia, serían como las vistas en una base de datos.

De este modo, al poder tener distintos objetos DataView asociados al mismo DataTable lo que conseguimos es poder tener distintos grupos de filas ordenadas y filtradas por diferentes criterios.

Como hemos dicho antes, bien podemos trabajar sobre el objeto DataView por defecto (DataTable.DefaultView) o bien crear un nuevo objeto DataView.

Para crear un nuevo objeto DataView:

Dim miDataView As New DataView(miDataTable)

Antes de comenzar con ejemplos más concretos sobre DataView cabría comentar que un objeto DataView es una colección de objetos DataRowView y que cada objeto DataRowView permite el acceso al objeto DataRow que está apuntado a través de su propiedad Row.

Por otro lado la propiedad DataView.Table devuelve el objeto DataTable al que está asociado el objeto DataView.

Finalmente en el objeto DataView ya sí disponemos de más y mejores métodos para la búsqueda, ordenación y filtrado de filas:

• RowFilter
• Sort
• ApplyDefaultSort
• Find
• FindRows

Con la propiedad RowFilter podemos filtrar a una expresión dada.

        ' Vista por defecto

        customersDataTable.DefaultView.RowFilter = "LockOrder = 1"

 

        ' Nueva vista

        Dim miDataView As New DataView(customersDataTable)

        miDataView.RowFilter = "LockOrder = 1"

 

        ' Eliminar filtro

        customersDataTable.DefaultView.RowFilter = String.Empty

        miDataView.RowFilter = String.Empty

 

Con la propiedad RowStateFilter podemos filtrar filas por un estado concreto según DataViewRowState.

Con la propiedad Sort podemos ordenar el conjunto de resultados de nuestro DataView.

        Dim miDataView As New DataView(customersDataTable)

        miDataView.Sort = "CompanyID DESC, CustomerID DESC"

 

El método Find busca según el criterio de ordenación actual y devuelve el primer índice del objeto DataRowView encontrado o -1.

Find necesita que previamente se haya especificado un criterio de ordenación que será sobre el que realice la búsqueda.

        Dim miDataView As New DataView(customersDataTable)

        miDataView.Sort = "CustomerID"

 

        Dim index As Integer = miDataView.Find("4309463")

Si la ordenación se ha realizado por múltiples campos, será necesario suministrar valores para cada uno de estos campos:

        Dim miDataView As New DataView(customersDataTable)

        miDataView.Sort = "CompanyID, CustomerID"

 

        Dim values(1) As String       

        values(0) = "520" ' CompanyID

        values(1) = "4309463" ' CustomerID

        Dim index As Integer = miDataView.Find(values)

Con el método Find encontramos el primer índice de una fila, pero si lo que queremos es obtener todas las filas que cumplan el criterio especificado (no sólo la primera), necesitamos utilizar el método FindRows.

        Dim miDataView As New DataView(customersDataTable)

        miDataView.Sort = "LockOrder"       

 

        Dim foundRows() As DataRowView = miDataView.FindRows("False")

        If foundRows.Length > 0 Then

            ' TODO Hacer algo

        End If

En este ejemplo quizás te extrañe la condición de búsqueda a “False”, pero es así como la expresión de filtro quiere comparar campos de tipo bit.

Por último, la propiedad ApplyDefaultSort indica si se va a utilizar o no el orden predeterminado. Aquí la pregunta es ¿Cuál es el orden predeterminado? Pues es el orden que tuviera el objeto DataTable asociado,y la única manera que hay de establecer este orden en el objeto DataTable es con la propiedad DataTable.PrimaryKey. De este modo, si nuestro objeto DataTable tiene establecida la propiedad PrimaryKey podremos buscar con Find sobre ella, si aplicamos la ordenación por defecto.

        Dim primaryKey() As DataColumn = { _

            customersDataTable.Columns.Item("CompanyID"), _

            customersDataTable.Columns.Item("CustomerID")}

        customersDataTable.PrimaryKey = primaryKey

 

        Dim miDataView As New DataView(customersDataTable)       

        miDataView.ApplyDefaultSort = True

 

        Dim values() As String = {"520", "4309397"}

        Dim index As Integer = miDataView.Find(values)

Habiendo ya explicado las propiedades y métodos principales para la búsqueda, ordenación y filtrado de filas en un objeto DataTable, ahora es momento de ver cómo realizar estas mismas tareas con Linq To DataSet.

Lo primero a mencionar es que con Linq To DataSet trabajaremos directamente sobre el objeto DataTable y no utilizaremos nunca el objeto DataView. Además, los conceptos de filtrado y ordenación conviven en la misma expresión de consulta como un sola operación que devuelve un conjunto de resultados.

        Dim q As Data.EnumerableRowCollection(Of DataRow)

 

        q =

            From r In customersDataTable.AsEnumerable

            Where

r.Field(Of String)("CustomerID").StartsWith("4309")
AndAlso r.Field(Of Boolean)("LockOrder") = True

            Order By

r.Field(Of String)("CompanyID"),
r.Field(Of String)("CustomerID") Descending

            Select r

En este ejemplo, hemos filtrado por una condición compleja y además hemos ordenado los resultados, todo ellos sin importarnos la ordenación predeterminada, si los campos de filtro son parte de la ordenación, etc.

Como podrás comprobar, con Linq To DataSet todo es más sencillo. Si aún no lo has leído, insisto en que visites el post Linq To DataSet para familiarizarte con la sintaxis.

Incluso el anterior código podría ser más compacto si utilizamos la inferencia de tipos (como es habitual) para la variable q y además omitimos la cláusula Select.

        Dim q =

            From r In customersDataTable.AsEnumerable

            Where

r.Field(Of String)("CustomerID").StartsWith("4309")
AndAlso r.Field(Of Boolean)("LockOrder") = True

            Order By

r.Field(Of String)("CompanyID"),
r.Field(Of String)("CustomerID") Descending

A partir de aquí podrías iterar sobre el conjunto de resultados para realizar las operaciones que creas oportunas.

        For Each r In q

            Response.Write(String.Format("CustomerID {0}", r.Item("CustomerID")))

        Next

 
En cualquier caso, la mitad de las veces que utilizo un DataTable es para enlazarlo a un control de datos. Con independencia de que se pueda enlazar la consulta Linq a un control, si queremos seguir enlazando objetos DataView (recuerda que cuando enlazas un DataTable a un control de datos, en realidad estás enlazando DataTable.DefaultView), lo que tenemos que hacer es transformar nuestra consulta a un objeto DataView con el método DataTable.AsDataView.

Este método (que está disponible si agregamos la referencia al ensamblado System.Data.DataSetExtensions), convierte nuestra consulta de Linq To DataSet en un objeto DataView.

        Dim q = From r In customersDataTable.AsEnumerable          

 

        Return q.AsDataView

El método tiene algunas limitaciones, como que no permite consultas que devuelvan tipos anónimos ni operaciones de combinación.

Por otro lado, también tenemos disponible el método CopyToDataTable que copia las filas del resultado de la consulta en un nuevo objeto DataTable. Es importante reseñar que CopyToDataTable “copia”, mientras que AsDataView no copia sino que “apunta” a filas. Es decir, cambios realizados en las filas del objeto DataView (con AsDataView) se verán reflejados en el objeto DataTable asociado, mientras que con CopyToDataTable estamos hablando de un nueva nuevo DataTable que nada tiene que ver con el original.

Al igual que AsDataView, CopyToDataTable tampoco puede copiar objetos anónimos ni operaciones de combinación, pero para este método podemos encontrar una implementación personalizado en MSDN para salvar estas limitaciones (esto es posible porque “creamos” un nuevo objeto DataTable, mientras que con AsDataView no es posible porque cada objeto DataRowView tiene que apuntar forzosamente a un objeto DataRow).

Aunque me he enrollado mucho con las limitaciones de AsDataView y CopyToDataTable, lo cierto es que si quieres enlazar tu consulta Linq a un control de datos en raras ocasiones querrás utilizar tipos anónimos, así que no hay excusa para no utilizar Linq To DataSet.

Un saludo!