A menudo es importante conocer que tablas o vistas indexadas ocupan un mayor espacio en nuestras bases de datos. Es importante sabe que las vistas indexadas ocupan espacio, ya que mantienen una copia de los datos, por lo tanto tambien incrementan el numero de escrituras a discos cuando se realizan inserciones.
Por ejemplo, la información devuelta por este script es muy util para detectar situaciones de 'fugas de datos', en las que tablas que se limpian periodicamente o en respuesta a un evento, dejan de hacerlo y en consecuencia nuestra base de datos crece desmesuradamente. Para una de estas situaciones escribí el script que se encuentra abajo.
Tambien puede ser muy útil en otras situaciones, como por ejemplo detectar las tablas que más ocupan para optimizar su almacenamiento, o para detectar las tablas con más filas, para pensar en su particionado.
–Cursor que contiene todos los objetos que ocupan espacio
DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name FROM
sysobjects o
WHERE
o.xtype = 'S' or –Tablas de sistema
o.xtype = 'U' or –Tablas de usuario
o.xtype = 'V' –Vistas (solo las indexadas devuelven tamaño)
–Tabla temporal para albergar los resultados
CREATE TABLE #results
(name SYSNAME, rows CHAR(11),
reserved VARCHAR(18), data VARCHAR(18),
index_size VARCHAR(18),Unused VARCHAR(18))
–Recorremos el cursor obteniendo la información de espacio ocupado
DECLARE @object_name AS SYSNAME
OPEN objects_cursor
FETCH NEXT FROM objects_cursor
INTO @object_name;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #results
EXEC sp_spaceused @object_name
FETCH NEXT FROM objects_cursor
INTO @object_name;
END;
CLOSE objects_cursor;
DEALLOCATE objects_cursor;
— Quitamos "KB" para poder ordenar
UPDATE
#results
SET
reserved = LEFT(reserved,LEN(reserved)-3),
data = LEFT(data,LEN(data)-3),
index_size = LEFT(index_size,LEN(index_size)-3),
Unused = LEFT(Unused,LEN(Unused)-3)
–Ordenamos la información por el tamaño ocupado
SELECT
Name,
reserved AS [Tamaño en Disco (KB)],
data AS [Datos (KB)],
index_size AS [Indices (KB)],
Unused AS [No usado (KB)],
Rows AS Filas FROM #results
ORDER BY
CONVERT(bigint, reserved) DESC
–Eliminar la tabla temporal
DROP TABLE #results
Reseñar que el script anterior solo funciona en SQL Server 2000, para que funcione en SQL Server 2005, tenemos que modificar la declaración del cursor. Esto es debido a que en SQL Server, el procedimiento almacenado sp_spaceused espera el nombre de los objetos calificados con el nombre del esquema al que pertnenecen. Además los tipos de objetos que devuelven en SQL Server información sobre su tamaño son más. En SQL Server 2005 debemos hacer la declaración del cursor como sigue:
DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY for
SELECT s.name + '.' + o.name from sys.schemas s
INNER JOIN sys.objects o
ON o.schema_id = s.schema_id
WHERE
o.type = 'S' or –Tablas de sistema
o.type = 'U' or –Tablas de usuario
o.type = 'V' or –Vistas (solo las indexadas devuelven tamaño)
o.type = 'SQ' or –Cola de servicio
o.type = 'IT' — Tablas internas usadas p.e. por el Service Broker o los indices XML
Pues no me funciono con mis bases me marca error en las vistas del sistema
En SQL Server 2005 o en SQL Server 2000. Piensa que para 2005 tienes que modificar el script como cuento al final del post.
Muchas gracias, funciona perfectamente
me parce super util este script pero quisiera saber como hago para utilizarlo en todas mis BD sin necesidad de ejecutarlo en cada una, osea que me extraiga la informacion en una sola tabla temporal de todas las tablas de todas las BD de mi servidor, gracias
Gracias Rodrigo,
realmente me funciono, aunque dejame comentar que estoy utilizando sql 2000 y el script tal cual me dio error, esto debido a la inclusion de vistas indizadas, solamente le quite que me incluyera estas vistas indizadas y me obtuvo la información que requeria. el mensaje que obtenia era «Las presentaciones no tienen espacio asignado».
Saludos
Nixon A. Morales
namorales@hotmail.com
De lujo, gracias tio!!!
Ejecutado en un 2005 con la modi. que indicaste y como la seda.!!! Bestial!
funciona muy bacano.
Gracias por tu aportación es muy util
Grande capo !!!!
muy bueno
Hola que tal, corri el script en un sql 2000 pero hubo algunos errores que me marco. y despues cheque el espacion de la bd y sigue igual no me afecto nada. me urge disminuir el espacio, que mas puedo hacer.
mi correo: ingfranciscom@gmail.com cualquier sugerencia es bienvenida.
Gracias !!!
Probé en 2000 y 2005 y funcionan muy bien, gracias por el script. Sigue adelante.
Me resulto muy útil. Sigue adelante
felicito
Cordialmente
AQ
Viejo Men.. excelente te felicito muy bueno el script. Inicialmente no me funciono porque no tenia vistas indexadas y me salia que las vistas no tienen espacio. Pero lo solucione quitando del where del cursor el filtro de las vistas.
Mil gracias por este script.
excelente y maravilloso doc
Muy bueno tio, me salvaste la vida
Funciono …Interesante depronto tienes algo parecido para sybase o lo has visto en algun foro.. gracias raul_orlando@hotmail.com
MUCHAS GRACIAS X TU EFICIENTE AYUDA.
YO TAMB COMENTE LA LINEA DE o.xtype = ‘V’ y jaló muy bien el script.
Excelente!!!!!!!!!
De lujo. Me funcionó como un tiro. Muchísimas Gracias
Gracias!!! Buenisimo.
Excelente aporte, me sirvio de maravilla!
Mil gracias
me marco unos errores pero me dio la informacion que necesitaba.
Muy buen script, es casi de cabecera 😉
Gracias
Hola, Como estan. Muy buen script, pero como el dicho dice: «si usas cursores….
Entonces no sabes SQL…»
Basandome en ese dicho (y con el aporte de este post, ofcourse) diseñe una consulta uq hace lo mismo y unas cuantas cosas mas.
Aca se los dejo
select name
,(select top 1 row_count from sys.dm_db_partition_stats where object_id=s.object_id)
,modify_date
,(select convert(decimal(8,2),(SUM ( CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END )*0.0078125 )) FROM sys.dm_db_partition_stats WHERE object_id = s.object_id) as PESO_TABLA from sys.tables s where type_desc = 'USER_TABLE' order by PESO_TABLA desc Esta diseñado en 2k5, no lo probe antes, aca en el trabajo si cargamos al servidor de produccion nos aplican, asi que nada de eso. Solo Scripts livianos. Bueno se cuidan saludos. Jim!
Eres un zadico maldito, buscate una novia
excelente, gracias por la aportacion
Excelente query funca al 100% en sql 2005.
Muy buen aporte