Drop Table if Exists
IF
OBJECT_ID(N'CensusReportData',
N'U')
IS
NOT
NULL
DROP
TABLE CensusReportData
Database and Logfile Sizes
Database sizes
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
GO
Logfile sizes
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
--WHERE DB_NAME(database_id) = 'AdventureWorks'
ORDER BY DB_NAME(database_id)
GO
Log file sizes v2
IF
OBJECT_ID(N'master.dbo.TempForLogSpace',
N'U')
IS
NOT
NULL
drop
table
master.dbo.TempForLogSpace
go
declare @sql_command
nvarchar(MAX)
CREATE
TABLE
master.dbo.TempForLogSpace
(
DBName
varchar(MAX),
LogSize
real,
LogSpaceUsed
real,
Status
int
)
SELECT @sql_command
=
'dbcc sqlperf (logspace)'
INSERT
INTO
master.dbo.TempForLogSpace
EXEC
(@sql_command)
--select * from master.dbo.TempForLogSpace
SELECT
DB_NAME(database_id)
AS DatabaseName,
sf.[Name]
AS Logical_Name,
Physical_Name,
(size*8)/1024
SizeMB,tmpLog.LogSpaceUsed
,state_desc,databasepropertyex(DB_NAME(database_id),'Recovery')
as [Recovery],tmpLog.[Status]
FROM
sys.master_files
sf LEFT
OUTER
JOIN
master.dbo.sysdatabases
sd ON
DB_NAME(database_id)=sf.[name]
LEFT
OUTER
JOIN
master.dbo.TempForLogSpace
tmpLog ON
DB_NAME(database_id)
= tmpLog.DBName
where physical_name
like
'%ldf%'
--WHERE DB_NAME(database_id) = 'nSightDB'
ORDER
BY
DB_NAME(database_id)
GO
sql table sizes
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
TotalSpaceMB DESC, t.Name
--https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database
Index Sizes
SELECT
[object_id] = t.[object_id]
,[schema_name] = s.[name]
,[table_name] = t.[name]
,[index_name] = CASE WHEN i.[type] in (0,1,5) THEN null ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
,[object_type] = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX' END
,[index_type] = i.[type_desc]
,[partition_count] = p.partition_count
,[row_count] = p.[rows]
,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
ELSE ( SELECT DISTINCT p.data_compression_desc
FROM sys.partitions p
WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
)
END
,[total_space_MB] = cast(round(( au.total_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[used_space_MB] = cast(round(( au.used_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[unused_space_MB] = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
FROM sys.partitions
GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table
Rebuild all indexes on DB's or Rebuilt all indexes on a specific DB's
--https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/
--Rebuild all indexes on DB's or Rebuilt all indexes on specific DB's
DECLARE @Database NVARCHAR(255)
DECLARE @Table NVARCHAR(255)
DECLARE @cmd NVARCHAR(1000)
DECLARE DatabaseCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases
----------------------------------------------------------------------
----------------------------------------------------------------------
--WHERE name NOT IN ('master','msdb','tempdb','model','distribution') -- databases to exclude
WHERE name IN ('TMS_Live') -- use this to select specific databases and comment out line above
----------------------------------------------------------------------
----------------------------------------------------------------------
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR READ_ONLY FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD'
PRINT @cmd -- uncomment if you want to see commands
EXEC (@cmd)
END TRY
BEGIN CATCH
PRINT '---'
PRINT @cmd
PRINT ERROR_MESSAGE()
PRINT '---'
END CATCH
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor