• AlwaysON,  SQL party

    AlwaysON RECOVERY PENDING SQL Server Bugs Enhancement Requests T-SQL Tuesday

    This month’s T-SQL Tuesday topic is “SQL Server Bugs & Enhancement Requests” and hosted by my favorite and inspirational Brent Ozar.     Here is my bug report: https://connect.microsoft.com/SQLServer/feedback/details/3022019 and it is still in active. It happen one of my production database, when I tried to remove the database from alwaysON and other important databases went recovery pending and inaccessible. I have no idea what happened to all other databases, then I came to know it is a bug.   ” The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state,…

  • VLDB

    VLDB very large database DBCC checkDB

    Database corruption – DBCC checkDB for Very large database We know SQL server data is stored in a filesystem storage. There has been always an (I/O) input and output interaction between SQL server and storage subsystem both in the memory and disk. IO subsystem plays a major role, 99% of the time database corruption can happen with IO subsystem (Such as in the controllers, disk and driver level etc.) In this post, I am sharing few things. 1. How important is the CHECKDB. 2. How to fine tune and use the checkDB for VLDBs. 3. Methods of troubleshooting the corruption issues. Storage / VM Admin:  Sent a graph states that, IOPS…

  • AlwaysON

    AlwaysON database not synchronizing suspect mode

      I got a call the database, not online/available mode, we suspect there was a corruption. One of my AlwaysON secondary database went suspect mode, it’s because of the log file and drive was full. I tried to resume the database. ALTER DATABASE dbname SET HADR RESUME;. It went “in recovery“ phase and fails. Since, it does not have even 1 MB space in the log file and drive to do a recovery phase. The drive has a good capacity for log, even though there was a huge transaction from application and the important one transaction log backup failed for five hours. I have a transaction log backup every five…

  • DBA,  VLDB

    TempDB database is Full and Optimization

    What is TempDB and best practice for TempDB TempDB is the system database and it is per instance. It is a common and shared by all other databases. All the temporary activities are done here and yes, definitely the TempDB will become full and occupy more space depends on the temporary tasks, which we are running. There are many activities can happen in tempDB. Best practice, create a TempDB in separate disk with the estimated initial file size, those are old days and now most of us using disk array. The spindles and HDDS/SSDs are striped through RAID and shared across LUNs and pools, check with your infra team about the…

  • AlwaysON

    How to solve the LSN mismatch in SQL server

      There are many times, we face the LSN mismatch issue in alwaysON and other HA technologies. It is a bit hard to find the missing transaction log backup to apply. Since, there are hundreds of thousands log generated, depends on the transaction log frequency and it can be run in any secondary alwaysON database server. Think about “the VLDB” and “the backup is in different data center” and database are out of sync in DR site because of LSN mismatch. For VLDB 8 TB database, we cannot take a full or differential backup to fix this. Since, it will take more and more time. Backup is in different data center…