Como reducir los logs de una base de datos en Sql Server 2008

Aquí tenemos un sencillo script para reducir los logs de una base de datos de Sql Server 2008:

USE MiBd
CHECKPOINT
GO
ALTER DATABASE MiBd SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (N'MiBd_Log' , 0, TRUNCATEONLY)
GO
ALTER DATABASE MiBd SET RECOVERY FULL
GO

Script para reorganizar todos los índices de una base de datos Sql Server (2)

Hace unos días os comentábamos un pequeño script para reorganizar todos los índices de una base de datos de Sql Server. Navegando por la red me he encontrado con una única instrucción que, haciendo uso de sp_MSforeachtable permite realizar la misma función

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Si no queremos que muestre tantos mensajes (sólo el nombre de los índices):

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80) WITH NO_INFOMSGS"

Usos del stored procedure sp_MSforeachtable

Sql Server tiene un procedimiento almacenado no documentado llamado sp_MSforeachtable que nos permite pasarle un comando a todas las tablas que tenemos en una base de datos. En 8 common uses of undocumented Stored Procedure sp_MSforeachtable muestran una lista de 8 operaciones que podemos realizar de forma muy sencilla gracias a este procedimiento almacenado. Algunos de ellos:

Saber el tamaño utilizado por todas las tablas

USE NORTHWIND

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Reconstruir todos los índices

USE YOURDBNAME
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

Desactivar todas las restricciones de todas las tablas

USE YOURDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Desactivar todos los triggers de todas las tablas

USE YOURDBNAME
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

Script para reorganizar todos los índices de una base de datos Sql Server

Sql Server nos proporciona un comando que nos permite reorganizar un índice de una tabla (reindexar). Este comando es DBCC DBREINDEX donde le debemos especificar el nombre de la tabla, el nombre del índice y el factor de relleno. Por ejemplo:

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)

En este ejmplo le estamos indicando que queremos reorganizar el índice UPKCL_auidind de la tabla authors de la base de datos pubs. Reorganizar es un índice es una operación que hay que realizar de vez en cuando ya que, a medida que se va trabajando con una tabla, el índice se va fragmentando y por tanto, va perdiendo eficacia.

Pero ¿se pueden reorganizar todos los índices de todas las tablas de una base de datos? Recurriendo a un sencillo script Sql podemos hacerlo:

DECLARE @Tabla sysname
DECLARE contTabla CURSOR LOCAL FOR SELECT name FROM sysobjects WHERE OBJECTPROPERTY(id,N'IsUserTable') = 1
OPEN contTabla
FETCH NEXT FROM contTabla INTO @Tabla
WHILE @@FETCH_STATUS = 0
BEGIN
 SET NOCOUNT ON
 DBCC DBREINDEX(@Tabla,'',0) WITH NO_INFOMSGS
 FETCH NEXT FROM contTabla INTO @Tabla
END
CLOSE contTabla
DEALLOCATE contTabla

Lo que hacemos es buscar todas las tablas que tenemos y reorganizar dentro de un bucle todos sus índices. Si al comando DBCC DBREINDEX no se le especifica el nombre del índice (se deja vacío), reorganiza todos los índices que tiene la tabla. Además, le añadimos la parte de WITH NO_INFOMSGS para que no muestre mensajes por pantalla.

Como medir tiempos de ejecución de un script sql

Muchas veces suele ocurrir el siguiente escenario: tenemos un script o una consulta en Sql y queremos saber cuanto tarda, especialmente si queremos compararlo con otro que hace algo similar. Pero ¿como podemos medir el tiempo de ejecución?

Yo suelo utilizar un método sencillo que hasta el momento me ha dato buen resultado. Consiste en utilizar la función getdate() antes y después de ejecutar el script, y realizando una sencilla resta tendremos el tiempo de ejecución. Un ejemplo:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

select getdate()
aqui ponemos nuestro código sql
select getdate()

Al comienzo del script se llama a dos comandos DBCC que lo que nos hace es eliminar todos los datos de buffers y caches que tengamos para que no se puedan falsear los tiempos de las consultas. Por último, deciros que normalmente yo ejecuto varias veces las consultas y suelo sacar tiempos medios.

Como crear un autonumérico en Sql Server

Una de las cosas que me gustan de Access es la posibilidad de trabajar con campos autonuméricos. Necesitas hace runa tabla con un campo único que sea clave y te creas un autonumérico, que se incrementa automáticamente sin que tengas que hacer nada.

En Sql Server no existe el tipo de campo autonumérico aunque si hay una manera de crearte un campo autonumérico. Creamos un campo de tipo int y añadimos la claúsula IDENTITY:


CREATE TABLE Prueba (
Contador INT IDENTITY(1, 1) NOT NULL,
campo1 varchar(30)
)


Al comando identity se le indica en que valor comienza y el valor del incremento. Por ejemplo, si queremos que comience en 20 y se incremente de dos en dos escribiremos: IDENTITY(20,2)

Como quitar manualmente una instancia de Microsoft SQL Server 2000 Desktop Engine (MSDE 2000)

Si alguna vez habés intentado desinstalar el MSDE os habréis dado cuenta que, o no hay manera de quitarla, o se quedan componentes y bastante mierda por el sistema. ¿Se puede eliminar de forma sencilla? Microsoft ha publicado un completo resumen titulado Cómo quitar manualmente una instancia de Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). Además, tenemos un completo post en el Blog de TresW con los pasos que tenemos que llevar a cabo para quitar el MSDE.

Activar y desactivar los triggers de una tabla

Los triggers son unos complementos que resultan muy útil cuando se trabaja con tablas en Sql. Sin embargo, a veces queremos hacer alguna operación que sabemos que no es correcta del todo, y que algún trigger que tenemos no nos deja (para eso se utilizan, claro). ¿se pueden desactivar temporalmente? Pues la respuesta es si. Basta con que pongamos:

ALTER TABLE YourTable DISABLE TRIGGER ALL

Para activarlos de nuevo sólo tenemos que escribir:

ALTER TABLE YourTable ENABLE TRIGGER ALL

Como nota personal sólo deciros que estas cosas hay que hacerlas con mucho cuidado y sobre todo, acordarse de volver a activarlos de nuevo... o se puede liar parda (jejeje).

Cambiar la fecha de creación de una base de datos en sql server

Si necesitas cambiar la fecha de creación de una base de datos en Sql Server (es la que aparece en la columna crdate de la tabal sysdatabases de la base de datos master) puedes ejecutar el siguiente script:

exec sp_configure 'allow updates',1
reconfigure with override
go
select crdate from sysdatabases where name='MIBD'
update master.dbo.sysdatabases set crdate=getdate() where name='MIBD'
select crdate from sysdatabases where name='MIBD'
go
exec sp_configure 'allow updates',1
reconfigure with override


Así evitarás los mensajes de error y verás como tu base de datos tiene la fecha actual.

Libro gratuito de Sql Server 2008

Si eres de los que estás peleándote y aprendiendo las novedades del nuevo Sql Server 2008, puedes descargarte de forma gratuita el ebook llamado Introducing Sql Server 2008. Allí podrás encontrar los siguientes capítulos:

Capítulo 1: Seguridad y Administración
Capítulo 2: Performance
Capítulo 3: Type System
Capítulo 4: Programación
Capítulo 5: Almacenamiento
Capítulo 6: Mejoras para Alta Disponibilidad
Capítulo 7: Mejoras para Inteligencia de Negocios

Visto en Fake Plastic

Consulta Sql para devolver los nombre de las bases de datos en Sql Server

Alguna que otra vez, desde algún programa, aparece la necesidad de conocer que bases de datos existen en el servidor Sql para poder crear una nueva o no crearla. Para saber las bases de datos que tenemos en Sql Server podemos utilizar una sencilla consulta:

select name from master..sysdatabases

Esta consulta sql nos devuelve un registro por cada una de las base de datos que tengamos instaladas en nuestro servidor Sql Server.

La función isnull en Sql

La función isnull es una de las que más suelo utilizar a menudo. ¿Para que sirve? Para reemplazar los valores nulos por uno que nosotros digamos. Por ejemplo, vamos a imaginar que tenemos una tabla que se llama facturas y un campo que se llama valor. Y queremos una sencilla consulta que nos diga la suma de estas facturas. Aquí haríamos algo así:

select sum(valor) from facturas

Pero claro ¿y que ocurre si ese campo valor tiene nulos? Pues que tendremos problemas... pero para evitarlos tenemos el isnull. Lo que vamos a hacer es decirle que si el campo valor es nulo, lo reemplazaremos por un cero ¿como? así de facil

isnull(valor,0)

Es decir, le ponemos el nombre del campo y el valor por el que se van a reemplazar los nulos. Con esto, nuestra consulta quedaría:

select sum(isnull(valor,0)) from facturas

Lo bueno del isnull es que se puede emplear casi en cualquier sitio, como por ejemplo en la parte where de la consulta. Por ejemplo, ahora vamos a sacar las facturas que no tengan valor o sea cero:

select * where isnull(valor,0)=0

Así de sencillo. Con isnull podrás evitar el problemático uso de los nulos que tantos dolores de cabeza suelen producir.

Devoler la intercalación de una base de datos de Sql Server

Alguna vez me ha ocurrido que he tenido problemas con alguna base de datos de que tiene una intercalación diferente de la que tengo en el servidor de Sql Server. El famoso collate da algún que otro dolor de cabeza, y al final, siempre intento que todas las bases de datos tengan Modern_Spanish_CI_AS para evitar problemas. Pero ¿como podemos saber con una consulta Sql la intercalación de una base de datos?. Utilizando la siguiente consulta vamos a ver la intercalación de la base de datos Northwind:

SELECT DATABASEPROPERTYEX('Northwind','Collation') AS Database_Default_Collation

Para sacar la intercalación de cualquier otra base de datos sólo necesitas cambiar Northwind por el nombre de la que te interese.

Más información sobre la intercalación en Msdn

Ver los logs de una base de datos en Sql Server

¿Tienes algún problema con tu base de datos y necesitas revisar los logs? Pues lo tienes sencillo. Ejecuta:

SELECT * FROM ::fn_dblog(NULL, NULL)

y te aparecerán todos los logs de tu base de datos. Eso sí, seguir los logs ya no será tan sencillo como esto ;-)

Reducir los logs de una base de datos en Sql Server

Comienzas a trabajar con una base de datos y claro, crece, crece, crece... y cuando te quieres dar cuenta, los logs ocupan varios gigas en tu disco duro. Y claro, ¿hay alguna manera de reducir esos logs y recuperar ese espacio en disco? La respuesta es ejecutar la siguiente consulta sql:

CHECKPOINT
BACKUP LOG NORTHWIND WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (NORTHWIND, TRUNCATEONLY )

Lo que hacemos primero es un checkpoint, es decir, terminamos todo lo que tengamos pendiente para no dejar información colgada. Luego, ejecutamos el backup y el dbcc shrinkdatabase, que será el encargado de reducir esos logs y recuperar esos gigas de tu disco duro.

Consulta Sql para conocer las tablas y vistas de una base de datos

En Sql Server tenemos una sencilla consulta que nos devolverá las tablas y vistas de una base de datos:

SELECT * from Information_Schema.Tables

De aquí nos interesa el campo table_name (nombre de la tabla) y table_type (nos dice si es una tabla o una vista). Por tanto, filtrar por tablas o vistas es bastante sencillo (con el campo table_type).

Para saber si existe una tabla en la base de datos (también nos sirve para las vistas) podemos utilizar la siguiente consulta (vamos a consultar si en Northwind existe la tabla 'Customers'):

SELECT * from Information_Schema.Tables where table_name='Customers'

Si esta consulta nos devuelve registros es que existe la tabla (o vista) y si nos devuelve vacío, es que no existe.