question

Karthick avatar image
Karthick asked

Finding user connected to a Database in SQL Server 2008

Hi,

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

sql-server-2008checkpoint
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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 b.name = '', but more like, a specific database. If the checkpoint is the only thing you're seeing there, then it means no-one is connected to the system, and the only thing going on is making sure that the transaction log is empty.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.