A) not everybody can carry unused allocated space forever:-P.
B) before I move DBs to a different host or space volume, I would shrink it before I move it.
C) SQLServer has "auto shrink DB" option but should not be enabled. This is the only way to manually control it.
D) I also wrote index defrag code "alter index ..." as good as Idera's Defrag Manager $1200/server. Wait till I publish it soon.
The first portion code can actually display free space usage, then the info is used to generate shrinkfile command. I inherited 150 DBs, some carry large percent unused space, some we determine to move to QA or archive. This code allows me to keep them clean before we migrate, .mdf and .ldf as well.
If you have not read the script, you think this is to shrink DB every night. That is not my scenario. You see the word "automatic" you assume it is. It automates the commands. You pick the correct command parameter > 100%, it would not pick any DBs to shrink.
My other code does pick up logical fragmentation by a threshold and issue "alter index" command, nightly by paramater frag% and rowcount.
My DBs under my watch have been running fine, at very low fragmentation. (I don't know about yours?) I do clean up when opportunities arise.
Paul also mentioned there is scenario you do shink which is about my scenario like before archive.
He says don't defrag then shrink to get it fragmented again.
For those who has not recently checked your DB unused space, you may be suprised how much free space you carried unused.
The beauty of this script is it calculates for you and generate script you can use to automate DBA tasks. Of course, you can override the script, then put on AgentJob to run at night, you can sleep tonight.
Consider the following information when you plan to shrink a database:
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
If you change the script for your needs on the line where it was determining how much space to keep in order to keep 20% extra, that will cover you for a while when the databases grow again. Of course, you should run my index defrag SP after shrink.
---- CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) *1.20 AS DECIMAL(15,0))----
---- query unused space all DBs
SET NOCOUNT ON;
DECLARE @DBFILENAME varchar(255), @DBFILESIZE int, @DBSPACEFREE int, @DBPERCENTFREE DECIMAL(18,2)
CREATE TABLE #TMPCOMMAND (
CREATE TABLE #TMPFIXEDDRIVES (
INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES
CREATE TABLE #TMPSPACEUSED (
CREATE TABLE #DB (
DECLARE @dbName sysname, @rc int
DECLARE @cmdSQL varchar(2000)
INSERT INTO #DB (NAME) SELECT NAME FROM master.sys.databases
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ADMINDB', 'ReplDistribution') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
SELECT @rc = 1, @dbName = MIN(name) FROM #DB
WHILE @rc <> 0
SET @cmdSQL = 'USE [' + @dbName + '];' + 'INSERT INTO #TMPSPACEUSED (DBNAME, FILENME, SPACEUSED) SELECT ''' + @dbName + ''', NAME, FILEPROPERTY(NAME, ''SpaceUsed'') FROM [' + @dbName + '].sys.sysfiles'
SELECT TOP 1 @dbName = name
WHERE name > @dbName
ORDER BY name
SET @rc = @@ROWCOUNT
DROP TABLE #DB
INSERT INTO #TMPCOMMAND(COMMANDNAME)
SELECT 'USE [' + A.NAME + '];' + ' DBCC SHRINKFILE(' +
B.NAME + ', ' +
CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,0))
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) *1.20 AS DECIMAL(15,0))
) AS VARCHAR(20) )+ ')'
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
WHERE CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) / CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) > 0.20
DROP TABLE #TMPFIXEDDRIVES
DROP TABLE #TMPSPACEUSED
DECLARE @PrintCommand VARCHAR(8000)
DECLARE Print_cursor CURSOR
SELECT COMMANDNAME FROM #TMPCOMMAND ORDER BY COMMANDNAME
FETCH NEXT FROM Print_cursor INTO @PrintCommand
WHILE (@@FETCH_STATUS <> -1)
IF (@@FETCH_STATUS <> -2)
FETCH NEXT FROM Print_cursor INTO @PrintCommand
DROP TABLE #TMPCOMMAND