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.:cool:

C) SQLServer has "auto shrink DB" option but should not be enabled. This is the only way to manually control it.:w00t:

D) I also wrote index defrag code "alter index ..." as good as Idera's Defrag Manager $1200/server. Wait till I publish it soon.:hehe:

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.

----------BOL---------

Best Practices

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

USE MASTER

GO

SET NOCOUNT ON;

DECLARE @DBFILENAME varchar(255), @DBFILESIZE int, @DBSPACEFREE int, @DBPERCENTFREE DECIMAL(18,2)

GO

CREATE TABLE #TMPCOMMAND (

COMMANDNAME VARCHAR(2048)

)

GO

CREATE TABLE #TMPFIXEDDRIVES (

DRIVE CHAR(1),

MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (

DBNAME NVARCHAR(255),

FILENME VARCHAR(255),

SPACEUSED FLOAT)

GO

CREATE TABLE #DB (

NAME NVARCHAR(255)

)

GO

DECLARE @dbName sysname, @rc int

DECLARE @cmdSQL varchar(2000)

INSERT INTO #DB (NAME) SELECT NAME FROM master.sys.databases

WHERE

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

BEGIN

SET @cmdSQL = 'USE [' + @dbName + '];' + 'INSERT INTO #TMPSPACEUSED (DBNAME, FILENME, SPACEUSED) SELECT ''' + @dbName + ''', NAME, FILEPROPERTY(NAME, ''SpaceUsed'') FROM [' + @dbName + '].sys.sysfiles'

EXEC(@cmdSQL)

SELECT TOP 1 @dbName = name

FROM #db

WHERE name > @dbName

ORDER BY name

SET @rc = @@ROWCOUNT

END

DROP TABLE #DB

INSERT INTO #TMPCOMMAND(COMMANDNAME)

SELECT 'USE [' + A.NAME + '];' + ' DBCC SHRINKFILE(' +

B.NAME + ', ' +

CAST(

(

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

FOR

SELECT COMMANDNAME FROM #TMPCOMMAND ORDER BY COMMANDNAME

OPEN Print_cursor

FETCH NEXT FROM Print_cursor INTO @PrintCommand

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

PRINT @PrintCommand

END

FETCH NEXT FROM Print_cursor INTO @PrintCommand

END

DROP TABLE #TMPCOMMAND

CLOSE Print_cursor

DEALLOCATE Print_cursor