Si te haces esta pregunta estás ante lo más común cuando comienzas a administrar SQL, la respuesta fácil sería darte dos comandos, y que luego funcionen o no. La verdad no me interesa eso en absoluto, creo que debes entender como funciona el log para saber porque crece, así entenderás porque aunque hagas backups todas las noches tu log sigue creciendo, y además, y mucho mejor sabrás como evitar que crezca si no tiene que hacerlo.

Para comenzar, que el log crezca depende directamente del modo de recuperación que tenga tu base de datos, solo en los modos FULL y BULK LOGGED crecerá sin medida (también puede crecer en el modo simple, es mucho más raro pero puede suceder, en 3 lineas veremos porqué).

El modo de recuperación de la bbdd puedes verlo en las propiedades (SQL Server Management Studio, te conectas al relacional, propiedades.. y en la pesstaña opciones aparece el modo de recuperación actual y en el desplegable el resto de modos. Yo tengo el cliente en inglés, así que disculpadme pero lo vereis en inglés

 

 El log de transacciones se encarga de registrar una tras otras de forma secuencial todas las modificaciones que se producen en la base de datos, esto es, la información que después ha de consolidarse en las tablas.  De esta forma el fichero MDF  (o los NDF’s ) no tienen porque contenter toda la información actualizada en un momento dado. Así pues, hagais lo que hagais NO BORREIS EL FICHERO DE LOG, que además de ser una barvaridad, os hará seguramente tener inconsistencias en la base de datos. Es más no se puede recuperar sin un comando totalmente indocumentado que usa soporte de microsoft.

Si es una estructura secuencial, y registra los cambios ¿debe crecer infinitamente?, la respuesta es depende, depende del modo de recuperación , si estamos en modo simple, en cuanto un proceso de baja prioridad que tiene SQL consolida la información del log en los datos pasa y salvaguarda los datos, esa información se puede sobre-escribir. ¿hay excepciones? SI, no se puede sobreescribir si hay una transacción antigua abierta, es decir si alguien hace, BEGIN TRAN, Update Tabla SET Campo=VAlor WHERE condicion, y se olvida de ejecutar el COMMIT TRAN o el ROLLBACK, sql no puede rehusar el trozo de log, porque no sabe que va a pasar con esa transacción y por tanto el fichero seguirá creciendo…  Si el modo de recuperación es sencillo se puede reaprovechar el log, como acabamos de ver, sin embargo, si algo falla, solo podremos recuperar el último backup completo (ningún backup del log puede garantizar que no se hallan reaprovechado trozos, y por tanto son inútiles, tanto que SQL no deja ni hacerlos ).

En el modo de recuperación completo y bulk logged solo se reaprovecharán estos trozos, si además de no quedar transacciones antiguas abiertas, se ha hecho copia de seguridad DEL LOG, es decir no BACKUP DATABASE sino BACKUP LOG.

Así pues ¿porque crece mi log de transacciones?,

Puede ser porque : Tienes una transacción muy antigua abierta (compruebalo con DBCC OPENTRAN en la BBDD que quieras hacer la operación), el comando devolverá algo así

Oldest active transaction:

SPID (server process ID): 53

UID (user ID) : -1

Name : user_transaction

LSN : (33:452:2)

Start time : Jan 5 2010 1:34:24:870AM

SID : 0×0105000000000005150000006ae2268ca3bb996b6e0e17c6e8030000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 Si no es esto ni la hubo, puede ser porque no hayas hecho nunca copias de seguridad del log de transacciones, en cuyo caso no se reaprovecha el trocito del log.

Bueno, ya sabes porque crece, pero ya ha crecido y ahora ¿como lo reduzco?.

Bueno, el tamaño del log depende del uso de la BBDD así que no hay una regla fija, pero vamos entre el 5 y el 40% de la BBDD es “aceptable” más es muy grande, y menos es muy raro, lo normal entre el 10% y el 15%, aunque insisto, que esto no te obsesione, que no hay reglas fijas.

Si tu tamaño es demasiado grande, reducirlo puede ser una tarea sencilla, prueba los siguientes pasos :

1.- Cambia a modo de recuperación Simple

2.- Ve al menú shrink y reduce el fichero de la bbdd.

¿puede que no se reduzca ni aún así? Si, puede que no se reduzca, el log es una estructura circular y no quita espacio del principio del fichero, sino solo del final así pues lo suyo es que reorganize las páginas antes.

Con esto en principio debiera reducirse, ahora, para que no vuelva a crecer, recuerda, o bien modo de recuperación sencillo (tendrá el tamaño de la transacción abierta que más haya durado y en la que concurrentemente más cosas se hayan hecho) o bien modo completo pero con copias de seguridad frecuentes. (varias al dia es lo habitual).

Saludos

Empezaremos este tercer artículo de la serie simplemente creando una tabla que alojará nuestros datos de tiempo en distintos idiomas, una vez rellenada esta tabla, puede servirnos perfectamente de base para la creación de una dimensión tiempo, que será el siguiente artículo, en el que comenzaremos a crear el proyecto de Analisys Services para construir la dimensión tiempo.

La tabla contiene la estrucutra básica para contener todos los datos necesarios relacionados con el tiempo , y además tiene la versión en castellano, francés e ingles de estos datos

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

CREATE TABLE [Dimensiones].[Tiempo](
[FechaKey] [int] not NULL primary key ,
[Fecha] [datetime] NULL,
[Año] [int] NULL,
[Numero_Mes] [int] NULL,
[Mes_Castellano] [nvarchar](30) NULL,
[Mes_año_Castellano] [nvarchar](35) NULL,
[MesKey] [int] NULL,
[Semana] [int] NULL,
[SemanaKey] [int] NULL,
[Fecha_Castellano] [nvarchar](52) NULL
[TipoDia] [varchar](13) NOT NULL,
[DiaDelAño] [int] NULL,
[DiaDeLaSemana] [int] NULL,
[DiaDelMes] [int] NULL,
[Dia_castellano] [nvarchar](30) NULL,<code>[Mes_Inglés] [nvarchar](30) NULL,
[Mes_año_Inglés] [nvarchar](35) NULL,
[Fecha_Inglés] [nvarchar](52) NULL,
[Dia_Inglés] [nvarchar](30) NULL,
 
[Mes_Francés] [nvarchar](30) NULL,
[Mes_año_Francés] [nvarchar](35) NULL,
[Fecha_Francés] [nvarchar](52) NULL,
[Dia_Francés] [nvarchar](30) NULL
 
) ON [PRIMARY]
 
GO

Una vez que tenemos la tabla tenemos que rellenarla para ello usaremos el mismo código que la vista del artículo anterior. En este caso si podemos utilizar las cláusulas que hablamos en el primer artículo SET DATE FIRST y SET LANGUAGE, de forma que todo esté adecuadamente configurado cuando hagamos los inserts y los updates corresponeientes. Veamnos como rellenar la tabla con las fechas que deseamos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

set language spanish;
set datefirst 1;
insert into dimensiones.tiempo
(FechaKey, Fecha, Año, Numero_Mes, Mes_Castellano, Mes_año_Castellano, MesKey, Semana, SemanaKey, Fecha_Castellano,
TipoDia, DiaDelAño, DiaDeLaSemana, DiaDelMes, Dia_castellano)
select
CAST(CONVERT(varchar(10),fecha + id ,112) as int) FechaKey,
fecha+id as Fecha,
YEAR(fecha+ID) as año,
MONTH(fecha+id) as Numero_Mes,
DATENAME(month,fecha+id) Mes_Castellano,
DATENAME(month,Fecha + id ) +' '+ cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Castellano,
year(Fecha + id )*100+month(Fecha + id ) MesKey,
DATEPART(DW,Fecha + id ) Semana,
year(Fecha + id )*100 + DATEPART(DW,Fecha + id ) SemanaKey,
cast(DAY(Fecha + id ) as varchar(10)) + ' de ' + DATENAME(MM,Fecha + id ) + ' de ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Castellano,
case when datepart(weekday,Fecha + id )>5 then 'Fin de semana' else 'Semana' end TipoDia,
datepart(dayofyear,Fecha + id ) DiaDelAño,
DATEPART(DW,Fecha + id ) DiaDeLaSemana,
DATEPART(dd,Fecha + id ) DiaDelMes,
DATENAME(dw,Fecha + id ) Dia_castellano</code>
 
from (select CAST('19991231' as datetime) fecha ) tabla
cross join nums;

Con este primer insert tendremos rellena la mayor parte de la tabla y los descriptores en castellano, sin embargo, aún no tenemos rellenados los campos en inglés y francés que pretendemos tener completos. Veamos el código para actualizar la parte en inglés

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

set language english;
 
update Dimensiones.Tiempo 
set Fecha_Inglés=a.Fecha_Inglés,
   Dia_Inglés=a.Dia_ingles,
   Mes_año_Inglés=a.Mes_año_Inglés,
   Mes_Inglés=a.Mes_Inglés 
from 
 (  select 
  CAST(CONVERT(varchar(10),fecha  + id ,112) as int) FechaKey,
  DATENAME(month,fecha+id) Mes_Inglés,
  DATENAME(month,Fecha + id ) +' '+  cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Inglés,
  DATENAME(MM,Fecha + id ) ++ ' ' +cast(DAY(Fecha + id ) as varchar(10)) +  ', ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Inglés,
  DATENAME(dw,Fecha + id ) Dia_ingles
 
  from (select CAST('19991231' as datetime) fecha ) tabla 
      cross join nums ) a inner join Dimensiones.Tiempo on a.FechaKey=Dimensiones.Tiempo.FechaKey

En francés tendremos que hacer algo parecido, simplemente hay que elegir de nuevo el idioma.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

set language français;
update Dimensiones.Tiempo 
set Fecha_Francés=a.Fecha_Francés,
   Dia_Francés=a.Dia_ingles,
   Mes_año_Francés=a.Mes_año_Francés,
   Mes_Francés=a.Mes_Francés 
from 
 (  select 
  CAST(CONVERT(varchar(10),fecha  + id ,112) as int) FechaKey,
  DATENAME(month,fecha+id) Mes_Francés,
  DATENAME(month,Fecha + id ) +' '+  cast(YEAR(Fecha + id ) as varchar(4)) Mes_año_Francés,
  DATENAME(MM,Fecha + id ) ++ ' ' +cast(DAY(Fecha + id ) as varchar(10)) +  ', ' + cast(YEAR(Fecha + id ) as varchar(4)) Fecha_Francés,
  DATENAME(dw,Fecha + id ) Dia_ingles
 
  from (select CAST('19991231' as datetime) fecha ) tabla 
      cross join nums ) a inner join Dimensiones.Tiempo on a.FechaKey=Dimensiones.Tiempo.FechaKey

Espero que os sirva el código.

Siguiendo con nuestra serie, hoy vamos a ver otro punto de vista, aún seguiremos usando CTE’s aunque el objetivo final es bien diferente, el objetivo es simplemente rellenar una tabla con números que después usaremos desde una vista, de esta forma podríamos usar esta vista para crear una dimensión de tipo tiempo. Esto es al fin y al cabo el objetivo de esta serie de artículos. Para hacerlo vamos a necesitar una tabla con los números del 1 al 10.000 y ahí es donde entra a jugar nuestra CTE, es una bastante típica, solamente que esta vez la usaremos para insertar esos diez mil registros. Vamos a ver un poquito de código que sirve para esto:

create table nums(id int not null primary key);
with mycte(id) as  (
 select 1 id
  union all 
select id+1 from mycte where id<10000  )
insert into nums (id)
select id from mycte option(maxrecursion 0);
 
 

Una vez que tenemos este código listo y por tanto tenemos nuestra tabla nums que contiene los valores del 1 al 10.000 vamos a usarla en una vista, la idea es bien sencilla, lo que haremos será elegir una fecha de inicio y sumar el valor de id, eso nos sumará el número de dias que estamos buscando. De esta forma tendremos 10.000 fechas disponibles lo que son haciendo una simple división más de 27 años. La idea de la vista es hacer join de una select que solo devolverá la fecha de inicio con la tabla de nums, obviamente no tenemos cláusula por la que unir pero eso no es problema, podemos usar un CROSS JOIN que no necesita claúsula ON. Veamos el código que nos servirá de base para nuestra vista.

SELECT

�

FECHA+id as Fecha FROM ( SELECT CAST('20000101' AS DATETIME) FECHA) t CROSS JOIN nums 

Esta consulta devuelve lo que puedes ver en esta imagen:

Esta es la idea obviamente, pero necesitamos  construir una vista que nos devuelva un resultado similar al que obteniamos en el artículo 1 de esta serie, la connotación aquí es ligeramente distinta porque no podemos asegurar en que valor está establecido el set datefirst ni el valor del lenguaje, por lo que resulta poco predecible,  aún así aquí teneis el código y una imagen del resultado…

CREATE
 

VIEW DimTiempo as 
select Fecha, 
CAST(CONVERT(varchar(10),fecha,112) as int) FechaKey,
YEAR(Fecha) as Año,
MONTH(Fecha) as Numero_Mes,
DATENAME(month,fecha) Mes_Castellano,
DATENAME(month,fecha) +' '+ cast(YEAR(fecha) as varchar(4)) Mes_ao_Castellano,
year(fecha)*100+month(fecha) MesKey,
DATEPART(DW,fecha) Semana,
year(FEcha)*100 + DATEPART(DW,fecha) SemanaKey,
cast(DAY(fecha) as varchar(10)) + ' de ' + DATENAME(MM,fecha) + ' de ' + cast(YEAR(fecha) as varchar(4)) Fecha_Castellano,
case when datepart(weekday,fecha)>5 then 'Fin de semana' else 'Semana' end TipoDia,
datepart(dayofyear,fecha) DiaDelAño,
DATEPART(DW,fecha) DiaDeLaSemana,
DATEPART(dd,fecha) DiaDelMes,
DATENAME(dw,fecha) Dia_castellano 
from 
(select CAST('20000101' as datetime) fecha ) tabla cross join nums

Esta vista devuelve el resultado que podeis ver en la siguiente imagen.

En la siguiente parte de esta serie, materializaremos esta vista en una tabla y pondremos tres idiomas dentro de nuestra tabla de tiempo, también generaremos un procedimiento almacenado para que todo esto se vea relleno. Al final de la serie pondremos una serie de artículos colgaré todos los script para que sirvan de referencia.