How to configure the logs

How do you configure the logs in SQL Server? Currently, all I see is my
backup processes. I'm trying to investigate a deadlock and I need much
more detail in the logging. I know I've done it before, but I can't
seem to find it now.Rick
Check out DBCC TRACEON in the BOL
"Rick Harrison" <rick@.knowware.com> wrote in message
news:40867FEE.3050505@.knowware.com...
> How do you configure the logs in SQL Server? Currently, all I see is my
> backup processes. I'm trying to investigate a deadlock and I need much
> more detail in the logging. I know I've done it before, but I can't
> seem to find it now.
>|||Hi,
To add on to previous post,
you can also you SQL profiler-- In the event class use the event Deadlock.
Otherwise like Uri pointed out use
DBCC TRACEON(1204)
This will send the deadlock information to SQL server error log.
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1CYZq6JEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Rick
> Check out DBCC TRACEON in the BOL
> "Rick Harrison" <rick@.knowware.com> wrote in message
> news:40867FEE.3050505@.knowware.com...
> > How do you configure the logs in SQL Server? Currently, all I see is my
> > backup processes. I'm trying to investigate a deadlock and I need much
> > more detail in the logging. I know I've done it before, but I can't
> > seem to find it now.
> >
>

How to configure ports in SQL server configuration manager

Hi,
unfortunately I mixed up the ip settings in SQL server configuration manager
(I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
have an event in the application protocol (3), it says that configuration for
SQL instances "bkupexec" and "sqlexpress" are invalid.
I tried 100 different settings in SQL configuratin manager, but the events
are remaining. I cannot find out to configure this. Backup Exce for example
has nothing where I could configure a port address for communicating with its
SQL instance.
Can someone provide me with information about what to configure in SQL
Server Configuration Manager (and probably in Backup Exec - Symantec did not
answer until now) to avoid the error messages?
Thanx in advance,
Tim
Hi
"Tim Meltzer" wrote:

> Hi,
> unfortunately I mixed up the ip settings in SQL server configuration manager
> (I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
> have an event in the application protocol (3), it says that configuration for
> SQL instances "bkupexec" and "sqlexpress" are invalid.
> I tried 100 different settings in SQL configuratin manager, but the events
> are remaining. I cannot find out to configure this. Backup Exce for example
> has nothing where I could configure a port address for communicating with its
> SQL instance.
> Can someone provide me with information about what to configure in SQL
> Server Configuration Manager (and probably in Backup Exec - Symantec did not
> answer until now) to avoid the error messages?
> Thanx in advance,
> Tim
By default SQLExpress will not have remote connections enabled. If you are
using a database on the same server then you should not need to enabled
TCP/IP.
Have you checked these instances of SQL Server are running using the service
applet or typing NET START at a command prompt?
In the Network Protocols branch under SQL Server Configuration Manager, is
the protocol Enabled? If you double click the tcp/ip entry that you will get
the IP and port settings in the properties dialog.
John
|||Hi John,
thanks for input.

> In the Network Protocols branch under SQL Server Configuration Manager, is
> the protocol Enabled?
Which protocol? I tried them all, the error no. 3 remained.

> If you double click the tcp/ip entry that you will get
> the IP and port settings in the properties dialog.
Yes, but I don't no, wether these IP settings are the right ones for Backup
Exec, I don't know, what exactly it is what Windows is complaining about.
Finally I uninstalled BE and SQL Server and reinstalled it, but without
success.
You stated, that TCP is not needed if everything is running on one server.
So I think I'm going to ignore the message since everything seems to work.
Thanks again.
|||Hi Tim
"Tim Meltzer" wrote:

> Hi John,
> thanks for input.
>
> Which protocol? I tried them all, the error no. 3 remained.
>
> Yes, but I don't no, wether these IP settings are the right ones for Backup
> Exec, I don't know, what exactly it is what Windows is complaining about.
> Finally I uninstalled BE and SQL Server and reinstalled it, but without
> success.
> You stated, that TCP is not needed if everything is running on one server.
> So I think I'm going to ignore the message since everything seems to work.
> Thanks again.
It is not clear where exactly where the error message is being displayed! Is
this in Backup exec, SQL Server error log or the Windows Event log?
If after installing Backup Exec again what protocols are enabled?
John

How to configure ports in SQL server configuration manager

Hi,
unfortunately I mixed up the ip settings in SQL server configuration manager
(I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
have an event in the application protocol (3), it says that configuration fo
r
SQL instances "bkupexec" and "sqlexpress" are invalid.
I tried 100 different settings in SQL configuratin manager, but the events
are remaining. I cannot find out to configure this. Backup Exce for example
has nothing where I could configure a port address for communicating with it
s
SQL instance.
Can someone provide me with information about what to configure in SQL
Server Configuration Manager (and probably in Backup Exec - Symantec did not
answer until now) to avoid the error messages?
Thanx in advance,
TimHi
"Tim Meltzer" wrote:

> Hi,
> unfortunately I mixed up the ip settings in SQL server configuration manag
er
> (I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
> have an event in the application protocol (3), it says that configuration
for
> SQL instances "bkupexec" and "sqlexpress" are invalid.
> I tried 100 different settings in SQL configuratin manager, but the events
> are remaining. I cannot find out to configure this. Backup Exce for exampl
e
> has nothing where I could configure a port address for communicating with
its
> SQL instance.
> Can someone provide me with information about what to configure in SQL
> Server Configuration Manager (and probably in Backup Exec - Symantec did n
ot
> answer until now) to avoid the error messages?
> Thanx in advance,
> Tim
By default SQLExpress will not have remote connections enabled. If you are
using a database on the same server then you should not need to enabled
TCP/IP.
Have you checked these instances of SQL Server are running using the service
applet or typing NET START at a command prompt?
In the Network Protocols branch under SQL Server Configuration Manager, is
the protocol Enabled? If you double click the tcp/ip entry that you will get
the IP and port settings in the properties dialog.
John|||Hi John,
thanks for input.

> In the Network Protocols branch under SQL Server Configuration Manager, is
> the protocol Enabled?
Which protocol? I tried them all, the error no. 3 remained.

> If you double click the tcp/ip entry that you will get
> the IP and port settings in the properties dialog.
Yes, but I don't no, wether these IP settings are the right ones for Backup
Exec, I don't know, what exactly it is what Windows is complaining about.
Finally I uninstalled BE and SQL Server and reinstalled it, but without
success.
You stated, that TCP is not needed if everything is running on one server.
So I think I'm going to ignore the message since everything seems to work.
Thanks again.|||Hi Tim
"Tim Meltzer" wrote:

> Hi John,
> thanks for input.
>
> Which protocol? I tried them all, the error no. 3 remained.
>
> Yes, but I don't no, wether these IP settings are the right ones for Backu
p
> Exec, I don't know, what exactly it is what Windows is complaining about.
> Finally I uninstalled BE and SQL Server and reinstalled it, but without
> success.
> You stated, that TCP is not needed if everything is running on one server.
> So I think I'm going to ignore the message since everything seems to work.
> Thanks again.
It is not clear where exactly where the error message is being displayed! Is
this in Backup exec, SQL Server error log or the Windows Event log?
If after installing Backup Exec again what protocols are enabled?
John

How to configure ports in SQL server configuration manager

Hi,
unfortunately I mixed up the ip settings in SQL server configuration manager
(I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
have an event in the application protocol (3), it says that configuration for
SQL instances "bkupexec" and "sqlexpress" are invalid.
I tried 100 different settings in SQL configuratin manager, but the events
are remaining. I cannot find out to configure this. Backup Exce for example
has nothing where I could configure a port address for communicating with its
SQL instance.
Can someone provide me with information about what to configure in SQL
Server Configuration Manager (and probably in Backup Exec - Symantec did not
answer until now) to avoid the error messages?
Thanx in advance,
TimHi
"Tim Meltzer" wrote:
> Hi,
> unfortunately I mixed up the ip settings in SQL server configuration manager
> (I'm running SBS 2003 R1 with Backup Exec 11d and SQL 2005 Express). Now I
> have an event in the application protocol (3), it says that configuration for
> SQL instances "bkupexec" and "sqlexpress" are invalid.
> I tried 100 different settings in SQL configuratin manager, but the events
> are remaining. I cannot find out to configure this. Backup Exce for example
> has nothing where I could configure a port address for communicating with its
> SQL instance.
> Can someone provide me with information about what to configure in SQL
> Server Configuration Manager (and probably in Backup Exec - Symantec did not
> answer until now) to avoid the error messages?
> Thanx in advance,
> Tim
By default SQLExpress will not have remote connections enabled. If you are
using a database on the same server then you should not need to enabled
TCP/IP.
Have you checked these instances of SQL Server are running using the service
applet or typing NET START at a command prompt?
In the Network Protocols branch under SQL Server Configuration Manager, is
the protocol Enabled? If you double click the tcp/ip entry that you will get
the IP and port settings in the properties dialog.
John|||Hi John,
thanks for input.
> In the Network Protocols branch under SQL Server Configuration Manager, is
> the protocol Enabled?
Which protocol? I tried them all, the error no. 3 remained.
> If you double click the tcp/ip entry that you will get
> the IP and port settings in the properties dialog.
Yes, but I don't no, wether these IP settings are the right ones for Backup
Exec, I don't know, what exactly it is what Windows is complaining about.
Finally I uninstalled BE and SQL Server and reinstalled it, but without
success.
You stated, that TCP is not needed if everything is running on one server.
So I think I'm going to ignore the message since everything seems to work.
Thanks again.|||Hi Tim
"Tim Meltzer" wrote:
> Hi John,
> thanks for input.
> > In the Network Protocols branch under SQL Server Configuration Manager, is
> > the protocol Enabled?
> Which protocol? I tried them all, the error no. 3 remained.
> > If you double click the tcp/ip entry that you will get
> > the IP and port settings in the properties dialog.
> Yes, but I don't no, wether these IP settings are the right ones for Backup
> Exec, I don't know, what exactly it is what Windows is complaining about.
> Finally I uninstalled BE and SQL Server and reinstalled it, but without
> success.
> You stated, that TCP is not needed if everything is running on one server.
> So I think I'm going to ignore the message since everything seems to work.
> Thanks again.
It is not clear where exactly where the error message is being displayed! Is
this in Backup exec, SQL Server error log or the Windows Event log?
If after installing Backup Exec again what protocols are enabled?
John

How to configure a job for maintenance plan?

hello, everyone:
I have a maintenane plan to backup user database daily, How to configure a job to execute this plan daily? Thanks
zytLook up SQLMAINT or XP_SQLMAINT in BOL, depending on whether you want to run the job as OS command or T-SQL, respectively.

How to clear all database connection using SQLDMO?

Hello guys! I'm using SQLDMO to restore a database backup. The problem is, the program cannot restore the backup if there are active connections using the database. Is there are way to clear all database connections using SQLDMO (or other libraries)? Thanks in advance

You could create a storedproc in master that kills any existing spids per db, then run that proc from DMO as part of the restore job.. something like this

CREATE procedure sp_KillOldspids

@.dbName varchar (30) = NULL
AS

set nocount on

declare @.currentspid int,@.cmdstring varchar(30)

dECLARE OPENSPIDS CURSOR FOR SELECT [SPID] from [MASTER].[DBO].[SYSPROCESSES] WHERE DBID = db_id (@.dbName)
OPEN OPENSPIDS

FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.cmdString = 'kill ' + convert(varchar(10), @.CURRENTspid)
IF @.@.SPID <> @.CURRENTSPID
BEGIN
EXEC (@.cmdString)
END
FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

END

CLOSE CURRENTSPIDS
DEALLOCATE CURRENTSPIDS

hopes this helps.

How to check/verify SQL login Credential

Hi,

I am making a module to backup SQL Db from webpage. I impersonate asp.net user to a given Windows Account and need to verify that account has required permission in SQL Server.

I don't know how to verify the windows account credential for SQL Server login.

Now what I am doing is to run SQL query against database to select row from some table, if it is successful, the user is valid SQL login. But I know it's not the way.

Any idea?

Thanks,

Why don't you just try to login and do the backup and if it fails you know you don't have permission