AlwaysON database not synchronizing mode SQL service account change
Suddenly, one of our alwaysON server databases are went not synchronizing mode.
Errors from the log
The availability group database “DB” is changing roles from “RESOLVING” to “PRIMARY” because the mirroring session or availability group failed over due to role synchronization.… Read more
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… Read more
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… Read more
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… Read more
AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING
I have recently come across an issue that, one of my alwaysON secondary replica databases are went into “NOT SYNCHRONIZED and RECOVERY PENDING” state. It is a geo cluster alwaysON with 4+2 nodes configured in both synchronous and asynchronous mode.
The issue… Read more
Move database without breaking alwaysON
This post is going to show the database movement from one drive to another drive, without breaking the database from alwaysON configuration. An application has created the many databases to both primary and secondary replica servers to the default location of C drive.
There are… Read more
Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix.
I received many emails for performance tuning help. It has… Read more
Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier.
It’s a great script with lots of parameter and I am going to show, which are all more important.
Run the stored… Read more
I am not sure, how to name this topic. May be, start by following questions.
How important and effective is clustered index for a table?
How to solve the heap fragmentation?
Why my query is running slow most of the time for this particular table?
Are the primary… Read more
There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J
In my opinion, it’s very easy to work on… Read more
Recently one of my application website went down.
I checked all the basic connectivity troubleshooting and seem everything was looking and working fine. Finally, I found the problem with the browser service but that’s also in running state.
Error from the event viewer:
The quick solution is… Read more
How to create and setup a SQL clustering on your Desktop or Laptop
Login failed for [SQLSTATE 28000] (Error 18456) the step failed
When you query the data from another SQL server (Linked server) and SQL server & agent service is run as a domain user. You may get the error following error message in your job history.
Executed as user:… Read more
The transaction log for database is full. To find out why space in the log cannot be reused
Could not allocate space for object because the filegroup is full
Hello DBA it seems our application not working today. We got some error it looks like some problem with… Read more
Recently one of our SQL server unable to start it. The problem with the storage LUNs.
Things you have to do: When you get this above message while starting SQL server service.
Check the event viewer both system and application log. There you will know the exact problem. Read more
Use the simplest script to find the latest database backup and the location.
SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name, b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model, b.has_bulk_logged_data,b.is_copy_only,f.mirror FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) WHERE database_name='test' --AND B.type='L' ORDER BY b.backup_finish_date DESC
You can add more conditions on where clause http://msdn.microsoft.com/en-us/library/ms186299.aspx. Read more
SQL server clustering the timeout period elapsed prior to completion of the operation
Recently I faced one of our application could not connect. It throws an error ” Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error:… Read more
Microsoft’s SQL Server 2012 (AKA SQL Server code-name ‘Denali’), new details have been released http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx.
1. What is new in SQL Server 2012?
SQL Server 2012 will provide Mission Critical Confidence with greater uptime, blazing-fast performance and enhanced security for mission critical workloads; Breakthrough Insight with managed self-service…
SQL server could not start cannot find object or property (0×80092004)
Recently I got a call from my team the users couldn’t connect the SQL server and it’s throwing error. As always, I checked the SQL server status via configuration manager and noticed the server 2008 r2 stopped. I tried… Read more
AWE has removed from SQL server 2012
AWE has removed from SQL server Denali onwards. Say bye bye to 32 bit servers. To use more than 4GB memory you have to buy 64 bit servers.
A small organization may suffer because of this feature has removed from the next… Read more