Script to shrink

CREATE TABLE freefilespace
    (
         fileid       INT,
         filegroup    INT,
         totalextents INT,
         usedextents  INT,
         dbname       VARCHAR(256),
         filename     VARCHAR(2000)
    )



INSERT INTO freefilespace
EXEC ('DBCC showfilestats')


Recently we had a space issue on one of the server
Since it will take a time to resolve space issue we had to shrink the files regularly for smooth operation

Finally we created a script to shrink the files
How ever I know that shrinking is not a good option  and we should do it only in exceptional case,
but we had to do it for a long time

Here is a script for this



TRUNCATE TABLE freefilespace

INSERT INTO freefilespace
EXEC ('DBCC showfilestats')

GO

DECLARE @i INT

DECLARE @dbname VARCHAR(50)

DECLARE @filesize INT

DECLARE @freespace INT

DECLARE TEMP CURSOR FOR
    SELECT dbname,( totalextents * 64 ) / 1024 filesize_kb,( ( totalextents * 64 ) - ( usedextents * 64 ) ) / 1024 freespace_kb
    FROM   freefilespace
    WHERE  ( ( totalextents * 64 ) - ( usedextents * 64 ) ) / 1024 > 1000

OPEN TEMP

FETCH NEXT FROM TEMP INTO @dbname, @filesize, @freespace

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @i = @filesize

        WHILE @i >= @filesize - ( @freespace + 100 )
              AND @i > 0
            BEGIN
                DBCC shrinkfile (@dbname, @i )

                SET @i = @i - 10
            END

        FETCH NEXT FROM TEMP INTO @dbname, @filesize, @freespace
    END

CLOSE TEMP

DEALLOCATE TEMP