Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

automatic database shrink Expand / Collapse
Author
Message
Posted Thursday, October 1, 2009 7:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 148, Visits: 468
Comments posted to this topic are about the item automatic database shrink
Post #796721
Posted Wednesday, October 7, 2009 12:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #798952
Posted Wednesday, October 7, 2009 4:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 29, 2010 5:23 AM
Points: 194, Visits: 2,357
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?
Post #799038
Posted Wednesday, October 7, 2009 4:40 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
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.
Post #799043
Posted Sunday, October 11, 2009 7:31 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
A) not everybody can carry unused allocated space forever.
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.

----------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


Jason
http://dbace.us
Post #801359
Posted Thursday, November 18, 2010 2:57 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
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)


Jason
http://dbace.us
Post #1023193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse