September 30, 2024 at 12:54 pm
Hi there! This should make some eyes roll for some of you DBA's out there. Disclaimer: I am not a DBA and shouldn't be trying to play one.
I desperately need help in trying to remedy a problem I caused on our 2016 SQL Server (Std Edition). We realized that the weekly maintenance job hadn't been running for years on this server and index fragmentation was off the charts. I was able to successfully drop and rebuild indexes on some tables showing up with > 90% fragmentation but one of the largest tables seems to be deadlocking everything else on the server when I try to re-add the constraint. I can also see that the table is now a heap and the old index size is now showing as unused space in the table. How in the heck can I rebuild the index/PK constraint without causing any locking of other processes? None of them were trying to hit the table in question. I did this during server downtime but there was still stuff running and I was worried that if I didn't kill the index rebuild it would cause other issues on the server.
Any help or suggestions would be greatly appreciated! I realize that this was likely a very junior mistake.
Table Stats:
# records: 100,378,478
reserved: 45,482,128
data: 44,683,544
indexes: 464 (after drop)
unused: 798,120
I just used the default drop/create statement that ssms generated for me:
/****** Object: Index [pk_tablename] Script Date: 9/25/2024 12:39:18 PM ******/ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [pk_tablename] WITH ( ONLINE = OFF )
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [pk_tablename] Script Date: 9/25/2024 12:39:20 PM ******/ALTER TABLE [dbo].[tablename] ADD CONSTRAINT [pk_tablename] PRIMARY KEY CLUSTERED
(
[cono] ASC,
[tstm] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply