Compacting all MSSQL databases
Now that my hangover has just about worn off from a rough night out, I thought i’d post something useful. Below is a quick and (very) dirty way of clearing down the log and shrinking MSSQL datbases. Something useful for people who want to keep their Windows systems/networks tidy.
-- DECLARE STUFF.
DECLARE @rowCount int
DECLARE @rowCurrent int
DECLARE @rowData table (dbID int IDENTITY (1, 1), dbName varchar(50))
DECLARE @dbName varchar(50)
SET @rowCurrent = 0
-- Populate @rowData with list of databases on server.
INSERT INTO @rowData(dbName) SELECT name FROM sysdatabases
-- Number of rows in @rowData.
SELECT @rowCount = COUNT(*) FROM @rowData
-- Itherate through rows.
WHILE @rowCurrent < @rowCount
BEGIN
SELECT @dbName = dbName FROM @rowData WHERE dbID = @rowCurrent
PRINT @dbName
BACKUP LOG @dbName WITH NO_LOG
DBCC SHRINKDATABASE (@dbName)
SET @rowCurrent = @rowCurrent + 1
END
If anyone has some suggestions, feel free to let me know.