Desfragmentar todas las BDs

[Missi]

Hola...

Supongo que conocereis el famoso script de ejemplo en la ayuda para Desfragmentar los Indices de todas las tablas de una BD.

http://technet.microsoft.com/es-es/l.../ms175008.aspx

La cuestión es ... como se podría hacer para que los haga para todas las BDs de un servidor.

Estoy intentándolo crear con un cursor y hacer algo parecido pero no llego a conseguirlo ya que no me hace el 'use' de las Bases de Datos correctamente...

Código:

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
   SELECT [name]  
   FROM master..sysdatabases 
   WHERE [name] NOT IN ('model', 'tempdb') 
   ORDER BY [name]

Dentro del cursor ejecutaría el famoso script

Código:

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(255);
DECLARE @execstr   varchar(400);
DECLARE @objectid  int;
DECLARE @indexid   int;
DECLARE @frag      decimal;
DECLARE @maxfrag   decimal;

-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;

-- Declare a cursor.
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + '.' + TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE';

-- Create the table.
CREATE TABLE #fraglist (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

-- Open the cursor.
OPEN tables;

-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
   FETCH NEXT
      FROM tables
      INTO @tablename;
END;

-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;

-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

-- Open the cursor.
OPEN indexes;

-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%';
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')';
   EXEC (@execstr);

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

[Libras]

creo que no se puede meter un use dentro de un procedure o dentro de un query dinamico, tendrias que hacerlo como mencionan en esta liga:

http://stackoverflow.com/questions/1...-with-variable

saludos!

[iislas]

Intenta utilizar el store procedure no documentado:

EXEC sp_msforeachdb 'use [?];....................................

Ademas de que el ejemplo que muestras, esa para la version 2000, ¿de acuerdo?

[Missi]

Gracias por vuestras respuestas modificaré el script y os comento que tal.

El ejemplo es para sql2000, era consciente de ello.

[matanga]

Antes de codificar cualquier procedimiento, evalúa la posibilidad de crear un plan de mantenimiento que ya incluye la opción para desfragmentar los índices de una o todas las bases de datos.

...Estoy intentándolo crear con un cursor y hacer algo parecido pero no llego a conseguirlo ya que no me hace el 'use' de las Bases de Datos correctamente...

Esto es un error muy común, el comando USE dentro de un SQL dinámico solo tiene ámbito durante la ejecución de ese SQL dinámico, es decir, si tienes la tabla T1 en la base DB1 y la tabla T2 en la base DB2, el comportamiento es:

Código:

--código con error, el contexto del segundo exec es DB1
use DB1
exec sp_executesql N'use DB2'
exec sp_executesql N'select * from T2'
go

--código correcto.
use DB1
exec sp_executesql N'use DB2 select * from T2'
go

Saludos

[iislas]

MATANGA

El problema al parecer, es que cuenta con la version 2000

[matanga]

Iniciado por iislas

MATANGA

El problema al parecer, es que cuenta con la version 2000

Me faltó más atención, respondí sin tener en cuenta la versión, de todos modos en este caso tuve suerte, en SQL Server 2000 ya existían los planes de mantenimiento para índices y también el sp_executesql.

Saludos

[iislas]

Sinceramente, no lo recuerdo, ya hace demasiado tiempo.....

[Missi]

Lo que buscaba es reorganizar o reconstruir dependiendo el nivel de fragmentación de los indices. (@maxfrag = 30.0)

SQL Server 2000 en los planes de mantenimiento no permite esa opción.

Con versiones posteriores a 2000 ya hace diferencia entre reorganizar y reconstruir indices pero no comprueba previamente el nivel de fragmentación para hacer una cosa u otra tal como te aconsejan en la misma ayuda de sqlserver (Books Online)