Exatract Data, thanks.

Greetings!

I would like to extract data from the raw data below, here are the
criteria:

(1) I want accounts starting with 2 only, i.e., excluding accounts
starting with 3.
(2) I want to keep the person_id, account, and open_date for each
account starting with 2.
(3) I want to create a new variable to show each person's earliest
open_date of the accounts starting with 2.

My query below would give me person 10001's earliest open_date for
EVERYONE. Can anyone tell me what the problem is? Thanks a lot!

select person_id, account, open_date
,min_open=(select min(open_date) from test_5 where
person_id=test_5.person_id
and account like '2%')
from test_5
where account like '2%'

-Raw Data-
person_idaccount open_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005

-Ideal Output-
person_idaccount open_dateMin_Open
10001220000015/15/20035/15/2003
10001220000026/20/20045/15/2003
10002220000038/12/20048/12/2004
10002220000049/15/20048/12/2004

-My Output-
person_idaccount open_dateMin_Open
10001220000015/15/20035/15/2003
10001220000026/20/20045/15/2003
10002220000038/12/20045/15/2003
10002220000049/15/20045/15/2003On 16 Mar 2005 12:17:29 -0800, rong.guo@.gmail.com wrote:

(snip)
>My query below would give me person 10001's earliest open_date for
>EVERYONE. Can anyone tell me what the problem is? Thanks a lot!
>select person_id, account, open_date
>,min_open=(select min(open_date) from test_5 where
>person_id=test_5.person_id
>and account like '2%')
>from test_5
>where account like '2%'

Hi rong.guo,

You didn't post CREATE TABLE and INSERT statements, so I can't test it,
but I guess that this is caused because you forgot to use aliasses to
distinguish the outer and the inner version of the table.

select person_id, account, open_date
,min_open=(select min(open_date)
from test_5 AS t2
where t2.person_id = t1.person_id
and account like '2%')
from test_5 AS t1
where account like '2%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo! It works perfectly!

I also tried Method 2, which gave me the same result as your method. I
am more used to using JOIN function. But I am wondering if Method 1 has
any advantages over Method 2 in the long term. It seems Method 1
doesn't need GROUP BY while Method 2 does, I am wondering if I have
more than 50 variables in the SELECT, will the GROUP BY in Method 2
create any potential problems? Thanks!

--Method 1--
select person_id, account, open_date
,min_open=(select min(open_date) from test_5 as X where
(X.person_id=Y.person_id
and account like '2%'))
from test_5 as Y
where account like '2%'

--Method 2--
select a.person_id, a.account, a.open_date, min(b.open_date)
from test_5 as a
join test_5 as b
on a.person_id=b.person_id
where a.account like '2%'
group by a.person_id, a.account, a.open_date|||On 17 Mar 2005 06:24:54 -0800, rong.guo@.gmail.com wrote:

>Thanks Hugo! It works perfectly!
>I also tried Method 2, which gave me the same result as your method. I
>am more used to using JOIN function. But I am wondering if Method 1 has
>any advantages over Method 2 in the long term.

Hi rong.guo,

That's hard to say from here. It depends on lots of factors. The best
way to find out which version is better in your situation, is to test
both (emptying the cache in between) and compare the execution times,
the output of SET STATISTICS IO ON and the execution plans used.

There's also a third method you can use:

SELECT a.person_id, a.account, a.open_date, b.min_open_date
FROM test_5 AS a
JOIN (SELECT person_id, MIN(open_date) AS min_open_date
FROM test_5
WHERE account LIKE '2%'
GROUP BY person_id) AS b
ON a.person_id = b.person_id
WHERE a.account LIKE '2%'

(snip)
>--Method 2--
>select a.person_id, a.account, a.open_date, min(b.open_date)
>from test_5 as a
>join test_5 as b
>on a.person_id=b.person_id
>where a.account like '2%'
>group by a.person_id, a.account, a.open_date

You'll have to add
AND b.account LIKE '2%'
to this query, otherwise it is not equivalent to query 1.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

examples

I see a lot of example, but where do these example procedures go. Like
declare (whatever)?
below is an example i read. Where do you put this to make it execute, is it
the view screen or the stored procedure screen?
I'm using MSDE now to learn, and I can't get nothing working except simple
select query statements.

In the northwind example (northwindcs), how would I do a parameter query
like this:
Have a dialog box ask user to enter customerid to bring up. (in a query now
not a form)
Also, how would you check if a certain customerid exist? Example, CHOPS is
one customerid. If I wanted to use a query to check if it exist, and return
no records, but just do an action (like add a record) if it didn't exist,
how?
> CPU SQL
> (ms)
> -- Convert to varchar (implicitly) and compare right two digits
> -- (original version -- no I didn't write it)
> 4546 select sum(case right(srp,2)
> when '99' then 1 when '49' then 1 else 0 end)
> from sf
> -- Use LIKE for a single comparison instead of two, much faster
> -- Note that the big speedup indicates that
> -- CASE expr WHEN y then a WHEN z then b .
> -- recalculates expr for each WHEN clause
> 2023 select sum(case when srp like '%[49]9' then 1 else 0 end)
> from sf

I tried some variations of this, and indeed it seems that there is a cost
when the expression appears with several WITH clauses. I tried a variation
of this, where I supplemented the test table with a char(2) column, so I
could factor out that the WITH clauses themselves were not the culprits.

CREATE TABLE realthing (realta real NOT NULL,
lasttwo char(2) NOT NULL)
go
INSERT realthing (realta, lasttwo)
SELECT r, right(r, 2)
FROM (SELECT r = convert(real, checksum(newid()))
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b) AS f
go
DBCC DROPCLEANBUFFERS
go
DECLARE @.start datetime
SELECT @.start = getdate()
SELECT SUM(CASE right(realta, 2)
WHEN '99' THEN 1
WHEN '49' THEN 1
WHEN '39' THEN 1
ELSE 0 END)
FROM realthing
SELECT datediff(ms, @.start, getdate()) -- 20766 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @.start datetime
SELECT @.start = getdate()
SELECT SUM(CASE WHEN right(realta, 2) LIKE '[349]9' THEN 1 ELSE 0 END)
FROM realthing
SELECT datediff(ms, @.start, getdate()) -- 8406 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @.start datetime
SELECT @.start = getdate()
SELECT SUM(CASE lasttwo
WHEN '99' THEN 1
WHEN '49' THEN 1
WHEN '39' THEN 1
ELSE 0 END)
FROM realthing
SELECT datediff(ms, @.start, getdate()) -- 920 ms.
go
DBCC DROPCLEANBUFFERS
go
DECLARE @.start datetime
SELECT @.start = getdate()
SELECT SUM(CASE WHEN lasttwo LIKE '[349]9' THEN 1 ELSE 0 END)
FROM realthing
SELECT datediff(ms, @.start, getdate()) -- 1466 ms.

Thus, when using the char(2) column LIKE is slower despite that there
is only one WHEN condition. So indeed it seems that right(realta, 2)
is computed thrice in the first test.

Another funny thing is the actual results from the queries - they are
different. When I ran:

select count(*) from realthing where lasttwo <> right(realta, 2)

The result was about half of the size of realthing! I can't see that
this difference affects the results though.

Now, your article had a lot more tests, but I have to confess that
you lost me quite early, because you never discussed what is the
actual problem. Since you are working with floating-poiont numbers
there is a great risk that different methods not only has different
execution times, but also gives different results.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspSince you replied to what was originally my post, I'll respond.

First, MSDE is just a database engine. It may be packaged with other
programming tools (eg. Visual Studio .NET), but is not itself a programming
tool. It is programmable in the sense that you can create stored procedures
and SQL batches, but these have to be entered from some sort of client tool.

re: where do these procedures go...

I'm not very famiar with MSDE. I'm assuming it works just like the full SQL
Server, but is lacking some client tools. I enter all my queries into Query
Analyzer. If you don't have that included with MSDE, perhaps it's worth
spending about $50 for MS SQL Server Developer Edition to get it. But if you
can enter SELECT statements, I think you should be able to enter any
Transact/SQL batch, as long as your database user has the proper access
permissions. It's been a while since I tried it, but I think even Access can
be used to create tables and procedures, using SQL Passthru queries.

My examples were lacking DDL (data definition language, eg CREATE TABLE),
because I didn't think it important that anyone else try running these
queries. Erland provided DDL in his reply.

Everything up to but not including a GO is a batch of SQL statements that
you should be able to enter anywhere a SQL query can be executed, I think,
though you should be using some sort of SQL do ($dbh->do() in Perl DBI) or
SQLExecute() I think (ODBC in C) or cmd.Execute I think (ADO in Visual
BASIC?) or a passthru query that is set to return no results in Access.
Check the documentation for whatever language you are using, because clearly
the use of MSDE (a developer tool) requires the use of some sort of
programming tool to do what you are talking about.

I dont think you should ask how to bring a dialog box up here; that isn't a
SQL question...

re: customerid exists...

If you can execute a SELECT query, you can do this:

SELECT 1 FROM Northwind..Customers
WHERE CustomerID = 'CHOPS'

...using dynamic SQL. But parameterized SQL is more secure, faster, etc.

In Perl using DBI and DBD::ODBC (see www.activestate.com for Perl for
Windows)...

perldoc DBI

# DBItest.pl
my $user = 'SA';
my $auth = 'SAPassword'; #bad practice?
my $dbh = DBI->connect("dbi:ODBC:Northwnd", $user, $auth) || die;
#$dbh->do("USE Northwind");
my $sql = q(SELECT 1 FROM Northwind..Customers
WHERE CustomerID = ?);
# replace with a dialog if you like:
print "Enter customer ID:";
my $custid=<>; #'CHOPS';
chomp $custid;
my $sth = $dbh->prepare($sql) || die;
$sth->execute();
my ($custexists) = $sth->fetchrow_array();
if ($custexists) {
print "Customer $custid exists\n";
} else {
print "Customer $custid does not exist\n";
#display a dialog, retrieve values, etc
#$dbh->do("INSERT northwnd..Customers (...) VALUES (...)");
}

You may prefer to use Visual Basic rather than Perl to build Windows GUI
apps, although it certainly should be possible in Perl as well. If I were
building a GUI app in Perl I might try TCL/Tk:
http://search.cpan.org/~vkon/TclTk-0.75/Tk.pm
.... but again all this is off topic for a SQL newsgroup.

Decide which programming tool you want to use, make sure you have that tool,
check the documentation for the product, then ask your question in a topical
group if you still have problems.

And try a tutorial for the sort of thing you are talking about. Many MS
tools include tutorials for using the Northwind database.

-aaron

"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:xFXFc.191192$Gx4.131412@.bgtnsc04-news.ops.worldnet.att.net...
I see a lot of example, but where do these example procedures go. Like
declare (whatever)?
below is an example i read. Where do you put this to make it execute, is it
the view screen or the stored procedure screen?
I'm using MSDE now to learn, and I can't get nothing working except simple
select query statements.

In the northwind example (northwindcs), how would I do a parameter query
like this:
Have a dialog box ask user to enter customerid to bring up. (in a query now
not a form)
Also, how would you check if a certain customerid exist? Example, CHOPS is
one customerid. If I wanted to use a query to check if it exist, and return
no records, but just do an action (like add a record) if it didn't exist,
how?|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I see a lot of example, but where do these example procedures go. Like
> declare (whatever)? below is an example i read. Where do you put this
> to make it execute, is it the view screen or the stored procedure
> screen?
> I'm using MSDE now to learn, and I can't get nothing working except simple
> select query statements.

If you are using MSDE only, there's no GUI tools with it. But you can
use the OSQL comand-line tool to run scripts.

However, judging from your talk of view and stored procedure screens,
it sounds like you have Enterprise Manager available. In such case you
also have Query Analyzer available, from which you can run all sorts of
queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Example Code Help - followup

Hi Hari,
I just made a modification in the DDL for the code u
posted yesterday.
In the below code if u observe, I did not Insert any
record for a user 'blabla' into the table FLogin but I get
the QA output "Login Successfully" when i query.
CREATE TABLE [dbo].[FLogin] (
[UserName] [nvarchar] (16) NOT NULL,
[WebPassword] [varbinary] (128) NOT NULL,
) ON [PRIMARY]
GO
-- INSERTING ENCRYPTED value
-- hard coded strings should be replaced by a text box
value from a user interface
INSERT INTO FLogin VALUES ('Chip',PWDENCRYPT ('unclechip'))
-- the Decryption phase
DECLARE @.pwd VARBINARY(128), @.chk TINYINT
-- trying to select password for a user whose record
doesnt exist
SELECT @.pwd=WebPassword from FLogin where UserName
='blabla'
-- comparing : 1 is success, 0 is failure
select @.chk=PWDCOMPARE ('unclechip',@.pwd)
-- do a check inside application , if the value returned
is "1" allow to login
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Whats going wrong?
Also I thank Tom for pointing the DPAPI but I have to
agree that I am not a application programmer and moreover
I didnt find examples on how to use the DPAPI from ASP 3.0
Sincerely
ChipHi,
There was a small bug in the code, Say if you do not have record for that
user, it returns a NULL values, While comparing NULL with 'unclechip' you
get a NULL and it reults in the failure. I have included a check in the
code.
if @.@.rowcount < 1
begin
print 'No User found'
return
end
New code is given below,
DECLARE @.pwd VARBINARY(128), @.chk TINYINT
-- trying to select password for a user whose record doesnt exist
SELECT @.pwd=WebPassword from FLogin where UserName ='blabla'
if @.@.rowcount < 1
begin
print 'No User found'
return
end
-- comparing : 1 is success, 0 is failure
select @.chk=PWDCOMPARE ('unclechip',@.pwd)
-- do a check inside application , if the value returned is "1" allow to
login
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Thanks
Hari
MCDBA
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:f4e901c40cc1$28b1aa10$a401280a@.phx.gbl...
> Hi Hari,
> I just made a modification in the DDL for the code u
> posted yesterday.
> In the below code if u observe, I did not Insert any
> record for a user 'blabla' into the table FLogin but I get
> the QA output "Login Successfully" when i query.
>
> CREATE TABLE [dbo].[FLogin] (
> [UserName] [nvarchar] (16) NOT NULL,
> [WebPassword] [varbinary] (128) NOT NULL,
> ) ON [PRIMARY]
> GO
> -- INSERTING ENCRYPTED value
> -- hard coded strings should be replaced by a text box
> value from a user interface
> INSERT INTO FLogin VALUES ('Chip',PWDENCRYPT ('unclechip'))
> -- the Decryption phase
> DECLARE @.pwd VARBINARY(128), @.chk TINYINT
> -- trying to select password for a user whose record
> doesnt exist
> SELECT @.pwd=WebPassword from FLogin where UserName
> ='blabla'
> -- comparing : 1 is success, 0 is failure
> select @.chk=PWDCOMPARE ('unclechip',@.pwd)
> -- do a check inside application , if the value returned
> is "1" allow to login
> if @.chk ! = 1
> Print 'Wrong Password Entered! Try Again'
> else
> Print 'Login Successfully'
>
> Whats going wrong?
> Also I thank Tom for pointing the DPAPI but I have to
> agree that I am not a application programmer and moreover
> I didnt find examples on how to use the DPAPI from ASP 3.0
> Sincerely
> Chip
>|||Hi,
Thanks for the Help!. Do you have any idea of how people
handle the existing encrypted data to avoid application
breaks, if any algorithm changes happen in the PWDENCRYPT
and PWDCOMPARE.
Also If any algorithm changes are done in any Un-
Documented functions, Do microsoft mention them in the
change list of Service Pack?.
Also I heard that Yukon will be a great product, Does
microsoft have any plans to inculde a password datatype?.
Sorry If my expectations are high.
Sincerely
Chip
>--Original Message--
>Hi,
>There was a small bug in the code, Say if you do not have
record for that
>user, it returns a NULL values, While comparing NULL
with 'unclechip' you
>get a NULL and it reults in the failure. I have included
a check in the
>code.
>if @.@.rowcount < 1
>begin
> print 'No User found'
> return
>end
>New code is given below,
>DECLARE @.pwd VARBINARY(128), @.chk TINYINT
>-- trying to select password for a user whose record
doesnt exist
>SELECT @.pwd=WebPassword from FLogin where UserName
='blabla'
>if @.@.rowcount < 1
>begin
> print 'No User found'
> return
>end
>-- comparing : 1 is success, 0 is failure
>select @.chk=PWDCOMPARE ('unclechip',@.pwd)
>-- do a check inside application , if the value returned
is "1" allow to
>login
>if @.chk ! = 1
>Print 'Wrong Password Entered! Try Again'
>else
>Print 'Login Successfully'
>Thanks
>Hari
>MCDBA
>
>"Chip" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f4e901c40cc1$28b1aa10$a401280a@.phx.gbl...
get
('unclechip'))
moreover
3.0
>
>.
>|||Chip,
No, you cannot count on any help or info from MS if and when they change the
implementation or usage of these procedures. This is one of the reasons to
not use undocumented functionality. And, indeed the interface and
implementation of these did change between (I think it was) 6.5 and 7.0. So
it is not unlikely it will happen again.
Regarding Yukon, I have not seen anything in this area. You can always wish
at sqlwish@.microsoft.com, but I doubt they will have time to add such a
feature at this late stage. I can have missed it, of course.
I recommend that you use something external to SQL Server, for above
reasons.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
news:c88201c40cd4$2155d5d0$a601280a@.phx.gbl...
> Hi,
> Thanks for the Help!. Do you have any idea of how people
> handle the existing encrypted data to avoid application
> breaks, if any algorithm changes happen in the PWDENCRYPT
> and PWDCOMPARE.
> Also If any algorithm changes are done in any Un-
> Documented functions, Do microsoft mention them in the
> change list of Service Pack?.
> Also I heard that Yukon will be a great product, Does
> microsoft have any plans to inculde a password datatype?.
> Sorry If my expectations are high.
> Sincerely
> Chip
> record for that
> with 'unclechip' you
> a check in the
> doesnt exist
> ='blabla'
> is "1" allow to
> message
> get
> ('unclechip'))
> moreover
> 3.0|||Hi Tibor,
Thanks for the valuable information.Do you have idea of
any third party encryption tools for SQL Server database
tables ?
Sincerely
Chip
>--Original Message--
>Chip,
>No, you cannot count on any help or info from MS if and
when they change the
>implementation or usage of these procedures. This is one
of the reasons to
>not use undocumented functionality. And, indeed the
interface and
>implementation of these did change between (I think it
was) 6.5 and 7.0. So
>it is not unlikely it will happen again.
>Regarding Yukon, I have not seen anything in this area.
You can always wish
>at sqlwish@.microsoft.com, but I doubt they will have time
to add such a
>feature at this late stage. I can have missed it, of
course.
>I recommend that you use something external to SQL
Server, for above
>reasons.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Chip" <chipsin007@.yahoo.nospam.com> wrote in message
>news:c88201c40cd4$2155d5d0$a601280a@.phx.gbl...
PWDENCRYPT
datatype?.
have
included
returned
I
box
returned
ASP
>
>.
>|||I'm afraid not. I suggest you search the ng archives for that and if you
don't find it, you might wan to create a new post with appropriate subject
(unless someone else jumps in here, of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Chip" <anonymous@.discussions.microsoft.com> wrote in message
news:b04201c40d09$41d71d20$a001280a@.phx.gbl...
> Hi Tibor,
> Thanks for the valuable information.Do you have idea of
> any third party encryption tools for SQL Server database
> tables ?
> Sincerely
> Chip

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
Event Type:Warning
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:312
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
Event Type:Information
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:311
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Attempting to to re-open the local eventlog...
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Alert Engine
Event ID:318
Date:27.10.2005
Time:08:53:31
User:N/A
Computer:DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
>
|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:

> It's chicken and egg. The reason you get the messages is because you cleared
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you will
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
>
>

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
----
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 312
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
----
--
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 311
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Attempting to to re-open the local eventlog...
----
--
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 318
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
----
--It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
--
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
--
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
--
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
--
>|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:

> It's chicken and egg. The reason you get the messages is because you clear
ed
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you wil
l
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
>
>

Events 311, 312, 318.

Each time I clear the event log on two of our SQL servers, I get the
following errors a few seconds later (see below). Config is as follows:
Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
servers).
Any help in clearing up these annoying error messages would be appreciated.
----
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 312
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Successfully re-opened the local eventlog - NOTE: Some events may have been
missed.
----
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 311
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Attempting to to re-open the local eventlog...
----
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
Event ID: 318
Date: 27.10.2005
Time: 08:53:31
User: N/A
Computer: DNS3
Description:
Unable to read local eventlog (reason: The event log file has changed
between read operations).
----It's chicken and egg. The reason you get the messages is because you cleared
the event log. SQLAgent monitors the event log for alerting purposes and
when you clear it, it has to try and re-establish its monitoring. However,
if you don't use SQL Agent alerts you can delete the demo alerts that are
created when you install SQL Server (they all start with Demo) and you will
no longer get these messages (because with no alerts defined there is no
reason for SQL Agent to monitor the event log)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> Each time I clear the event log on two of our SQL servers, I get the
> following errors a few seconds later (see below). Config is as follows:
> Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> servers).
> Any help in clearing up these annoying error messages would be
> appreciated.
> ----
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 312
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Successfully re-opened the local eventlog - NOTE: Some events may have
> been
> missed.
> ----
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 311
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Attempting to to re-open the local eventlog...
> ----
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> Event ID: 318
> Date: 27.10.2005
> Time: 08:53:31
> User: N/A
> Computer: DNS3
> Description:
> Unable to read local eventlog (reason: The event log file has changed
> between read operations).
> ----
>|||Thankyou Jasper!
I have no need for SQL Agent Alerts, so the demo alerts have been deleted
and the problem is now resolved...
"Jasper Smith" wrote:
> It's chicken and egg. The reason you get the messages is because you cleared
> the event log. SQLAgent monitors the event log for alerting purposes and
> when you clear it, it has to try and re-establish its monitoring. However,
> if you don't use SQL Agent alerts you can delete the demo alerts that are
> created when you install SQL Server (they all start with Demo) and you will
> no longer get these messages (because with no alerts defined there is no
> reason for SQL Agent to monitor the event log)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "The Vogon" <The Vogon@.discussions.microsoft.com> wrote in message
> news:14E7C465-925F-4B88-9222-7CA5DED549DD@.microsoft.com...
> > Each time I clear the event log on two of our SQL servers, I get the
> > following errors a few seconds later (see below). Config is as follows:
> > Win2k3, IIS, SQL2000SP4, NOT Active Directory (these are primarily web
> > servers).
> >
> > Any help in clearing up these annoying error messages would be
> > appreciated.
> >
> > ----
> > Event Type: Warning
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 312
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Successfully re-opened the local eventlog - NOTE: Some events may have
> > been
> > missed.
> > ----
> > Event Type: Information
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 311
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Attempting to to re-open the local eventlog...
> > ----
> > Event Type: Error
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > Event ID: 318
> > Date: 27.10.2005
> > Time: 08:53:31
> > User: N/A
> > Computer: DNS3
> > Description:
> > Unable to read local eventlog (reason: The event log file has changed
> > between read operations).
> > ----
> >
>
>

event viewer msg thrown for ms sql

Hi guys

i am puzzled on how to decipher the below msg on my sql server
Had tried adjusting the settings but apparantly doesnt work

is this due to my license or what other reasons?

Error msg:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 30 queries and performance may be adversely affected.


sql server : MS SQL Enterprise 8.0any help from anyone?

Event log error message: Database log truncated: Database

Hi there,
Every few days (4 to 7 days), I got this error message. I have included the
complete error below. Is this something to do with my
maintenance/backup/recovery plan setting?
Thanks to your help.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 1/3/2005
Time: 10:00:04 PM
User: NT AUTHORITY\SYSTEM
Computer: BELDISP2
Description:
18278 :
Database log truncated: Database: PowerBiz.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 09 00 00 00 42 00 45 00 ...B.E.
0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
0018: 50 00 32 00 00 00 09 00 P.2....
0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
0028: 65 00 72 00 42 00 69 00 e.r.B.i.
0030: 7a 00 00 00 z...What recovery mode is this DB in? Are there any scheduled jobs that are
issuing a Truncate Log or Backp Log with Truncate?
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included
> the
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 1/3/2005
> Time: 10:00:04 PM
> User: NT AUTHORITY\SYSTEM
> Computer: BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>|||First of all, this is NOT an error message, it is an Application Event log
event entry. Informational. Issued by SQL Server.
The message says that the System explicitly truncated the transaction log.
Depending on your current database recovery mode setting, this may or may no
t
be an issue.
If the database is currently set to SIMPLE recovery, then there would be no
need to truncate the transaction log--it would happen automatically with
every checkpoint process.
On the other hand, if you are in Bulk_Logged or Full recovery, then
truncating the transaction log could render your database unrecoverable.
You'd have to restore the database from backup.
There are two possible recommendations:
1. Create a Full backup immediately after a truncate log action.
2. Initiate a transaction log backup schedule and stop truncating the log
entirely.
Sincerely,
Anthony Thomas
"Abel Chan" wrote:

> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included t
he
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 1/3/2005
> Time: 10:00:04 PM
> User: NT AUTHORITY\SYSTEM
> Computer: BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>|||Hi Andrew and Anthony,
Thanks to you both for the prompt response. Really appreciate this level of
support.
I think this event log is related to the way we create our maintenance plan
as I stated in another request:
http://msdn.microsoft.com/newsgroup...exp=&sloc=en-us
Currently, our maintenance plan combines both master and the User databases
together. It also selects Transaction Log Backup option. Transaction Log
Backup always failed under the SQL Server Agent Jobs list.
Since disk space is not an issue at this point, I am thinking to restructure
the maintenance plan. Please let me know if you would also recommend the
following measure to protect our data. If you have a better idea which I
believe you definitely have, please let me know also. Again, thanks so much
for your help and support.
Maintenance Plan for System databases
---
1. Select Recovery mode Simple for master.
2. Select Recovery mode Full for model.
3. Select Recovery mode Simple for msdb.
4. Create a maintenance plan for these system databases.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
except backup the transaction log.
Maintenance Plan for the User database
---
1. Select Recovery mode Full for the User database.
2. Create a maintenance plan for the User database.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
including backup the transaction log.
Additional information: We have a Raid 5 disk array on our production
database server. We copy the database backup into a backup library and
another backup server.
"Andrew J. Kelly" wrote:

> What recovery mode is this DB in? Are there any scheduled jobs that are
> issuing a Truncate Log or Backp Log with Truncate?
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
>
>|||That is fine with a few exceptions. There is no need to do the
optimizations on the system dbs since the maint plan uses DBREINDEX which is
not valid for system tables anyway. Make sure you don't have it set to
shrink the databases or logs as part of the MP or as an option for the DB's.
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...[vbcol=seagreen]
> Hi Andrew and Anthony,
> Thanks to you both for the prompt response. Really appreciate this level
> of
> support.
> I think this event log is related to the way we create our maintenance
> plan
> as I stated in another request:
> http://msdn.microsoft.com/newsgroup...exp=&sloc=en-us
> Currently, our maintenance plan combines both master and the User
> databases
> together. It also selects Transaction Log Backup option. Transaction Log
> Backup always failed under the SQL Server Agent Jobs list.
> Since disk space is not an issue at this point, I am thinking to
> restructure
> the maintenance plan. Please let me know if you would also recommend the
> following measure to protect our data. If you have a better idea which I
> believe you definitely have, please let me know also. Again, thanks so
> much
> for your help and support.
> Maintenance Plan for System databases
> ---
> 1. Select Recovery mode Simple for master.
> 2. Select Recovery mode Full for model.
> 3. Select Recovery mode Simple for msdb.
> 4. Create a maintenance plan for these system databases.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> except backup the transaction log.
> Maintenance Plan for the User database
> ---
> 1. Select Recovery mode Full for the User database.
> 2. Create a maintenance plan for the User database.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> including backup the transaction log.
> Additional information: We have a Raid 5 disk array on our production
> database server. We copy the database backup into a backup library and
> another backup server.
> "Andrew J. Kelly" wrote:
>|||Got it!!!
Thanks so much for your help.
You are really a MVP - quick, care, and to the point.
Abel Chan
"Andrew J. Kelly" wrote:

> That is fine with a few exceptions. There is no need to do the
> optimizations on the system dbs since the maint plan uses DBREINDEX which
is
> not valid for system tables anyway. Make sure you don't have it set to
> shrink the databases or logs as part of the MP or as an option for the DB'
s.
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...
>
>

Event log error message: Database log truncated: Database

Hi there,
Every few days (4 to 7 days), I got this error message. I have included the
complete error below. Is this something to do with my
maintenance/backup/recovery plan setting?
Thanks to your help.
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(6)
Event ID:17055
Date:1/3/2005
Time:10:00:04 PM
User:NT AUTHORITY\SYSTEM
Computer:BELDISP2
Description:
18278 :
Database log truncated: Database: PowerBiz.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 09 00 00 00 42 00 45 00 ...B.E.
0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
0018: 50 00 32 00 00 00 09 00 P.2....
0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
0028: 65 00 72 00 42 00 69 00 e.r.B.i.
0030: 7a 00 00 00 z...
What recovery mode is this DB in? Are there any scheduled jobs that are
issuing a Truncate Log or Backp Log with Truncate?
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included
> the
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 1/3/2005
> Time: 10:00:04 PM
> User: NT AUTHORITY\SYSTEM
> Computer: BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>
|||First of all, this is NOT an error message, it is an Application Event log
event entry. Informational. Issued by SQL Server.
The message says that the System explicitly truncated the transaction log.
Depending on your current database recovery mode setting, this may or may not
be an issue.
If the database is currently set to SIMPLE recovery, then there would be no
need to truncate the transaction log--it would happen automatically with
every checkpoint process.
On the other hand, if you are in Bulk_Logged or Full recovery, then
truncating the transaction log could render your database unrecoverable.
You'd have to restore the database from backup.
There are two possible recommendations:
1. Create a Full backup immediately after a truncate log action.
2. Initiate a transaction log backup schedule and stop truncating the log
entirely.
Sincerely,
Anthony Thomas
"Abel Chan" wrote:

> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included the
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type:Error
> Event Source:MSSQLSERVER
> Event Category:(6)
> Event ID:17055
> Date:1/3/2005
> Time:10:00:04 PM
> User:NT AUTHORITY\SYSTEM
> Computer:BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>
|||Hi Andrew and Anthony,
Thanks to you both for the prompt response. Really appreciate this level of
support.
I think this event log is related to the way we create our maintenance plan
as I stated in another request:
http://msdn.microsoft.com/newsgroups...xp=&sloc=en-us
Currently, our maintenance plan combines both master and the User databases
together. It also selects Transaction Log Backup option. Transaction Log
Backup always failed under the SQL Server Agent Jobs list.
Since disk space is not an issue at this point, I am thinking to restructure
the maintenance plan. Please let me know if you would also recommend the
following measure to protect our data. If you have a better idea which I
believe you definitely have, please let me know also. Again, thanks so much
for your help and support.
Maintenance Plan for System databases
1. Select Recovery mode Simple for master.
2. Select Recovery mode Full for model.
3. Select Recovery mode Simple for msdb.
4. Create a maintenance plan for these system databases.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
except backup the transaction log.
Maintenance Plan for the User database
1. Select Recovery mode Full for the User database.
2. Create a maintenance plan for the User database.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
including backup the transaction log.
Additional information: We have a Raid 5 disk array on our production
database server. We copy the database backup into a backup library and
another backup server.
"Andrew J. Kelly" wrote:

