Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is ver
y
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:

> We have a reporting database that end users run ad-hoc queries against (SQ
L
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log fo
r
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she i
s
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of dis
k
> space, and have gotten this message with tempdb log set to unrestricted an
d
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because
it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this
?
> Thanks,
> Dan

Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
Dan
Dan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:

> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan

Error 9002, tempdb log full

We have a reporting database that end users run ad-hoc queries against (SQL
Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
following error message:
Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for
the database to free up some log space.
When I track down the user associated with the Event Log message, he/she is
running a huge query that needs better selection criteria, but I don't
believe that the tempdb log is truely out of space. We have plenty of disk
space, and have gotten this message with tempdb log set to unrestricted and
with a limit of 15 GB.
Question: Is this a sql server bug or related to an i/o problem, because it
really isn't a true tempdb log full condition. Does this impact all users
using tempdb (I would think so) and any suggestions on how to prevent this?
Thanks,
DanDan,
This is not a bug. It is truly a tempdb log full alert. It does not matter
how much space you have because you are limiting the log to 15 GB. It is very
much possible for the log to be full on a reporting database. Remember, all
sorting, agregations and temp tables are created in the tempdb.
I don't think there is a right answer for the size, you will have to
increase it and watch it and increase it again as needed.
hth
DeeJay
"Dan H" wrote:
> We have a reporting database that end users run ad-hoc queries against (SQL
> Server 2000 SP 4 running on Windows 2000). Periodically, we'll get the
> following error message:
> Error: 9002, Severity: 17, State: 6
> The log file for database 'tempdb' is full. Back up the transaction log for
> the database to free up some log space.
> When I track down the user associated with the Event Log message, he/she is
> running a huge query that needs better selection criteria, but I don't
> believe that the tempdb log is truely out of space. We have plenty of disk
> space, and have gotten this message with tempdb log set to unrestricted and
> with a limit of 15 GB.
> Question: Is this a sql server bug or related to an i/o problem, because it
> really isn't a true tempdb log full condition. Does this impact all users
> using tempdb (I would think so) and any suggestions on how to prevent this?
> Thanks,
> Dan

Error 6107

I tried to detach my database (using enterprise manager).
I got an error when I tried to clear users ( Error 6107 --Only user
procesess can be killed)
I checked sp_who and realized that autoshrink is running.Is there anyway to
kill it.
When I tried kill <pid> from query Anz. same error -- Only user procesess
can be killed
ThanksI would recommend you wait for it to complete.
Another route would be shutting down sqlserver service. This is quite
drastic and error prone.
-oj
http://www.rac4sql.net
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uDz1gYe4DHA.1804@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I tried to detach my database (using enterprise manager).
> I got an error when I tried to clear users ( Error 6107 --Only user
> procesess can be killed)
> I checked sp_who and realized that autoshrink is running.Is there anyway

---

to

quote:

---

> kill it.
> When I tried kill <pid> from query Anz. same error -- Only user procesess
> can be killed
> Thanks
>

---

Error 6107

I tried to detach my database (using enterprise manager).
I got an error when I tried to clear users ( Error 6107 --Only user
procesess can be killed)
I checked sp_who and realized that autoshrink is running.Is there anyway to
kill it.
When I tried kill <pid> from query Anz. same error -- Only user procesess
can be killed
ThanksI would recommend you wait for it to complete.
Another route would be shutting down sqlserver service. This is quite
drastic and error prone.
--
-oj
http://www.rac4sql.net
"Abraham" <binu_ca@.yahoo.com> wrote in message
news:uDz1gYe4DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I tried to detach my database (using enterprise manager).
> I got an error when I tried to clear users ( Error 6107 --Only user
> procesess can be killed)
> I checked sp_who and realized that autoshrink is running.Is there anyway
to
> kill it.
> When I tried kill <pid> from query Anz. same error -- Only user procesess
> can be killed
> Thanks
>

Error 53

I have SQL Server 8 running locally on my networked XP pc. I would like to have users connect to the database there through the network. Each time they try to connect, the following error occures:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection open(CreateFile()).
Connection failed:
SQLState:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
When a user tries to connect, are they to use my IP address, my computer name, my SQL Server Group name? I think I've tried all ways, but they can't connect (myself included).
</scratching head>
tia,
JMorrell
Could be an error with authentication. Make sure that they are using named
pipes protocol and are authenticated to your PC with an ID that has
permissions for SQL server. Try having them map a drive first with this ID.
"JMorrell" wrote:

> I have SQL Server 8 running locally on my networked XP pc. I would like to have users connect to the database there through the network. Each time they try to connect, the following error occures:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection open(CreateFile()).
> Connection failed:
> SQLState:'08001'
> SQL Server Error: 6
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
> When a user tries to connect, are they to use my IP address, my computer name, my SQL Server Group name? I think I've tried all ways, but they can't connect (myself included).
> </scratching head>
> tia,
> --
> JMorrell

Error 53

I have SQL Server 8 running locally on my networked XP pc. I would like to
have users connect to the database there through the network. Each time the
y try to connect, the following error occures:
Connection failed:
SQLState: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][Named Pipes]Connection open(
CreateFile()).
Connection failed:
SQLState:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not exist or access denied.
When a user tries to connect, are they to use my IP address, my computer nam
e, my SQL Server Group name? I think I've tried all ways, but they can't co
nnect (myself included).
</scratching head>
tia,
--
JMorrellCould be an error with authentication. Make sure that they are using named
pipes protocol and are authenticated to your PC with an ID that has
permissions for SQL server. Try having them map a drive first with this ID.
"JMorrell" wrote:

> I have SQL Server 8 running locally on my networked XP pc. I would like t
o have users connect to the database there through the network. Each time t
hey try to connect, the following error occures:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 53
> [Microsoft][ODBC SQL Server Driver][Named Pipes]Connection ope
n(CreateFile()).
> Connection failed:
> SQLState:'08001'
> SQL Server Error: 6
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server
does not exist or access denied.
> When a user tries to connect, are they to use my IP address, my computer n
ame, my SQL Server Group name? I think I've tried all ways, but they can't
connect (myself included).
> </scratching head>
> tia,
> --
> JMorrell

Error 500, Maximum concurrent users have reached

We are running three web sites in a clustered environment
WLBS. The web servers are connected to a Database Server
running MS SQL server enterprise edition licensed as per
CPU license. The Windows 2000 Advanced Server Licensing
mode on all the servers is per SEAT license.

Our customers are accessing our web servers and after some
time getting error 500, Maximum concurrent users have
reached.

We have been told by our software provider that the
connection problem lies with the ADO components which
communicating with the SQL server.

We have been turning round and round, without any luck.

Your help will be highly appreciated.

Thanks and best regards

Dave

Learning is a Never Ending process

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Dave Kissoondoyal (network@.bowmans.intnet.mu) writes:
> We are running three web sites in a clustered environment
> WLBS. The web servers are connected to a Database Server
> running MS SQL server enterprise edition licensed as per
> CPU license. The Windows 2000 Advanced Server Licensing
> mode on all the servers is per SEAT license.
> Our customers are accessing our web servers and after some
> time getting error 500, Maximum concurrent users have
> reached.
> We have been told by our software provider that the
> connection problem lies with the ADO components which
> communicating with the SQL server.

A message that starts with a three-digit number with a leading 5
looks like a response code from a protocol that sits on top of
TCP/IP. That is, I would assume that this message comes from the
web server and not SQL Server.

Whether the web server reacts this way due to some error code
from SQL Server, I don't know. But it seems that you will have
to investigate the web server first.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Error 500 Takes it's toll

The Error:

An attempt to attach an auto-named database for file C:\Users\CodeFreak\Documents\Visual Studio 2005\WebSites\PersonalSite\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I've tried allowing permissions I'VE EVEN TRIED ALLOWING EVERYONE! I've tried right clicking the datasource under the datasource explorer and clicking 'detatch.' It works Locally but fails when i pusblish with IIS. Whats with these SQL databases that just dont want to work with IIS 7 ?

Published with: IIS 7

I've tried removing the "user instance."

This is User roles and all that which uses SQL Server 2005.

So my question is, How do i publish a website with SQL Server 2005 database?

Please give it to me step by step as im still quite a newbie.

Thanks in advance

Does the Account the Web Service is running with have the appropiate permissions to access the file ? Did you made sure that once the database is opened, the connection is reused at the second time the database is accessed ? Do you have the autoclose property set on or off ?

Jens K. Suessmeyer.

http://www.sqlserver2008.de
|||It was automatically set up, I checked the permissions in both NTFS and the website's permissions they all checked out fine|||

Auto CLose?

Can you elaborate?

I even tried Microsoft's template which failed aswell

I just don't know how to publish a web page after i click and drag a login for and use the administer options to create accounts and set permissions. Remember I'm still a newbie when it comes to aspx, VWD and IIS. I've only used HTML 2 weeks ago (ps. I'm a fast learner). So I would perfer step by step instructions. It works fine during debug but falls over on publish (yes i closed VWD).

|||

Problem solved. It turns out I put it in the same directory as the website.

Moving it (and modifying the connection string accordingly works)

Old connectiong String (From web.config)

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|datadirectory|aspnetdb.mdf;User Instance=true

Modified Connection String -works (after moving the database files of course)

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=C:\Mydatabase\aspnetdb.mdf;User Instance=true

Soo easy, I couldn't believe I missed it!

error 500 maximum concurent users have reached

We have three Windows 2000 web servers in a WLBS environment accessing a DB server. Though we have only 180,140 and 40 connections respectively on the servers we received ERROR 500 - maximum concurent users have reached.

Please help us to identify the problem.

Best regards

Network BowmansYou may have only 300 users on the website, but the web pages themselves could be opening hordes of connections. Two things to check would be the license for SQL Server (in control panel should be a license applet for SQL Server). Check to see if you have entered 500 CALs. If you have set the server up as per-processor license, or if you have plenty of cals entered on the system, you can check in Enterprise manager to see how many connections are allowed. Right click on the server, then properties, then the connections tab. Make sure the max connections is set to 0 (unlimited). 0 is the default, and I have not had any problems with it, so far. Hope this helps.|||Hmm. After reviewing the post, I seem to have mistaken the error number for a parameter. Looks like my suggestion may not help after all. Are you certain this is a DB error, and not a web error? Check the SQL Error log to be sure.

Error 3627 (Could not create worker thread) - Transaction Log Back

Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLL
Could it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL
|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:

>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL
|||Thanks everyone!!!
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:

> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL

Error 3627 (Could not create worker thread) - Transaction Log Back

Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLLCould it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>Recieved this error this morning, at the same time are users were kicked out
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entries
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL|||We are on SQL Server 2000, no error message that we have exceeded Work Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
"Warren Brunk" wrote:
> Could it be that you were over your max worker threads?
> http://msdn2.microsoft.com/en-us/library/ms187024.aspx
> When all worker threads are active with long running queries, SQL Server may
> appear unresponsive until a worker thread completes and becomes available.
> Though not a defect, this can sometimes be undesirable. If a process appears
> to be unresponsive and no new queries can be processed, then connect to SQL
> Server using the dedicated administrator connection (DAC), and kill the
> process. To prevent this, increase the number of max worker threads.
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Thomas.LeBlanc@.NoSpam.Com"
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
> news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> > Recieved this error this morning, at the same time are users were kicked
> > out
> > of SQL Server.
> >
> > We have anout 1500 connections at a time. There are no SQL Error log
> > entries
> > other than the Transaction Log failing, no Application Events or System
> > Events.
> >
> > The server did not go down, the CPUs (8) went to almost zero.
> >
> > SQL Server Enterprise Manager was getting a network error message when
> > trying to connect and within 5 minutes everything was OK.
> >
> > Any help?
> >
> > Thanks,
> > ThomasLL
>
>|||> Are you using any third party backup software?
No
We are on SQL Server 2000, no error message that we have exceeded Work
Threads.
We do have 8 processors and 64 Gigs, neither are maxed out.
Thanks for your responce.
Most sites I have read say you should NOT increase worker threads because of
overhead with more threads.
Cache Hit Ratio 101.05824
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 2261.0
--
Thanks,
ThomasLL
--
Thanks,
ThomasLL,
"Sue Hoegemeier" wrote:
> Are you using any third party backup software?
> You can also get this error if you are running low on system
> resources - mostly memory.
> You may have too much of a server load at the time you tried
> to do the backup. You'd want to get some baselines numbers
> using Performance Monitor.
> -Sue
> On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
> <ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
> >Recieved this error this morning, at the same time are users were kicked out
> >of SQL Server.
> >
> >We have anout 1500 connections at a time. There are no SQL Error log entries
> >other than the Transaction Log failing, no Application Events or System
> >Events.
> >
> >The server did not go down, the CPUs (8) went to almost zero.
> >
> >SQL Server Enterprise Manager was getting a network error message when
> >trying to connect and within 5 minutes everything was OK.
> >
> >Any help?
> >
> >Thanks,
> >ThomasLL
>|||You are correct about the worker threads which is why I
didn't suggest changing that setting. On SQL 2000, you will
almost always have problems if you increase the value from
the default 255. It is not advisable and you would want to
be monitoring UMS stats as well as other indicators before
even considering changing that.
But...most of the time the error you received is really due
to resource constraints. A one time snapshot of a few
performance metrics isn't going to tell you much. The
article on sql-server-performance.com for performance audits
provides some good information on what you want to check:
http://www.sql-server-performance.com/sql_server_performance_audit.asp
-Sue
On Mon, 9 Oct 2006 13:32:01 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:
>> Are you using any third party backup software?
>No
>We are on SQL Server 2000, no error message that we have exceeded Work
>Threads.
>We do have 8 processors and 64 Gigs, neither are maxed out.
>Thanks for your responce.
>Most sites I have read say you should NOT increase worker threads because of
>overhead with more threads.
>Cache Hit Ratio 101.05824
>Cache Flushes 0.0
>Free Page Scan (Avg) 0.0
>Free Page Scan (Max) 0.0
>Min Free Buffers 331.0
>Cache Size 4362.0
>Free Buffers 2261.0
>--
>Thanks,
>ThomasLL|||Thanks everyone!!!
--
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:
> Recieved this error this morning, at the same time are users were kicked out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL

Error 3627 (Could not create worker thread) - Transaction Log Back

Recieved this error this morning, at the same time are users were kicked out
of SQL Server.
We have anout 1500 connections at a time. There are no SQL Error log entries
other than the Transaction Log failing, no Application Events or System
Events.
The server did not go down, the CPUs (8) went to almost zero.
SQL Server Enterprise Manager was getting a network error message when
trying to connect and within 5 minutes everything was OK.
Any help?
Thanks,
ThomasLLCould it be that you were over your max worker threads?
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
When all worker threads are active with long running queries, SQL Server may
appear unresponsive until a worker thread completes and becomes available.
Though not a defect, this can sometimes be undesirable. If a process appears
to be unresponsive and no new queries can be processed, then connect to SQL
Server using the dedicated administrator connection (DAC), and kill the
process. To prevent this, increase the number of max worker threads.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Thomas.LeBlanc@.NoSpam.Com"
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote in message
news:759C0C8D-1413-45CB-BE38-743CF952CBF5@.microsoft.com...
> Recieved this error this morning, at the same time are users were kicked
> out
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log
> entries
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL|||Are you using any third party backup software?
You can also get this error if you are running low on system
resources - mostly memory.
You may have too much of a server load at the time you tried
to do the backup. You'd want to get some baselines numbers
using Performance Monitor.
-Sue
On Mon, 9 Oct 2006 09:54:02 -0700, Thomas.LeBlanc@.NoSpam.Com
<ThomasLeBlancNoSpamCom@.discussions.microsoft.com> wrote:

>Recieved this error this morning, at the same time are users were kicked ou
t
>of SQL Server.
>We have anout 1500 connections at a time. There are no SQL Error log entrie
s
>other than the Transaction Log failing, no Application Events or System
>Events.
>The server did not go down, the CPUs (8) went to almost zero.
>SQL Server Enterprise Manager was getting a network error message when
>trying to connect and within 5 minutes everything was OK.
>Any help?
>Thanks,
>ThomasLL|||Thanks everyone!!!
--
Thanks,
ThomasLL, MCDBA
"Thomas.LeBlanc@.NoSpam.Com" wrote:

> Recieved this error this morning, at the same time are users were kicked o
ut
> of SQL Server.
> We have anout 1500 connections at a time. There are no SQL Error log entri
es
> other than the Transaction Log failing, no Application Events or System
> Events.
> The server did not go down, the CPUs (8) went to almost zero.
> SQL Server Enterprise Manager was getting a network error message when
> trying to connect and within 5 minutes everything was OK.
> Any help?
> Thanks,
> ThomasLL

Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users collection

Hello --
We have a SQL Server 2000 machine that has approximately 500 user databases.
Don't ask why -- I did not create this mess.
Anyway, we have about 200 users access the SQL Server as a DSS machine
using SA. We've finally weaned all users off of SA and each have their on
ID per customer.
Tonight, I was finally trying to change the "SA" password from within
Enterprise Manager and received the following message:
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
collection. If the name is a qualified name, use [] to separate various
parts of the name, and try again.
It looks like I'm receiving this message for every database we have.
I brought up another instance of Enterprise Manager and looked at each
database property and noticed that the owner of database was either "SA",
"NT user that happens to be logged into server most of the time", or some
other local SQL Server user.
How do I fix this problem?
All databases should simply belong to DBO and no one inparticular.
The problem is, I have multiple people that create databases and they all
log into the server differenently and use different steps in creating the
database.
Can I simply ignore these error messages?
If I do need to change the owner of all 500 databases, is there an easier
way than manually running sp_changedbowner?
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!All databases need to be owned by a valid login or you may get errors in
Enterprise Manager. You can use a script below to generate a script that
changes database ownership to the login of your choosing.
SELECT 'EXEC ' +
CATALOG_NAME +
'..sp_changedbowner ''sa'''
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN
(
'master',
'model',
'msdb',
'tempdb',
'distribution'
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:uYTjmAcxDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hello --
> We have a SQL Server 2000 machine that has approximately 500 user
databases.
> Don't ask why -- I did not create this mess.
> Anyway, we have about 200 users access the SQL Server as a DSS machine
> using SA. We've finally weaned all users off of SA and each have their on
> ID per customer.
> Tonight, I was finally trying to change the "SA" password from within
> Enterprise Manager and received the following message:
> Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
> collection. If the name is a qualified name, use [] to separate various
> parts of the name, and try again.
> It looks like I'm receiving this message for every database we have.
> I brought up another instance of Enterprise Manager and looked at each
> database property and noticed that the owner of database was either "SA",
> "NT user that happens to be logged into server most of the time", or some
> other local SQL Server user.
> How do I fix this problem?
> All databases should simply belong to DBO and no one inparticular.
> The problem is, I have multiple people that create databases and they all
> log into the server differenently and use different steps in creating the
> database.
> Can I simply ignore these error messages?
> If I do need to change the owner of all 500 databases, is there an easier
> way than manually running sp_changedbowner?
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users collection

Hello --
We have a SQL Server 2000 machine that has approximately 500 user databases.
Don't ask why -- I did not create this mess.
Anyway, we have about 200 users access the SQL Server as a DSS machine
using SA. We've finally weaned all users off of SA and each have their on
ID per customer.
Tonight, I was finally trying to change the "SA" password from within
Enterprise Manager and received the following message:
Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
collection. If the name is a qualified name, use [] to separate various
parts of the name, and try again.
It looks like I'm receiving this message for every database we have.
I brought up another instance of Enterprise Manager and looked at each
database property and noticed that the owner of database was either "SA",
"NT user that happens to be logged into server most of the time", or some
other local SQL Server user.
How do I fix this problem?
All databases should simply belong to DBO and no one inparticular.
The problem is, I have multiple people that create databases and they all
log into the server differenently and use different steps in creating the
database.
Can I simply ignore these error messages?
If I do need to change the owner of all 500 databases, is there an easier
way than manually running sp_changedbowner?
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!All databases need to be owned by a valid login or you may get errors in
Enterprise Manager. You can use a script below to generate a script that
changes database ownership to the login of your choosing.
SELECT 'EXEC ' +
CATALOG_NAME +
'..sp_changedbowner ''sa'''
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN
(
'master',
'model',
'msdb',
'tempdb',
'distribution'
)
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:uYTjmAcxDHA.2448@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hello --
> We have a SQL Server 2000 machine that has approximately 500 user

---

databases.

quote:

---

> Don't ask why -- I did not create this mess.
> Anyway, we have about 200 users access the SQL Server as a DSS machine
> using SA. We've finally weaned all users off of SA and each have their on
> ID per customer.
> Tonight, I was finally trying to change the "SA" password from within
> Enterprise Manager and received the following message:
> Error 21776: [SQL-DMO] The name 'XXXX' was not found in the Users
> collection. If the name is a qualified name, use [] to separate various
> parts of the name, and try again.
> It looks like I'm receiving this message for every database we have.
> I brought up another instance of Enterprise Manager and looked at each
> database property and noticed that the owner of database was either "SA",
> "NT user that happens to be logged into server most of the time", or some
> other local SQL Server user.
> How do I fix this problem?
> All databases should simply belong to DBO and no one inparticular.
> The problem is, I have multiple people that create databases and they all
> log into the server differenently and use different steps in creating the
> database.
> Can I simply ignore these error messages?
> If I do need to change the owner of all 500 databases, is there an easier
> way than manually running sp_changedbowner?
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

---

Error 21776 when mapping orphaned users..

When restoring a database to another server (both servers running SQL Server
7) I use the sp_change_users_login procedure ("Update_one" argument) to map
one particular orphaned user to a user on the new server. This orphaned user
happens to be the dbo of the database on the original server.
The procedure exucutes correctly, but when I look in Enterprise Manager
(login properties) I get the Error 21776 message "The name "xxx" was not
found in the users collection..." etc.
Is this only a refresh problem whitin Enterprise Manager? If not, can I just
ignore it anyway, or do I have to take further actions to have it working
the way I want it to (that is, to let the user on the new server access the
restored database)
Thanks for your help!
Per WilliamsonTry to exit EM and start it again, to see if it is a refresh issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Per Williamson" <per.williamson@.telia.com> wrote in message
news:G5dEb.42639$dP1.166871@.newsc.telia.net...
> When restoring a database to another server (both servers running SQL Server
> 7) I use the sp_change_users_login procedure ("Update_one" argument) to map
> one particular orphaned user to a user on the new server. This orphaned user
> happens to be the dbo of the database on the original server.
> The procedure exucutes correctly, but when I look in Enterprise Manager
> (login properties) I get the Error 21776 message "The name "xxx" was not
> found in the users collection..." etc.
> Is this only a refresh problem whitin Enterprise Manager? If not, can I just
> ignore it anyway, or do I have to take further actions to have it working
> the way I want it to (that is, to let the user on the new server access the
> restored database)
> Thanks for your help!
> Per Williamson
>

Error 21776 [SQL DMO]

HELLO,
I have a bunch of users I need to change their defult db's and grant access
to that db, so I run the following script:
EXEC sp_defaultdb 'CGallego' , 'CRCS'
exec sp_grantdbaccess 'CGallego', 'CGallego'
When I go into EM to view their properties, I get the following error messag
e:
Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
Collection.
If the name is a qualified name use [] to seprate the various parts of t
he
name.
How can I resolve this issue. Thanks.
--
New SQL Server DBAHi,
Looks like the database owner for CRCS database is not available in
syslogins table. Did you restored this database from any other server.
If yes then the Login may not be there in Syslogins table. In that case
change the database owner using the stored procedure sp_changedbowner (see
books online for the usage)
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of
the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA|||The owner of the db is sa and is in the syslogins table.
--
New SQL Server DBA
"Hari Prasad" wrote:

> Hi,
> Looks like the database owner for CRCS database is not available in
> syslogins table. Did you restored this database from any other server.
> If yes then the Login may not be there in Syslogins table. In that case
> change the database owner using the stored procedure sp_changedbowner (see
> books online for the usage)
> Thanks
> hari
> SQL Server MVP
> "Newbie" <Newbie@.discussions.microsoft.com> wrote in message
> news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
>
>|||make sure CGallego is a valid SQL login first..
Then perhaps try to do sp_grantdbaccess first, then defaultdb..
also check sp_helpuser... Perhaps the sps are working properly,but you are
having trouble with SEM which is not related to your scripts.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of
the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA

Error 21776 [SQL DMO]

HELLO,
I have a bunch of users I need to change their defult db's and grant access
to that db, so I run the following script:
EXEC sp_defaultdb'CGallego' , 'CRCS'
exec sp_grantdbaccess 'CGallego', 'CGallego'
When I go into EM to view their properties, I get the following error message:
Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
Collection.
If the name is a qualified name use [] to seprate the various parts of the
name.
How can I resolve this issue. Thanks.
New SQL Server DBA
Hi,
Looks like the database owner for CRCS database is not available in
syslogins table. Did you restored this database from any other server.
If yes then the Login may not be there in Syslogins table. In that case
change the database owner using the stored procedure sp_changedbowner (see
books online for the usage)
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA
|||The owner of the db is sa and is in the syslogins table.
New SQL Server DBA
"Hari Prasad" wrote:

> Hi,
> Looks like the database owner for CRCS database is not available in
> syslogins table. Did you restored this database from any other server.
> If yes then the Login may not be there in Syslogins table. In that case
> change the database owner using the stored procedure sp_changedbowner (see
> books online for the usage)
> Thanks
> hari
> SQL Server MVP
> "Newbie" <Newbie@.discussions.microsoft.com> wrote in message
> news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
>
>
|||make sure CGallego is a valid SQL login first..
Then perhaps try to do sp_grantdbaccess first, then defaultdb..
also check sp_helpuser... Perhaps the sps are working properly,but you are
having trouble with SEM which is not related to your scripts.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA

Error 21776 [SQL DMO]

HELLO,
I have a bunch of users I need to change their defult db's and grant access
to that db, so I run the following script:
EXEC sp_defaultdb 'CGallego' , 'CRCS'
exec sp_grantdbaccess 'CGallego', 'CGallego'
When I go into EM to view their properties, I get the following error message:
Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
Collection.
If the name is a qualified name use [] to seprate the various parts of the
name.
How can I resolve this issue. Thanks.
--
New SQL Server DBAHi,
Looks like the database owner for CRCS database is not available in
syslogins table. Did you restored this database from any other server.
If yes then the Login may not be there in Syslogins table. In that case
change the database owner using the stored procedure sp_changedbowner (see
books online for the usage)
Thanks
hari
SQL Server MVP
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA|||The owner of the db is sa and is in the syslogins table.
--
New SQL Server DBA
"Hari Prasad" wrote:
> Hi,
> Looks like the database owner for CRCS database is not available in
> syslogins table. Did you restored this database from any other server.
> If yes then the Login may not be there in Syslogins table. In that case
> change the database owner using the stored procedure sp_changedbowner (see
> books online for the usage)
> Thanks
> hari
> SQL Server MVP
> "Newbie" <Newbie@.discussions.microsoft.com> wrote in message
> news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> > HELLO,
> >
> > I have a bunch of users I need to change their defult db's and grant
> > access
> > to that db, so I run the following script:
> >
> > EXEC sp_defaultdb 'CGallego' , 'CRCS'
> > exec sp_grantdbaccess 'CGallego', 'CGallego'
> >
> > When I go into EM to view their properties, I get the following error
> > message:
> >
> > Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> > Collection.
> > If the name is a qualified name use [] to seprate the various parts of the
> > name.
> >
> > How can I resolve this issue. Thanks.
> > --
> > New SQL Server DBA
>
>|||make sure CGallego is a valid SQL login first..
Then perhaps try to do sp_grantdbaccess first, then defaultdb..
also check sp_helpuser... Perhaps the sps are working properly,but you are
having trouble with SEM which is not related to your scripts.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:60DAE6E4-8738-42AD-A44B-D4639BE8501D@.microsoft.com...
> HELLO,
> I have a bunch of users I need to change their defult db's and grant
> access
> to that db, so I run the following script:
> EXEC sp_defaultdb 'CGallego' , 'CRCS'
> exec sp_grantdbaccess 'CGallego', 'CGallego'
> When I go into EM to view their properties, I get the following error
> message:
> Error 21776 [SQL DMO] The name 'CGallego' was not found in the Users
> Collection.
> If the name is a qualified name use [] to seprate the various parts of the
> name.
> How can I resolve this issue. Thanks.
> --
> New SQL Server DBA