Recently I have come across one challenging issue and thought of sharing with you all.
We have a SQL Server instance with replication setup. This replication setup is little different than normal. Customer is using script based replication setup wherein for each table they have separate publisher and subscriber. There were around 100 such publishers.
The issue started when log reader agent at subscriber failed with the error message below:
"Replicated transactions are waiting for next Log backup or for mirroring partner to catch up."
Read the rest of this entry »
Corruption in your production database!! Always sounds scary isn’t it?
How about corruption in In-Memory OLTP table?? It’s even scarier…..
You may have situation where you have created an In-Memory database which contains both disk based and memory optimized tables. What will happen if you have corruption in one of the memory optimized tables? You will find the database in Restore_Pending state.
Easiest way to come out from this situation to restore from backup. Unfortunately in this situation we do not have backup so somehow we need to bring the database online. Remember, we cannot run DBCC CHECKDB on Memory optimized tables as well. Stuck, isn’t it? Read the rest of this entry »
While looking at a SQL server health report, I found affinity mask parameter in sp_configure output showing a negative value.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 -1066394617 -1066394617
This output was from a SQL Server 2008 R2 instance running on Windows Server 2008 R2.
Affinity mask is a SQL Server configuration option which is used to assign processors to specific threads for improved performance. To know more about affinity mask, read this. Usually, the value for affinity mask is a positive integer (decimal format) in sp_configure. The article in previous link shows an example of binary bit mask and corresponding decimal value to be set in sp_configure. Read the rest of this entry »
When we setup this blog, we didn’t know that this very first post will be so popular that it will alone have one-third of the total views on the site, that it will rank# 1 in the major search engines (as of today), see here and here. And we didn’t know that it has been **two wonderful years**, it just feels like yesterday that we setup this blog. Time flies! J
Yes, we are celebrating two years of http://sqlactions.com today, April 15th 2014. On behalf of the authors of sqlactions Manish, Karthic and Prashant, we would like to thank all our blog readers for the continued support. The increased no. of site views and followers, the technical discussion thru blog comments and some readers contacting us for their urgent productions issues… all have been phenomenal in the past two years.
As we go on this journey of SQL Server, we promise to publish the same quality blogs, covering more topics, latest versions and more of video blogs. If there is any special topic you would like us to blog about, please feel free to contact us.
To cherish the moment, here are top 10 blogs on sqlactions.com which were most viewed and most popular so far.
- Collection and Reporting of Perfmon data for SQL Server “Capacity Planning” and “Trend Analysis”
- Automated Reports for SQL Server Perfmon data
- How to create custom schedule for SQL Server Agent Job
- A read operation on a large object failed while sending data to the client
- SQL Agent Job reports error “SQLServerAgent is not currently running ” Though Agent Service is running
- Part-1 How to build SQL Server Failover Cluster Lab on Windows 8 – Also see part-2 and part-3
- DBCC MEMORYSTATUS : How is Stolen Potential calculated
- Latch Timeout: To worry or not to?
- Simple backup Strategy for Distribution database
- [Part-1]Let’s drill why you “Cannot generate SSPI context” – Also see part-2
Today I would like to discuss about one of the new enhancements done in SQL Server 2012 called LogPool.
Before we get into the details, let me try to explain how does the log flush occur in SQL Server. When a record is inserted or modified in a database, it is first copied into the log buffer in memory, these log buffers are like log blocks of different size (512 B to 60 KB). When this buffer is full or a commit is issued these log blocks are flushed to the transaction log. Read the rest of this entry »
Wish you all very happy new year 2014!!
This year we are planning to focus more on recording video blogs and in this series I decided to write my first video blog on Extended Events.
Below are the highlights of this video blog.
Introduction to Extended Events
Why Extended Events?
When to use Extended Events?
How it works?
Highlights of Demo
New Extended Events related DMV’s
Configure an Extended Event session using GUI
How to read the XEL files.
Some special troubleshooting tips!
Note: Please watch this video in HD for better quality. If you wish to download, please click on the drop menu on the top left corner of this video. Read the rest of this entry »