Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

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

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

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
Turn off autoclose for the database. What probably happened is that you have autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren't open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine and
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>
|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,[vbcol=seagreen]
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
state.[vbcol=seagreen]
and
>
|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off the autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> have autoclose, some other
> have it opened, SQL server
> weren't open by other process,
> state.
> and
>

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.Turn off autoclose for the database. What probably happened is that you have
autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have
it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren'
t open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine a
nd
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
state.[vbcol=seagreen]
and[vbcol=seagreen]
>|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off th
e autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> have autoclose, some other
> have it opened, SQL server
> weren't open by other process,
> state.
> and
>

Repeated 'Starting up database' messages

Hi,
Our network monitoring software reported the following message :
The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
The state is: Suspect
After looking at SQL Enterprise Manager, the database appears to be fine and
I can browse tables, views, stored procedures and query the database as
normal. However when I looked at the SQL Server Error Log I found many
messages reporting 'Starting up database 'MSCRM'.
Any ideas?
Jonathan
Here is a sample log from this morning :
2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.Turn off autoclose for the database. What probably happened is that you have autoclose, some other
process (like anti-virus) got hold of the file while SQL Server didn't have it opened, SQL server
tried to access the file, ouch!, suspect database. Next restart, file weren't open by other process,
SQL Server turned off the autoclose option.
Autoclose is not designed for production databases!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> Our network monitoring software reported the following message :
> The database "MSCRM" in the instance "MSSQLSERVER" is in a critical state.
> The state is: Suspect
> After looking at SQL Enterprise Manager, the database appears to be fine and
> I can browse tables, views, stored procedures and query the database as
> normal. However when I looked at the SQL Server Error Log I found many
> messages reporting 'Starting up database 'MSCRM'.
> Any ideas?
> Jonathan
> Here is a sample log from this morning :
>
> 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
>|||Many thanks Tibor!
Jonathan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Turn off autoclose for the database. What probably happened is that you
have autoclose, some other
> process (like anti-virus) got hold of the file while SQL Server didn't
have it opened, SQL server
> tried to access the file, ouch!, suspect database. Next restart, file
weren't open by other process,
> SQL Server turned off the autoclose option.
> Autoclose is not designed for production databases!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Our network monitoring software reported the following message :
> >
> > The database "MSCRM" in the instance "MSSQLSERVER" is in a critical
state.
> > The state is: Suspect
> >
> > After looking at SQL Enterprise Manager, the database appears to be fine
and
> > I can browse tables, views, stored procedures and query the database as
> > normal. However when I looked at the SQL Server Error Log I found many
> > messages reporting 'Starting up database 'MSCRM'.
> >
> > Any ideas?
> >
> > Jonathan
> >
> > Here is a sample log from this morning :
> >
> >
> > 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> > 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> > 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> > 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
> >
> >
>|||I just noticed a typo in my last post. I wrote:
" Next restart, file weren't open by other process, SQL Server turned off the autoclose option."
Should have been:
"...SQL Server turned off the suspect status."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
news:%23R%23QHwBYEHA.2016@.TK2MSFTNGP11.phx.gbl...
> Many thanks Tibor!
> Jonathan
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23U0H0dBYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> > Turn off autoclose for the database. What probably happened is that you
> have autoclose, some other
> > process (like anti-virus) got hold of the file while SQL Server didn't
> have it opened, SQL server
> > tried to access the file, ouch!, suspect database. Next restart, file
> weren't open by other process,
> > SQL Server turned off the autoclose option.
> >
> > Autoclose is not designed for production databases!
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Jonathan Wareham" <JonathanW@.CooperParry.com> wrote in message
> > news:OHdeeaBYEHA.644@.tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > Our network monitoring software reported the following message :
> > >
> > > The database "MSCRM" in the instance "MSSQLSERVER" is in a critical
> state.
> > > The state is: Suspect
> > >
> > > After looking at SQL Enterprise Manager, the database appears to be fine
> and
> > > I can browse tables, views, stored procedures and query the database as
> > > normal. However when I looked at the SQL Server Error Log I found many
> > > messages reporting 'Starting up database 'MSCRM'.
> > >
> > > Any ideas?
> > >
> > > Jonathan
> > >
> > > Here is a sample log from this morning :
> > >
> > >
> > > 2004-07-02 09:29:53.76 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:54.48 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.17 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.43 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.67 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:29:59.93 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.24 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.48 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:00.82 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.06 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.46 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:01.74 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:02.03 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:30:02.32 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:32:19.81 spid89 Starting up database 'MSCRM'.
> > > 2004-07-02 09:45:00.48 spid72 Starting up database 'MSCRM'.
> > > 2004-07-02 09:45:01.06 spid72 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.23 spid91 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.65 spid91 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:00.89 spid101 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:01.60 spid101 Starting up database 'MSCRM'.
> > > 2004-07-02 10:00:06.99 spid91 Starting up database 'MSCRM'.
> > >
> > >
> >
> >
>

Repeated message in SQL Server Logs

I'm getting the following messages repeatedly (several times a minute) in
the sql server logs:
Starting up database 'Northwind'.
Analysis of database 'Northwind' (6) is 100% complete (approximately 0 more
seconds)
I don't see this behaviour in any of our other servers. What would cause
this?
Thanks,
DrewAutoclose set to "on" would be one possiblity.
Keith
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:976BC816-3DF2-47D5-B41F-7ED65096C37A@.microsoft.com...
> I'm getting the following messages repeatedly (several times a minute) in
> the sql server logs:
> Starting up database 'Northwind'.
> Analysis of database 'Northwind' (6) is 100% complete (approximately 0
more
> seconds)
> I don't see this behaviour in any of our other servers. What would cause
> this?
> Thanks,
> Drew

Repeated message in SQL Server Logs

I'm getting the following messages repeatedly (several times a minute) in
the sql server logs:
Starting up database 'Northwind'.
Analysis of database 'Northwind' (6) is 100% complete (approximately 0 more
seconds)
I don't see this behaviour in any of our other servers. What would cause
this?
Thanks,
Drew
Autoclose set to "on" would be one possiblity.
Keith
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:976BC816-3DF2-47D5-B41F-7ED65096C37A@.microsoft.com...
> I'm getting the following messages repeatedly (several times a minute) in
> the sql server logs:
> Starting up database 'Northwind'.
> Analysis of database 'Northwind' (6) is 100% complete (approximately 0
more
> seconds)
> I don't see this behaviour in any of our other servers. What would cause
> this?
> Thanks,
> Drew

Repeated message in SQL Server Logs

I'm getting the following messages repeatedly (several times a minute) in
the sql server logs:
Starting up database 'Northwind'.
Analysis of database 'Northwind' (6) is 100% complete (approximately 0 more
seconds)
I don't see this behaviour in any of our other servers. What would cause
this?
Thanks,
DrewAutoclose set to "on" would be one possiblity.
Keith
"Drew" <Drew@.discussions.microsoft.com> wrote in message
news:976BC816-3DF2-47D5-B41F-7ED65096C37A@.microsoft.com...
> I'm getting the following messages repeatedly (several times a minute) in
> the sql server logs:
> Starting up database 'Northwind'.
> Analysis of database 'Northwind' (6) is 100% complete (approximately 0
more
> seconds)
> I don't see this behaviour in any of our other servers. What would cause
> this?
> Thanks,
> Drew

Repeated "Login failed for user 'sa'" entries in SQL Server log

I'm getting repeating "Login failed for user 'sa'" messages in my SQL Server
log - every 20 to 30 seconds. These are also being recorded to the Event
Viewer / Application log. This is a SQL Server 2000 SP4 instance inside the
firewall.
I'm running SQL Profiler and capturing as follows:
Events:
Security Audig - Audit Login Failed
Sessions - ExistingConnection
Stored Procedures - RPC:Completed
Data Columns: All columns
SQL Profiler is returning:
Application Name: OSQL-32
ClientProcessID: <differs>
DatabaseID: 1
Error: 18456
Hostname: <server name>
LoginName: sa
LoginSid: 0x01
StartTime: <differs>
Success: 0
TextData: Login failed for user 'sa'.
I don't see much useful information here that can help me track down where
this is coming from; all the other data columns are empty. Am I missing
something? Is there some other tool I could use to track this down?
I'm guessing it's something on the server, based upon the ApplicationName
and HostName values being returned. Could it be a monitoring agent, i.e. MO
M?
Thanks,
MikeIn most cases this is usually a drone (BotNet) PC hijacked and trying to gai
n
access to your SQL server using the sa account. Why Microsoft haven't
produced any useful tools to:
1. Track to the source
2. Automatically filter out traffic once the repeatative pattern has been
established and notify
But there again, I don't expect much from a company like Microsoft who's
moto is "just enough effort to get revenue and leverage the profit".
netstat should help you identify the IP (in your case it sounds like someone
behind your firewall has infested a PC with a BotNet -- since it happens
every 20-30 seconds it should be pretty clear which IP is the source.
You can also use ActivePorts (freeware) to identify the source connections.
Anyway, why these tools aren't built into SQL 2005 is beyond me -- but I
guess that just goes to show you Microsoft's true "commitment" to security
and why their OS/services are such a easy target when compared to *nix based
platforms.
"mikron2" wrote:

> I'm getting repeating "Login failed for user 'sa'" messages in my SQL Serv
er
> log - every 20 to 30 seconds. These are also being recorded to the Event
> Viewer / Application log. This is a SQL Server 2000 SP4 instance inside t
he
> firewall.
>
> I'm running SQL Profiler and capturing as follows:
> Events:
> Security Audig - Audit Login Failed
> Sessions - ExistingConnection
> Stored Procedures - RPC:Completed
> Data Columns: All columns
> SQL Profiler is returning:
> Application Name: OSQL-32
> ClientProcessID: <differs>
> DatabaseID: 1
> Error: 18456
> Hostname: <server name>
> LoginName: sa
> LoginSid: 0x01
> StartTime: <differs>
> Success: 0
> TextData: Login failed for user 'sa'.
> I don't see much useful information here that can help me track down where
> this is coming from; all the other data columns are empty. Am I missing
> something? Is there some other tool I could use to track this down?
> I'm guessing it's something on the server, based upon the ApplicationName
> and HostName values being returned. Could it be a monitoring agent, i.e.
MOM?
> Thanks,
> Mike
>