Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL error
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?

Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL error
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?
Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?
sql

Abend info in SQL Log

Is there a way to get application/sql query abends to appear in the SQL erro
r
log?
For example, we have an application that executes a SQL query. The
application will abend because the query failed (invalid object, Foreign key
conflict ...). Can we change a setting in SQL to see these errors in the
error log?Hi
No. If you need to see failed queries, you need to run SQL Server Profiler.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"vtcs212" <vtcs212@.discussions.microsoft.com> wrote in message
news:72A431E7-5581-435D-A6C9-3253A30692BB@.microsoft.com...
> Is there a way to get application/sql query abends to appear in the SQL
> error
> log?
> For example, we have an application that executes a SQL query. The
> application will abend because the query failed (invalid object, Foreign
> key
> conflict ...). Can we change a setting in SQL to see these errors in the
> error log?

A User connections question

Is there anyway or SQL script to configure the SQL server to automatically
log users out if their connection remains idle for a predetermined period of
time?
The convenience of allowing users to login to Great Plains in the morning
and staying there all day, even if they actually use the application for only
a fraction of the time, is too expensive to cater to.
I want to start by demanding that users logout of Great Plains when they
will not be using the application for a period of time (i.e. during lunch,
during coffee breaks, during meetings, while they are performing tasks not
requiring immediate access to Great Plains, etc.).
Thank You...clairvoyant316 wrote:
> Is there anyway or SQL script to configure the SQL server to automatically
> log users out if their connection remains idle for a predetermined period of
> time?
> The convenience of allowing users to login to Great Plains in the morning
> and staying there all day, even if they actually use the application for only
> a fraction of the time, is too expensive to cater to.
> I want to start by demanding that users logout of Great Plains when they
> will not be using the application for a period of time (i.e. during lunch,
> during coffee breaks, during meetings, while they are performing tasks not
> requiring immediate access to Great Plains, etc.).
> Thank You...
Tried to post this yesterday, apparently it didn't "go". Here's a
script (and table) that I've used to handle this very problem in GP.
CREATE TABLE [dbo].[sb_IdleUsersRemoved] (
[iRowID] [int] IDENTITY (1, 1) NOT NULL ,
[iSPID] [int] NULL ,
[vchUserName] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[dtDateRemoved] [datetime] NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE sb_RemoveIdleUsers
AS
DECLARE @.iSPID INT
DECLARE @.vchCommand VARCHAR(255)
/* Create list of SPIDs that have been idle for 12 hours, and are still
listed in dex_session */
SELECT a.spid, a.loginame
INTO #tempSPIDs
FROM master.dbo.sysprocesses a
INNER JOIN tempdb.dbo.dex_session b
ON a.spid = b.sqlsvr_spid
WHERE GETDATE() - a.last_batch > .5
UNION
SELECT spid, loginame
FROM master.dbo.sysprocesses
WHERE dbid IN (7, 16, 17) AND GETDATE() - last_batch > .5
BEGIN TRANSACTION
/* Record the list of spids and logins that are being removed */
INSERT INTO sb_custom.dbo.sb_IdleUsersRemoved
SELECT spid, loginame, GETDATE()
FROM #tempSPIDs
/* Delete records from dex_lock for any idle sessions */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id FROM tempdb.dbo.dex_session WHERE
sqlsvr_spid IN (SELECT spid FROM #tempSPIDs))
/* Delete records from dex_session for any idle sessions */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid IN (SELECT spid FROM #tempSPIDs)
COMMIT TRANSACTION
/* Kill SQL processes for idle sessions */
SELECT @.iSPID = 0
WHILE EXISTS(SELECT * FROM #tempSPIDs WHERE spid > @.iSPID)
BEGIN
SELECT TOP 1 @.iSPID = spid
FROM #tempSPIDs
WHERE spid > @.iSPID
ORDER BY spid
SELECT @.vchCommand = 'KILL ' + LTRIM(RTRIM(CONVERT(VARCHAR(10),
@.iSPID)))
EXEC (@.vchCommand)
END
DROP TABLE #tempSPIDs
/* Delete records from dex_lock for any records in dex_session that have
no active SQL connection */
DELETE
FROM tempdb.dbo.dex_lock
WHERE session_id IN (SELECT session_id
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
)
/* Delete records from dex_session that have no active SQL connection */
DELETE
FROM tempdb.dbo.dex_session
WHERE sqlsvr_spid NOT IN (SELECT spid FROM master.dbo.sysprocesses)
GO
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||clairvoyant316 wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
Not likely - the script I sent you simply creates a table and a stored
procedure.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
>
The script that I gave you creates a stored procedure. YOU must run
that stored procedure before it will remove any users. Surely you
realized that when you reviewed the script? Sounds like you might be
better off just leaving things alone.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
tempdb is recreated when you restart SQL Server. If it was really corrupted
I would suspect a hardware failure rather than this script. There seems to be
a couple of table creation statements missing from the script and the
database names need changing on the four part names that are not tempdb.
John
"clairvoyant316" wrote:
> Thanks for your prompt response.
> I ran that script and crashed the MS SQL 2000 with timeout as well as
> corrupting tempdb. I restored the tempdb from the MS SQL 2000 CD.
> It didn't kick users out of the Great Plains at all.
> Is there any condition or special configuration that I am not aware of to
> run that script?
> Thank You
> "Tracy McKibben" wrote:
> > Tried to post this yesterday, apparently it didn't "go". Here's a
> > script (and table) that I've used to handle this very problem in GP.
>|||John Bell wrote:
> Hi
> tempdb is recreated when you restart SQL Server. If it was really corrupted
> I would suspect a hardware failure rather than this script. There seems to be
> a couple of table creation statements missing from the script and the
> database names need changing on the four part names that are not tempdb.
>
Yes, it's just a snippet of code from one of my production systems. I
made the assumption that the OP would be able to adapt it to his needs,
I assumed wrong.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy
I would personally be wary of killing of processes like this in case there
is the situation you kill off something that you don't want to kill. This may
have also been something that the occurred with the OP. BOL gives a list of
processes that you should not kill such as AWAITING COMMAND, CHECKPOINT
SLEEP, LAZY WRITER, LOCK MONITOR, SELECT,SIGNAL HANDLER which you don't seem
to check in the code,
John
"Tracy McKibben" wrote:
> John Bell wrote:
> > Hi
> >
> > tempdb is recreated when you restart SQL Server. If it was really corrupted
> > I would suspect a hardware failure rather than this script. There seems to be
> > a couple of table creation statements missing from the script and the
> > database names need changing on the four part names that are not tempdb.
> >
> Yes, it's just a snippet of code from one of my production systems. I
> made the assumption that the OP would be able to adapt it to his needs,
> I assumed wrong.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

A self-generated certificate was successfully loaded for encryption?

Log shows (on SS2005)
“A self-generated certificate was successfully loaded for encryption”

No encryption is used. Properties of Protocol for MSSQLSERVER shows no for Force Encryption, certificates are empty

How, why, where from and what for does it get and load self-generated certificate?

This certificate refers to a self-generated certificate used as a “best-effort” mechanism to protect the SQL login information (including password) when using SQL authentication.

By default this self-signed certificate should only affect the login information and only when using SQL authentication (no penalty for using Windows auth).

Pleaes be aware that because this is a self-signed certificate it is subject to an active attack (i.e. MiM), but it provides a better defense against a passive attacker (“sniffing” passwords) than SQL Server 2000.

For more information refer to BOL:

http://blogs.msdn.com/sql_protocols/archive/2005/11/10/491563.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/11/479869.aspx

I hop this information helps

-Raul Garcia

SDE/T

SQL Server Engine

A question about Log Shipping

If I switching recovery model from Full to Bulk-Logged, will the log
shipping break?
Thanks
LijunHi Lijun,
IMO, it should work eventhough I haven't tried this yet. Log-shipping
criteria is that the database partcipating in log-shipping should be either
in full or bulk-logged recovery mode.
Thanks
Yogish

A question about Log Shipping

If I switching recovery model from Full to Bulk-Logged, will the log
shipping break?
Thanks
Lijun
Hi Lijun,
IMO, it should work eventhough I haven't tried this yet. Log-shipping
criteria is that the database partcipating in log-shipping should be either
in full or bulk-logged recovery mode.
Thanks
Yogish

A question about Log Shipping

If I switching recovery model from Full to Bulk-Logged, will the log
shipping break?
Thanks
LijunHi Lijun,
IMO, it should work eventhough I haven't tried this yet. Log-shipping
criteria is that the database partcipating in log-shipping should be either
in full or bulk-logged recovery mode.
--
Thanks
Yogish

A query runs 1 times slower from a .NET application the from Query

Just a guess.
It might be the delay in creating and opening the connection.
Why don't you log the current time just before calling the SP and after it
and find the time difference. That can narrow down on what the issue is.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Boaz Ben-Porat" wrote:

> Computer: 3.4 Ghz CPU, 1 GB RAM, 2003 Server
> database : MS SqlServer 2000 Enterprise. ~ 10 GB database file. Largest
> table in the database contains 11,000,000 records.
> Framework: .NET 2.0
> I try to run a query against the database, selecting aggregated data from
> views based on the large table.
> When executed from the Query Analizer, it takes 13 seconds.
> When executed from a .NET application, it takes 140 seconds.
> The database is well tuned (or else the query analizer would go slowly), s
o
> I can't find the reason for this difference.
> Any suggestion ?
> TIA
> Boaz Ben-Porat
> Milestone Systems
>
>Thanks for a quick answer.
The time I refer to is after the connection is opened.
the relevant code:
DbDataReader dr = null;
try
{
// This method opens a connection, if not allready opened
Connect();
// dbCommand is an input parameter of type DbCommand. It contains the SQL
statement
dbCommand.Connection = _connection;
DateTime t1 = DateTime.Now;
dr = dbCommand.ExecuteReader();
DateTime t2 = DateTime.Now;
TimeSpan ts = t2 - t1;
int milli = (int)ts.TotalMilliseconds; // milli contains the execution time
of dbCommand.ExecuteReader();
Boaz Ben-Porat
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:49A213DD-227B-4602-81ED-5ADF4E32687E@.microsoft.com...
> Just a guess.
> It might be the delay in creating and opening the connection.
> Why don't you log the current time just before calling the SP and after it
> and find the time difference. That can narrow down on what the issue is.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Boaz Ben-Porat" wrote:
>

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?
Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?
|||It is right that it does not require you to log off the users.

>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?|||It is right that it does not require you to log off the users.

>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
--
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?|||It is right that it does not require you to log off the users.
>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel