October 1, 2009 at 7:19 pm
Comments posted to this topic are about the item automatic database shrink
October 7, 2009 at 12:44 am
Jason, I sense a storm brewing...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2009 at 4:31 am
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?
October 7, 2009 at 4:40 am
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.
October 11, 2009 at 7:31 pm
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
Jason
http://dbace.us
😛
November 18, 2010 at 2:57 pm
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
😛
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply