Issue with create statement in SQL Server 2016

Question

Hi Team,

Today we are facing a strange issue.
Like even simple create statement is not executing on this DB

Version: SQL Server 2016 with CU 17 on Windows Server 2012 VM

this DB is around 4.5 TB (Part of Always on) no DR.

example:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
);

this statement ran for 8 mins still not completed and i canceled.
Don't know what's going on.
This issue was started in the past 2 days and no recent changes.

Answer 1

Sounds like a blocking issue, like a process holding a lock on system tables.

You could use my beta_lockinfo to investigate the blocking. Start the CREATE TABLE statement and look for rows with !! in the BlkLvl column. That is the root blocker.

Answer 2

Hi，@Vijay Kumar

Welcome to Microsoft T-SQL Q&A Forum!

If you are recreating a dropped table then I feel that the definition of the table must conform to the foreign key constraints referencing it.
It must have the correct column names and types, and must have an index on the referenced key. If not, does the system prompt any error message or code?

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Vijay Kumar

Here is the official article about how to investigate the blocking，please check out this thread：https://learn.microsoft.com/en-US/troubleshoot/sql/performance/understand-resolve-blocking

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.