25112
asked on
selective rebuild of SQL Tables in scheduled job
how do you recommend selectively rebuilding tables whose size is big, data file size is big, but the row count is too small (might have been a big delete and since
then lot of unused space in the big data file)
the database has 100s of tables.. so i want to rebuild only tables where rowcount is small and datasize(file) is big.. (I can get this from
https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries )
how you can isolate the tables that are most needful case (small rowcount but filesize big) to rebuild every 10 days or so...
then lot of unused space in the big data file)
the database has 100s of tables.. so i want to rebuild only tables where rowcount is small and datasize(file) is big.. (I can get this from
https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries )
how you can isolate the tables that are most needful case (small rowcount but filesize big) to rebuild every 10 days or so...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Matt Bowler
Pretty sure from 2008 on you can do an ALTER TABLE ... REBUILD for a heap.
ASKER
EXEC sp_spaceused 'tablename'
name rows reserved data index_size unused
tablename 0 2285104 KB 1473896 KB 1464 KB 809744 KB
there are 2 index, but non clustered.
I did a ALTER TABLE TableName rebuild
EXEC sp_spaceused 'tablename'
now gives:
name rows reserved data index_size unused
tablename 0 88 KB 16 KB 16 KB 56 KB
there are other huge tables
sp_spaceused 'tableName2'
name rows reserved data index_size unused
tableName2 71528 20374104 KB 19853440 KB 50864 KB 469800 KB
is it safe to get all these in a filtered list and routinely (weekly?) do a REBUILD on the table regardless of clustered index or not? (this also has a few nonclustered indexes)
it is a 24/7
datatype of table: (200+ columns.. probably 50 of them will be most useful for later reports)
name rows reserved data index_size unused
tablename 0 2285104 KB 1473896 KB 1464 KB 809744 KB
there are 2 index, but non clustered.
I did a ALTER TABLE TableName rebuild
EXEC sp_spaceused 'tablename'
now gives:
name rows reserved data index_size unused
tablename 0 88 KB 16 KB 16 KB 56 KB
there are other huge tables
sp_spaceused 'tableName2'
name rows reserved data index_size unused
tableName2 71528 20374104 KB 19853440 KB 50864 KB 469800 KB
is it safe to get all these in a filtered list and routinely (weekly?) do a REBUILD on the table regardless of clustered index or not? (this also has a few nonclustered indexes)
it is a 24/7
datatype of table: (200+ columns.. probably 50 of them will be most useful for later reports)
numeric 9
numeric 9
varchar 20
varchar 12
date 3
varchar 12
varchar 11
varchar 9
varchar 25
varchar 15
varchar 1
varchar 25
varchar 30
date 3
varchar 20
varchar 40
varchar 40
varchar 30
varchar 3
varchar 10
varchar 14
varchar 40
varchar 25
varchar 14
varchar 25
varchar 3
varchar 10
varchar 24
varchar 24
varchar 10
varchar 20
varchar 25
varchar 30
varchar 4
varchar 3
varchar 15
varchar 3
numeric 9
numeric 9
varchar 3
varchar 6
varchar 3
varchar 3
varchar 3
varchar 25
varchar 12
varchar 20
varchar 12
varchar 4
numeric 17
numeric 17
numeric 17
numeric 17
numeric 9
varchar 1
varchar 1
numeric 17
varchar 1
varchar 1
varchar 1
varchar 3
varchar 30
varchar 15
date 3
numeric 9
varchar 20
varchar 20
numeric 9
varchar 3
date 3
numeric 17
numeric 17
varchar 3
varchar 3
varchar 3
varchar 3
varchar 20
numeric 17
varchar 24
numeric 17
varchar 4
varchar 1
varchar 1
varchar 30
varchar 3
varchar 25
varchar 3
varchar 20
varchar 1
date 3
date 3
date 3
varchar 20
varchar 25
varchar 15
varchar 40
varchar 14
date 3
varchar 20
varchar 40
varchar 50
date 3
date 3
varchar 3
varchar 8
varchar 8
varchar 10
varchar 12
varchar 25
varchar 20
varchar 40
varchar 3
varchar 20
varchar 3
varchar 3
varchar 20
varchar 3
numeric 9
varchar 10
varchar 75
varchar 3
varchar 20
varchar 20
varchar 20
varchar 3
varchar 75
varchar 75
varchar 4
varchar 4
numeric 9
varchar 10
varchar 75
varchar 75
date 3
varchar 75
varchar 4
varchar 3
varchar 10
varchar 10
varchar 10
varchar 10
varchar 3
varchar 20
varchar 3
varchar 20
varchar 40
varchar 5
varchar 4
varchar 40
varchar 40
varchar 40
varchar 30
varchar 3
varchar 5
varchar 4
varchar 8
varchar 12
varchar 3
varchar 4
varchar 60
varchar 12
varchar 12
varchar 12
varchar 30
varchar 3
varchar 20
varchar 20
varchar 12
varchar 12
varchar 3
varchar 3
varchar 3
varchar 3
varchar 8
varchar 5
varchar 8
varchar 5
varchar 25
varchar 3
varchar 20
varchar 3
varchar 3
varchar 250
numeric 17
numeric 17
varchar 20
varchar 20
varchar 20
varchar 30
varchar 4
varchar 4
date 3
varchar 20
varchar 75
varchar 20
varchar 20
numeric 9
varchar 25
varchar 20
varchar 3
varchar 20
varchar 250
varchar 20
varchar 3
varchar 20
varchar 20
varchar 20
varchar 20
varchar 20
varchar 20
varchar 3ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that helps me;thank u