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...

  To view Microsoft documentation for SQL Server and its features, you can visit the following website: https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15 This website contains a wealth of information on SQL Server, including documentation on installation, configuration, administration, and development. It also provides information on the latest features and enhancements in each version of SQL Server. To find specific information on a particular feature or topic, you can use the search bar on the website or navigate through the different categories and sections. You can also find tutorials, videos, and other resources to help you learn more about SQL Server and its features. Overall, the Microsoft documentation website is a valuable resource for anyone working with SQL Server, whether you are a database administrator, developer, or data analyst.

  Kerberos and NTLM authentication methods in SQL Server.   Kerberos Authentication: Kerberos is a network authentication protocol that is used to provide secure authentication between clients and servers in a Windows environment. In SQL Server, Kerberos authentication can be used to authenticate users without the need to provide a username and password each time they connect to the server. Kerberos authentication works by using a ticket-based authentication system . When a user logs into a Windows machine, their credentials are verified by the Domain Controller (DC) and a Kerberos ticket is issued. This ticket is then used to authenticate the user to other network resources, including SQL Server.   To use Kerberos authentication with SQL Server, the following requirements must be met:   Ø   The SQL Server instance must be running on a Windows Server operating system. Ø   The SQL Server service account must be a domain account. Ø   ...

 https://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/ https://learn.microsoft.com/en-us/sql/connect/jdbc/troubleshooting-connectivity?view=sql-server-ver16 https://sqlbits.com/Sessions/Event14/Think_Inside_the_Box_Understanding_the_Query_Life_Cycle

  Telnet and Test-NetConnection (TNC) commands are used for network connectivity testing, but they have some key differences in how they work and what they can be used for:   Telnet Command: The Telnet command is an older, text-based protocol that allows you to establish a connection to a remote host or server over the network. It is often used to test the connectivity of a specific port on a remote server or device. Telnet can be used to check whether a specific port is open, to test network connectivity, or to establish a remote session on a server that supports Telnet. However, Telnet is not a secure protocol and should not be used for sensitive data transmission.   Test-NetConnection (TNC) Command: The Test-NetConnection command is a modern command-line tool that is included in Windows operating systems starting from Windows 8. It allows you to test network connectivity to a remote server or device and provides more detailed output than Telnet. It can be u...

      If your database is not synchronizing between the primary and secondary replicas in an Always On Availability Group, there may be several reasons why this is happening. Here are some possible causes and solutions:   Check the replica synchronization state: First, check the status of the primary and secondary replicas to see if they are both online and healthy. You can do this by checking the replica state using the "sys.dm_hadr_database_replica_states" dynamic management view or by checking the Always On Dashboard in SQL Server Management Studio. If the replica synchronization state is not healthy or if there are synchronization issues, you can try to troubleshoot the issue by looking at the error messages or event logs.   Check the network connectivity: Always On Availability Groups require a reliable and fast network connection between the primary and secondary replicas. Make sure that there are no network connectivity issues or that th...