SQL Server - Calculo de Espacio que se puede ganar.

 
Vista:

Calculo de Espacio que se puede ganar.

Publicado por Angel (6 intervenciones) el 17/09/2020 21:36:15
Hola colegas.

Tengo una base de datos que mide mas o menos 1.5 Tb.

El backup de esta base mide mas o menos 300 Gb.

Estamos haciendo una migracion hacia un nuevo sistema de almacenamiento y me han pedido saber cuanto espacio necesitariamos para mover esta base de datos.

Actualmente se tiene un cluster storage de poco mas de 4 Tb para ella, sin embargo me han pedido especial enfasis en el espacio que se pueda llegar a ocupar.

Al mover todas las bases de datos incluyendo las del sistema, me pregunta cual es el espacio que se puede llegar a ganar si reducimos el tamanho actual de la base.

Esto con el fin de solo asignar lo justo al nuevo drive.

Teniendo en cuenta que anualmente se gana mas o menos 150 Gb en espacio

Es posible saber cuando espacio se pudria llegar a ganar, a travez de algun query, funcion, store procedure?

Tambien me preguntan cuando tiempo podria tomar reconstruir los indices?

Hay alguna forma de estimar el tiempo que podria tomar reducir la base de datos, moverla y recontruir los indices?


Muchas gracias por la ayuda.
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Isaias
Val: 3.250
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Calculo de Espacio que se puede ganar.

Publicado por Isaias (4392 intervenciones) el 18/09/2020 02:02:01
Veamos si me quedo claro lo que necesitas

Vas a mover tu(s) base(s) a un nuevo drive y solo le quieren asignar el "espacio necesario" para estas bases, ¿como puedo saber cuanto espacio ocupan mis bases?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

Yo le agregaría al resultado un 30% de crecimiento

Tambien me preguntan cuando tiempo podria tomar reconstruir los indices?

Dependerá de la cantidad de indices dañados por encima del 30% de daño y el numero de paginas > 500

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
/*----------------------------------------------
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

Todo el código se entrega bajo la premisa "AS-IS" y este ultimo, también actualiza estadísticos

Otra opción para determinar los tamaños de las bases
1
2
3
4
5
6
7
8
9
10
11
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
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Gracias por la res

Publicado por Angel (6 intervenciones) el 18/09/2020 15:23:06
Muchas gracias por la respuesta y por los scripts, esto me ayuda mucho a realizar mi analizis.

Una pregunta. Durante el proceso de migracion, debere hacer un shrinkfile sobre los files de bases de datos.

Hay alguna manera de calcular cuanto espacio se puede recuperar de este proceso.

Otro colega realizo un liberacion de espacio de logs, y aparentemente recupero como 3Gb de espacio.

No tengo muy claro que hizo, pero por lo visto se puede hacer shrink de logs y recuperar espacio.

Muchas gracias por la ayuda.

Buen dia.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 3.250
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Gracias por la res

Publicado por Isaias (4392 intervenciones) el 18/09/2020 16:34:31
Antes que todo, crea un respaldo FULL de tus bases.

Hablas de MIGRACION, ¿No solo es cambio de disco?

Si vas a: Task -> Shrink -> Files -> Selecciona el LOG y abajo te dara el porcentaje y espacio que va a recuperar


Captura
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Gracias por la res

Publicado por Angel (6 intervenciones) el 18/09/2020 16:55:38
Lo que se piensa hacer es un cambio total de los discos.

Las bases de datos estan instaladas sobre un sistema de virtual storage y quieren renovarlo por que la version actual esta ya dando muchos problemas.

Entonces estan pidiendo que se muevan todas las bases de datos a una nuevo storage. Estoy incluyde las bases del sistema: master, model, msdb y el temp, un par de bases de datos pequenas y el elefante de 1.5 Tb.

Solo el elefante possee 4 file sistem asignados a el, que en suma tiene 4Tb para poder crecer. El problema es el costo del storage, parte del cual se ha recuperado, pero por las dimensciones de la base de datos que tenemos yo pensaria no reducir tamanho, pero me piden que se reduzca el tamanho y poder pedir una cantidad en estorage mas pequena a instalar en el nuevo sistema de almacenamiento ($$)

Logs: 300 Gb
File 1: 700Gb
File 2: 800 Gb
Tmp: 30 files de 5 Gb cada uno.

El file 2 es basicamente solo una tabla.

Entonces no estoy migrando a otro servidor, pero basicamente estoy moviendo la base de datos de un lugar a otro. Lo que conllevara un tiempo de innaccesibilidad, mas el moviento, mas la reconstruccion de indices. Todo paso a paso.

Muchas gracias.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 3.250
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Gracias por la res

Publicado por Isaias (4392 intervenciones) el 18/09/2020 20:14:17
Ok, entonces NO ES MIGRACION, cuando hablas de este tema se refiere a que instancia esta en 2008 R2, por ejemplo y la quieres migrar a 2016.

A que te refieres con ¿Tmp: 30 files de 5 Gb cada uno?, estamos hablando de la base TEMPDB

La base TEMPDB, no la mueves, se crea cada que SQL Server se arranca.

¿Ya viste cuanto puedes reducir con el SHRINK?

¿Dónde radicas?
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Gracias por la respuesta

Publicado por Angel (6 intervenciones) el 21/09/2020 18:44:47
Gracias por la ayuda.

Si son 30 files de TempDB, de la base de datos en cuestion, he visto que podemos recuperar unicamente 17% haciendo un shrink file sobre los datos.

De los logs, menciona que se puede tener hasta un 99%.

Es que hay otros archivos que pueden comprimirse con seguridad?

Me hacen mencion de los logs y de si se puede hacer un cleanup de tablas, yo he visto que hay muchas tablas que se pueden suprimir de la base de datos, ya que su ultima actualizacion has sido desde 2014, lo que aun no estoy seguro es sobre la integridad refencial de esas tablas o las posibles dependencias que estan puedan posser en la tabla principal. De todas maneras eliminandolas no se recupera gran cosa.

Estoy tratando de crean un ambiente de prueba para poder llevar a cabo un par de pruebas de movimiento y ver si los calculos de shrink son los correctos, ademas poder estimar cuanto tiempo llevaria poder hacer eso.

Me encuentro radicaco en Montreal Quebec.

Muchas gracias por la informacion.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 3.250
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Gracias por la respuesta

Publicado por Isaias (4392 intervenciones) el 21/09/2020 21:02:09
La base TEMPDB debe tener como máximo OCHO ARCHIVOS (MDF, NDF) y un solo LOG (LDF), este numero es proporcional al numero de procesadores de sus servidor, no se de donde saca tantos archivos.

¿cleanup de tablas?, No se a que se refiere con esto.

¿Tabla principal?

Espero que todo vaya bien en cuanto a lo que esta planeando, no es gran cosa, baja los servicios de SQL Server, copia TODAS sus bases, incluidas las del sistema (menos TEMPDB).

Cambia el modo de arranque de SQL Server a MANUAL

Coloca los nuevos discos

Copia sus MDF y LDF en las rutas donde se encontraban originalmente

Levanta el servicio de SQL Server y todo debe funcionar.

Saludos
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Gracias por la respuesta

Publicado por angel (6 intervenciones) el 21/09/2020 21:10:59
Muchas gracias por la information y los scritps.

Exitos.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Gracias por la respuesta

Publicado por Angel (6 intervenciones) el 08/10/2020 19:46:31
Hola

Muchas gracias por la informacion brindada.

Finalmente logramos hacer ciertas pruebas y todo funciono bien.

El shrink de logs nos brindo casi 700 Mb de espacio, el shrink de datafiles por otro lado, casi no brindo nada.

Esto fue solo una prueba con diferentes tipos de sistemas de almacenamientos para poder brindar un tiempo bien medido y hacer comparaciones.

Surgen un par de preguntas:

1. Ya que quedo en evidencia que el shrink de logs nos ha devuelto una buena porcion de espacio, el shrink de datafiles, no es necesario?
2. Si no se lleva a cabo el shrink de datafiles, una reconstruccion de indices no es necesaria, verdad?
3. El espacio devuelto gracias al shrink de logs no debe de ser indicativo de un reajuste de espacio en los logs, ni antes ni despues del shrink? ya que el nivel de transaccionalidad es alto por el momento en esa base de datos, lo que indica que el espacio debera mantenerse tal cual sin modificacion hasta que se haya hecho el shrink y que el sistema este en linea de nuevo, verdad?

Muchas gracias.

Angel
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Isaias
Val: 3.250
Oro
Ha mantenido su posición en SQL Server (en relación al último mes)
Gráfica de SQL Server

Gracias por la respuesta

Publicado por Isaias (4392 intervenciones) el 08/10/2020 21:56:28
1. Ya que quedo en evidencia que el shrink de logs nos ha devuelto una buena porcion de espacio, el shrink de datafiles, no es necesario?
R= No es necesario hacer SHRINK a la base

2. Si no se lleva a cabo el shrink de datafiles, una reconstruccion de índices no es necesaria, verdad?
R= La reconstruccion de los índices debe ser parte del PLAN DE MANTENIMIENTO, es para dar un mejor tiempo de respuesta

3. El espacio devuelto gracias al shrink de logs no debe de ser indicativo de un reajuste de espacio en los logs, ni antes ni despues del shrink? ya que el nivel de transaccionalidad es alto por el momento en esa base de datos, lo que indica que el espacio debera mantenerse tal cual sin modificacion hasta que se haya hecho el shrink y que el sistema este en linea de nuevo, verdad?

R=El SHRINKFILE devuelve el espacio liberado al sistema operativo, el log se comporta como un acordeón, crece y decrece, dependiendo de la transaccionalidad.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar