AlwaysON Secondary database going to “Not Synchronizing/ Suspect” State!

In this blog post I will share an issue we had with a database which is configured with AlwaysON. Before proceeding any further, the environment which we’ve got is:

Each node has Windows Server 2008R2(With all the service packs and hot fixes recommended for AlwaysON)
Running on top of VMware VShpere 5.1
SQL Server 2012(SP1) Enterprise Edition
RAM: 10 GB (8 GB assigned to SQL Server).
2 VCPU’s.
Availability Mode- Synchronous Commit

Issue: Daily around 5 AM, the secondary database is going to “Not Synchronizing/Suspect” state and until we fix this the T-Log on primary grows and all that normal jazz once the AlwaysON databases get out of Sync…(See below)

1
So, what’s happening?
The App team is performing data load daily around 4.30 AM. Okay…So what’s bad about that? They are loading ~30 Million Records daily, in a single transaction. Oops!!!…
From SQL Server error logs, we see the below message:
Message

AlwaysOn Availability Groups data movement for database ‘Test_DB’ has been suspended for the following reason: “system” (Source ID 2; Source string: ‘SUSPEND_FROM_REDO‘). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
This message is always accommodated with another message(Shown below):
Message
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Ummm…This doesn’t looks good. If you are thinking, what Locks have to do with AlwaysON secondaries, let me tell you this. With Database Mirroring and AlwaysON Rollbacks/Redo thread will also take a lock on the secondary side to avoid any other transaction to interrupt REDO process, thus guaranteeing consistency. If for some reason SQL Server is not able to acquire locks for redo thread it won’t synchronize the database starting that point. (It’s by design).

In our case what’s happening was SQL Server was running out of memory and was not able to acquire any further locks(Remember, each lock structure in SQL Server will need certain amount of memory).Basically, it says “Since I wasn’t able to acquire a lock during the REDO, I don’t know what else happened at that time and I can’t guarantee the database to be consistent. So…am not going to synchronize from this point and I will suspend the data movement and also take the database to Suspect state”).

From AlwaysON standpoint, Suspending Synchronization when the REDO thread encounters any error is by design and is done on purpose by SQL Server.

To avoid this, all they(App team) have to do is optimize their load process to better manage lock acquisition.(We are not being granted any more memory on these boxes unfortunately).

Bottom Line: Avoid huge transactions on tiny SQL Servers. Try to split the transactions into multiple chunks especially when dealing with millions/billions of rows.  That helps in general many ways, not just in this particular scenario.

Have a safe and happy long weekend guys!

 

This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

This morning one of our developers contacted me regarding this error message he got when he tried to design a table from his SSMS.

1

Also, he reported that his SSMS goes to “Not Responding” state when he tries to edit rows using his SSMS. So…What’s happening?

He was trying to edit a table within a database which is running on SQL Server 2014 version using his SSMS 2012.  Now, all he needs is upgrading his client tools to 2014. Remember, you can manage/manipulate older versions of your SQL Servers using your 2014 SSMS, However be prepared to face these annoying issues if you are trying the other way.

Happy weekend folks!

 

Physical Server or a Virtual Machine?

Lot of times, when stepping up for supporting a new SQL Server Instance, we as a DBA need to know whether it’s running on a Physical Machine or a VM. In this post, I will provide you guys with a simple T-SQL Script which you can run from your client machine(SSMS) connecting to the respective SQL Server, which lets you know if it’s a Physical machine or a VM.

Note: This Script works only on 2008R2 SP1 and above.

SELECT SERVERPROPERTY('computernamephysicalnetbios') AS ServerName
,dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

/* If you have a CMS configured, run the below Script from your CMS against multiple servers*/
SELECT dosi.virtual_machine_type_desc
,Server_type = CASE 
WHEN dosi.virtual_machine_type = 1
THEN 'Virtual' 
ELSE 'Physical'
END
FROM sys.dm_os_sys_info dosi

Additional Comments:
“virtual_machine_type_desc” Output value description
NONE – SQL Server is not running inside a virtual machine.
HYPERVISOR – SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.

SQL Server 2012 SP2 RTM got released :)

Yesterday, microsoft released Service Pack 2 for SQL Sever 2012. They made Slipstream Installation packages available as well to the customers :)

And hey, you know what….CU1 for SQL Server 2012 SP2 is scheduled to release in July 2014. It’s time to upgrade all your SQL Server 2012 Instances to SP2!

SP2 can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=43340

For the list of bugs which are fixed in this Sevice pack, visit http://support.microsoft.com/kb/2958429

Couple of very critical bugs which got fixed: ( they are critical to me, YMMV) 

SQL Server crashes when LDF file for Tempdb is full.
Rollback causes SQL Server Database to go to Suspect mode.
No error message when Log backup fails in SQL Server 2012.

Cheers!

The Wait is Over-Much awaited SQL Server Service packs are just around the corner.

Hurray…! At laaaaaast Microsoft officially announced today that they are planning to ship one last Service pack for both SQL Server 2008 and 2008R2. Timing of the release will be little odd though. They will be available to public after July 8th 2014(After Mainstream support ends). This is defenitely a good news (In fact great news)  for companies which don’t let DBA’s to Install CU’s and just rely on Service Packs.

If you are curious, SQL Server 2008 SP3 was released back in 2011 October and 2008R2 SP2 was released in July 2012. Since then microsoft is releasing Cumulative updates.
Get ready for SQL Server 2008 SP4 and SQL Server 2008R2 SP3 folks  :)

See this blog post from SQL Server Team for more information.

Idera’s SQL Extended Events Profiler – concise and Elegant tool for SQL DBAs!

In this blog post, I would like to introduce all of you to a very cool and concise Free tool from Idera.

Note:  I Do not work for Idera. Idera has nothing to do with this blog post. Am expressing just my opinion with my experiences with this free tool.

Okay, so….what is this tool al about?

If you are a SQL Server DBA, you might have already been in a situation where you had to run SQL Server trace on your SQL Server(s) per a developer request or for investigation purposes or some thing else.  Profiler, which comes with SQL Server installtion is a very heavy tool, especially if you are  not filtering out your events when running your trace. To avoid any overhead this graphical tool(profiler) causes, we could use “Server Side tracing” which basically runs the trace server side and stores the results either in a file or a table without the need for using profiler(Which constantly displays the results, which is very resource intensive). Impressive…But not as impressive as “Extended Events”. MSFT introduced “Extended Events” in SQL Server 2008 which is the low level/High performance eventing solution. Slowly and steadily…I am seeing people moving away from traces and Profiler which is nice.  If you are a SQL Server Profiler person, I’ve got a sad news for you, Profiler is depricated in SQL Server 2014(Instead, microsoft is asking you to use Extended events session(s) which is embedded in your SSMS under management Node). You might not like this initially, especially the way how you’ve to interact with XEvents from SSMS. This is where this free tool from Idera comes handy. This is very similar to how you would interact with Profiler(Selecting templates, events, filtering columns etc), but instead of starting a trace, it pulls info from Extended events which are very light weight and has significantly less over head on your SQL Server.

See below Screenshots on what am talking about…If you are already familiar with Profiler, this doesn’t need any further explanation on how to get started with this free tool.

1

Once connected, choose your template

2

Filter out columns as needed…

3

As you can see this could be very helpful tool to track down few things on your SQL Server without hindering it’s performance. Once you’ve got what you are lookin for, you can export the results as well.  This is a free tool, you can download it from idera and play with it and see if it meets your requirements.

PS Discussing the true power of Extended events which microsoft introduced for us is way beyond scope of this blog post.

Cheers!

SQL Won’t come up after Installing Service pack!!

Hello folks! In this blog post, I would like to share a strange experience which I’ve never experienced till today while Installing Service packs for SQL Server.

Well, I’m in the process of Installing new SQL Server(2012) on Windows Server 2012. Installed RTM…Everything went well. Now I kicked off applying Service pack 1 for SQL Server 2012. Erkk…..SQL services won’t come up and the event viewer says “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.” and the one which is shown below!!!…

1

 “Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

Whatttt….?? Really? Nothing useful found in the SQL Error logs as well :( My immediate thought was to run Repair and I kicked off the repair wizard. It took its own sweet time. 25 mins passed and I got this stupid message:(

2

Agian, Nothing useful found in the SQL Error logs :( End result is it couldn’t fix it :(

3

This is not good…!

So what’s the work around/Fix for this issue which worked for me in this case?

I changed the Service account for SQL Server to Local System and Boom…it worked :) SQL Server DB Engine Service started and the build number has been updated as well! Once am done with Installing Service pack and the CU, I changed it back to the appropriate domain account(as it was earlier).

So, this sounds to me like some missing permissions issue but am not 100% sure what exactly it was looking for. Please let me know if you’ve encountered this issue in the past and what was the resolution.