Flash Tip: March 7, 2007

Flash Tip: Last_Batch in Master..Sysprocesses
By Brian Moran

Q: What does it mean when I see a value of 1900-01-01 00:00:00.000 in master..sysprocesses' last_batch column?

A: The last_batch name is misleading. The term “batch” in Microsoft SQL Server refers to a batch of SQL statements that's sent to the server and received by either the SQL listener or the remote procedure call (RPC) listener. The first type has a SQL:BatchCompleted event class in SQL Server Profiler; the second type has a RPC:BatchCompleted event class. However, SQL Server Books Online (BOL) says that the definition of last_batch in master..sysprocesses is the "last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the time at which SQL Server startup occurred is stored." That definition seems to mean that the value for last_batch isn't updated when a SQL:BatchCompleted event happens. But that's not true; the value will be updated when a SQL:BatchCompleted event happens.

The value will never be set, however, if a given connection doesn't execute at least one RPC:BatchCompleted event on the connection. Most clients issue at least one RPC call during the connection process. But it's possible to create a connection to SQL Server without issuing any RPC calls at all. In this case, the value of last_batch will never have been set and master..sysprocesses will display the value as 1900-01-01 00:00:00.000. This behavior is discussed in more detail in the Microsoft article "INF: Last Batch Date Is Seen as 1900-01-01 00:00:00.000" at https://support.microsoft.com/?kbid=306625.

Don't miss SQL Server Magazine in 2007. Subscribe now and get full access to must-have coverage on high availability, SQL Server & Office integration, business intelligence, clustering, reporting services, and much more. In addition, you'll get 12 issues and free access to the entire article archive. Order today and save 58 percent.