0

I have a problem where my query that has the DROP TABLE command causes DBCC SHRINKFILE to timeout.

We are using NHibernate as ORM and .NET. The Hangfire is used for the jobs.

The scenario is like this: Hangfire runs a job, in which all the queries are executed one by one. One of the commands is also a shrink file which timeout in the end because it is blocked by some implicit transaction in the background (which I cannot find :/) I found out that if I remove DROP TABLE command from one specific query, the problem no longer exists. I have tried to run the query and then shrink from SSMS, it executes without any problems. There is nothing suspicious in the table, and all the constraints are removed before DROP, the only thing is that the table has a lot of rows but from SSMS it drops the table in less than a second. Extending the statement timeout for the shrink did not help, I have tried to wait for 2+ hrs but without any results, it still timeout. When I try to search for the blocking transaction it all leads to the hangfire job, which is basically all the queries...

Does anyone have any idea how to trace why DROP TABLE from code causes SHRINKFILE to be blocked...?

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Staša Sekulić
  • 17
  • 6
  • 7
    Why are you shrinking files in the first place? Shrinking is generally *not* a good idea. – Thom A Aug 17 '23 at 12:48
  • 5
    Probably it's busy running `sp_clean_db_free_space` to get rid of the dropped table's data (which is not removed until either that is run, or the Ghost Cleanup Process picks it up and this is the reason the `DROP` is very fast). Unless you have a *pressing* need for the space taken up by the database, do not shrink it, it's a waste of time and effort. – Charlieface Aug 17 '23 at 12:52
  • 2
    https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ – Sean Lange Aug 17 '23 at 12:58
  • I have to do the shrink because the log file is 20gb+ and after shrinking it is way smaller. I cannot remove the shrink step from the system, it is mandatory for it to stay... If we do not do it our db files are going to be 5x bigger, which is not acceptable. – Staša Sekulić Aug 18 '23 at 10:21
  • Sounds like you need to investigate why the logs are growing. Perhaps you need to sort out your backups, or switch to `SIMPLE` model if you are not doing backups. Shrinking a DB regularly is an extremely bad idea, do it only as a one off if you *really* need the space. – Charlieface Aug 18 '23 at 17:36

0 Answers0