Error: Could not find an entry for table or index with partition ID xxxxxx in database & Ser

Hello! guys

I am using sql server 2005 enterprise edition (x64) with sp1

Periodically, the sql server is crashing. Before crashing i am getting these messages in the error log

1) During undoing of a logged operation in database 'msdb', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

2) Could not find an entry for table or index with partition ID xxxxxx in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

3) Unable to drop worktable with partition ID xxxxx after repeated attempts. Worktable is marked for deferred drop. This is an informational message only. No user action is required.

4) Error occurred while attempting to drop worktable with partition ID xxxxx.

5) SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

6)The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Usually i get (2),(3),(4) (5) error message. But last time i also got (1) and (6)
Why msdb database is mentioned in error messages? Whats wrong with msdb dattabase?

Can someone tell me how to fix this issue?

I have seen this hotfix -

http://support.microsoft.com/kb/916086
( FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005 BUG #: 518 sql )

Should i apply this hotfix? or should apply SP2? I checked SP2 bug list but i dont see anywhere about above bug. Does that mean it is not included in SP2?

Has anyone experienced such an issue? How you have resolved it?

Please any help would be apprecited.

Thanks

Database id:1 is master, :2 is tempdb. msdb database id should be 4.

It's a good thing to apply that hotfix. Also, please post the sql log. It gives us a lot more info than windows event log.

|||
Thanks so much for the reply.

All the above errors are from sql log only. I looked at the event log and it shows the same error messages.

Also that hotfix says-

This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.

So is it safe to apply that hotfix? and if not does sp2 includes this hotfix bug?

Also wondering why the error message showing msdb database?

Thanks

|||

All hotfixes have that disclaimer. Basically, hotfixes are released to customers who truly need the fix before a formal release of a service pack (which they could wait for a long time for).

Sql2k5 sp2 does have this fix included. You can tell by looking at the build number (hotfix: 1533 and sp2: 3042).

The "missing log for msdb" error implies your sqlserver can't get to the log file (*.ldf) which implies a storage problem. Please check system event log for any indication of hardware failure.

sql

Error: Could not find an entry for table or index with partition ID xxxxxx in database & Ser

Hello! guys

I am using sql server 2005 enterprise edition (x64) with sp1

Periodically, the sql server is crashing. Before crashing i am getting these messages in the error log

1) During undoing of a logged operation in database 'msdb', an error occurred at log record ID (). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

2) Could not find an entry for table or index with partition ID xxxxxx in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.

3) Unable to drop worktable with partition ID xxxxx after repeated attempts. Worktable is marked for deferred drop. This is an informational message only. No user action is required.

4) Error occurred while attempting to drop worktable with partition ID xxxxx.

5) SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

6)The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Usually i get (2),(3),(4) (5) error message. But last time i also got (1) and (6)
Why msdb database is mentioned in error messages? Whats wrong with msdb dattabase?

Can someone tell me how to fix this issue?

I have seen this hotfix -

http://support.microsoft.com/kb/916086
( FIX: Errors may be generated in the tempdb database when you create and then drop many temporary tables in SQL Server 2005 BUG #: 518 sql )

Should i apply this hotfix? or should apply SP2? I checked SP2 bug list but i dont see anywhere about above bug. Does that mean it is not included in SP2?

Has anyone experienced such an issue? How you have resolved it?

Please any help would be apprecited.

Thanks

Database id:1 is master, :2 is tempdb. msdb database id should be 4.

It's a good thing to apply that hotfix. Also, please post the sql log. It gives us a lot more info than windows event log.

|||
Thanks so much for the reply.

All the above errors are from sql log only. I looked at the event log and it shows the same error messages.

Also that hotfix says-

This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.

So is it safe to apply that hotfix? and if not does sp2 includes this hotfix bug?

Also wondering why the error message showing msdb database?

Thanks

|||

All hotfixes have that disclaimer. Basically, hotfixes are released to customers who truly need the fix before a formal release of a service pack (which they could wait for a long time for).

Sql2k5 sp2 does have this fix included. You can tell by looking at the build number (hotfix: 1533 and sp2: 3042).

The "missing log for msdb" error implies your sqlserver can't get to the log file (*.ldf) which implies a storage problem. Please check system event log for any indication of hardware failure.

Error: Could not create an acceptable cursor.

I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs.

The error I get is:

OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.

The full script I am running is:


CREATE procedure [dbo].[proc_AuditServer]
as

/*
** Auditing Script for SQL Servers.
**
** D Maxwell, June 2007
**
** This script takes configuration and job status information
** and writes it to a designated logging server. I'll describe
** each section in detail, below. We write to the local box first,
** Then upload everything to the logging server.
**
** This is the SQL 2005 version.
*/

/*
** We want to know exactly what server this is, so
** we get the server name, instance name, as well as
** SQL Version, Edition, and Service Pack level.
*/


truncate table admin.dbo.sql_servers

insert into admin.dbo.sql_servers
select convert(varchar(15), serverproperty('ServerName')),
convert(varchar(25), serverproperty('InstanceName')),
convert(char(9), serverproperty('ProductVersion')),
convert(varchar(4), serverproperty('ProductLevel')),
convert(varchar(20), serverproperty('Edition')),
getdate()

/*
** Now, having that, we get the list of databases,
** as well as thier creation dates and file names.
*/

truncate table admin.dbo.databases

insert into admin.dbo.databases
select
convert(varchar(15), serverproperty('ServerName')),
dbid,
name,
crdate,
filename
from master..sysdatabases
where dbid > 4
order by dbid

/*
** We need to know how the server is configured, so we
** can compare it to a list of preferred configuration
** values, as well as the defaults. I cut this out of
** sp_configure.
*/

truncate table admin.dbo.server_config

insert into admin.dbo.server_config
select
convert(varchar(15), serverproperty('ServerName')),
name,
config_value = c.value,
run_value = master.dbo.syscurconfigs.value
from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs
where type = 'C'
and number = c.config
and number = master.dbo.syscurconfigs.config

and
((c.status & 2 <> 0 )
OR
(c.status & 2 = 0)
)
order by lower(name)

/*
** The next configuration item we want to get is the
** list of jobs that run on the server. We're looking
** specifically for backup and other maintenance jobs.
** (Which will hopefully be named appropriately...)
** We use Neil Boyle's job report script for this.
** My comments and changes prefaced by a 'DM:'
*/

truncate table admin.dbo.jobs

insert into admin.dbo.jobs
select
convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting
j.job_id, -- DM: More unique than a name.
convert(varchar(22), j.name) as job_name,
case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule

, case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency

-- All the subsrings are because the times are stored as an integer with no leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00)

, substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at

,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at
from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c
where j.job_id = s.job_id and s.schedule_id = c.schedule_id
order by j.name, start_at

/*
** Now that we know what jobs we have, let's find out
** how they did recently.
*/

truncate table job_status

insert into job_status
select convert(varchar(15), serverproperty('ServerName')),
job_id, run_status, run_date,
run_time, run_duration
from msdb..sysjobhistory
where step_name = '(job outcome)' -- The last 90 days' worth.
and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', ''))
order by run_date desc


/*
** If this server is already known to the audit server,
** we need to remove the existing data from the audit
** tables.
*/


declare @.known bit
set @.known =
(select count(*)
from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name =
(select convert(varchar(15), serverproperty('servername'))))

/*
** Now we remove the existing information from the audit tables,
** if need be.
*/

if @.known = 1
begin

delete from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.databases
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.server_config
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.jobs
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.job_status
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

end



/*
** Finally, we upload the new info from here to the audit server.
*/

insert into [192.168.0.126].AUDITDB.dbo.sql_servers
select * from admin.dbo.sql_servers

insert into [192.168.0.126].AUDITDB.dbo.server_config
select * from admin.dbo.server_config

insert into [192.168.0.126].AUDITDB.dbo.databases
select * from admin.dbo.databases

insert into [192.168.0.126].AUDITDB.dbo.jobs
select * from admin.dbo.jobs

insert into [192.168.0.126].AUDITDB.dbo.job_status
select * from admin.dbo.job_status



This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test.

Any ideas as to what I should look at next? Thanks.

-D.Check DB_option to see whether local and global cursors are default, SP1 for SQL2005 is older one and latest is SP2.

Also see this http://support.microsoft.com/kb/302477 that talks about hotfix.|||OK, I went back and read that article again. It says this happens "If a cursor is created on a system stored procedure that returns schema information from a remote server..." I'm sorry, but I'm still learning to program in SQL and I don't see where I'm doing that. Is it in the DELETE FROM statement? And that article talks about SQL 2000 SP2. I'm running SQL 2000 SP4 on that box. Would I still need the hotfix?

I tried this with a server running SQL2005 SP2 (Version 9.00.3159), and got the same error.

I also tried setting the cursor option on the databases involved on both servers, to both global and local, in all 4 configurations. Still getting the same error.

Any other ideas?

Error: ConnectionCheckForData (CheckforData())

Hi,
I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
drives and Windows Server 2003 sp1. I'm running a fairly
simple stored proc and I get the following error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I've tried changing protocols in the client utiltiy (tcp
and named pipes) as suggested elsewhere. There are no
errors in the application log or the sql log.
Any ideas what is happening? The same query doesn't
crash on W2k Server/SQL Server 2k. It happens repeatedly,
but sometimes on the first, second or third execution of
the stored proc.
Thanks for any insight.Fred,
You could try to adjust couple of parameters of your SQL
Server:
network packet size (B):
default 4096, change it to eg. 1024
remote query timeout (s):
default 30 or 60, change it to eg. 600
You can see the current configuration with "sp_configure".
You could also design your procedure to interact
with "the client program" more frequently. One
possibility is to add rows like
PRINT 'Just sending something to the interface'
to your procedure. The idea is to keep the connection
alive.
I've tested these and it helped with some procedures (but
not with everyone).
If anyone has any other ideas, please let us know!
.mika
>--Original Message--
>Hi,
>I'm running SQL 2k SP3a on a 2.2P4 workstation with
250GB
>drives and Windows Server 2003 sp1. I'm running a
fairly
>simple stored proc and I get the following error:
>[Microsoft][ODBC SQL Server Driver][Shared Memory]
>ConnectionCheckForData (CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>I've tried changing protocols in the client utiltiy (tcp
>and named pipes) as suggested elsewhere. There are no
>errors in the application log or the sql log.
>Any ideas what is happening? The same query doesn't
>crash on W2k Server/SQL Server 2k. It happens
repeatedly,
>but sometimes on the first, second or third execution of
>the stored proc.
>Thanks for any insight.
>.
>|||Hi Fred,
Have had this problem recently, it seems a common error with quite a
few different symptoms. I will tell you our story, which may or may
not help.
For our case, we had this error executing dynamic SQL over ADO.NET,
but could also recreate the problem via Query Analyser using different
protocols.
We were using a complex query and got either your error or the
following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
We only got this error under certain conditions but could reproduce it
faithfully under those conditions.
Sniffing around the newsgroups, the general opinion was that if you
changed the query slightly, then in some cases the problem just "went
away".
I found 2 solutions to our problem.
Solution 1
Our query was using a view and by putting a "TOP 100 PERCENT" clause
in the view the problem went away. This is the solution we went with.
Solution 2
By using "OPTION(MERGE JOIN)" or "OPTION(LOOP JOIN)" in the SQL
statement, the problem went away. By forcing "OPTION(HASH JOIN)" the
problem re-appeared.
My theory is that merge joins were causing the problem (possibly in
conjunction with parallel query execution) but can't prove anything
because when you get this problem, you can't even get a query plan.
Looking at the query plan after using "TOP 100 PERCENT" I noticed that
there were no merge joins, although that proves nothing.
I wish you luck.
"Fred Jones" <anonymous@.discussions.microsoft.com> wrote in message news:<98f701c3ea99$2d681e80$a501280a@.phx.gbl>...
> Hi,
> I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
> drives and Windows Server 2003 sp1. I'm running a fairly
> simple stored proc and I get the following error:
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I've tried changing protocols in the client utiltiy (tcp
> and named pipes) as suggested elsewhere. There are no
> errors in the application log or the sql log.
> Any ideas what is happening? The same query doesn't
> crash on W2k Server/SQL Server 2k. It happens repeatedly,
> but sometimes on the first, second or third execution of
> the stored proc.
> Thanks for any insight.

Error: ConnectionCheckForData (CheckforData())

Hi,
I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
drives and Windows Server 2003 sp1. I'm running a fairly
simple stored proc and I get the following error:
[Microsoft][ODBC SQL Server Driver][Shared Memory]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I've tried changing protocols in the client utiltiy (tcp
and named pipes) as suggested elsewhere. There are no
errors in the application log or the sql log.
Any ideas what is happening? The same query doesn't
crash on W2k Server/SQL Server 2k. It happens repeatedly,
but sometimes on the first, second or third execution of
the stored proc.
Thanks for any insight.Hi Fred,
Have had this problem recently, it seems a common error with quite a
few different symptoms. I will tell you our story, which may or may
not help.
For our case, we had this error executing dynamic SQL over ADO.NET,
but could also recreate the problem via Query Analyser using different
protocols.
We were using a complex query and got either your error or the
following:
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection Broken
We only got this error under certain conditions but could reproduce it
faithfully under those conditions.
Sniffing around the newsgroups, the general opinion was that if you
changed the query slightly, then in some cases the problem just "went
away".
I found 2 solutions to our problem.
Solution 1
Our query was using a view and by putting a "TOP 100 PERCENT" clause
in the view the problem went away. This is the solution we went with.
Solution 2
By using "OPTION(MERGE JOIN)" or "OPTION(LOOP JOIN)" in the SQL
statement, the problem went away. By forcing "OPTION(HASH JOIN)" the
problem re-appeared.
My theory is that merge joins were causing the problem (possibly in
conjunction with parallel query execution) but can't prove anything
because when you get this problem, you can't even get a query plan.
Looking at the query plan after using "TOP 100 PERCENT" I noticed that
there were no merge joins, although that proves nothing.
I wish you luck.
"Fred Jones" <anonymous@.discussions.microsoft.com> wrote in message news:<98f701c3ea99$2
d681e80$a501280a@.phx.gbl>...
> Hi,
> I'm running SQL 2k SP3a on a 2.2P4 workstation with 250GB
> drives and Windows Server 2003 sp1. I'm running a fairly
> simple stored proc and I get the following error:
> [Microsoft][ODBC SQL Server Driver][Shared Memory]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> I've tried changing protocols in the client utiltiy (tcp
> and named pipes) as suggested elsewhere. There are no
> errors in the application log or the sql log.
> Any ideas what is happening? The same query doesn't
> crash on W2k Server/SQL Server 2k. It happens repeatedly,
> but sometimes on the first, second or third execution of
> the stored proc.
> Thanks for any insight.sql

Error: 7105, Severity: 22, State: 6

it is a SQL 2000 with SP4 on Windows server 2003 SP1.
"ME" <ME@.mail.com> wrote in message news:...
> My SQL server logs this in Application log:
> Error: 7105, Severity: 22, State: 6
> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
> for
> a hot fix for this problem.
> I believe this call is free because this is a bug. Can anyone confirm
> this?
> Thanks.
>
The affinity mask is set as default. The server is 2 dual core CPUs. SQL
sees and uses all of 4 CPUs.
"ME" <ME@.mail.com> wrote in message
news:egU%23OuT$GHA.1224@.TK2MSFTNGP04.phx.gbl...
> it is a SQL 2000 with SP4 on Windows server 2003 SP1.
>
> "ME" <ME@.mail.com> wrote in message news:...
>

Error: 7105, Severity: 22, State: 6

it is a SQL 2000 with SP4 on Windows server 2003 SP1.
"ME" <ME@.mail.com> wrote in message news:...
> My SQL server logs this in Application log:
>
> Error: 7105, Severity: 22, State: 6
> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
>
> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
> for
> a hot fix for this problem.
>
> I believe this call is free because this is a bug. Can anyone confirm
> this?
> Thanks.
>
>The affinity mask is set as default. The server is 2 dual core CPUs. SQL
sees and uses all of 4 CPUs.
"ME" <ME@.mail.com> wrote in message
news:egU%23OuT$GHA.1224@.TK2MSFTNGP04.phx.gbl...
> it is a SQL 2000 with SP4 on Windows server 2003 SP1.
>
> "ME" <ME@.mail.com> wrote in message news:...
>> My SQL server logs this in Application log:
>> Error: 7105, Severity: 22, State: 6
>> Page (1:952446), slot 39 for text, ntext, or image node does not exist.
>> MS KB890755 http://support.microsoft.com/kb/890755 asked to contact MPSS
>> for
>> a hot fix for this problem.
>> I believe this call is free because this is a bug. Can anyone confirm
>> this?
>> Thanks.
>>
>

Error: 18456, Severity: 14, State: 11

We have SQL Server 2005 Developer Edition with SP2 installed on Windows
Server 2003 Standard Edition SP1. When one of our programmers tries to
connect to the SQL instance he receives the 'Error: 18456, Severity: 14,
State: 11' error.
I found where this refers to a connect to server error and have checked, he
does have connect priviliges. I deleted both the server account and database
account and recreated them but he still receives the error. The only way he
can get access is to be a member of the sysadmin role. This was done for
testing but cannot be given as normal practice.
Does anyone have any ideas or solutions?
Thank You,
Bertbert
CREATE LOGIN test
WITH PASSWORD = 'balabalabala';USE AdventureWorks;
CREATE USER 'test' FOR LOGIN test;
GO Note , you can use ALTER LOGIN ... WITH default_database=..."-bert"
<bert@.discussions.microsoft.com> wrote in message
news:C9CDD261-B1AD-48BC-B821-9DCC80F64480@.microsoft.com...
> We have SQL Server 2005 Developer Edition with SP2 installed on Windows
> Server 2003 Standard Edition SP1. When one of our programmers tries to
> connect to the SQL instance he receives the 'Error: 18456, Severity: 14,
> State: 11' error.
> I found where this refers to a connect to server error and have checked,
> he
> does have connect priviliges. I deleted both the server account and
> database
> account and recreated them but he still receives the error. The only way
> he
> can get access is to be a member of the sysadmin role. This was done for
> testing but cannot be given as normal practice.
> Does anyone have any ideas or solutions?
> Thank You,
> Bert

Error: 18456 Severity: 14, State: 11

I have a SQL 2005 SP1 test server running Developer Edition on top of Windows Server 2003 SP1. We're testing out a 3rd party application that is using Windows Authentication to connect to SQL 2005. The SQL Error Log is saturated with the error message listed in the subject line. The text accompanying the message alternates between "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: <named pipe>]" and "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: xxx.xxx.xxx.xxx]".

There are two strange parts to this: 1) The application appears to be working fine on the surface, and, 2) The MYDOMAIN\TESTDA1$ account mentioned in the error text is not the user account that we are using to connect the application to SQL 2005. TESTDA1 is the server name that the 3rd party software is running remotely from.

I've looked up "error state 11" using the following resource:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

It describes error states 11 and 12 as "Valid login but server access failure" but I'm not sure what that means. Why would SQL care about the computer account hosting the application?

Thanks in advance for any help.

From your description it seems like the 3^rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

Check the 3^rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul. You were correct - the software was indeed trying to connect using the "Local System" account. We changed this to a domain account that had permission to login to SQL and now everything is working well. No more errors in the SQL Error Log.

Thanks again!

|||I have the same error message in my logs for SQL Server 2005. I've set up a report, gave both the user and the group she belongs to full access to the reports and Content Manager and Browser. She can go through the folders, but if she gets the report and clicks on the link to open the report (on http://servername/reports etc) she gets the error message "cannot create connection to data source 'databasename' and in the error logs it says:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'DOMAIN\User.Name'. [CLIENT: <local machine>]

where domain = an active directory (not local box) domain and User.Name is the actual name of the user who is in that domain.

The error being state 11 means Valid login but server access failure so where do I look to figure out why she can't get into the report? She's trying to access across the network so she's not physically logging onto that computer. Any idea what I need to check?

Thanks!

Linda
|||

Connect to SQL server using a sysadmin account and look if the Windows user has access to SQL Server. You can look first in sys.server_principals for example:

SELECT * FROM sys.server_principals

Go

And look for your Windows principal name directly, or for a group she belongs to and grants permission to connect. If you cannot find the proper entry, you can grant permission to connect to SQL Server by adding a login.

On the other hand, if the login exists, it may be possible that that particular user doesn’t have permissions to connect in the right endpoint. To see all the permissions on the endpoints you can run the following query:

SELECT endpnt.name,

suser_name(perms.grantee_principal_id) as grantee_principal,

perms.permission_name, perms.state_desc

FROM

sys.server_permissions perms,

sys.endpoints endpnt

WHERE

perms.class = 105

AND perms.major_id = endpnt.endpoint_id

go

If there is a permission missing for this principal, just grant the CONNECT permission at the correct endpoint.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul Garcia - MS wrote:

From your description it seems like the 3^rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

Check the 3^rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

Hello there,

I got exactly the same scenario. Unfortunately I am not able to change the service account to any other thing than local system.

If I would like to add access to my database for the machine$ accounts, what exactly would I have to do ?

Could anyone give me a hint please ?

Thanks in advance

Gordon

|||

You can use the CREATE LOGIN DDL to create the login for the machine account, for example:

CREATE LOGIN [DOMAIN\machine_name$] FROM WINDOWS

Let us know if this information helped and/or if you have any additional questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks for the hint. This didn't solve my problem. Do I have to grant any special rights after creating those logins ?|||

After creating the logins I forgot to map a role to the users for my database.

I just followed the instructions found in another article here :

navigate through Security > Logins > Properties > User Mapping > Choose the Database > Map the user to the database (you will see that he will get the public role by default)

Error: 18456 Severity: 14, State: 11

I have a SQL 2005 SP1 test server running Developer Edition on top of Windows Server 2003 SP1. We're testing out a 3rd party application that is using Windows Authentication to connect to SQL 2005. The SQL Error Log is saturated with the error message listed in the subject line. The text accompanying the message alternates between "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: <named pipe>]" and "Login failed for user 'MYDOMAIN\TESTDA1$'. [CLIENT: xxx.xxx.xxx.xxx]".

There are two strange parts to this: 1) The application appears to be working fine on the surface, and, 2) The MYDOMAIN\TESTDA1$ account mentioned in the error text is not the user account that we are using to connect the application to SQL 2005. TESTDA1 is the server name that the 3rd party software is running remotely from.

I've looked up "error state 11" using the following resource:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

It describes error states 11 and 12 as "Valid login but server access failure" but I'm not sure what that means. Why would SQL care about the computer account hosting the application?

Thanks in advance for any help.

From your description it seems like the 3^rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

Check the 3^rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul. You were correct - the software was indeed trying to connect using the "Local System" account. We changed this to a domain account that had permission to login to SQL and now everything is working well. No more errors in the SQL Error Log.

Thanks again!

|||I have the same error message in my logs for SQL Server 2005. I've set up a report, gave both the user and the group she belongs to full access to the reports and Content Manager and Browser. She can go through the folders, but if she gets the report and clicks on the link to open the report (on http://servername/reports etc) she gets the error message "cannot create connection to data source 'databasename' and in the error logs it says:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'DOMAIN\User.Name'. [CLIENT: <local machine>]

where domain = an active directory (not local box) domain and User.Name is the actual name of the user who is in that domain.

The error being state 11 means Valid login but server access failure so where do I look to figure out why she can't get into the report? She's trying to access across the network so she's not physically logging onto that computer. Any idea what I need to check?

Thanks!

Linda
|||

Connect to SQL server using a sysadmin account and look if the Windows user has access to SQL Server. You can look first in sys.server_principals for example:

SELECT * FROM sys.server_principals

Go

And look for your Windows principal name directly, or for a group she belongs to and grants permission to connect. If you cannot find the proper entry, you can grant permission to connect to SQL Server by adding a login.

On the other hand, if the login exists, it may be possible that that particular user doesn’t have permissions to connect in the right endpoint. To see all the permissions on the endpoints you can run the following query:

SELECT endpnt.name,

suser_name(perms.grantee_principal_id) as grantee_principal,

perms.permission_name, perms.state_desc

FROM

sys.server_permissions perms,

sys.endpoints endpnt

WHERE

perms.class = 105

AND perms.major_id = endpnt.endpoint_id

go

If there is a permission missing for this principal, just grant the CONNECT permission at the correct endpoint.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul Garcia - MS wrote:

From your description it seems like the 3^rd party software is attempting to connect to SQL Server using the service account credentials, it is most likely running under “Network Service”, “Local System” or another local account and that’s why when trying to access resources (in this case connect to SQL Server) it uses the machine account (<domain>\<machine_name>$), and as this account doesn’t have privileges to connect to SQL Server it fails.

Check the 3^rd party product documentation, it may be possible that you need to run it using a domain account (I also recommend verifying with your domain administrator regarding your specific environment policies regarding running services under domain accounts).

Another potential workaround would be to grant permission to connect to this machine account, and granting it the minimum permissions required for the software you are testing. Bear in mind than any other service from the same machine will have the same privileges.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

Hello there,

I got exactly the same scenario. Unfortunately I am not able to change the service account to any other thing than local system.

If I would like to add access to my database for the machine$ accounts, what exactly would I have to do ?

Could anyone give me a hint please ?

Thanks in advance

Gordon

|||

You can use the CREATE LOGIN DDL to create the login for the machine account, for example:

CREATE LOGIN [DOMAIN\machine_name$] FROM WINDOWS

Let us know if this information helped and/or if you have any additional questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||Thanks for the hint. This didn't solve my problem. Do I have to grant any special rights after creating those logins ?|||

After creating the logins I forgot to map a role to the users for my database.

I just followed the instructions found in another article here :

navigate through Security > Logins > Properties > User Mapping > Choose the Database > Map the user to the database (you will see that he will get the public role by default)

Error: 18456 Severity: 14, State: 10

I have SQL 2005 Expess installed on Windows Server 2003 SP1.

My application is running as a service and is set to depend on SQL Server instance...
During the startup my app attempts to connect to the databes and it fails to do so,
after 30 retries (1second appart). SQL Server log has this error msg:
Error: 18456 "Severity: 14, State: 10"

Once the server has started, I cam manually start my application and it then sucesfully
opens a connection to the database.

What does this error mean?

Srdjan

Hi Srdjan,

Looks like you are getting a login failed error:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

How is your app connecting to your db? If you are using Windows authentication, it could be that the service account you are using when running your app as a service does not have permission to log on to the database.

Sung

|||

This error seems to only be reachable when using SQL Authentication, but I'm not sure what could cause it.

You can start SQL Server with "-y 18456", to produce a dump when the error occurs. As the dump will happen during authentication, the login password might appear in it, so you should use a bogus test password for the purpose of the creating this dump, just in case. Once you have the dump, please open a report at https://connect.microsoft.com/feedback/default.aspx?SiteID=68, attach the dump, and mention my name in the report, so it will get assigned to me.

If you can repro this issue with a smaller application that attempts the same connection on startup, you could attach that application as well, so we can attempt to repro the issue in our labs.

Thanks
Laurentiu

|||tried a couple of times to post a bug report...
but getting errors from the web site.

Anyway, new information:

if I run using Windows authentication, error is the ame but
state is now 16.

Still, after loging in - I have no problem starting the same service that failed during automatic startup.

Even the simplest windows servce that just opens connection
behaves the same...

Thanks,
Srdjan|||

State 16 means there was an error in determining the database to be used by the connection. Can you still connect if you start your application manually?

Thanks
Laurentiu

|||yes, as soon as I log in, I can start my application manually.
During Startup my application fails to start because of the unhandled exception thrown from Sql.Client:

System.Data.SqlClient.SqlException: Cannot open database "Db_260" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Also here are couple of lines from the SQL errorlog file during machine startup:
2006-07-15 13:56:39.67 spid8s Clearing tempdb database.
2006-07-15 13:56:39.71 Logon Error: 18456, Severity: 14, State: 16.
2006-07-15 13:56:39.71 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2006-07-15 13:56:39.95 Logon Error: 18456, Severity: 14, State: 16.
2006-07-15 13:56:39.95 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2006-07-15 13:56:40.34 spid8s Starting up database 'tempdb'.
2006-07-15 13:56:40.45 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-07-15 13:56:40.46 spid11s The Service Broker protocol transport is disabled or not configured.
2006-07-15 13:56:40.46 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-07-15 13:56:40.56 spid11s Service Broker manager has started.
2006-07-15 13:56:44.78 spid51 Starting up database 'ReportServer$TRBR'.
2006-07-15 13:56:45.09 spid52 Starting up database 'Db_260'.
2006-07-15 13:57:38.96 Server Server resumed execution after being idle 25 seconds: user activity awakened the server. This is an informational message only. No user action is required.

Srdjan|||

You are trying to connect to database Db_260, but the database has not been started up yet. If you will look in the log, you'll see that the server is accepting connections before starting up the database Db_260, so the requests that you make before the database startup will fail. You should take this into account and retry to connect after a delay, to give time to the server to start up the database.

Thanks
Laurentiu

|||Laurentiu,

My code actually retries until it gets connection succesfully.
The problem is that after a couple of failed atempts, I actally get a success, but then it is the the next attempt to connect to database fails with the exception:

Login Failed for user 'NT AUTHORITY SYSTEM'...

Now, I've tried to postpone the first call to database for up to
3 minutes (more is not practical) and nothing really changes,
I get the same behaviour (System.Data.SqlClient.SqlException) on the second call to database...

Srdjan

|||

Are you saying that you get login failure errors after the database is started up? The log excerpt from your previous message doesn't show that. Can you post your errorlog from beginning up to and including the first couple of login failures that you get AFTER the Db_260 database was started up?

Thanks
Laurentiu

|||Laurentiu,

I managed to get around this problem...
My original code that was checking if SQL is ready was simply opening a connection but didn't execute any dabase commands.

For some reason at one point one of these attempts would successed, but then on the first real database access, exception was thrown.

So, I now do retries on the first real access to datbase and that seems to work.

I appreciate your help.

Srdjan
|||

Hi Laurentiu,

I am having a similar problem when after programmatically creating a database I try to create a table, if I do not put an arbitrarily large delay before issuing the create table statement, I get this same "Error: 18456, Severity: 14, State: 16."

Is there any way to issue a SQL statement and block on the call until a new database has been fully initialized and started before continuing? Alternatively, is there a way to get a callback once the DB is online?, Finally, if neither of the previous are possible, is there any entry in the master DB that one could poll to see when the new DB is up and running? I've tried the ovbious state in sys.databases, and Database.Status in DMO with no luck.

Any ideas? Thanks in advance!

GDM

|||

If you are getting a login failed message, it sounds like you are executing the CREATE DATABASE and CREATE TABLE statements on two different connections. Have you considered executing these statements using the same connection? If you would execute the CREATE TABLE after the CREATE DATABASE, on the same connection, you would not see such a failure.

The state you can query is state_desc in sys.databases - if it is ONLINE, it means the database was started.

Thanks
Laurentiu

|||How do i start sql server with "-y 18456"|||

Start->Run...->services.msc

Then go to the SQL Server service and stop it. Then Click <Properties> and in the <Start Parameters> field enter -y 18456. You can then click <Start> to restart the server with that parameter and then press cancel to exit without saving the parameter, so that future restarts won't use it. If you press OK, then the server will be restarted with that parameter - you probably don't want that.

Thanks
Laurentiu

|||

Ok I have an issue here....I am trying to do a full system recovery and this is what I am getting...

File/minint/system32/biosinfo.inf could not be loaded the error is code 14 ?

And I do NOT have the Windows XP Disk either...so if theres a way around this please by all means help me .......

can some please help me with this please

Error: 18456 Severity: 14, State: 10

I have SQL 2005 Expess installed on Windows Server 2003 SP1.

My application is running as a service and is set to depend on SQL Server instance...
During the startup my app attempts to connect to the databes and it fails to do so,
after 30 retries (1second appart). SQL Server log has this error msg:
Error: 18456 "Severity: 14, State: 10"

Once the server has started, I cam manually start my application and it then sucesfully
opens a connection to the database.

What does this error mean?

Srdjan

Hi Srdjan,

Looks like you are getting a login failed error:

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

How is your app connecting to your db? If you are using Windows authentication, it could be that the service account you are using when running your app as a service does not have permission to log on to the database.

Sung

|||

This error seems to only be reachable when using SQL Authentication, but I'm not sure what could cause it.

You can start SQL Server with "-y 18456", to produce a dump when the error occurs. As the dump will happen during authentication, the login password might appear in it, so you should use a bogus test password for the purpose of the creating this dump, just in case. Once you have the dump, please open a report at https://connect.microsoft.com/feedback/default.aspx?SiteID=68, attach the dump, and mention my name in the report, so it will get assigned to me.

If you can repro this issue with a smaller application that attempts the same connection on startup, you could attach that application as well, so we can attempt to repro the issue in our labs.

Thanks
Laurentiu

|||tried a couple of times to post a bug report...
but getting errors from the web site.

Anyway, new information:

if I run using Windows authentication, error is the ame but
state is now 16.

Still, after loging in - I have no problem starting the same service that failed during automatic startup.

Even the simplest windows servce that just opens connection
behaves the same...

Thanks,
Srdjan|||

State 16 means there was an error in determining the database to be used by the connection. Can you still connect if you start your application manually?

Thanks
Laurentiu

|||yes, as soon as I log in, I can start my application manually.
During Startup my application fails to start because of the unhandled exception thrown from Sql.Client:

System.Data.SqlClient.SqlException: Cannot open database "Db_260" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()

Also here are couple of lines from the SQL errorlog file during machine startup:
2006-07-15 13:56:39.67 spid8s Clearing tempdb database.
2006-07-15 13:56:39.71 Logon Error: 18456, Severity: 14, State: 16.
2006-07-15 13:56:39.71 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2006-07-15 13:56:39.95 Logon Error: 18456, Severity: 14, State: 16.
2006-07-15 13:56:39.95 Logon Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]
2006-07-15 13:56:40.34 spid8s Starting up database 'tempdb'.
2006-07-15 13:56:40.45 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-07-15 13:56:40.46 spid11s The Service Broker protocol transport is disabled or not configured.
2006-07-15 13:56:40.46 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-07-15 13:56:40.56 spid11s Service Broker manager has started.
2006-07-15 13:56:44.78 spid51 Starting up database 'ReportServer$TRBR'.
2006-07-15 13:56:45.09 spid52 Starting up database 'Db_260'.
2006-07-15 13:57:38.96 Server Server resumed execution after being idle 25 seconds: user activity awakened the server. This is an informational message only. No user action is required.

Srdjan|||

You are trying to connect to database Db_260, but the database has not been started up yet. If you will look in the log, you'll see that the server is accepting connections before starting up the database Db_260, so the requests that you make before the database startup will fail. You should take this into account and retry to connect after a delay, to give time to the server to start up the database.

Thanks
Laurentiu

|||Laurentiu,

My code actually retries until it gets connection succesfully.
The problem is that after a couple of failed atempts, I actally get a success, but then it is the the next attempt to connect to database fails with the exception:

Login Failed for user 'NT AUTHORITY SYSTEM'...

Now, I've tried to postpone the first call to database for up to
3 minutes (more is not practical) and nothing really changes,
I get the same behaviour (System.Data.SqlClient.SqlException) on the second call to database...

Srdjan

|||

Are you saying that you get login failure errors after the database is started up? The log excerpt from your previous message doesn't show that. Can you post your errorlog from beginning up to and including the first couple of login failures that you get AFTER the Db_260 database was started up?

Thanks
Laurentiu

|||Laurentiu,

I managed to get around this problem...
My original code that was checking if SQL is ready was simply opening a connection but didn't execute any dabase commands.

For some reason at one point one of these attempts would successed, but then on the first real database access, exception was thrown.

So, I now do retries on the first real access to datbase and that seems to work.

I appreciate your help.

Srdjan
|||

Hi Laurentiu,

I am having a similar problem when after programmatically creating a database I try to create a table, if I do not put an arbitrarily large delay before issuing the create table statement, I get this same "Error: 18456, Severity: 14, State: 16."

Is there any way to issue a SQL statement and block on the call until a new database has been fully initialized and started before continuing? Alternatively, is there a way to get a callback once the DB is online?, Finally, if neither of the previous are possible, is there any entry in the master DB that one could poll to see when the new DB is up and running? I've tried the ovbious state in sys.databases, and Database.Status in DMO with no luck.

Any ideas? Thanks in advance!

GDM

|||

If you are getting a login failed message, it sounds like you are executing the CREATE DATABASE and CREATE TABLE statements on two different connections. Have you considered executing these statements using the same connection? If you would execute the CREATE TABLE after the CREATE DATABASE, on the same connection, you would not see such a failure.

The state you can query is state_desc in sys.databases - if it is ONLINE, it means the database was started.

Thanks
Laurentiu

|||How do i start sql server with "-y 18456"|||

Start->Run...->services.msc

Then go to the SQL Server service and stop it. Then Click <Properties> and in the <Start Parameters> field enter -y 18456. You can then click <Start> to restart the server with that parameter and then press cancel to exit without saving the parameter, so that future restarts won't use it. If you press OK, then the server will be restarted with that parameter - you probably don't want that.

Thanks
Laurentiu

|||

Ok I have an issue here....I am trying to do a full system recovery and this is what I am getting...

File/minint/system32/biosinfo.inf could not be loaded the error is code 14 ?

And I do NOT have the Windows XP Disk either...so if theres a way around this please by all means help me .......

can some please help me with this please

sql

Error with web synchronization

Hello,

We have SQL2k5 SP1 as our database server. We have another box running IIS for web synchronization. We are seeing a lot of errors on the client side stating that they are unable to connect to the distributor. When we look at the event log on the IIS box we find application errors with the replisapi.dll. Below is a dump from the event log of the IIS box. Both boxes are 2003 with the currect patches and each have 4 GB RAM. Any help would be appreciated.

John

Event Type:Error

Event Source:Application Error

Event Category:(100)

Event ID:1000

Date:9/12/2006

Time:8:12:48 PM

User:N/A

Computer: SERVER-IIS5

Description:

Faulting application w3wp.exe, version 6.0.3790.1830, faulting module replisapi.dll, version 2005.90.1399.0, fault address 0x0002bd67.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Data:

0000: 41 70 70 6c 69 63 61 74Applicat

0008: 69 6f 6e 20 46 61 69 6cion Fail

0010: 75 72 65 20 20 77 33 77urew3w

0018: 70 2e 65 78 65 20 36 2ep.exe 6.

0020: 30 2e 33 37 39 30 2e 310.3790.1

0028: 38 33 30 20 69 6e 20 72830 in r

0030: 65 70 6c 69 73 61 70 69eplisapi

0038: 2e 64 6c 6c 20 32 30 30.dll 200

0040: 35 2e 39 30 2e 31 33 395.90.139

0048: 39 2e 30 20 61 74 20 6f9.0 at o

0050: 66 66 73 65 74 20 30 30ffset 00

0058: 30 32 62 64 36 3702bd67

Hi John

1) What results do you get running the diagnostics mode?

"https://{secuseServerAddress}/{virtualFolder}/replisapi.dll?diag"

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

Just a few pointers from my experience so far. I'm not too knowledgable about IIS and it's more detailed inner workings there may be others better placed to suggest action?

Rab|||

Rab,

1.

SQL Websync diagnostic information

Client Information:

AUTH_USER:	domain\user
AUTH_TYPE:	Basic
HTTPS:	on
LOGON_USER:	domain\user
REMOTE_ADDR:	24.30.144.122
REMOTE_HOST:	24.30.144.122
REMOTE_USER:	domain\user
REQUEST_METHOD:	GET
SERVER_NAME:	replicate.url.com
SERVER_PORT:	443
SERVER_PROTOCOL:	HTTP/1.1
SERVER_SOFTWARE:	Microsoft-IIS/6.0
URL:	/replia/replisapi.dll

Class Initialization test:

Class	Status	ErrorCode
replisapi.dll classes	SUCCESS	0x0
CLSID_SQLReplErrors	SUCCESS	0x0
replrec.dll classes	SUCCESS	0x0
msxml6.dll classes	SUCCESS	0x0

ReplIsapi Statistics:

Counter	Value
Active worker threads.	8
Active Requests.	1
Total Query Requests	1
Total File Download Requests	0
Total Sync Requests	373
Total Requests Handled	1120
Max queue wait time (ms)	0
Average time in Queue (ms)	0
Max sync duration (ms)	148078
Average Sync Duration (ms)	6615

ReplIsapi Settings:

Property	Value
SNAC version (sqlncli.dll)	2005.90.1399.0
Logging Enabled	FALSE

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

We created the files manually as SQL isn't really installed on this box other than what the documents told us to do
3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

IIS was already installed on the box
4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

When we upgraded the server to SP1 we took the new replisapi.dll and replaced the old one on the IIS box.

|||

Firstly you should be using the install process to install the tools only installation on the IIS box.

Next, you should not be just copying the replisapi.dll and replacing it on the IIS box. The IIS SQL components should be upgraded to SP1 too.

If you are already doing this and still hitting the error, I would recommend contacting CSS and opening a case with them.

Error with web synchronization

Hello,

We have SQL2k5 SP1 as our database server. We have another box running IIS for web synchronization. We are seeing a lot of errors on the client side stating that they are unable to connect to the distributor. When we look at the event log on the IIS box we find application errors with the replisapi.dll. Below is a dump from the event log of the IIS box. Both boxes are 2003 with the currect patches and each have 4 GB RAM. Any help would be appreciated.

John

Event Type:Error

Event Source:Application Error

Event Category:(100)

Event ID:1000

Date:9/12/2006

Time:8:12:48 PM

User:N/A

Computer: SERVER-IIS5

Description:

Faulting application w3wp.exe, version 6.0.3790.1830, faulting module replisapi.dll, version 2005.90.1399.0, fault address 0x0002bd67.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Data:

0000: 41 70 70 6c 69 63 61 74Applicat

0008: 69 6f 6e 20 46 61 69 6cion Fail

0010: 75 72 65 20 20 77 33 77urew3w

0018: 70 2e 65 78 65 20 36 2ep.exe 6.

0020: 30 2e 33 37 39 30 2e 310.3790.1

0028: 38 33 30 20 69 6e 20 72830 in r

0030: 65 70 6c 69 73 61 70 69eplisapi

0038: 2e 64 6c 6c 20 32 30 30.dll 200

0040: 35 2e 39 30 2e 31 33 395.90.139

0048: 39 2e 30 20 61 74 20 6f9.0 at o

0050: 66 66 73 65 74 20 30 30ffset 00

0058: 30 32 62 64 36 3702bd67

Hi John

1) What results do you get running the diagnostics mode?

"https://{secuseServerAddress}/{virtualFolder}/replisapi.dll?diag"

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

Just a few pointers from my experience so far. I'm not too knowledgable about IIS and it's more detailed inner workings there may be others better placed to suggest action?

Rab
|||

Rab,

1.

SQL Websync diagnostic information

Client Information:

AUTH_USER:	domain\user
AUTH_TYPE:	Basic
HTTPS:	on
LOGON_USER:	domain\user
REMOTE_ADDR:	24.30.144.122
REMOTE_HOST:	24.30.144.122
REMOTE_USER:	domain\user
REQUEST_METHOD:	GET
SERVER_NAME:	replicate.url.com
SERVER_PORT:	443
SERVER_PROTOCOL:	HTTP/1.1
SERVER_SOFTWARE:	Microsoft-IIS/6.0
URL:	/replia/replisapi.dll

Class Initialization test:

Class	Status	ErrorCode
replisapi.dll classes	SUCCESS	0x0
CLSID_SQLReplErrors	SUCCESS	0x0
replrec.dll classes	SUCCESS	0x0
msxml6.dll classes	SUCCESS	0x0

ReplIsapi Statistics:

Counter	Value
Active worker threads.	8
Active Requests.	1
Total Query Requests	1
Total File Download Requests	0
Total Sync Requests	373
Total Requests Handled	1120
Max queue wait time (ms)	0
Average time in Queue (ms)	0
Max sync duration (ms)	148078
Average Sync Duration (ms)	6615

ReplIsapi Settings:

Property	Value
SNAC version (sqlncli.dll)	2005.90.1399.0
Logging Enabled	FALSE

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

We created the files manually as SQL isn't really installed on this box other than what the documents told us to do
3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

IIS was already installed on the box
4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

When we upgraded the server to SP1 we took the new replisapi.dll and replaced the old one on the IIS box.

|||

Firstly you should be using the install process to install the tools only installation on the IIS box.

Next, you should not be just copying the replisapi.dll and replacing it on the IIS box. The IIS SQL components should be upgraded to SP1 too.

If you are already doing this and still hitting the error, I would recommend contacting CSS and opening a case with them.

Error with web synchronization

Hello,

We have SQL2k5 SP1 as our database server. We have another box running IIS for web synchronization. We are seeing a lot of errors on the client side stating that they are unable to connect to the distributor. When we look at the event log on the IIS box we find application errors with the replisapi.dll. Below is a dump from the event log of the IIS box. Both boxes are 2003 with the currect patches and each have 4 GB RAM. Any help would be appreciated.

John

Event Type:Error

Event Source:Application Error

Event Category:(100)

Event ID:1000

Date:9/12/2006

Time:8:12:48 PM

User:N/A

Computer: SERVER-IIS5

Description:

Faulting application w3wp.exe, version 6.0.3790.1830, faulting module replisapi.dll, version 2005.90.1399.0, fault address 0x0002bd67.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Data:

0000: 41 70 70 6c 69 63 61 74Applicat

0008: 69 6f 6e 20 46 61 69 6cion Fail

0010: 75 72 65 20 20 77 33 77urew3w

0018: 70 2e 65 78 65 20 36 2ep.exe 6.

0020: 30 2e 33 37 39 30 2e 310.3790.1

0028: 38 33 30 20 69 6e 20 72830 in r

0030: 65 70 6c 69 73 61 70 69eplisapi

0038: 2e 64 6c 6c 20 32 30 30.dll 200

0040: 35 2e 39 30 2e 31 33 395.90.139

0048: 39 2e 30 20 61 74 20 6f9.0 at o

0050: 66 66 73 65 74 20 30 30ffset 00

0058: 30 32 62 64 36 3702bd67

Hi John

1) What results do you get running the diagnostics mode?

"https://{secuseServerAddress}/{virtualFolder}/replisapi.dll?diag"

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

Just a few pointers from my experience so far. I'm not too knowledgable about IIS and it's more detailed inner workings there may be others better placed to suggest action?

Rab|||

Rab,

1.

SQL Websync diagnostic information

Client Information:

AUTH_USER:	domain\user
AUTH_TYPE:	Basic
HTTPS:	on
LOGON_USER:	domain\user
REMOTE_ADDR:	24.30.144.122
REMOTE_HOST:	24.30.144.122
REMOTE_USER:	domain\user
REQUEST_METHOD:	GET
SERVER_NAME:	replicate.url.com
SERVER_PORT:	443
SERVER_PROTOCOL:	HTTP/1.1
SERVER_SOFTWARE:	Microsoft-IIS/6.0
URL:	/replia/replisapi.dll

Class Initialization test:

Class	Status	ErrorCode
replisapi.dll classes	SUCCESS	0x0
CLSID_SQLReplErrors	SUCCESS	0x0
replrec.dll classes	SUCCESS	0x0
msxml6.dll classes	SUCCESS	0x0

ReplIsapi Statistics:

Counter	Value
Active worker threads.	8
Active Requests.	1
Total Query Requests	1
Total File Download Requests	0
Total Sync Requests	373
Total Requests Handled	1120
Max queue wait time (ms)	0
Average time in Queue (ms)	0
Max sync duration (ms)	148078
Average Sync Duration (ms)	6615

ReplIsapi Settings:

Property	Value
SNAC version (sqlncli.dll)	2005.90.1399.0
Logging Enabled	FALSE

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

We created the files manually as SQL isn't really installed on this box other than what the documents told us to do
3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

IIS was already installed on the box
4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

When we upgraded the server to SP1 we took the new replisapi.dll and replaced the old one on the IIS box.

|||

Firstly you should be using the install process to install the tools only installation on the IIS box.

Next, you should not be just copying the replisapi.dll and replacing it on the IIS box. The IIS SQL components should be upgraded to SP1 too.

If you are already doing this and still hitting the error, I would recommend contacting CSS and opening a case with them.

Error with web synchronization

Hello,

We have SQL2k5 SP1 as our database server. We have another box running IIS for web synchronization. We are seeing a lot of errors on the client side stating that they are unable to connect to the distributor. When we look at the event log on the IIS box we find application errors with the replisapi.dll. Below is a dump from the event log of the IIS box. Both boxes are 2003 with the currect patches and each have 4 GB RAM. Any help would be appreciated.

John

Event Type:Error

Event Source:Application Error

Event Category:(100)

Event ID:1000

Date:9/12/2006

Time:8:12:48 PM

User:N/A

Computer: SERVER-IIS5

Description:

Faulting application w3wp.exe, version 6.0.3790.1830, faulting module replisapi.dll, version 2005.90.1399.0, fault address 0x0002bd67.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Data:

0000: 41 70 70 6c 69 63 61 74Applicat

0008: 69 6f 6e 20 46 61 69 6cion Fail

0010: 75 72 65 20 20 77 33 77urew3w

0018: 70 2e 65 78 65 20 36 2ep.exe 6.

0020: 30 2e 33 37 39 30 2e 310.3790.1

0028: 38 33 30 20 69 6e 20 72830 in r

0030: 65 70 6c 69 73 61 70 69eplisapi

0038: 2e 64 6c 6c 20 32 30 30.dll 200

0040: 35 2e 39 30 2e 31 33 395.90.139

0048: 39 2e 30 20 61 74 20 6f9.0 at o

0050: 66 66 73 65 74 20 30 30ffset 00

0058: 30 32 62 64 36 3702bd67

Hi John

1) What results do you get running the diagnostics mode?

"https://{secuseServerAddress}/{virtualFolder}/replisapi.dll?diag"

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

Just a few pointers from my experience so far. I'm not too knowledgable about IIS and it's more detailed inner workings there may be others better placed to suggest action?

Rab|||

Rab,

1.

SQL Websync diagnostic information

Client Information:

AUTH_USER:	domain\user
AUTH_TYPE:	Basic
HTTPS:	on
LOGON_USER:	domain\user
REMOTE_ADDR:	24.30.144.122
REMOTE_HOST:	24.30.144.122
REMOTE_USER:	domain\user
REQUEST_METHOD:	GET
SERVER_NAME:	replicate.url.com
SERVER_PORT:	443
SERVER_PROTOCOL:	HTTP/1.1
SERVER_SOFTWARE:	Microsoft-IIS/6.0
URL:	/replia/replisapi.dll

Class Initialization test:

Class	Status	ErrorCode
replisapi.dll classes	SUCCESS	0x0
CLSID_SQLReplErrors	SUCCESS	0x0
replrec.dll classes	SUCCESS	0x0
msxml6.dll classes	SUCCESS	0x0

ReplIsapi Statistics:

Counter	Value
Active worker threads.	8
Active Requests.	1
Total Query Requests	1
Total File Download Requests	0
Total Sync Requests	373
Total Requests Handled	1120
Max queue wait time (ms)	0
Average time in Queue (ms)	0
Max sync duration (ms)	148078
Average Sync Duration (ms)	6615

ReplIsapi Settings:

Property	Value
SNAC version (sqlncli.dll)	2005.90.1399.0
Logging Enabled	FALSE

2) How did you set up Web Synchronization on the IIS box i.e. Through "Configure Web Synchronization Wizard" or by manually creating folders, copying and registering replisapi.dll etc..? (Incorrectly registered dll and web extensions not being installed will also cause issues)

We created the files manually as SQL isn't really installed on this box other than what the documents told us to do
3) Was IIS already installed prior to installing the SQL Server 2005 Components onto this box? (SQL Server setup may not have been able to set up the extensions correctly)

IIS was already installed on the box
4) Is the Database Server box running the same version of SQL Server 2005 i.e. Service Packs included. (I have experienced problems with this in the past)

When we upgraded the server to SP1 we took the new replisapi.dll and replaced the old one on the IIS box.

|||

Firstly you should be using the install process to install the tools only installation on the IIS box.

Next, you should not be just copying the replisapi.dll and replacing it on the IIS box. The IIS SQL components should be upgraded to SP1 too.

If you are already doing this and still hitting the error, I would recommend contacting CSS and opening a case with them.