Skip to content

Instantly share code, notes, and snippets.

View BobPusateri's full-sized avatar

Bob Pusateri BobPusateri

View GitHub Profile
@BobPusateri
BobPusateri / All_File_Info.sql
Created June 24, 2015 19:41
Displays info for all files and filegroups in the current Microsoft SQL Server database
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
@BobPusateri
BobPusateri / PSNotebookCommentBehavior.ipynb
Created January 31, 2020 21:13
Demonstrating behaviors of PowerShell notebooks that contain both functions and comments
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@BobPusateri
BobPusateri / Luhn_Algorithm.ipynb
Created February 19, 2020 21:59
An implementation of the Luhn Algorithm in PowerShell. Demonstrates certain scenarios where digit transposition results in identical checksums.
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@BobPusateri
BobPusateri / dbsizes.sql
Created July 13, 2022 14:25
Sizes of all databases on a SQL Server instance
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 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,
@BobPusateri
BobPusateri / FilegroupDetail.sql
Created February 4, 2023 22:36
For a given filegroup, list out object & index info and data/LOB filegroup names
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,
@BobPusateri
BobPusateri / FilegroupFilesInfo.sql
Created February 4, 2023 22:37
Gets info about all the files in a specific filegroup
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
@BobPusateri
BobPusateri / CollectIndexUsageStats.sql
Created February 5, 2023 00:40
Collects index usage statistics over time.
-----------------------------------------------------------
-- 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
@BobPusateri
BobPusateri / FilegroupSummary.sql
Last active July 17, 2023 15:22
Lists all filegroups in the current database, number of files, number of objects, total size of the files, total space used, total free space, and percentage of free space
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
),