How to trouble shoot a deadlock problem

I have a job that took 1 hour and 55 mintes to run, which is causing deadlock situatin with another database. How would I go about troubleshoot this problem.
Thanks
LystrWell, you could run Enterprise Manager, go to Manage->Current activity and look at process info to find the locking process. Look at the locks / process id / object.

Try running your queries with the (nolock) hint or use temp tables.|||The message I am getting is:

The VB Application identified by the event source logged this Application ANSDAC: Thread ID: 2768, Logged: Microsoft OLE DB Provider for ODBC Drivers Session.Save() -2147467259 [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosed as the deadlock victim. Rerun the transaction.

Would I need to rerun the process ID 66?|||Yes, whatever query/statement being run by that process was aborted due to deadlocking. It must be re-run.

Try running your job on non-peak/business hours, if possible. What job are you running, btw?|||Then name of the job is call Archive IIS Log which consists of 4 steps
1-Update department-run sp_Update_Log
2-Copy to Archive Database-run sp_ArchiveLog
3-Clean up Log-run sp_cleaningLog
4-Free log space-Backup Log IISLog with No_Log

It runs at 9am everyday which is a busy time. The developer feel that the solution should be to move the database to another server. The Job did finish it just took 1 and 55 minutes, but no one can tell me how long do the job normally take.

So I should run each stored procedure.

Also, when I look at process ID 66
this is what I see"

SELECT MonthEnd."Accounting_date", Manual_Adjustment."Accounting date", Manual_Adjustment."Adjustment_amount", Manual_Adjustment."CheckNumber" FROM { oj "MonthEnd" MonthEnd INNER JOIN "ManualAdjustment" Manual_Adjustment ON MonthEnd."Accounting_date" = Ma

However this is not the database that they are complaining about. Is it possible for 66 to be connected with more than 1 database?

Thanks

Lystra|||use Trace Flags. Check out in Books Online about them else search on google key words [TRACE FLAG, DEADLOCK].|||Yes, process 66 can change the database it is running against at any time.

-When it is blocking, look at the blocking process (the job process) and see what database and query/statement it is running.

-Also look at running it at off peak hours if at all possible. If it is something that updates a bunch of records in a single table you are bound to cause locking. Espcially if this thing runs that long.

-On the database they are complaining about - do the job scripts have a lot of references to this table or are there only a few? If there are a few it should be easier to pinpoint which statement is causing the locking.

-Try starting Sql Profiler before the job runs and let it run to track all sql statements made by the job. As soon as the blocking stops, look to see if any long queries just finished running in the profiler. These could be the culprits.

Once you know the statements causing the issue you can better determine how to resolve the issue.|||First order of business, go beat the developer soundly for scheduling an automated process during a high usage time for users. That may take a while to beat them soundly, but it is an important step, so I'll wait while you finish.

Now that they've been beaten, go back and beat them again. That might make them think twice about such idiocy in the future.

Ok, that is as good a preventative as we can manage at the moment, so now we should move on to correcting the underlying problem. Pick the lowest usage period of the day when you can be certain that all the affected systems will be available. 03:00 works well for me, although some folks prefer 20:00 or something like it. Change the job so that it now runs at a low usage time instead of a high usage time.

After the job runs at its new time, check the SQL Server and NT logs for any error messages. Sometimes there are hidden dependancies that can trip you up when you move a job. If those both check out, then check the log tables that this process maintains to be sure that they also look as you'd expect.

Once you get done cleaning up the problem, go hose the blood off of the developer and carry them to a nice quiet closet somewhere to sleep off the beatings... There's no point in being mean about things, you just have to curb that behavior problem!

-PatP|||The database is called ISSLOG, which log all of the traffic from the web. Now why would this logging database cause deadlocking? The database log information like, clienthost, username, logtime, service, machine, serverIP, processing time, etc..,

I have added the code please look to see why it would cause a deadlock issue.

Thanks

Lystra|||I think my eyes just popped. I think if code1, code2 and code3 are run one after the other, there might be some other processing running that's causing the deadlock. If Code2 and Code3 are run at the same time, it might cause some trouble.|||Then name of the job is call Archive IIS Log which consists of 4 steps
1-Update department-run sp_Update_Log (CODE 3)
2-Copy to Archive Database-run sp_ArchiveLog (CODE 1)
3-Clean up Log-run sp_cleaningLog (CODE 3)
4-Free log space-Backup Log IISLog with No_Log

The code is ran one after the other.

If the code is looking to log traffic why should it create deadlock, because it's not looking at rows of data.

Thanks|||First order of business, go beat the developer soundly for scheduling an automated process during a high usage time for users. That may take a while to beat them soundly, but it is an important step, so I'll wait while you finish.

Now that they've been beaten, go back and beat them again. That might make them think twice about such idiocy in the future.

Ok, that is as good a preventative as we can manage at the moment, so now we should move on to correcting the underlying problem. Pick the lowest usage period of the day when you can be certain that all the affected systems will be available. 03:00 works well for me, although some folks prefer 20:00 or something like it. Change the job so that it now runs at a low usage time instead of a high usage time.

After the job runs at its new time, check the SQL Server and NT logs for any error messages. Sometimes there are hidden dependancies that can trip you up when you move a job. If those both check out, then check the log tables that this process maintains to be sure that they also look as you'd expect.

Once you get done cleaning up the problem, go hose the blood off of the developer and carry them to a nice quiet closet somewhere to sleep off the beatings... There's no point in being mean about things, you just have to curb that behavior problem!

-PatP

What pat said, but just for good measure, beat them again after the problem's sorted, then hose them down. ;)

How to trap EXECUTE failure

We are running SQL 2000 sp3a on Win2003 Server with all patches and SPs applied.

I want to run a stored procedure from a SQL Agent job. I want the stored procedure to run as a TRANSACTION to force a ROLLBACK in case of failure. However, if execute the procedure and an error of severity level 20 (I think) or greater happens I get blown out of the job immediately. Therefore I cannot call RAISEERROR or use @.@.ERROR. So, how do I assure that I can ROLLBACK the TRANSACTION?

I have searched the web and BOL and cannot find anything that addresses this specifically.

Thanks in advance for any help.Severity level 20 indicates a fatal error that the session encountered. The batch terminates and the connection is severed. The fate of your transaction is predetermined, - it will roll back. You (your spid) cannot roll it back with ROLLBACK because the batch execution will not reach that point.

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remov
e
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.
|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.
|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.
sql

How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
--
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.

How to test a stored procedure ? how ?

Ok ,
I'm new in stored procedures, I created one, so I want to test it without
going and coding a program in C# or VB.NET, is it any way to run it where it
will ask my parameters and get the result using the SQL Enterprise Manager ?
ThanksEasiest in Query Analyzer:
EXEC usp_proc_name
@.param1 = 123,
@.param2 = 'ABC'
David Portas
SQL Server MVP
--|||In addition to executing within Query Analyzer, there is also a feature to
run the procedure step by step in debug mode. Read up on "Transact-SQL
Debugger" in Books Online or MSDN.
"Carlos" <cp@.swa.com> wrote in message
news:%23$UsSnCyFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Ok ,
> I'm new in stored procedures, I created one, so I want to test it without
> going and coding a program in C# or VB.NET, is it any way to run it where
> it will ask my parameters and get the result using the SQL Enterprise
> Manager ?
> Thanks
>|||Ok I used the Query analyzer now where do I see my returned value ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
> Easiest in Query Analyzer:
> EXEC usp_proc_name
> @.param1 = 123,
> @.param2 = 'ABC'
> --
> David Portas
> SQL Server MVP
> --
>|||Pass a variable and specify OUTPUT. A nice feature of Query Analyzer is
that it will script the EXEC statement for you. Press F8 to open the
Object Browser, right-click on your proc, click "Script Object to new
window as" and then "EXECUTE". Result:
DECLARE @.RC int
DECLARE @.param1 int
DECLARE @.param2 varchar(10)
-- Set parameter values
EXEC @.RC = [JUNK].[dbo].[usp_proc_name] @.param1 OUTPUT , @.param2

>From the same point in the Object Browser you can also access the
debugger as JT suggested.
David Portas
SQL Server MVP
--|||declare @.x as int
If you want to print the result of an output parameter:
exec myproc @.a=1, @.b=2, @.c = @.x output
print @.x
If you want to print the result of a return value:
EXECUTE @.x = myproc @.a=1, @.b=2
print @.x
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Can you post a sample of what you are trying to test? It would be much
easier for us to determine what you exactly want to test.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Carlos" <cp@.swa.com> wrote in message
news:ufE8JxCyFHA.448@.TK2MSFTNGP11.phx.gbl...
> Ok I used the Query analyzer now where do I see my returned value ?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128351195.556320.319300@.g49g2000cwa.googlegroups.com...
>|||Hi Carlos,
Here are some articles for your reference about how to debug SQL Server
stored procedures and the necessary configuration settings and steps for
each approach
HOW TO: Debug Stored Procedures in Visual Studio .NET
http://support.microsoft.com/kb/316549/EN-US/
Troubleshooting tips for T-SQL Debugger in Visual Studio .NET
http://support.microsoft.com/kb/817178
Debugging Stored Procedures
http://www.windowsitpro.com/Article...6356/16356.html
You may also search keywords debug SQL stored procedure in google.com for
more
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

How to tell whether SQL Server is listening on port 1433 and port

If I run netstat -a on a machine with a default instance of SQL Server 2000 and the port has not been changed, should I see port 1433 and port 1434? Does it matter which SQL Server service pack has been applied?
Hi,
The port should be 1433.
You can the see the port SQl Serevr Listening by-
1. Open Server network utility
2. In the enabled protocols -- Select TCP /IP and click properties to get
the PORT number.
Note:
For Default Instance SQL Server listen on port 1433, which is static.
Thanks
Hari
MCDBA
"peter" <peter@.discussions.microsoft.com> wrote in message
news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> If I run netstat -a on a machine with a default instance of SQL Server
2000 and the port has not been changed, should I see port 1433 and port
1434? Does it matter which SQL Server service pack has been applied?
>
|||Port 1433 is used by default only for the default (unnamed) instance
of the server. Named instances communicate over random prt numbers
unless you set them for port 1433 using the Server Network Utility.
Look under Program files and run the Svrnetcn.exe file that is in the
SQL Server Tools\Binn folder
See the article:
http://support.microsoft.com/default.aspx?kbid=814130
On Sun, 20 Jun 2004 08:02:18 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>The port should be 1433.
>You can the see the port SQl Serevr Listening by-
>1. Open Server network utility
>2. In the enabled protocols -- Select TCP /IP and click properties to get
>the PORT number.
>--
>Note:
>For Default Instance SQL Server listen on port 1433, which is static.
>Thanks
>Hari
>MCDBA
>
>
>"peter" <peter@.discussions.microsoft.com> wrote in message
>news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
>2000 and the port has not been changed, should I see port 1433 and port
>1434? Does it matter which SQL Server service pack has been applied?
>

How to tell whether SQL Server is listening on port 1433 and port

If I run netstat -a on a machine with a default instance of SQL Server 2000 and the port has not been changed, should I see port 1433 and port 1434? Does it matter which SQL Server service pack has been applied?Hi,
The port should be 1433.
You can the see the port SQl Serevr Listening by-
1. Open Server network utility
2. In the enabled protocols -- Select TCP /IP and click properties to get
the PORT number.
--
Note:
For Default Instance SQL Server listen on port 1433, which is static.
Thanks
Hari
MCDBA
"peter" <peter@.discussions.microsoft.com> wrote in message
news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> If I run netstat -a on a machine with a default instance of SQL Server
2000 and the port has not been changed, should I see port 1433 and port
1434? Does it matter which SQL Server service pack has been applied?
>|||Hi Hari,
I can see port 1433 in the Server Network Utility. But I don't see port 1433 when I run netstat -a to check what port is active. I'm using Windows XP SP2 RC2 and have disabled Windows Firewall.
Thank you for your help,
Peter
"Hari Prasad" wrote:
> Hi,
> The port should be 1433.
> You can the see the port SQl Serevr Listening by-
> 1. Open Server network utility
> 2. In the enabled protocols -- Select TCP /IP and click properties to get
> the PORT number.
> --
> Note:
> For Default Instance SQL Server listen on port 1433, which is static.
> Thanks
> Hari
> MCDBA
>
>
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> > If I run netstat -a on a machine with a default instance of SQL Server
> 2000 and the port has not been changed, should I see port 1433 and port
> 1434? Does it matter which SQL Server service pack has been applied?
> >
> >
>
>|||Hi,
Login to SQL server using a query analyzer window and issue the below
command from command prompt
nbtstat -n
In the local address you iwl be able to see the server ip addree:1433
Otherwise :- , From command prompt Telnet and see whether the port 1433 is
accessible.
TELNET ipaddress 1433
If the port is accessible, automatically the connection will be accessible
else u will get an error.
Thanks
Hari
MCDBA
"peter" <peter@.discussions.microsoft.com> wrote in message
news:142340D1-70EB-4F27-BF82-E2C8ED172081@.microsoft.com...
> Hi Hari,
> I can see port 1433 in the Server Network Utility. But I don't see port
1433 when I run netstat -a to check what port is active. I'm using Windows
XP SP2 RC2 and have disabled Windows Firewall.
> Thank you for your help,
> Peter
> "Hari Prasad" wrote:
> > Hi,
> >
> > The port should be 1433.
> >
> > You can the see the port SQl Serevr Listening by-
> >
> > 1. Open Server network utility
> > 2. In the enabled protocols -- Select TCP /IP and click properties to
get
> > the PORT number.
> >
> > --
> >
> > Note:
> > For Default Instance SQL Server listen on port 1433, which is static.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> > "peter" <peter@.discussions.microsoft.com> wrote in message
> > news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> > > If I run netstat -a on a machine with a default instance of SQL Server
> > 2000 and the port has not been changed, should I see port 1433 and port
> > 1434? Does it matter which SQL Server service pack has been applied?
> > >
> > >
> >
> >
> >|||Port 1433 is used by default only for the default (unnamed) instance
of the server. Named instances communicate over random prt numbers
unless you set them for port 1433 using the Server Network Utility.
Look under Program files and run the Svrnetcn.exe file that is in the
SQL Server Tools\Binn folder
See the article:
http://support.microsoft.com/default.aspx?kbid=814130
On Sun, 20 Jun 2004 08:02:18 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:
>Hi,
>The port should be 1433.
>You can the see the port SQl Serevr Listening by-
>1. Open Server network utility
>2. In the enabled protocols -- Select TCP /IP and click properties to get
>the PORT number.
>--
>Note:
>For Default Instance SQL Server listen on port 1433, which is static.
>Thanks
>Hari
>MCDBA
>
>
>"peter" <peter@.discussions.microsoft.com> wrote in message
>news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
>> If I run netstat -a on a machine with a default instance of SQL Server
>2000 and the port has not been changed, should I see port 1433 and port
>1434? Does it matter which SQL Server service pack has been applied?
>>
>

How to tell whether SQL Server is listening on port 1433 and port

If I run netstat -a on a machine with a default instance of SQL Server 2000
and the port has not been changed, should I see port 1433 and port 1434? Do
es it matter which SQL Server service pack has been applied?Hi,
The port should be 1433.
You can the see the port SQl Serevr Listening by-
1. Open Server network utility
2. In the enabled protocols -- Select TCP /IP and click properties to get
the PORT number.
Note:
For Default Instance SQL Server listen on port 1433, which is static.
Thanks
Hari
MCDBA
"peter" <peter@.discussions.microsoft.com> wrote in message
news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> If I run netstat -a on a machine with a default instance of SQL Server
2000 and the port has not been changed, should I see port 1433 and port
1434? Does it matter which SQL Server service pack has been applied?
>|||Port 1433 is used by default only for the default (unnamed) instance
of the server. Named instances communicate over random prt numbers
unless you set them for port 1433 using the Server Network Utility.
Look under Program files and run the Svrnetcn.exe file that is in the
SQL Server Tools\Binn folder
See the article:
http://support.microsoft.com/default.aspx?kbid=814130
On Sun, 20 Jun 2004 08:02:18 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>The port should be 1433.
>You can the see the port SQl Serevr Listening by-
>1. Open Server network utility
>2. In the enabled protocols -- Select TCP /IP and click properties to get
>the PORT number.
>--
>Note:
>For Default Instance SQL Server listen on port 1433, which is static.
>Thanks
>Hari
>MCDBA
>
>
>"peter" <peter@.discussions.microsoft.com> wrote in message
>news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
>2000 and the port has not been changed, should I see port 1433 and port
>1434? Does it matter which SQL Server service pack has been applied?
>

How to tell whether SQL Server is listening on port 1433 and p

Hi Hari,
I can see port 1433 in the Server Network Utility. But I don't see port 1433 when I run netstat -a to check what port is active. I'm using Windows XP SP2 RC2 and have disabled Windows Firewall.
Thank you for your help,
Peter
"Hari Prasad" wrote:

> Hi,
> The port should be 1433.
> You can the see the port SQl Serevr Listening by-
> 1. Open Server network utility
> 2. In the enabled protocols -- Select TCP /IP and click properties to get
> the PORT number.
> --
> Note:
> For Default Instance SQL Server listen on port 1433, which is static.
> Thanks
> Hari
> MCDBA
>
>
> "peter" <peter@.discussions.microsoft.com> wrote in message
> news:17C8C6CA-4929-41B8-BA6E-2FEF6AAB91BD@.microsoft.com...
> 2000 and the port has not been changed, should I see port 1433 and port
> 1434? Does it matter which SQL Server service pack has been applied?
>
>
Hi,
Login to SQL server using a query analyzer window and issue the below
command from command prompt
nbtstat -n
In the local address you iwl be able to see the server ip addree:1433
Otherwise :- , From command prompt Telnet and see whether the port 1433 is
accessible.
TELNET ipaddress 1433
If the port is accessible, automatically the connection will be accessible
else u will get an error.
Thanks
Hari
MCDBA
"peter" <peter@.discussions.microsoft.com> wrote in message
news:142340D1-70EB-4F27-BF82-E2C8ED172081@.microsoft.com...
> Hi Hari,
> I can see port 1433 in the Server Network Utility. But I don't see port
1433 when I run netstat -a to check what port is active. I'm using Windows
XP SP2 RC2 and have disabled Windows Firewall.[vbcol=seagreen]
> Thank you for your help,
> Peter
> "Hari Prasad" wrote:
get[vbcol=seagreen]
sql

how to tell if a login has already been created

I want to create a script that will run and create a predetermined set of logins AND dbrolemembership but only if they do not presently exist in the database. I have played around with sp_addlogin and sp_addrolemember. I then checked the sysusers and syslogins table to determine the existence. I cannot get a row to insert into sysusers. Can someone elighten me on where the login & rolemembership is stored. I also spent some time looking for a proc that might give me what i need.

any direction or comments are appreciated.

MikeRole membership is stored in sysusers table of each database (except for tempdb) and login info in master.dbo.syslogins. You right on track based on sp's and tables you mentioned. All you need to do is script those sp's (sp_addlogin and sp_addrolemember) in your QA and you'll have all the answers yourself.|||Thanks for the response.

I have written the scritps to create the users and add them to the correct roles. I would like to test to see if the user and /or role(s) are already existing when I run the script.

PRINT '===> Add logins'
EXEC sp_addlogin 'pubuser','pubuser'
EXEC sp_addlogin 'opsuser','opsuser'

PRINT '===> Add user, attach login and assign permissions for users'
USE Main
EXEC sp_grantdbaccess 'pubuser'
EXEC sp_addrolemember 'db_datareader','pubuser'
EXEC sp_addrolemember 'db_datawriter','pubuser'

EXEC sp_grantdbaccess 'opsuser'
EXEC sp_addrolemember 'db_datareader','opsuser'
EXEC sp_addrolemember 'db_datawriter','opsuser'

The issue / question is when I select * from syslogins I can see the pubuser and opsuser. However, I cannot see a cooresponding entry on sysusers. Still missing a piece somewhere?

mike|||I just ran your script and replaces Main with my database, and when I do a SELECT from sysusers of that database I see both users added. Did I miss anything?

And of course, you'd do something like "if not exist (select1 from master.dbo.syslogins where name = <new_login>) exec sp_addlogin <...>" to validate the presence or abscence of a login, same with users, etc.|||I was using the master sysusers table.

Thanks

how to stop conversing status?

Once my target service start to process, how can I stop the rest from running?

right now I'm looping sys.conversation_endpoints and run "END CONVERSATION @.dhValue"

Is there a simpler way to stop processing a service without interfering with other services?

I don't understand what you mean by "stopping the rest from running". If you want a way to stop an activated stored procedure from running, you could either kill the spid it is running on, or disable the queue which will cause it to abort.

Rushi

|||

yes, that' s it.

thanks