Shrinking a database

Most companies running Dynamics GP are using the Full backup model.  This means that they are backing up the log files more frequently than the database.

When on this model, it should not be necessary to shrink the data or log file very often.

However, there are instances where you would want to do this, such as immediately after archiving (removing) a large amount of data from the database.

Here are the commands to get the files shrunk back down. You should do this at a time when no users or other processes are accessing the database.

--1. Create a full backup of the database.  Since this will involve changing the backup model, we will be breaking any log chains that exist.

--2. Set the database to single user mode.  This will prevent anything from connecting to the database while we are working on it

ALTER DATABASE [YourDbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

--3. Set the recovery model to simple.  This will allow us to quickly shrink the files.

ALTER DATABASE [YourDbName] SET RECOVERY SIMPLE;

GO

--4. Shrink the data and log files.  The number here represents 10% free space after shrinking.  This can take a while to run.

DBCC SHRINKDATABASE ([YourDbName], 10);

GO

--5. Shrinking trashes the indexes, so now we reorganize them.  This can take a very long time to run.

USE [YourDbName]

GO

DECLARE @TableName VARCHAR(255),

@sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE;'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

--6. Set recovery model back to full

ALTER DATABASE [YourDbName] SET RECOVERY FULL;

GO

--7. Set Database back to multiuser mode

ALTER DATABASE [YourDbName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

--8. Remember to take a new full backup after you are done so that the full backup model can start a new log chain.