Display date: connections

显示标签为“connections”的博文。显示所有博文

2012年3月20日星期二

Discounted pass to SQL Connections Conference in Las Vegas next week.

For anyone interested, I have a full pass to the SQL Connections
conference in Las Vegas next week which I won in last year's drawing.
I cannot go and so I am selling it for the best offer I get. Just
reply to this post if you are interested. The full cost of this pass
is $1395. Don't miss out if you are interested. It is a good
opportunity to help improve your SQL skills.
IlanEbay? :-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Aspersage wrote:
> For anyone interested, I have a full pass to the SQL Connections
> conference in Las Vegas next week which I won in last year's drawing.
> I cannot go and so I am selling it for the best offer I get. Just
> reply to this post if you are interested. The full cost of this pass
> is $1395. Don't miss out if you are interested. It is a good
> opportunity to help improve your SQL skills.
> Ilan

Discounted pass to SQL Connections Conference in Las Vegas next week.

2012年3月19日星期一

Disconnect/Clear connections

I have a scheduled job where I'm attempting to restore a full backup of our
Production database, over our Test/Dev database.
However, it keeps failing.
When I try to run the Restore Database command manually, I get an error that
states "Exclusive access could not be obtained".
Right now, the only way I know how to clear the connections is to go into
the Detach Database task, and select 'Clear all connections'.
I tried using the 'Exec SQL Disconnect ALL' command, but that statement
won't parse.
Is there another way to clear all database connections? Preferebly in
Trasact-SQL language, that I could schedule to run immediately prior to the
Restore Database command?
Please advise.
Thanks!
YsandreYsandre wrote:
> I have a scheduled job where I'm attempting to restore a full backup of our
> Production database, over our Test/Dev database.
> However, it keeps failing.
> When I try to run the Restore Database command manually, I get an error that
> states "Exclusive access could not be obtained".
> Right now, the only way I know how to clear the connections is to go into
> the Detach Database task, and select 'Clear all connections'.
> I tried using the 'Exec SQL Disconnect ALL' command, but that statement
> won't parse.
> Is there another way to clear all database connections? Preferebly in
> Trasact-SQL language, that I could schedule to run immediately prior to the
> Restore Database command?
> Please advise.
> Thanks!
> Ysandre
Read up on the ALTER DATABASE command
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I did, but I don't see anything in there regarding clearing connections to
the database.
Is there somewhere else I should look?
All I'm looking for is something along the lines of:
EXEC SQL DISCONNECT ALL
Or why the above doesn't parse.
Thanks!
Ysandre
"Tracy McKibben" wrote:
> Ysandre wrote:
> > I have a scheduled job where I'm attempting to restore a full backup of our
> > Production database, over our Test/Dev database.
> > However, it keeps failing.
> > When I try to run the Restore Database command manually, I get an error that
> > states "Exclusive access could not be obtained".
> > Right now, the only way I know how to clear the connections is to go into
> > the Detach Database task, and select 'Clear all connections'.
> > I tried using the 'Exec SQL Disconnect ALL' command, but that statement
> > won't parse.
> > Is there another way to clear all database connections? Preferebly in
> > Trasact-SQL language, that I could schedule to run immediately prior to the
> > Restore Database command?
> > Please advise.
> > Thanks!
> > Ysandre
> Read up on the ALTER DATABASE command
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||In short: When you go to Detach/Clear in the EMC to disconnect all the
users it is going to do a couple things.
First - It searches through the sysprocesses for the DB you want to
clear and finds the spid's
Then - set noexec off set parseonly off
Lastly - kill spid#
Hope this helps.
Cheers,
Ysandre wrote:
> I did, but I don't see anything in there regarding clearing connections to
> the database.
> Is there somewhere else I should look?
> All I'm looking for is something along the lines of:
> EXEC SQL DISCONNECT ALL
> Or why the above doesn't parse.
> Thanks!
> Ysandre
> "Tracy McKibben" wrote:
> > Ysandre wrote:
> > > I have a scheduled job where I'm attempting to restore a full backup of our
> > > Production database, over our Test/Dev database.
> > > However, it keeps failing.
> > > When I try to run the Restore Database command manually, I get an error that
> > > states "Exclusive access could not be obtained".
> > > Right now, the only way I know how to clear the connections is to go into
> > > the Detach Database task, and select 'Clear all connections'.
> > > I tried using the 'Exec SQL Disconnect ALL' command, but that statement
> > > won't parse.
> > > Is there another way to clear all database connections? Preferebly in
> > > Trasact-SQL language, that I could schedule to run immediately prior to the
> > > Restore Database command?
> > > Please advise.
> > > Thanks!
> > > Ysandre
> >
> > Read up on the ALTER DATABASE command
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
> >|||You could try something like this. Replace the DBID with the database
you are backing up:
DECLARE SpidsToKill CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = 9
DECLARE @.Spid int
DECLARE @.SQL varchar(255)
OPEN SpidsToKill
FETCH NEXT FROM SpidsToKill
INTO @.Spid
WHILE ( @.@.FETCH_STATUS = 0 )
BEGIN
SELECT @.SQL = 'KILL ' + CONVERT( varchar(10), @.Spid )
EXEC ( @.SQL )
FETCH NEXT FROM SpidsToKill
INTO @.Spid
END
CLOSE SpidsToKill
DEALLOCATE SpidsToKill|||Ysandre wrote:
> I did, but I don't see anything in there regarding clearing connections to
> the database.
You want to use ALTER DATABASE to put the database in SINGLE_USER mode,
WITH immediate ROLLBACK of transactions...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That worked.
THANK YOU!!!
"10001110101" wrote:
> You could try something like this. Replace the DBID with the database
> you are backing up:
> DECLARE SpidsToKill CURSOR FOR
> SELECT spid
> FROM master..sysprocesses
> WHERE dbid = 9
> DECLARE @.Spid int
> DECLARE @.SQL varchar(255)
> OPEN SpidsToKill
> FETCH NEXT FROM SpidsToKill
> INTO @.Spid
> WHILE ( @.@.FETCH_STATUS = 0 )
> BEGIN
> SELECT @.SQL = 'KILL ' + CONVERT( varchar(10), @.Spid )
> EXEC ( @.SQL )
> FETCH NEXT FROM SpidsToKill
> INTO @.Spid
> END
> CLOSE SpidsToKill
> DEALLOCATE SpidsToKill
>|||Ysandre wrote:
> That worked.
> THANK YOU!!!
>
Ugh, it's too painful to watch... Drop that cursor nonsense, you can
accomplish this with ONE STATEMENT. Since you can't read documentation
and piece together the command yourself, here it is:
ALTER DATABASE DBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That will put the database in single-user mode, allowing one connection
only, and will gracefully rollback any outstanding transactions.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_141C_01C6AF0A.34C3D1A0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
The problem with the script above to 'kill' sessions, is that a session =may immediately reconnect.
The BEST solution, is as Tracy advised. Use
ALTER DATABASE MyDatabase
SET SINGLE_USER WTIH ROLLBACK IMMEDIATE
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message =news:%23EmybC0rGHA.1592@.TK2MSFTNGP02.phx.gbl...
> Ysandre wrote:
>> I did, but I don't see anything in there regarding clearing =connections to >> the database.
> > You want to use ALTER DATABASE to put the database in SINGLE_USER =mode, > WITH immediate ROLLBACK of transactions...
> > > -- > Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
--=_NextPart_000_141C_01C6AF0A.34C3D1A0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The problem with the script above to ='kill' sessions, is that a session may immediately reconnect.
The BEST solution, is as Tracy advised. =Use
ALTER DATABASE =MyDatabase
SET SINGLE_USER =WTIH ROLLBACK IMMEDIATE
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
"Tracy McKibben" =wrote in message news:%23EmybC0rGHA.1592@.TK2MSFTNGP02.phx.gbl...> =Ysandre wrote:> I did, but I don't see anything in there regarding =clearing connections to > the database.> > You want to =use ALTER DATABASE to put the database in SINGLE_USER mode, > WITH =immediate ROLLBACK of transactions...> > > -- > Tracy McKibben> MCDBA>">http://www.realsqlguy.com</FONT></A></BODY></HTML>
--=_NextPart_000_141C_01C6AF0A.34C3D1A0--

Disconnect/Clear connections

Disconnect from sql server database

Hi
I have c# application that uses SQL SERVER databases.
The problem is that after I close all connections to a database in order to
make actions (detach, copy or delete) on the current database I get a
message saying that the database still has connections.
At the Enterprise manager I can see that the current database still has 2
connections although I closed them in my application.
I would like to disconnect from the database through the application and
make the actions that were mentioned above, how can I do that?
Thanks,
Eyal
On SQL SERVER 2000, you can disconnect all the current connections with:
ALTER DATABASE your_database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
DROP DATABASE your_database etc..
You need USE master, otherwise you're still connected yourself.
Jacco Schalkwijk
SQL Server MVP
"Eyal" <eg@.margan.com> wrote in message
news:eYltEA3BFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hi
>
> I have c# application that uses SQL SERVER databases.
> The problem is that after I close all connections to a database in order
> to
> make actions (detach, copy or delete) on the current database I get a
> message saying that the database still has connections.
>
> At the Enterprise manager I can see that the current database still has 2
> connections although I closed them in my application.
>
> I would like to disconnect from the database through the application and
> make the actions that were mentioned above, how can I do that?
>
> Thanks,
> Eyal
>

Disconnect from sql server database

Hi
I have c# application that uses SQL SERVER databases.
The problem is that after I close all connections to a database in order to
make actions (detach, copy or delete) on the current database I get a
message saying that the database still has connections.
At the Enterprise manager I can see that the current database still has 2
connections although I closed them in my application.
I would like to disconnect from the database through the application and
make the actions that were mentioned above, how can I do that?
Thanks,
Eyal
My guess is that you are using connection pooling. This is the default
setting for SqlClient. Do please check about connection pooling in .Net
documentation if this is your problem.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Eyal" <eg@.margan.com> wrote in message
news:eAAkq$2BFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi
>
> I have c# application that uses SQL SERVER databases.
> The problem is that after I close all connections to a database in order
to
> make actions (detach, copy or delete) on the current database I get a
> message saying that the database still has connections.
>
> At the Enterprise manager I can see that the current database still has 2
> connections although I closed them in my application.
>
> I would like to disconnect from the database through the application and
> make the actions that were mentioned above, how can I do that?
>
> Thanks,
> Eyal
>

Disconnect from sql server database

Hi
I have c# application that uses SQL SERVER databases.
The problem is that after I close all connections to a database in order to
make actions (detach, copy or delete) on the current database I get a
message saying that the database still has connections.
At the Enterprise manager I can see that the current database still has 2
connections although I closed them in my application.
I would like to disconnect from the database through the application and
make the actions that were mentioned above, how can I do that?
Thanks,
EyalMy guess is that you are using connection pooling. This is the default
setting for SqlClient. Do please check about connection pooling in .Net
documentation if this is your problem.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Eyal" <eg@.margan.com> wrote in message
news:eAAkq$2BFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Hi
>
> I have c# application that uses SQL SERVER databases.
> The problem is that after I close all connections to a database in order
to
> make actions (detach, copy or delete) on the current database I get a
> message saying that the database still has connections.
>
> At the Enterprise manager I can see that the current database still has 2
> connections although I closed them in my application.
>
> I would like to disconnect from the database through the application and
> make the actions that were mentioned above, how can I do that?
>
> Thanks,
> Eyal
>

Disconnect Client Connection via T-SQL/Store Proce

Hi Gents,
If there are some client connections on the database and I would like to
detach the file, it's failed when execute the store proc "sp_detach_db". Any
SQL statement or Store Procedure on clearing/kicking out the current client
connections? Thanks in advance.
Regards,
CurtisLYou can use the KILL command.|||Moreover, using kill <id> with statusonly. So this way I can see the rollbac
k
progression out of danger.
"markc600@.hotmail.com" wrote:

> You can use the KILL command.
>

Disconnect all connections

Hi All,
I trying to write a script that will detach a DB copy the physical file to a new location then remount the original DB.
the Problem that i am having it detaching the DB as i get error telling me that the DB is in use.
There is an Access front end open with and ODBC connection to the SQL DB and this needed to be open to as it is where the command to create the Copy is coming from.

I have tried the following code
USE master ALTER DATABASE EclipseSQL SET SINGLE_USER with no_wait
exec sp_detach_db 'EclipseSQL'
exec xp_cmdshell 'MKDIR "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices" '

exec xp_cmdshell 'ECHO Y| cacls "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" /p everyone:f'

exec xp_cmdshell 'xCopy "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices\" '

exec sp_Attach_db 'EclipseSQL', 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf'

i get the following output from this

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'EclipseSQL'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'EclipseSQL' because it is currently in use.

(2 row(s) affected)

(3 row(s) affected)
Msg 1801, Level 16, State 3, Line 1
Database 'EclipseSQL' already exists. Choose a different database name.

any know how i get disconnect all users no questions asked?

Try:

Code Snippet

alterdatabase EclipseSQL setread_onlywithrollback immediate

alterdatabase EclipseSQL setread_writewithrollback immediate

|||That did the trick thanks

2012年3月7日星期三

Disadvantages of using uniqueidentifier (Tell your Tale)

Dear SQL...

I'm about to useuniqueidentifier andNEWID() for my new DB,

The DB may grow wild and I want to super safely create connections between tables,
so it would not just rely on numbers (that can B changed) as used with IDENTITY...

If U had some BAD experience with this or if U have any tips I can use please let me know

Should I take any special steps when usingindex on it ?I found a GREAT post that helped a lot:
Seeded(int) Identity columns vs. UniqueIdentifiers(GUIDs)

But still -what about the INDEX ?

1. Should I put an index on a uniqueidentifier column at all ?
2. Will the index consume big SQL Server space ? (what proportion)

Is it a good idea to save it as achar string instead of uniqueidentifier ?
(will it help to manage the index better N faster)|||Hi,

I love GUIDs as surrogate primary keys. They solve a lot of problems and make coding simpler. And for most apps, the slight performance degradation is nothing compared to network latency and other factors.

No, don't bother indexing them separately. There's nothing to order; order doesn't matter at all.

Don|||Don, so what will happen if I'll have a big table ?
I guess that as the table gets bigger selecting one record will take longer...

Am I wrong ? - will SQL Server not just go throgh *ALL* records to find one ?

+
What is your opinion about saving the GUID as a string of chars ?
(will allow using the speed of an index ?)|||No, it won't necessarily. That's why I said aseparate index. If you use it as the primary key, SQL Server indexes it for you. That's necessary for it to be a primary key. I see that I said that poorly. Hope this clarifies it.

But also to clarify, you're going to use it as a primary key, right? You didn't say that in your posts, but you referenced the Seeded(int) Identity columns vs. UniqueIdentifiers(GUIDs) article, which suggests you are.

Nope on saving it as a string of characters. You'll just be circumventing the performance optimizations Microsoft built in.

Don|||Thanks 4 your educated answers :-)

I am going to use it for my Primary keys...

ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTable] DEFAULT (newid()) FOR [MyTable_ID],
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[MyTable_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_MyTable] UNIQUE NONCLUSTERED
(
[MyTable_ID]
) ON [PRIMARY]
GO

(am I right on the NONCLUSTERED ?)

About saving the GUIDs as a string of chars:
I thought that maybe I can also add a prefix (like Date()) to each GUID
so that I can create an effective CLUSTERED index instead of NONCLUSTERED
(using a string of chars or uniqueidentifier this is really my last dilemma on this issue...)

I may also need to create "replications" of this database (which I know none on...)
should I use uniqueidentifier (and not a string of chars) to allow replication ?

Thanks in advanced, Yovav.|||> If you use it as the primary key, SQL Server indexes it for you

yes. and it indexes it as clustered if you don't specify otherwise.

if you're using a guid as a primary key (i regularly do... the benefits far outweight the cost, especially when used in oo applications where you're rarely joining tables anyway), you want to make sure it's NOT clustered. otherwise when you go to insert a new record, data may have to be moved around, as guids could come in any sequence.

i would suggest using a guid as a pk. make it non-clustered. pick something else as your clustered index - normally whatever you would want to order by as default.

2012年2月14日星期二

disable external connections to sql server 2000

Hi,
I am trying to disable external connections to my sql server installation:
removed (disabled) all the network protocols, restarted the server machine,
but I can still run EM and connect to that sql server. What am I missing to
disable the external connections?
Thank you,
VadimHi
Did you remove the client protocols instead of the server ones? What machine
are you connecting from?
John
"Vadim" wrote:

> Hi,
> I am trying to disable external connections to my sql server installation:
> removed (disabled) all the network protocols, restarted the server machine
,
> but I can still run EM and connect to that sql server. What am I missing t
o
> disable the external connections?
> Thank you,
> Vadim
>
>

disable external connections to sql server 2000

Hi,
I am trying to disable external connections to my sql server installation:
removed (disabled) all the network protocols, restarted the server machine,
but I can still run EM and connect to that sql server. What am I missing to
disable the external connections?
Thank you,
Vadim
Hi
Did you remove the client protocols instead of the server ones? What machine
are you connecting from?
John
"Vadim" wrote:

> Hi,
> I am trying to disable external connections to my sql server installation:
> removed (disabled) all the network protocols, restarted the server machine,
> but I can still run EM and connect to that sql server. What am I missing to
> disable the external connections?
> Thank you,
> Vadim
>
>

disable buffer cache - very urgert !

I would like to disable the buffer cache. How to do that?
We have setup load test where connections with "same user activities" are
concurrently hitting the database. The problem with the "set of activities"
are same for all connections and worried whether the data will be buffered?
and it wont be real test for performance?
Thanks,
RamuYou can't disable it, but you can minimize it (sp_configure and "max server
memory") and/or you can
flush the buffer (DBCC DROPCLEANBUFFERS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6113D3F2-8409-4874-A043-9E31A0D00213@.microsoft.com...
>I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of activities
"
> are same for all connections and worried whether the data will be buffered
?
> and it wont be real test for performance?
> Thanks,
> Ramu
>|||"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:6113D3F2-8409-4874-A043-9E31A0D00213@.microsoft.com...
>I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of
> activities"
> are same for all connections and worried whether the data will be
> buffered?
> and it wont be real test for performance?
>
Testing with a cold cache is _very_ unrealistic. Testing with an unusually
warm cache may be somewhat unrealistic, but no load test is perfect.
Without real production data and a real production workload, you can only
use your load-test results for analyzing performance bottlenecks and
generating rough estimates of capacity and throughput.
David|||Ramu wrote:
> I would like to disable the buffer cache. How to do that?
> We have setup load test where connections with "same user activities" are
> concurrently hitting the database. The problem with the "set of activities
"
> are same for all connections and worried whether the data will be buffered
?
> and it wont be real test for performance?
> Thanks,
> Ramu
>
Query results ARE NOT CACHED, only the raw data pages from the
underlying tables are cached. SQL Server uses some very complex
algorithms for caching data to minimize disk reads in order to improve
performance. Since that's part of normal operation, it seems like you
would want that reflected in your load test. Regardless, you can't
disable it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

订阅：博文 (Atom)

2012年3月20日星期二

Discounted pass to SQL Connections Conference in Las Vegas next week.

Discounted pass to SQL Connections Conference in Las Vegas next week.

2012年3月19日星期一

Disconnect/Clear connections

Disconnect/Clear connections

Disconnect from sql server database

Disconnect from sql server database

Disconnect from sql server database

Disconnect Client Connection via T-SQL/Store Proce

Disconnect all connections

2012年3月7日星期三

Disadvantages of using uniqueidentifier (Tell your Tale)

2012年2月14日星期二

disable external connections to sql server 2000

disable external connections to sql server 2000

disable buffer cache - very urgert !

Display date

Blog Archive

About Me