.::: Sample Shrink Database SQL Server MSSQL :::.

 

Correlation shrink log http://teguhth.blogspot.com/2023/05/how-to-shrink-log-database-sql-server.html

simulation shrink database

1. Create table & insert 5 million row for lab

CREATE TABLE TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50),
    CreatedDate DATETIME
);

2. insert data dummy

-- Gunakan WHILE Loop untuk mengisi data sebanyak 1 juta baris
DECLARE @Counter INT = 1;

WHILE @Counter <= 20000000
BEGIN
    INSERT INTO TestTable (Name, CreatedDate)
    VALUES (CONCAT('Name-', @Counter), GETDATE());
    SET @Counter = @Counter + 1;
END;

-- or using

INSERT INTO TestTable (Name, CreatedDate)
SELECT TOP (20000000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowID,
    GETDATE() AS CreatedDate
FROM master.dbo.spt_values t1
CROSS JOIN master.dbo.spt_values t2;
 

Lakukan Shrink Database Setelah data ditambahkan, Anda dapat melakukan shrink untuk mengurangi ukuran database. Gunakan perintah berikut:
 

3. check size & identification shrink before delete & shrink

DECLARE @database_id int
DECLARE @database_name sysname
DECLARE @sql_string nvarchar(2000)
DECLARE @file_size TABLE
    (
    [database_name] [sysname] NULL,
    [groupid] [smallint] NULL,
    [groupname] sysname NULL,
    [fileid] [smallint] NULL,
    [file_size] [decimal](12, 2) NULL,
    [space_used] [decimal](12, 2) NULL,
    [free_space] [decimal](12, 2) NULL,
    [name] [sysname] NOT NULL,
    [filename] [nvarchar](260) NOT NULL
    )

SELECT TOP 1 @database_id = database_id
    ,@database_name = name
FROM sys.databases db
WHERE database_id > 0
AND db.state < 6 --#skip offline db
ORDER BY database_id

WHILE @database_name IS NOT NULL
BEGIN

    SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10)
    SET @sql_string = @sql_string + 'SELECT
                                        DB_NAME()
                                        ,sysfilegroups.groupid
                                        ,sysfilegroups.groupname
                                        ,fileid
                                        ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size
                                        ,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used
                                        ,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space
                                        ,sysfiles.name
                                        ,sysfiles.filename
                                    FROM sys.sysfiles
                                    LEFT OUTER JOIN sys.sysfilegroups
                                        ON sysfiles.groupid = sysfilegroups.groupid'

    INSERT INTO @file_size
        EXEC sp_executesql @sql_string   

    --Grab next database
    SET @database_name = NULL
    SELECT TOP 1 @database_id = database_id
        ,@database_name = name
    FROM sys.databases db
    WHERE database_id > @database_id
    AND db.state < 6 --#skip offline db
    ORDER BY database_id
END

--File Sizes
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename
--FROM @file_size
FROM @file_size where database_name='teguh_testingdb'
order by free_space desc

SELECT
      @@servername as ServerName,DBName = DB_NAME(database_id)
    , CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as LogFileMb
    , CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as DataFileMb
    , CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) as TotalSizeMb
FROM sys.master_files WITH(NOWAIT)
WHERE DB_NAME(database_id) like '%teguh_testingdb%'
GROUP BY database_id;

SELECT @@Servername as [ServerName],db_name() as [DBName],getdate() [Date],
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows as Rows,
   -- SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
  --  SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
  --  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t with (NOLOCK)
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceMB DESC, t.Name;

DBCC SHOWFILESTATS;
 

4. delete table delete from TestTable

delete from TestTable

5. check size & identification after delete before shrink
 

6. shrink database

USE [teguh_testingdb]
GO
DBCC SHRINKDATABASE(N'teguh_testingdb' )
GO

7. check size & identification after delete after shrink