This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
fg.data_space_id AS FilegroupID, | |
fg.name AS FileGroupName, | |
f.file_id AS FileID, | |
f.name AS LogicalName, | |
f.physical_name, | |
fileproperty(f.name,'SpaceUsed')/128 AS Used_MB, | |
(f.size-fileproperty(f.name,'SpaceUsed'))/128 As Free_MB | |
FROM | |
sys.database_files f |
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
DB_NAME(db.database_id) DBName, | |
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSize_MB, | |
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSize_MB, | |
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSize_MB, | |
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSize_MB | |
FROM sys.databases db | |
LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id | |
LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id | |
LEFT JOIN (SELECT database_id, SUM(CAST(size AS BIGINT)) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- This script will generate 3 reports that give an overall or high level | |
-- view of the indexes in a particular database. The sections are as follows: | |
-- 1. Lists ALL indexes and constraints along with the key details of each | |
-- 2. Lists any tables with potential Redundant indexes | |
-- 3. Lists any tables with potential Reverse indexes | |
-- Create a table variable to hold the core index info | |
CREATE TABLE #AllIndexes ( | |
TableID INT NOT NULL, | |
SchemaName SYSNAME NOT NULL, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @FGID INT; | |
SET @FGID = ; -- Filegroup ID | |
SELECT QUOTENAME(s.name) AS SchemaName, | |
QUOTENAME(o.name) AS ObjName, | |
o.object_id AS ObjID, | |
p.index_id AS IndexID, | |
QUOTENAME(i.name) AS IndexName, | |
ROUND(CAST(au.data_pages AS FLOAT)/128,2) AS MB_Used, | |
p.data_compression_desc, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
a.FILEID As FileID, | |
a.groupid AS FileGroupID, | |
a.[name] AS LogicalName, | |
a.[filename] AS FilePath, | |
ROUND(CAST(a.size AS FLOAT)/128,2) AS Reserved_MB, | |
ROUND(CAST(FILEPROPERTY(a.name,'SpaceUsed') AS FLOAT)/128,2) AS Used_MB, | |
ROUND(CAST(a.size AS FLOAT)/128 - CAST(FILEPROPERTY(a.name,'SpaceUsed') AS FLOAT)/128,2) AS Free_MB | |
FROM dbo.sysfiles a | |
WHERE a.groupID = 16 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
----------------------------------------------------------- | |
-- Name: CollectIndexUsageStats.sql | |
-- | |
-- Description: Collects index usage statistics over time. | |
-- | |
-- Author: Bob Pusateri, https://www.bobpusateri.com | |
-- | |
-- THIS SCRIPT IS PROVIDED "AS-IS" WITHOUT ANY WARRANTY. | |
-- DO NOT RUN THIS ON A PRODUCTION SYSTEM UNTIL YOU HAVE | |
-- COMPLETE UNDERSTANDING OF THE TASKS IT PERFORMS AND |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH fg_sizes AS ( | |
SELECT fg.data_space_id AS FGID, | |
COUNT(f.file_id) AS FileCount, | |
ROUND(CAST(SUM(f.size) AS FLOAT)/128,2) AS Reserved_MB, | |
ROUND(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB, | |
ROUND((CAST(SUM(f.size) AS FLOAT)/128)-(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB | |
FROM sys.filegroups fg | |
LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id | |
GROUP BY fg.data_space_id | |
), |