We were using the below query to find any users connected to a particular database in MSDE 2000.
select * from master..sysprocesses a, master..sysdatabases b where a.dbid = b.dbid and b.name = ''
Now we are planning to upgrade the Database to SQL Server 2008, After upgrading to 2008 and restored the SQL Server 2000 database backup. We are always getting a record with lastwaittype as "CHECKPOINT_QUEUE" in the above query result.
I want to know the following
1.Why the checkpoint_queue Record is coming? 2.How to avoid checkpoint_queue record?
Can anyone help me on this? Thanks in Advance.
Regards, Karthick R
Since you've moved to 2008, I'd strongly suggest you start taking advantage of the new functionality available there. To see who is currently on the system you can query the dynamic management view (dmv) sys.dm_exec_requests. If you filter it by adding to the WHERE clause SESSION_ID > 50, then you'll also eliminate the system connections, which will keep you from seeing stuff like the checkpoint queue.
There is a lot you can do from the dmv, including connecting it to other dmv's to get the query being run, execution plans and all kinds of other information.
answered Dec 29, 2009 at 09:44 AM
Grant Fritchey ♦♦
A checkpoint is when the system syncs all the information in the transaction log into the MDF/NDF files, so that the log can be truncated. It uses a queuing mechanism to do this, saying "When you have a moment, do this..."
Presumably you don't actually mean that you're looking for
answered Dec 29, 2009 at 04:50 AM