— List all Objects and Indexes — per Filegroup / Partition and Allocation Type — including the allocated data size SELECT DS.name AS DataSpaceName ,AU.type_desc AS AllocationDesc ,AU.total_pages / 128 AS TotalSizeMB ,AU.used_pages / 128 AS UsedSizeMB ,AU.data_pages / 128 AS DataSizeMB ,SCH.name AS SchemaName ,OBJ.type_desc AS ObjectType ,OBJ.name AS ObjectName ,IDX.type_desc AS IndexType ,IDX.name […]
Monthly Archives: April 2015
Move SQL Server database files to a new folder location
ALTER DATABASE my SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE my SET OFFLINE; ALTER DATABASE my MODIFY FILE ( Name = my_Data, Filename = ‘D:DATAmy.MDF’ ); Move the file to the new location. ALTER DATABASE my SET ONLINE; ALTER DATABASE my SET MULTI_USER;
Moving all non-clustered indexes to another filegroup in SQL Server
EXEC MoveIndexToFileGroup @DBName = ”, @SchemaName = ”, @ObjectNameList = ”, @IndexName = ”, @FileGroupName = ”;
Move Tables To Filegroup
1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY: EXEC dbo.sp_MoveTablesToFileGroup @SchemaFilter = ‘%’, — chooses schemas using the LIKE operator @TableFilter = ‘%’, — chooses tables using the LIKE operator @DataFileGroup = ‘SECONDARY’, — The name of the filegroup to move index and in-row data to. @ClusteredIndexes = […]
Gradually Deleting Data in SQL Server
If you have a situation where you have a very large table in SQL Server, where you need to periodically delete tens of millions of rows of data, there are several ways to do it. If you have a maintenance window (or your database is not required to be available 24 x 7 x 365), […]