> What recovery mode is this DB in? Are there any scheduled jobs that are
> issuing a Truncate Log or Backp Log with Truncate?
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
>
>
|||That is fine with a few exceptions. There is no need to do the
optimizations on the system dbs since the maint plan uses DBREINDEX which is
not valid for system tables anyway. Make sure you don't have it set to
shrink the databases or logs as part of the MP or as an option for the DB's.
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...[vbcol=seagreen]
> Hi Andrew and Anthony,
> Thanks to you both for the prompt response. Really appreciate this level
> of
> support.
> I think this event log is related to the way we create our maintenance
> plan
> as I stated in another request:
> http://msdn.microsoft.com/newsgroups...xp=&sloc=en-us
> Currently, our maintenance plan combines both master and the User
> databases
> together. It also selects Transaction Log Backup option. Transaction Log
> Backup always failed under the SQL Server Agent Jobs list.
> Since disk space is not an issue at this point, I am thinking to
> restructure
> the maintenance plan. Please let me know if you would also recommend the
> following measure to protect our data. If you have a better idea which I
> believe you definitely have, please let me know also. Again, thanks so
> much
> for your help and support.
> Maintenance Plan for System databases
> 1. Select Recovery mode Simple for master.
> 2. Select Recovery mode Full for model.
> 3. Select Recovery mode Simple for msdb.
> 4. Create a maintenance plan for these system databases.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> except backup the transaction log.
> Maintenance Plan for the User database
> 1. Select Recovery mode Full for the User database.
> 2. Create a maintenance plan for the User database.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> including backup the transaction log.
> Additional information: We have a Raid 5 disk array on our production
> database server. We copy the database backup into a backup library and
> another backup server.
> "Andrew J. Kelly" wrote:
|||Got it!!!
Thanks so much for your help.
You are really a MVP - quick, care, and to the point.
Abel Chan
"Andrew J. Kelly" wrote:

> That is fine with a few exceptions. There is no need to do the
> optimizations on the system dbs since the maint plan uses DBREINDEX which is
> not valid for system tables anyway. Make sure you don't have it set to
> shrink the databases or logs as part of the MP or as an option for the DB's.
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...
>
>

Event log error message: Database log truncated: Database

Hi there,
Every few days (4 to 7 days), I got this error message. I have included the
complete error below. Is this something to do with my
maintenance/backup/recovery plan setting?
Thanks to your help.
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 17055
Date: 1/3/2005
Time: 10:00:04 PM
User: NT AUTHORITY\SYSTEM
Computer: BELDISP2
Description:
18278 :
Database log truncated: Database: PowerBiz.
Data:
0000: 66 47 00 00 10 00 00 00 fG.....
0008: 09 00 00 00 42 00 45 00 ...B.E.
0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
0018: 50 00 32 00 00 00 09 00 P.2....
0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
0028: 65 00 72 00 42 00 69 00 e.r.B.i.
0030: 7a 00 00 00 z...What recovery mode is this DB in? Are there any scheduled jobs that are
issuing a Truncate Log or Backp Log with Truncate?
--
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included
> the
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 1/3/2005
> Time: 10:00:04 PM
> User: NT AUTHORITY\SYSTEM
> Computer: BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>|||First of all, this is NOT an error message, it is an Application Event log
event entry. Informational. Issued by SQL Server.
The message says that the System explicitly truncated the transaction log.
Depending on your current database recovery mode setting, this may or may not
be an issue.
If the database is currently set to SIMPLE recovery, then there would be no
need to truncate the transaction log--it would happen automatically with
every checkpoint process.
On the other hand, if you are in Bulk_Logged or Full recovery, then
truncating the transaction log could render your database unrecoverable.
You'd have to restore the database from backup.
There are two possible recommendations:
1. Create a Full backup immediately after a truncate log action.
2. Initiate a transaction log backup schedule and stop truncating the log
entirely.
Sincerely,
Anthony Thomas
"Abel Chan" wrote:
> Hi there,
> Every few days (4 to 7 days), I got this error message. I have included the
> complete error below. Is this something to do with my
> maintenance/backup/recovery plan setting?
> Thanks to your help.
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (6)
> Event ID: 17055
> Date: 1/3/2005
> Time: 10:00:04 PM
> User: NT AUTHORITY\SYSTEM
> Computer: BELDISP2
> Description:
> 18278 :
> Database log truncated: Database: PowerBiz.
> Data:
> 0000: 66 47 00 00 10 00 00 00 fG.....
> 0008: 09 00 00 00 42 00 45 00 ...B.E.
> 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> 0018: 50 00 32 00 00 00 09 00 P.2....
> 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> 0030: 7a 00 00 00 z...
>|||Hi Andrew and Anthony,
Thanks to you both for the prompt response. Really appreciate this level of
support.
I think this event log is related to the way we create our maintenance plan
as I stated in another request
http://msdn.microsoft.com/newsgroups/managed/default.aspx?query=abel+chan&dg=&cat=en-us-msdnman&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us
Currently, our maintenance plan combines both master and the User databases
together. It also selects Transaction Log Backup option. Transaction Log
Backup always failed under the SQL Server Agent Jobs list.
Since disk space is not an issue at this point, I am thinking to restructure
the maintenance plan. Please let me know if you would also recommend the
following measure to protect our data. If you have a better idea which I
believe you definitely have, please let me know also. Again, thanks so much
for your help and support.
Maintenance Plan for System databases
---
1. Select Recovery mode Simple for master.
2. Select Recovery mode Full for model.
3. Select Recovery mode Simple for msdb.
4. Create a maintenance plan for these system databases.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
except backup the transaction log.
Maintenance Plan for the User database
---
1. Select Recovery mode Full for the User database.
2. Create a maintenance plan for the User database.
5. Select all options (Reorganize data and index pages, check database
integrity, back up the database as part of the maintenance plan and etc)
including backup the transaction log.
Additional information: We have a Raid 5 disk array on our production
database server. We copy the database backup into a backup library and
another backup server.
"Andrew J. Kelly" wrote:
> What recovery mode is this DB in? Are there any scheduled jobs that are
> issuing a Truncate Log or Backp Log with Truncate?
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
> > Hi there,
> >
> > Every few days (4 to 7 days), I got this error message. I have included
> > the
> > complete error below. Is this something to do with my
> > maintenance/backup/recovery plan setting?
> >
> > Thanks to your help.
> >
> > Event Type: Error
> > Event Source: MSSQLSERVER
> > Event Category: (6)
> > Event ID: 17055
> > Date: 1/3/2005
> > Time: 10:00:04 PM
> > User: NT AUTHORITY\SYSTEM
> > Computer: BELDISP2
> > Description:
> > 18278 :
> > Database log truncated: Database: PowerBiz.
> >
> > Data:
> > 0000: 66 47 00 00 10 00 00 00 fG.....
> > 0008: 09 00 00 00 42 00 45 00 ...B.E.
> > 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> > 0018: 50 00 32 00 00 00 09 00 P.2....
> > 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> > 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> > 0030: 7a 00 00 00 z...
> >
>
>|||That is fine with a few exceptions. There is no need to do the
optimizations on the system dbs since the maint plan uses DBREINDEX which is
not valid for system tables anyway. Make sure you don't have it set to
shrink the databases or logs as part of the MP or as an option for the DB's.
--
Andrew J. Kelly SQL MVP
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...
> Hi Andrew and Anthony,
> Thanks to you both for the prompt response. Really appreciate this level
> of
> support.
> I think this event log is related to the way we create our maintenance
> plan
> as I stated in another request:
> http://msdn.microsoft.com/newsgroups/managed/default.aspx?query=abel+chan&dg=&cat=en-us-msdnman&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us
> Currently, our maintenance plan combines both master and the User
> databases
> together. It also selects Transaction Log Backup option. Transaction Log
> Backup always failed under the SQL Server Agent Jobs list.
> Since disk space is not an issue at this point, I am thinking to
> restructure
> the maintenance plan. Please let me know if you would also recommend the
> following measure to protect our data. If you have a better idea which I
> believe you definitely have, please let me know also. Again, thanks so
> much
> for your help and support.
> Maintenance Plan for System databases
> ---
> 1. Select Recovery mode Simple for master.
> 2. Select Recovery mode Full for model.
> 3. Select Recovery mode Simple for msdb.
> 4. Create a maintenance plan for these system databases.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> except backup the transaction log.
> Maintenance Plan for the User database
> ---
> 1. Select Recovery mode Full for the User database.
> 2. Create a maintenance plan for the User database.
> 5. Select all options (Reorganize data and index pages, check database
> integrity, back up the database as part of the maintenance plan and etc)
> including backup the transaction log.
> Additional information: We have a Raid 5 disk array on our production
> database server. We copy the database backup into a backup library and
> another backup server.
> "Andrew J. Kelly" wrote:
>> What recovery mode is this DB in? Are there any scheduled jobs that are
>> issuing a Truncate Log or Backp Log with Truncate?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
>> news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
>> > Hi there,
>> >
>> > Every few days (4 to 7 days), I got this error message. I have
>> > included
>> > the
>> > complete error below. Is this something to do with my
>> > maintenance/backup/recovery plan setting?
>> >
>> > Thanks to your help.
>> >
>> > Event Type: Error
>> > Event Source: MSSQLSERVER
>> > Event Category: (6)
>> > Event ID: 17055
>> > Date: 1/3/2005
>> > Time: 10:00:04 PM
>> > User: NT AUTHORITY\SYSTEM
>> > Computer: BELDISP2
>> > Description:
>> > 18278 :
>> > Database log truncated: Database: PowerBiz.
>> >
>> > Data:
>> > 0000: 66 47 00 00 10 00 00 00 fG.....
>> > 0008: 09 00 00 00 42 00 45 00 ...B.E.
>> > 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
>> > 0018: 50 00 32 00 00 00 09 00 P.2....
>> > 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
>> > 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
>> > 0030: 7a 00 00 00 z...
>> >
>>|||Got it!!!
Thanks so much for your help.
You are really a MVP - quick, care, and to the point. :)
Abel Chan
"Andrew J. Kelly" wrote:
> That is fine with a few exceptions. There is no need to do the
> optimizations on the system dbs since the maint plan uses DBREINDEX which is
> not valid for system tables anyway. Make sure you don't have it set to
> shrink the databases or logs as part of the MP or as an option for the DB's.
> --
> Andrew J. Kelly SQL MVP
>
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:98779723-E9A8-45C4-B90D-6E7E4598AEE0@.microsoft.com...
> > Hi Andrew and Anthony,
> >
> > Thanks to you both for the prompt response. Really appreciate this level
> > of
> > support.
> >
> > I think this event log is related to the way we create our maintenance
> > plan
> > as I stated in another request:
> > http://msdn.microsoft.com/newsgroups/managed/default.aspx?query=abel+chan&dg=&cat=en-us-msdnman&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us
> >
> > Currently, our maintenance plan combines both master and the User
> > databases
> > together. It also selects Transaction Log Backup option. Transaction Log
> > Backup always failed under the SQL Server Agent Jobs list.
> >
> > Since disk space is not an issue at this point, I am thinking to
> > restructure
> > the maintenance plan. Please let me know if you would also recommend the
> > following measure to protect our data. If you have a better idea which I
> > believe you definitely have, please let me know also. Again, thanks so
> > much
> > for your help and support.
> >
> > Maintenance Plan for System databases
> > ---
> > 1. Select Recovery mode Simple for master.
> > 2. Select Recovery mode Full for model.
> > 3. Select Recovery mode Simple for msdb.
> > 4. Create a maintenance plan for these system databases.
> > 5. Select all options (Reorganize data and index pages, check database
> > integrity, back up the database as part of the maintenance plan and etc)
> > except backup the transaction log.
> >
> > Maintenance Plan for the User database
> > ---
> > 1. Select Recovery mode Full for the User database.
> > 2. Create a maintenance plan for the User database.
> > 5. Select all options (Reorganize data and index pages, check database
> > integrity, back up the database as part of the maintenance plan and etc)
> > including backup the transaction log.
> >
> > Additional information: We have a Raid 5 disk array on our production
> > database server. We copy the database backup into a backup library and
> > another backup server.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> What recovery mode is this DB in? Are there any scheduled jobs that are
> >> issuing a Truncate Log or Backp Log with Truncate?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> >> news:E1D93214-50C0-4573-A00A-EF83E853FC93@.microsoft.com...
> >> > Hi there,
> >> >
> >> > Every few days (4 to 7 days), I got this error message. I have
> >> > included
> >> > the
> >> > complete error below. Is this something to do with my
> >> > maintenance/backup/recovery plan setting?
> >> >
> >> > Thanks to your help.
> >> >
> >> > Event Type: Error
> >> > Event Source: MSSQLSERVER
> >> > Event Category: (6)
> >> > Event ID: 17055
> >> > Date: 1/3/2005
> >> > Time: 10:00:04 PM
> >> > User: NT AUTHORITY\SYSTEM
> >> > Computer: BELDISP2
> >> > Description:
> >> > 18278 :
> >> > Database log truncated: Database: PowerBiz.
> >> >
> >> > Data:
> >> > 0000: 66 47 00 00 10 00 00 00 fG.....
> >> > 0008: 09 00 00 00 42 00 45 00 ...B.E.
> >> > 0010: 4c 00 44 00 49 00 53 00 L.D.I.S.
> >> > 0018: 50 00 32 00 00 00 09 00 P.2....
> >> > 0020: 00 00 50 00 6f 00 77 00 ..P.o.w.
> >> > 0028: 65 00 72 00 42 00 69 00 e.r.B.i.
> >> > 0030: 7a 00 00 00 z...
> >> >
> >>
> >>
> >>
>
>

Event ID: 19011 - Microsoft SQL Server Service

Hello,
Thanks for taking the time to look over my post ! I would appreciate
any suggestions to fix the problem I am having below:
OS: Windows 2000 Workstation
Database: Microsoft SQL Server Service v8.00.760
Event ID: 19011
Category: 8
Type: Error
Description:
The description for Event ID ( 19011 ) in Source ( MSSQL$TIMEFORCE )
cannot be found. The local computer may not have the necessary
registry information or message DLL files to display messages from a
remote computer. The following is part of the event: ConnectionListen
(Shared-Memory (LPC)): Error 5.
The database does function, however, after a week or so the database
is unresponsive and the error above appears within the application
event log. I restart the machine and it's good to go again. There
are no other error messages.
Any help would be much appreciated. There isn't much on the net that
explain a resolution. I will also add that I rebuilt another machine
and the same error came up. This database is used by a commercial
application called TimeForce, so there is no way of migrating to SQL
Server 2000.
Thanks !
Chadcwwilly wrote:
> Hello,
> Thanks for taking the time to look over my post ! I would appreciate
> any suggestions to fix the problem I am having below:
> OS: Windows 2000 Workstation
> Database: Microsoft SQL Server Service v8.00.760
> Event ID: 19011
> Category: 8
> Type: Error
> Description:
> The description for Event ID ( 19011 ) in Source ( MSSQL$TIMEFORCE )
> cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following is part of the event: ConnectionListen
> (Shared-Memory (LPC)): Error 5.
> The database does function, however, after a week or so the database
> is unresponsive and the error above appears within the application
> event log. I restart the machine and it's good to go again. There
> are no other error messages.
> Any help would be much appreciated. There isn't much on the net that
> explain a resolution. I will also add that I rebuilt another machine
> and the same error came up. This database is used by a commercial
> application called TimeForce, so there is no way of migrating to SQL
> Server 2000.
> Thanks !
> Chad
You don't need to migrate to SQL Server 200 because you are already
running SQL Server 2000. Could you be running out of disk space on the
log or data drive? Is the databaes running in simple recovery model or
is it running in full/bulk-logged. Can the data and log file auto-grow?
Is there sufficient space?
--
David Gugick
Imceda Software
www.imceda.com

Event 17055: SQL Server Assertion error

Every night I am seeing the below error followed by a restart of SQL. This
occurs at 10:05 PM.
Any ideas?
Thanks,
Bob
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 10/11/2007
Time: 10:05:28 PM
Description:
17066 :
SQL Server Assertion: File: <umsio.cpp>, line=563
Failed Assertion = 'm_busyCnt == 1'.This is often a SQL Server bug, and I'd talk to the MS SQL support.
Linchi
"BobW" wrote:
> Every night I am seeing the below error followed by a restart of SQL. This
> occurs at 10:05 PM.
> Any ideas?
> Thanks,
> Bob
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 10/11/2007
> Time: 10:05:28 PM
> Description:
> 17066 :
> SQL Server Assertion: File: <umsio.cpp>, line=563
> Failed Assertion = 'm_busyCnt == 1'.|||I did find article KB914298 and requested the hotfix. What I find
troublesome is I have two SQL servers are very close to identical and only
one is exhibiting the behavior
Thanks,
Bob
"Linchi Shea" wrote:
> This is often a SQL Server bug, and I'd talk to the MS SQL support.
> Linchi
> "BobW" wrote:
> > Every night I am seeing the below error followed by a restart of SQL. This
> > occurs at 10:05 PM.
> >
> > Any ideas?
> > Thanks,
> > Bob
> >
> >
> > Event Type: Error
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17055
> > Date: 10/11/2007
> > Time: 10:05:28 PM
> > Description:
> > 17066 :
> > SQL Server Assertion: File: <umsio.cpp>, line=563
> > Failed Assertion = 'm_busyCnt == 1'.|||Dear Bob,
Thank you for posting here.
Before going further, I would like to know you load the data into database
by which method? As KB914298 described, you can workaround this issue by
using either the Bulk-Copy program or Data Transformation Service (DTS) to
import the data. Does this information helps?
Based on my experience, this issue seems to be a data corruption. According
to your situation, we can first attempt to install the hotfix KB914298 and
also run the "DBCC CheckDB" to do the database check. If the issue
persists, we may need to analyze the crash dump file to determine the root
cause. Unfortunately, we cannot help you analyze the dump file in newsgroup
and you may need to speak directly with a Microsoft Support Professional so
that the crash dump can be analyzed. Regarding this point, I'll appreciate
your understanding.
For current situation, my suggestion is:
1. Install the Hotfix KB914298
2. Use "DBCC CheckDB" command to check the allocation and structural
integrity of all the objects in the database. I suggest you check all
databases.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
dbcc_00gy.asp
Please let me know the exact output of DBCC CheckDB. Meanwhile, please pack
the SQL error log and System & Application event log into a zip file. Send
me this zip file to v-adamqu@.microsoft.com I hope we can find any clues
from these.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================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.
--
| Thread-Topic: Event 17055: SQL Server Assertion error
| thread-index: AcgVAug1OJA0yFz4TWe2zKieAYot/g==| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?Qm9iVw==?= <Osm3um@.news.postalias>
| References: <4C12F2AF-6B8E-403B-BA80-76C5A48B6C3E@.microsoft.com>
<035A5E8F-8CBB-4255-AACD-83CE90F93846@.microsoft.com>
| Subject: RE: Event 17055: SQL Server Assertion error
| Date: Mon, 22 Oct 2007 16:26:01 -0700
| Lines: 33
| Message-ID: <3C62397A-B661-4F51-82C9-6FF74BE36F21@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2929
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:28508
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I did find article KB914298 and requested the hotfix. What I find
| troublesome is I have two SQL servers are very close to identical and
only
| one is exhibiting the behavior
|
| Thanks,
| Bob
|
| "Linchi Shea" wrote:
|
| > This is often a SQL Server bug, and I'd talk to the MS SQL support.
| >
| > Linchi
| >
| > "BobW" wrote:
| >
| > > Every night I am seeing the below error followed by a restart of SQL.
This
| > > occurs at 10:05 PM.
| > >
| > > Any ideas?
| > > Thanks,
| > > Bob
| > >
| > >
| > > Event Type: Error
| > > Event Source: MSSQLSERVER
| > > Event Category: (2)
| > > Event ID: 17055
| > > Date: 10/11/2007
| > > Time: 10:05:28 PM
| > > Description:
| > > 17066 :
| > > SQL Server Assertion: File: <umsio.cpp>, line=563
| > > Failed Assertion = 'm_busyCnt == 1'.
|