SQL Server Data Archiving, Query SQL Server Agent, ISJSON enhancements and Tables Per Filegroup Script

[MSSQLTips.com logo]( [View email in your browser]( TODAY'S NEWS FROM MSSQLTips Learn something new every day! [mssqltips image]( [Archive SQL Server Data using the SQL OUTPUT Clause]( This tip explores a better, more direct, and more optimized method for moving or archiving data in SQL Server using the SQL OUTPUT clause.... [Read Now]( [mssqltips image]( [Best-of-class SQL Server Performance Monitoring and Alerting]( Learn about SolarWinds' SQL Sentry designed to monitor SQL Servers, collect Key Performance Indicators (KPIs), highlight poorly performing queries, identify performance-related issues, and send alerts to notify DBA teams promptly.... [Read Now]( [mssqltips image]( [ISJSON enhancements in SQL Server]( In this article, we look at an enhancement to the ISJOSN function that allows you to validate whether the JSON format is valid for a value, array, or object.... [Read Now]( TODAY'S SCRIPT ON MSSQLTips SQL Server Tables Per Filegroup SELECT FileGroupName = DS.name ,FileGroupType = CASE DS.[type] WHEN 'FG' THEN 'Filegroup' WHEN 'FD' THEN 'Filestream' WHEN 'FX' THEN 'Memory-optimized' WHEN 'PS' THEN 'Partition Scheme' ELSE 'Unknown' END -- ,AllocationDesc = AU.type_desc ,TotalSizeKB = SUM(AU.total_pages / 0.128) -- 128 pages per megabyte ,UsedSizeKB = SUM(AU.used_pages / 0.128) ,DataSizeKB = SUM(AU.data_pages / 0.128) ,SchemaName = SCH.name ,TableName = OBJ.name ,IndexType = CASE IDX.[type] WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' WHEN 2 THEN 'Nonclustered' WHEN 3 THEN 'XML' WHEN 4 THEN 'Spatial' WHEN 5 THEN 'Clustered columnstore' WHEN 6 THEN 'Nonclustered columnstore' WHEN 7 THEN 'Nonclustered hash' END ,IndexName = IDX.name ,is_default = CONVERT(INT,DS.is_default) ,is_read_only = CONVERT(INT,DS.is_read_only) FROM sys.filegroups DS -- you could also use sys.data_spaces LEFT JOIN sys.allocation_units AU ON DS.data_space_id = AU.data_space_id LEFT JOIN sys.partitions PA ON (AU.[type] IN (1, 3) AND AU.container_id = PA.hobt_id) OR (AU.[type] = 2 AND AU.container_id = PA.[partition_id]) LEFT JOIN sys.objects OBJ ON PA.[object_id] = OBJ.[object_id] LEFT JOIN sys.schemas SCH ON OBJ.[schema_id] = SCH.[schema_id] LEFT JOIN sys.indexes IDX ON PA.[object_id] = IDX.[object_id] AND PA.index_id = IDX.index_id WHERE OBJ.type_desc = 'USER_TABLE' -- only include user tables OR DS.[type] = 'FD' -- or the filestream filegroup GROUP BY DS.name,SCH.name,OBJ.name,IDX.[type],IDX.name,DS.[type],DS.is_default,DS.is_read_only ORDER BY DS.name,SCH.name,OBJ.name,IDX.name; Learn more: [Custom SSMS report to show SQL Server tables per filegroup]( Feedback on this script or today's newsletter? [Provide feedback]( to the MSSQLTips.com Team. RECOMMENDED READING ON MSSQLTips [mssqltips image]( [SQL Server Data Warehouse Data Correction Options]( Data Quality | SSIS Workflow | Downloadable Solution - How can we clean, validate and enhance our Contact dimension in our SQL Server Data Warehouse without writing code?... [Read Now]( [mssqltips image]( [Query SQL Server Agent Jobs, Steps and Schedules]( In this tip we look at different queries you can use to directly get information about SQL Server Agent Jobs instead of using SQL Server Management Studio.... [Read Now]( [mssqltips image]( [Clumio - SQL Backups Built for DBAs, Loved by IT]( Clumio's cloud-native SQL Server backups for AWS is the perfect backup solution for SQL Server on Amazon EC2 for successful cloud migrations, .NET applications in AWS, and running Windows workloads in tandem with AWS services. ... [Read Now]( [Manage MSSQLTips Subscriptions | Unsubscribe](. Edgewood Solutions LLC PO Box 682 | Wilton, NH 03086 [Privacy Statement]( Sent to: {EMAIL} [tag]