How It Works: XEL Display in SQL Server Management Studio (SSMS) Row Limit

This is a simple issue but if you don’t expect the behavior it can surprise you. The grid, used by SSMS, is limited to a maximum number of rows, that can be displayed, of 1 million.    Note:  There is no warning dialog or flashing toolbar. Shown in the figure below is the display vs event…

3

How It Works: SQL Server (BCP, Database I/O, Backup/Restore, …) Reports Operating System Error (665, 1450 or 33) when writing to the file - BIG DATA

Suresh and I have blogged about these issues before but this post will put another spin on the information, as it applies to BIG DATA. Previous Blog References http://blogs.msdn.com/b/psssql/archive/2008/07/10/sql-server-reports-operating-system-error-1450-or-1452-or-665-retries.aspx http://blogs.msdn.com/b/psssql/archive/2009/03/04/sparse-file-errors-1450-or-665-due-to-file-fragmentation-fixes-and-workarounds.aspx http://blogs.msdn.com/b/psssql/archive/2009/01/20/how-it-works-sql-server-sparse-files-dbcc-and-snapshot-databases-revisited.aspx I ran into a 665 issue with a customer attempting to BCP data out of a database.  The scenario was that it worked if…

2

How It Works: When is the FlushCache message added to SQL Server Error Log?

FlushCache is the SQL Server routine that performs the checkpoint operation.  The following message is output to the SQL Server error log when trace flag (3504) is enabled. 2012-05-30 02:01:56.31 spid14s     FlushCache: cleaned up 216539 bufs with 154471 writes in 69071 ms (avoided 11796 new dirty bufs) for db 6:0 2012-05-30 02:01:56.31 spid14s                 average throughput: …

9

AlwaysON - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases

I am on several e-mail aliases related to Always On databases (reference Availability Group, AG, HADRON) and the question of worker thread usage is a hot topic this week.  I developed some training around this during the beta so I decided to pull out the relevant details and share them with you all.   Hopefully this…

6

How It Works: HealthCheckTimeout Interval Activities

As I wrote my recent blog posts and did more research I found that is would be helpful to highlight the HealthCheckTimeout behavior in more detail. Always On FCI (Failover Cluster Instance) vs Non-FCI Installations Documentation The first thing that I need to point out is the subtle wording difference in Books Online and other…

1

SQL Server 2012: RML, XEvent Viewer and Distributed Replay

Bob Ward, Keith Elmore and I establish goals for every release of SQL Server.   A primary goal is always to make supporting the SQL Server easier.  Not just in the sense of Microsoft support, although it does play a factor, but more so for you (the customer). During SQL Server 2012 planning we spent a…

5

How It Works: Failover Cluster/Availability Group XEL Logging Frequency

I had a great question from my post on sp_server_diagnostics (http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx).  As ‘luck’ would have it, Bob Ward, was working on some training and had a similar question.  As I investigated I uncovered a few details that can help us all. Connection Scope As soon as the Failover Cluster Instance (FCI) or Availability Group is…

2

How It Works: XEvent Action vs Field data values.

I have seen several traces and questions relating to the output of the XEvent so I thought I would try to explain them a bit in an effort to reduce confusion. Terms Field == Event Data Action == Action Data – The action data is gathered from the current session/request state.  Let’s look at the…

0

Management Studio tricks you may or may not know about

I was sitting through SQL Server 2012 training, and Ajay Jagannathan was showing us Management Studio.  Eric Burgess had worked on this topic.  He started covering some neat things, that have apparently been there for a while, but I just never played around with it enough to see what all you could do.  I’m sure…

7