Ali Shaik's Database Diary

DBCC SHRINKFILE and DBCC SHRINKDATABASE are two SQL Server commands that allow you to shrink the size of a database or data file. This can be helpful if you have large amounts of unused space in your database that you want to reclaim. However, shrinking a database or data file can also lead to concurrency issues, especially if there are long-running transactions in progress. In the past, when encountering blockings while shrinking a database or data file, the only solution was to stop the shrinking process manually and allow the transaction to continue. This was not ideal, as it could lead to extended downtime and user frustration. With the release of SQL Server 2022, Microsoft has introduced a new feature called WAIT_AT_LOW_PRIORITY, which addresses this issue. This feature is an additional option for DBCC SHRINKDATABASE and DBCC SHRINKFILE. When you use the WAIT_AT_LOW_PRIORITY option, if a new shrink operation cannot obtain the necessary locks due to a long-running query already in ...

  1.        What is SQL Server build? 2.        What are the system DBs available in SQL Server, and what is the significance of each DB? 3.        How will you troubleshoot a log file growth issue that is causing disk space issues? 4.        What are startup parameters? 5.        What are the DBCC commands you use regularly? 6.        What are the DMVs you know, and what is the purpose? 7.        How will you troubleshoot blocking? 8.        What is the difference between locking and blocking and deadlock? 9.        What is the tail log backup, and when will it be useful? 10.    How does SQL Server identify differential backups? 11.    How will you verify logins and users usin...