Monday, March 16, 2015

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

Tuesday, February 3, 2015

How big are the tables in my databases?

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

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

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, p.Rows

ORDER BY

TotalSpaceKB desc




-- t.Name

Wednesday, October 8, 2014

List procedures and their dependent tables

select

distinct s1.name as 'Procedure Schema Name', o1.name as 'Procedure Name', s2.name as 'Table Schema Name', o2.name as 'Table Name' from sysdepends d


join sys.objects o1 on d.id = o1.object_id


join sys.schemas s1 on o1.schema_id = s1.schema_id


join sys.objects o2 on d.depid = o2.object_id


join sys.schemas s2 on o2.schema_id = s2.schema_id


where


o1.type = 'P' and


o2.type = 'U'

order

by 1,2,3,4

Query to look at filegroups by table


SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name

,t.name AS table_name

,i.index_id

,i.name AS index_name

,ds.name AS filegroup_name

,p.rows

FROM sys.tables t

INNER JOIN sys.indexes i ON t.object_id=i.object_id

INNER JOIN sys.filegroups ds ON i.data_space_id=ds.data_space_id

INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
ORDER BY t.name, i.index_id