Comments (8)
+1. I'm managing a vendor DB with 1400+ heap tables. An option to rebuild heaps would be quite welcome. :)
from sql-server-maintenance-solution.
Guys, as we wait for this new feature, I share the code I use in a job to accomplish this task. As it is, it only creates and shows the syntax needed. Uncomment the EXEC line to put it to work. I think you already have written something like this, but this can be helpful to someone.
--> BEGIN SCRIPT
DECLARE @Tablename SYSNAME;
DECLARE @Sintaxis VARCHAR( 500 )
DECLARE Cursor_Heaps CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT SCH.name + '.' + TBL.name
FROM sys.tables AS TBL
INNER JOIN sys.schemas AS SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS IDX
ON TBL.object_id = IDX.object_id
AND IDX.type = 0;
OPEN Cursor_Heaps;
FETCH NEXT FROM Cursor_Heaps INTO @Tablename;
WHILE @@fetch_status = 0
BEGIN
SET @Sintaxis = 'ALTER TABLE ' + @Tablename + ' REBUILD';
PRINT @Sintaxis
--EXEC( @Sintaxis )
FETCH NEXT FROM Cursor_Heaps INTO @Tablename;
END;
CLOSE Cursor_Heaps;
DEALLOCATE Cursor_Heaps;
--> END SCRIPT
from sql-server-maintenance-solution.
See https://www.brentozar.com/archive/2016/07/fix-forwarded-records/ on advice to modify IndexOptimize for this situation.
I work with a lot of vendor applications. Getting vendor approval for clustered indexes meets a ton of resistance even after doing writeups with facts. Rebuilding heaps using an existing solution is the way to go.
from sql-server-maintenance-solution.
This would be a very welcome addition
from sql-server-maintenance-solution.
This makes complete sense. There are some situations in which is needed to rebuild a heap (a new datafile was added, for example). If we could have a @RebuildHEAP option in the solution, it would be great !
from sql-server-maintenance-solution.
Thanks, @EmilioBoucau . I have also been working on a sproc to help rebuild heaps. It only rebuilds ones that have user scans in the last x # of days and non-zero forward fetch counts. My first public repo:
https://github.com/iampeterdaniels/sp_rebuild_heaps
from sql-server-maintenance-solution.
+1 on this request
from sql-server-maintenance-solution.
Brent Ozar has a valid suggestion here for an intermediary fix: https://www.brentozar.com/archive/2016/07/fix-forwarded-records/#:~:text=Ola%20Hallengren%E2%80%99s%20IndexOptimize%20does%20not%20rebuild%20heaps
from sql-server-maintenance-solution.
Related Issues (20)
- Exclude Multiple tables
- Include the name of the day, for daily differential backups.
- Default Backup Location Folder doesn't exists HOT 1
- Fallback backup location
- ChangeBackuplocation
- Add an option to generate the Task Scheduler commands for SQL Express
- Update Statistics very slow on a 3Tb database HOT 3
- PRINT when TimeLimit has been reached in IndexOptimize HOT 1
- Diff backup of new database created fom another database is not being converted to full.
- (JOBNAME) variable not working on SQL Server 2014? HOT 2
- ColumnStore Index Fragmentation Issue
- Support Veeam Databses backups
- IndexOptimize job randomly reporting failure even when the log file says the Outcome succeeded HOT 6
- running rebuild on individual index takes a long time compared to native SQL commands HOT 2
- Enhance Index Rebuild Decision Process with Page Density Factor
- Parallel processing for index maintenance
- IndexOptimize - wrong ONLINE setting when table contains ColumnStore index
- Clustered indexes
- 7Zip compression
- @Clearuptime does not consider is_copy_only backups
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-server-maintenance-solution.