Finding user connected to a Database in SQL Server 2008


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

more ▼

asked Dec 29, 2009 at 03:50 AM in Default

avatar image

22 2 2 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Dec 29, 2009 at 09:44 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 29, 2009 at 04:50 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 29, 2009 at 03:50 AM

Seen: 5781 times

Last Updated: Dec 29, 2009 at 04:51 AM

Copyright 2018 Redgate Software. Privacy Policy