martache27
Technical User
Sort of new to the administration of a SQL Db. Is there a utility to shrink down the database once a week?
Thanks!
Thanks!
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure ShrinkFile_allUserdatabases
AS
SET NOCOUNT ON
CREATE TABLE #LogicalNamelist (
dbName CHAR (255),
LogicalNamelist CHAR(50))
INSERT INTO #LogicalNamelist
SELECT sysdb.Name as dbName, CONVERT(VARCHAR(30),sysalt.name) as dblogicalFileName
FROM master..sysaltfiles sysalt INNER JOIN master..sysdatabases sysdb ON sysalt.dbid = sysdb.dbid
WHERE sysdb.dbid > 4 and sysalt.name like '%_log%'and sysalt.name not like '%_dat%'
GROUP BY sysalt.name , sysdb.name
ORDER BY sysalt.name
DECLARE AllDatabases CURSOR FOR
SELECT dbName,LogicalNamelist
from #LogicalNamelist
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(158)
DECLARE @Statement NVARCHAR(300)
DECLARE @logicalename VARCHAR (128)
FETCH NEXT FROM AllDatabases
INTO @DBNameVar,@logicalename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'Shrinkingfile ' + RTRIM(@logicalename )+ ' '+ 'of' +' '+ RTRIM(@DBNameVar )
SET @Statement = N'USE [' + RTRIM(@DBNameVar) + ']'+ CHAR(13)
+ N'DBCC SHRINKFILE (['+ RTRIM(@logicalename)+ '],' + ' ' + 'TRUNCATEONLY)' + 'WITH NO_INFOMSGS'
--PRINT @Statement
EXEC sp_executesql @Statement
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases
INTO @DBNameVar,@logicalename
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
Drop table #LogicalNamelist
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO