Fixing VLF issues for all databases on a server

Note: This script presumes that databases are in SIMPLE recovery mode (as it was at this client site at the time). It reduces log file size to minimum, then increases it to what it was before it was shrunk, in one big alter. At the same time, it changes autogrowth to 10% across the board.

 

/*

select db_name(),(size+127)/128,* from sys.sysfiles where fileid = 2

DBCC SHRINKFILE (N'IMChartData_log' , 2)

GO

ALTER DATABASE [AMDDEMO] MODIFY FILE ( NAME = N'IMChartData_log', SIZE = 267264KB )

GO

*/

DECLARE @cmd VARCHAR(6000)

SET @cmd = 'USE [?] ;

declare @filename varchar(1000), @dbcc varchar(2000), @alter varchar(1000), @size int

select @filename = name, @size = (size+127)/128 from sys.sysfiles where filename like ''%ldf''

print db_name()

print @filename

set @dbcc = ''dbcc shrinkfile (xx, 2)''

set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)

set @alter = ''ALTER DATABASE ['' + db_name() +

''] MODIFY FILE ( NAME = xx, SIZE = '' + convert(varchar, @size ) + ''MB, FILEGROWTH = 10%)''

set @alter = REPLACE(@alter, ''xx'' ,@filename)

print @dbcc

print @alter

exec (@dbcc)

exec (@alter)

'

Exec sp_msforeachdb @cmd

How big are the tables in my databases?

I stole (and slightly modified) this code, and put it here so that I could find it. Have fun.

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
sum(p.rows) AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name 
ORDER BY
TotalSpaceKB desc



-- t.Name