Link to home
Start Free TrialLog in
Avatar of chandradineshbabu
chandradineshbabuFlag for India

asked on

stored procedure for shrinking datafiles

I have to create a stored procedure for dynamically shrinking data files(only mdf and ndf files) in a database if a file exceeds its size in sqlserver 2000.how do it in sqlserver 2000?
if any body has a script,please send it to me ASAP.

Thanks in advance.
Avatar of chapmandew
chapmandew
Flag of United States of America image

WHY do you need to do this?
Avatar of chandradineshbabu

ASKER

i have 6 datafiles in my database.some times they are exceeding their sizes.i want to shrink them dynamically to some standared size.i will put the sp in job and run it daily once.

thanks in advance.
This is a bad idea.  They are getting larger for a reason.  Shrinking them is just going to fragment the files leading to worse performance.  If you are not running out of space, do not shrink them.  
If the previous comments have not convinced you yet, I can post I suspect at least 10 links to articles that tell you why this is not good.

No doubt, somebody will post the code to do what you asked, but they may as well also show you how to format and fdisk your drives as well.
I wrote the below script for my question.
could you please debug it?

DECLARE @FILE_NAME VARCHAR(100)
DECLARE @SHRINK_SIZE INT
DECLARE @TARGET_SIZE  VARCHAR(100)
DECLARE @STOP_AT VARCHAR(100)
DECLARE @DATABASE_NAME VARCHAR(100)

SET @DATABASE_NAME='TEST'
SET @SHRINK_SIZE=50
SET @TARGET_SIZE=10

CREATE TABLE #DBINFO(

DATABASENAME VARCHAR(100),  
FILESIZEMB INT,  
LOGICALFILENAME SYSNAME,  
PHYSICALFILENAME NVARCHAR(520),  
FREESPACEMB INT,TYPE VARCHAR(100)
)  
DECLARE @COMMAND VARCHAR(5000)  

SELECT @COMMAND = 'USE [' + '?' + '] SELECT  
 
' + '''' + '?' + '''' + ' AS DATABASENAME,  
CAST(SYSFILES.SIZE/128.0 AS INT) AS FILESIZE,  
SYSFILES.NAME AS LOGICALFILENAME, SYSFILES.FILENAME AS PHYSICALFILENAME,  
 
CAST(SYSFILES.SIZE/128.0 - CAST(FILEPROPERTY(SYSFILES.NAME, ' + '''' +  
       'SPACEUSED' + '''' + ' ) AS INT)/128.0 AS INT) AS FREESPACEMB, '+
       
       
       'CASE STATUS & 0X40   WHEN   0X40 THEN '+''''+'LOG ONLY'+''''+'    ELSE  '+''''+'DATA ONLY'+''''+'  END
       
FROM DBO.SYSFILES'  

INSERT INTO #DBINFO  
   (  DATABASENAME,  FILESIZEMB,  LOGICALFILENAME,  PHYSICALFILENAME,  FREESPACEMB,TYPE )  
EXEC SP_MSFOREACHDB @COMMAND  


CREATE TABLE #FILEDETAILS
 (ID INT IDENTITY(1,1), DATABASENAME VARCHAR(1000), FILESIZEMB VARCHAR(1000), LOGICALFILENAME VARCHAR(1000), PHYSICALFILENAME VARCHAR(1000), FREESPACEMB VARCHAR(1000) )
 
 INSERT INTO #FILEDETAILS(DATABASENAME,FILESIZEMB,LOGICALFILENAME,PHYSICALFILENAME,FREESPACEMB)
SELECT DATABASENAME,FILESIZEMB,LOGICALFILENAME,PHYSICALFILENAME,FREESPACEMB FROM #DBINFO WHERE TYPE='DATA ONLY' AND DATABASENAME=@DATABASE_NAME AND FREESPACEMB>0

 DECLARE @MAXID INT
 DECLARE @INC INT
  SET @INC=1
 
 DECLARE @COMM VARCHAR(1000)
 DECLARE @DATABASENAME VARCHAR(500)
 DECLARE @LOGICALFILENAME VARCHAR(500)
 DECLARE @FREESPACEMB INT
 DECLARE @SHRINKVALUEMB INT
 SET @SHRINKVALUEMB=0
 SELECT @MAXID=COUNT(*) FROM #FILEDETAILS
 WHILE (@INC<=@MAXID)
 BEGIN
    -- PRINT @INC
     SET @COMM=''
     SELECT @DATABASENAME=DATABASENAME,@LOGICALFILENAME=LOGICALFILENAME,@FREESPACEMB=CONVERT(INT,FREESPACEMB) FROM #FILEDETAILS WHERE ID=@INC
     
     SET @SHRINKVALUEMB=@FREESPACEMB-ISNULL(@SHRINK_SIZE,0)
     
     WHILE (@SHRINKVALUEMB>@TARGET_SIZE)
     BEGIN
       
        -- SELECT @DATABASENAME,@LOGICALFILENAME,@SHRINKVALUEMB
         SET @COMM='USE '+ @DATABASENAME + ' DBCC SHRINKFILE('+@LOGICALFILENAME+','+CONVERT(VARCHAR(100),@SHRINKVALUEMB)+')'
         SET @SHRINKVALUEMB=@SHRINKVALUEMB-ISNULL(@SHRINK_SIZE,0)
        EXEC ( @COMM)
         --SELECT @COMM
     END
    --USE TEST DBCC SHRINKFILE(DAASDA,98)
     SET @INC=@INC+1
 END
 
--SELECT * FROM #FILEDETAILS
DROP TABLE #DBINFO
DROP TABLE #FILEDETAILS

thanks in advance
>>I wrote the below script for my question.
could you please debug it?<<
I am afraid not.  You have just handed me a gun and you are asking me to shoot you.  I pass.

Note to future readers (if any):
If you value the performance of your database do not automate any SHRINKFILE or SHRINKDATABASE.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It is a excellent answer