gistfile1.md

Alternatives for shrinking

Pieces:

• Simple Recovery Model
• Bulk of < 4k items
• Rebuild index at end
• Ensure no user is working in app when you do it

Something like:

-- First do the backup, incrementally or otherwise
USE master;
GO

-- Ensure no users in DB, creating things you're going to destroy the TX log for
ALTER DATABASE DB_0000
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

-- Use the large DB so you target it
USE DB_0000
GO

-- Ensure you have a clean tx log by backing it up
BACKUP DATABASE DB_0000
TO DISK = 'C:\MSSQL\BACKUP\DB_0000.Bak'
   WITH FORMAT,
   NAME = 'Full Backup of CV Hoarder'
GO

-- Now, don't log so much to disk!
ALTER DATABASE DB_0000 SET RECOVERY SIMPLE;
GO

-- Go through all items in the table and older than Date X and delete them in bulk,
-- but not everything at once or you'll run out of disk and crash like you did today.
-- Stop if there's not enough disk space. You may have to syntax check this code:
WHILE EXISTS (
  SELECT 1 AS MatchCVs, CVId
  FROM dbo.CVs
  WHERE CVs.InsertDate < '2010-01-01 00:00:00'
  INNER JOIN (
    SELECT
      CASE
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableMiB
        WHEN AvailableMiB > 10240
          1
        WHEN AvailableMiB <= 10240
          0
      END AS MatchMiB
    FROM sys.database_files
  ) AS FreeSpace
  ON FreeSpace.MatchMiB = MatchCVs)
BEGIN
  -- delete old CVs, everything a tx, be explicit!
  BEGIN TRANSACTION
    DELETE TOP(4900) FROM dbo.CVs WHERE CVs.InsertDate < '2010-01-01 00:00:00';
  COMMIT TRANSACTION
END
GO

BEGIN TRANSACTION
  -- You need to check if it stopped from over-usage of log file, or from
  -- it being done:
  SELECT 
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableMiB
  FROM sys.database_files
END TRANSACTION
GO

-- If needed, see above!
DBCC SHRINKFILE (DB_0000_DataFile, 10240) TRUNCATEONLY;
GO

BEGIN TRANSACTION
  -- rebuild the index because it's fragmented now
  ALTER INDEX ALL ON dbo.CVs REBUILD;

  -- rebuild query execution plans
  UPDATE STATISTICS dbo.CVs WITH SAMPLE 10 PERCENT;

  -- some stats from the delete
  SELECT  database_transaction_log_bytes_used
  FROM    sys.dm_tran_database_transactions
  WHERE   database_id = db_id('DB_0000');
END TRANSACTION
GO

-- Change back to full recovery mode
ALTER DATABASE DB_0000 SET RECOVERY FULL;
GO

-- Ensure no users in DB
ALTER DATABASE DB_0000
SET MULTI_USER
WITH ROLLBACK  IMMEDIATE;
GO

References:

• https://social.technet.microsoft.com/wiki/contents/articles/20651.sql-server-delete-a-huge-amount-of-data-from-a-table.aspx
• https://stackoverflow.com/questions/5925471/how-can-i-delete-expired-data-from-a-huge-table-without-having-the-log-file-grow
• http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/
• http://msdn.microsoft.com/en-us/library/ms365418.aspx
• http://msdn.microsoft.com/en-us//library/ms189493.aspx
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a372de2d-f247-4333-ae22-680fff5d2fe0/backup-log-and-dbcc?forum=transactsql
• http://technet.microsoft.com/en-us/library/ms345598.aspx
• https://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select
• http://technet.microsoft.com/en-us/library/ms187348.aspx