SQL Server - Calculo de Espacio que se puede ganar.

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

-- Uncomment if you need to query for a particular database

-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(database_id), Type_Desc),

                     (DB_NAME(database_id))

              )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

/*----------------------------------------------

Reconstruye los indices dañados por mas del 30%

y con mas de 50 paginas por los indices

IIslas (DR) Mexico

*/----------------------------------------------

IF OBJECT_ID('tempdb..#work_to_do') IS NOT NULL

        DROP TABLE tempdb..#work_to_do

BEGIN TRY

--BEGIN TRAN

use YOURDATABASE -- Cambie el nombre de su base de datos

-- Modifico el tipo de recuperacion a SIMPLE

ALTER DATABASE super14 SET RECOVERY SIMPLE

    SET NOCOUNT ON;

    DECLARE @objectid INT;

    DECLARE @indexid INT;

    DECLARE @partitioncount BIGINT;

    DECLARE @schemaname NVARCHAR(130);

    DECLARE @objectname NVARCHAR(130);

    DECLARE @indexname NVARCHAR(130);

    DECLARE @partitionnum BIGINT;

    DECLARE @partitions BIGINT;

    DECLARE @frag FLOAT;

    DECLARE @pagecount INT;

    DECLARE @command NVARCHAR(4000);

    DECLARE @page_count_minimum SMALLINT

    SET @page_count_minimum = 500

    DECLARE @fragmentation_minimum FLOAT

    SET @fragmentation_minimum = 30.0

-- Seleccione condicionalmente tablas e índices de la función sys.dm_db_index_physical_stats

-- y convertir ID de índice y objeto en nombres.

    SELECT  object_id AS objectid ,

            index_id AS indexid ,

            partition_number AS partitionnum ,

            avg_fragmentation_in_percent AS frag ,

            page_count AS page_count

    INTO    #work_to_do

    FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,

                                           'LIMITED')

    WHERE   avg_fragmentation_in_percent > @fragmentation_minimum

            AND index_id > 0

            AND page_count > @page_count_minimum;

IF CURSOR_STATUS('global', 'partitions') >= -1

BEGIN

 PRINT 'partitions CURSOR DELETED' ;

    CLOSE partitions

    DEALLOCATE partitions

END

-- Declare el cursor para la lista de particiones a procesar.

    DECLARE partitions CURSOR LOCAL

    FOR

        SELECT  *

        FROM    #work_to_do;

-- Se abre el cursor.

    OPEN partitions;

-- Recorre la particións.

    WHILE ( 1 = 1 )

        BEGIN;

            FETCH NEXT

	    FROM partitions

	    INTO @objectid, @indexid, @partitionnum, @frag, @pagecount;

            IF @@FETCH_STATUS < 0

                BREAK;

            SELECT  @objectname = QUOTENAME(o.name) ,

                    @schemaname = QUOTENAME(s.name)

            FROM    sys.objects AS o

                    JOIN sys.schemas AS s ON s.schema_id = o.schema_id

            WHERE   o.object_id = @objectid;

            SELECT  @indexname = QUOTENAME(name)

            FROM    sys.indexes

            WHERE   object_id = @objectid

                    AND index_id = @indexid;

            SELECT  @partitioncount = COUNT(*)

            FROM    sys.partitions

            WHERE   object_id = @objectid

                    AND index_id = @indexid;

            SET @command = N'ALTER INDEX ' + @indexname + N' ON '

                + @schemaname + N'.' + @objectname + N' REBUILD';

            IF @partitioncount > 1

                SET @command = @command + N' PARTITION='

                    + CAST(@partitionnum AS NVARCHAR(10));

            EXEC (@command);

            -- print (@command); //uncomment for testing

	    -- Si dan las 7 am y no ha terminado, termina el ciclo

	    IF DATEPART(HH,GETDATE()) = 6

                BREAK;

            PRINT N'Rebuilding index ' + @indexname + ' on table '

                + @objectname;

            PRINT N'  Fragmentation: ' + CAST(@frag AS VARCHAR(15));

            PRINT N'  Page Count:    ' + CAST(@pagecount AS VARCHAR(15));

            PRINT N' ';

        END;

-- Cierra y elimina el cursor.

    CLOSE partitions;

    DEALLOCATE partitions;

-- Regreso la base a FULL en su tipo de recuperacion

    ALTER DATABASE YOURDATABASE SET RECOVERY FULL  -- Cambie el nombre de su base de datos

-- Elimina la tabla temporal.

    DROP TABLE #work_to_do;

--COMMIT TRAN

-- Actualizo estadisticos

EXEC sp_updatestats

END TRY

BEGIN CATCH

--ROLLBACK TRAN

    PRINT 'Se encontro un error:' + ERROR_MESSAGE()

END CATCH

SELECT

    DB_NAME(db.database_id) DatabaseName,

    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,

    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,

    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,

    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB

FROM sys.databases db

    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id

    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id

    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id

    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id