April 14, 2011 at 11:21 pm
Hi All,
I used below query to find active db connections.
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
And the output as below
db_name noofconnection loginame
db_1 1 xxx\xxx4
db_1 1 xxx\xxx3
master 3 xxx\xxx2
master 16sa
db_1 1sa
WHERE dbid > 0
GROUP BY dbid, loginame
My questions is in the output it giving 3 connection to a db_1 database and 16 active connection to master database is any harm if i release connection from db_1 using alter database db_1 set 'sigle_user' with rollback immidiate
Why the connections are active if there is no activity going in the database.
Please guide
April 15, 2011 at 12:09 am
A connection will be registered until the client disconnects ( or if connection pooling is enabled, until it is released from the connection pool (I think this timeout value defaults to 60sec)) or sqlserver detects the connection is broken.
Have a look at Adams rewarded helper function: sp_WhoIsActive
http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
( and the gui that can be add on to ssms )
These connections will lose their db context with your statement.
Don't put a "go" in between your alter statement and the next statement on that db, so you are sure you are the single user having access to the db.
( otherwise chances are a concurrent connection performing a statement at the time your "go" statement runs will have the single user connection, shutting you out from db access)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2011 at 1:03 am
thanks for your respose. My concern is to move the database files to another disk drive now when i detach the database it says there are 3 active connection. So then find the active connection i used the above query and got the results and then the questions started in my mind 16 connections to master database what does it mean.
Also what is the effect on serer if i release the connection and if dont release the connection from dba perpespective
April 15, 2011 at 3:12 am
Very valid questions, but since you are planning on moving database files, (parts of ) your db will be offline for a while (to be able to actually move the db files to the new location)
so users will have to be kicked out anyway.
In your preparation double check your service account is able to use the new file locations (windows authority)
I always copy the empty file structure from source to target using xcopy.
rem /T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories.
rem /T /E includes empty directories and subdirectories.
rem /O Copies file ownership and ACL information
xcopy e:\MSSQL.1\MSSQL F:\MSSQL.1\MSSQL /E /T /O
This is a script I used to move db files (trying to avoid the detach methode)
/* Move/Rename database files */
sp_helpdb YourDatabase
-- SQL2005
alter database YourDatabase
MODIFY FILE ( NAME = YourDatabase, FILENAME = 'D:\MSSQL.1\MSSQL\DATA\YourDatabase.MDF' )
;
/*
alter database YourDatabase
MODIFY FILE ( NAME = YourDatabase_log, FILENAME = 'D:\MSSQL.1\MSSQL\DATA\YourDatabase_log.LDF' )
;
*/
/*
YourDatabaseD:\MSSQL.1\MSSQL\DATA\YourDatabase.MDF
YourDatabase_logD:\MSSQL.1\MSSQL\DATA\YourDatabase_log.LDF
*/
alter database YourDatabase set offline;
go
print 'NOW MOVE/RENAME THE ACTUAL FILES !!!!'
/* after the move completes, you need to put your db online
alter database YourDatabase set online;
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2011 at 3:35 am
Ok thanks this solved my a part of question.
Now it will be helpful if u put light on master 16 connection or say db_xyz has 4 connection still it is not in use why the connections are there and why they are not relesed
April 15, 2011 at 3:44 am
your instance has a number of connections to be able to run (for system processes)
These are mostly connected to master
in sys.processes system processes will have a number lower than 50
The script I provided can only be used for regular user databases !
If you also need to move systems databases ( master/model/msdb/tempdb)
please read SQL Server 2005 Books Online topic "Moving System Databases"
I forgot to mention to always start with regular sqlserver full database backups !!!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2011 at 3:50 am
Thanks alot for the response.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply