automatic database shrink

  • Comments posted to this topic are about the item automatic database shrink

  • Jason, I sense a storm brewing...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Or at least someone asking "WHY are you doing this?"

    Or arguments pointing out issues as highlighted in http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/629059.aspx perhaps?

  • Andrew Gothard-467944 (10/7/2009)


    Or at least someone asking "WHY are you doing this?"

    Or arguments pointing out issues as highlighted in http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/13/629059.aspx perhaps?

    Agreed. In general this is a bad idea. Maybe if you REALLY are short on space and need it in a dev environment, but can't see where this would be a good idea in a production environment.

  • 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

  • can't see where this would be a good idea in a production environment

    Oh, YES, if you pay for leased production space and/or you know your db is not growing, there is a threshold that you keep spending and usage in balance. Defrag after shrink when needed. Your company can save money to pay your bonus.

    Why uses a script, same reason commercial-jets are using auto-pilot. This automate some tasks when you want and where you want, without having to logon in the middle of weekend night. It is called DBA Automation. (beats outsourcing)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply