DB_ID() Replacement

In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.

The original statement is:

SELECT

object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

I replaced DB_ID() as follows but none worked:

DB_ID(N'pubs')
pubs

What should the syntax look like?

I tried to run the following and it worked for me:

SELECT object_id AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent

AS frag

INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID( N'AdventureWorks'), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

|||

The BOL statement indicates the following issue:

If database is in 80 or earlier compat mode, certain newer features or syntax or keywords will not work. In this specific example, the ability to pass expressions as parameter values to TVFs is not possible if the database is below 90 compat mode. You will get a syntax error actually. So you have three alternatives to make it work:

1. Run the statement from a database that is in 90 compat mode. You can then do:

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (DB_ID('pubs'), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

2. Or specify the database id explicitly like (will work in db with any compat mode):

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (14, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- or

use pubs

go

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (14, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

3. Or use variable to specify the database id like (will work in db with any compat mode):

declare @.dbid int;

set @.dbid = db_id('pubs');

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM pubs.sys.dm_db_index_physical_stats (@.dbid, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

--or

use pubs

go

declare @.dbid int;

set @.dbid = db_id('pubs');

SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@.dbid, NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

Db Trigger Question

Hello,

I have a db trigger (example below) that is supposed to update a column with the current date. However, I need to know which row is being updated by a user so that I would update ONLY this row. Is there a straightforward way to figure out which row is updated by a user? My table includes a column called rowid which is a primary key.

Thanks for any help!

CREATE TRIGGER dbo.[DatetimeUpdate]

ON dbo.DepartmentMapping] AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.DepartmentMapping

SET DateUpdated = GetDate()

Donnie:

You need to take advangage of the INSERTED pseudo-table in your trigger. Look up CREATE TRIGGER in books online and examine their examples. Your update ought to change to something like:

UPDATE dbo.DepartmentMapping
SET DateUpdated = GetDate()
FROM inserted i
INNER JOIN dbo.DepartmentMapping a
ON a.{departmentMappingKey} = i.{departmentMappingKey}

|||

When code inside a trigger is being executed, the code has access to two 'Virtual Tables' named Inserted and Deleted.

In the case of an UPDATE statement, the Deleted table contains the rows as they would have been before any changes and the Inserted table contains the rows as they are after the changes. The virtual tables have the same columns as the table on which the trigger is defined.

You could amend your code to that shown below.

Chris

UPDATE dm

SET DateUpdated = GetDate()

FROM dbo.DepartmentMapping dm

INNER JOIN deleted d ON d.DepartmentMappingID = dm.DepartmentMappingID

--or equally

--INNER JOIN inserted i ON i.DepartmentMappingID = dm.DepartmentMappingID

|||Great. Thank You!|||Of course you wouldn't want to use deleted in this case, since you would be trying to update a deleted row :)sql

DB Query (complex maybe)

I have database like below.

BLOG
--POSTER_ID
--POSTED_BY (a FK refer to USERID)
--UPDATED_BY (a FK refer to USERID)

USER
--USER_ID
--NAME

I want to retrieve the info for all posts that posted and updated by different users, also the query returns both user names for every posted and updated action.

I can write two queries to do that

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.POSTED_BY = U.USER_ID

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.UPDATED_BY = U.USER_ID

Then write some code to combine the result for the posted and updated user name. But this is not efficient.

I am wondering how can I write one query to archeive that?

Thanks.You could use a UNION operator for starters.|||Use a UNION, or try this:
select B.POSTER_ID,
U.NAME
from Blog B
inner join USER U
ON B.POSTED_BY = U.USER_ID
OR B.UPDATE_BY = U.USER_ID
...but I'm guessing UNION will give you more functionality, as it makes it easy to determine what the user's role was regarding the thread:
select B.POSTER_ID,
'POSTER' as Role,
U.NAME
from Blog B
inner join USER U ON B.POSTED_BY = U.USER_ID
UNION
select B.POSTER_ID,
'RESPONDER' as Role,
U.NAME
from Blog B
inner join USER U ON B.UPDATED_BY = U.USER_ID|||select B.POSTER_ID,
U.NAME
from Blog B
inner join USER U
ON U.USER_ID in (B.UPDATE_BY,B.POSTED_BY)

I am not sure if this will work .. not close to a SQL machine to test.|||Thanks ya'll quick replies.

The UNION function still not perfect for what I expected.

In this case, what I exactly want to return is three fields:

POSTER_ID (in the blog), User Name (who posted it), User Name (who last modified it)

Suppose, I have POSTER_ID 1 in the BLOG table, John Doe posted it, and Mike Smith modified it.

I wish I could write a query to return a row like this:

POSTER_ID--|--POSTED_BY--|--MODIFIED_BY
---1---|--John Doe---|--Mike Smith

If I use UNION (ALL), the best case I can get is

POSTER_ID--|--USER NAME
---1---|--John Doe---
---1---|--Mike Smith---

Just wondering is that possible to use query to acheive that or use more than one query is the better idea?

Thank you.|||How 'bout this:

CREATE TABLE #BLOG (
POSTER_ID INTEGER,
POSTED_BY INTEGER,
UPDATED_BY INTEGER
)

INSERT INTO #BLOG
SELECT 1, 1,1
UNION ALL
SELECT 2,2,1
UNION ALL
SELECT 3,1,2
UNION ALL
SELECT 4,2,2

CREATE TABLE #USER (
USERID INTEGER,
USERNAME VARCHAR(20)
)

INSERT INTO #USER
SELECT 1, 'JOHN DOE'
UNION ALL
SELECT 2, 'JANE SMITH'

SELECT POSTER_ID, US1.USERNAME, US2.USERNAME
FROM #BLOG
INNER JOIN #USER US1 ON #BLOG.POSTED_BY=US1.USERID
INNER JOIN #USER US2 ON #BLOG.UPDATED_BY=US2.USERID
WHERE POSTED_BY<>UPDATED_BY

DROP TABLE #BLOG
DROP TABLE #USER|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.

Why is left outer needed instead of inner? Did I miss something?|||Henxian Dude, learn some basic SQL...
select Blog.POSTER_ID,
Posters.NAME as Poster,
Updaters.NAME as Updater
from Blog
left outer join USER Posters ON Blog.POSTED_BY = Posters.USER_ID
left outer join USER Updaters ON Blog.UPDATED_BY = Updaters.USER_ID
Please read the Books Online section on SELECT statements, and we can answer any further questions you have.

Wow Blindman you rock!!!. :shocked:

I figure out another way that use CASE, WHEN, THEN clause to acheive the same result as yours. (It take me several hours to figure out that way) But compare to yours, my query is too much longer and complicate. (too shy to post my SQL here:p )

Yeah, I nerver use outer join in my queries before. Will look at it in the furture.

Appreicate you all's help. This is a great forum!|||Why is left outer needed instead of inner? Did I miss something?
I'd recommend outer joins here to cover cases where either the POSTED_BY column is null (unlikely) or the UPDATED_BY column is null (more than likely).

You could get away with an inner join on the POSTED_BY column, and I suppose this may even be preferable if you only want to show users that have posted.

DB PROCESS IS DEAD

Dear Friends,

Please help me in below unexpected error.

Server Details:

HP Proliant DL385 G1, 142*6 GB

AMD Opteron ? Processor 280, 2.40 GHz

4.00 GB RAM, DVD ROM

Win2K3 R2, SP1 64bit

SQL Server Details:

SQL Server 2000 Enterprise Edition 32bit

Service Pack4

Network Details:

Clients can access the server by Terminal Server and also by Direct Connectivity.

The connectivity is provided by RF network Provider.

Application Details:

Application was designed by Power Builder 6.5.

Error Details:

If I divide my clients connectivity to the server then it is divided into 3 types

TYPE A >> Users inside my LAN using a DNS server they give the server name in their Parameter file to connect to the Database Server.

TYPE B >> Users who are in WAN connect to the server by Terminal Servers for Data Input.

Terminal Servers are inside my LAN.

TYPE C >> Users who are in WAN connect to the Server by Direct Connection for Reports and Print Out they give the IP address of the Database server directly in their Parameter files.

After every 6 to 7 days the ERROR DB Processes Dead comes to the TYPE C.

The error Numbers are 100025 and 100005.

At the same time TYPE A AND TYPE B are not getting any errors.

For further diagnosis I have taken a client machine to Net Meeting and taken his desktop in control. I have connected the client machine to the database server by ISQL in command prompt.

From Backend I can see that the SQL server has allocated a SPID to the client machine. After I have executed

1>SELECT * FROM SYSDATABASES

2>GO

It will return me all data but it will not show me how many rows selected or EOF.

After some time it will give me the prompt (3>) but without EOF and after that if I give another query then it will give the below error

DB-Library: Attempt to initiate a new SQL Server Operation with results pending.

DB-Library: Unexpected EOF from SQL Server. General Network Error. Check Your Documentation.

Net-Library error 10054: ConnectionCheckForData(CheckForData())

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

At the same time if I give IP address of another server then it works and TYPE A and TYPE B don’t get any problem.

To stop this error I have to restart the server after that for again seven days it works and again the same problem.

I cant find anything I have checked tempdb, cache size but it is normal no abnormal activities.

No virus Issues.

Please advice.

Regards,

James

You might want to try posting this in the "SQL Server Data Access" forum which deals with network issues.

One consideration is that your output indicates DB-Library which has been obsoleted in newer versions of SQL. You might consider using an ODBC or OLEDB client to see if you get the same result.

|||

Hello Guys the solution of the above problem is Installing the latest Service Pack 8.0.2187.

My problem is solved.... Till Now I couldn't findout what was the problem.

Thanks to ALL,

James

DB PROCESS IS DEAD

Dear Friends,

Please help me in below unexpected error.

Server Details:

HP Proliant DL385 G1, 142*6 GB

AMD Opteron ? Processor 280, 2.40 GHz

4.00 GB RAM, DVD ROM

Win2K3 R2, SP1 64bit

SQL Server Details:

SQL Server 2000 Enterprise Edition 32bit

Service Pack4

Network Details:

Clients can access the server by Terminal Server and also by Direct Connectivity.

The connectivity is provided by RF network Provider.

Application Details:

Application was designed by Power Builder 6.5.

Error Details:

If I divide my clients connectivity to the server then it is divided into 3 types

TYPE A >> Users inside my LAN using a DNS server they give the server name in their Parameter file to connect to the Database Server.

TYPE B >> Users who are in WAN connect to the server by Terminal Servers for Data Input.

Terminal Servers are inside my LAN.

TYPE C >> Users who are in WAN connect to the Server by Direct Connection for Reports and Print Out they give the IP address of the Database server directly in their Parameter files.

After every 6 to 7 days the ERROR DB Processes Dead comes to the TYPE C.

The error Numbers are 100025 and 100005.

At the same time TYPE A AND TYPE B are not getting any errors.

For further diagnosis I have taken a client machine to Net Meeting and taken his desktop in control. I have connected the client machine to the database server by ISQL in command prompt.

From Backend I can see that the SQL server has allocated a SPID to the client machine. After I have executed

1>SELECT * FROM SYSDATABASES

2>GO

It will return me all data but it will not show me how many rows selected or EOF.

After some time it will give me the prompt (3>) but without EOF and after that if I give another query then it will give the below error

DB-Library: Attempt to initiate a new SQL Server Operation with results pending.

DB-Library: Unexpected EOF from SQL Server. General Network Error. Check Your Documentation.

Net-Library error 10054: ConnectionCheckForData(CheckForData())

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

DB-Library: DBPROCESSES is dead or not enabled.

At the same time if I give IP address of another server then it works and TYPE A and TYPE B don’t get any problem.

To stop this error I have to restart the server after that for again seven days it works and again the same problem.

I cant find anything I have checked tempdb, cache size but it is normal no abnormal activities.

No virus Issues.

Please advice.

Regards,

James

You might want to try posting this in the "SQL Server Data Access" forum which deals with network issues.

One consideration is that your output indicates DB-Library which has been obsoleted in newer versions of SQL. You might consider using an ODBC or OLEDB client to see if you get the same result.

|||

Hello Guys the solution of the above problem is Installing the latest Service Pack 8.0.2187.

My problem is solved.... Till Now I couldn't findout what was the problem.

Thanks to ALL,

James

DB Maintenance Plan optimization job aborts

My weekly SQL Server Database Maintenance Plan optimization job aborts
with the error listed below.
Please me resolve this error.
Thank You,
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
found this
http://groups-beta.google.com/group/...osoft+SQL-DMO+(ODBC+SQLState:+42000)]+Error+1934:+[Microsoft][ODBC+SQL&rnum=4#434422e74f541c1b

DB Maintenance Plan optimization job aborts

My weekly SQL Server Database Maintenance Plan optimization job aborts
with the error listed below.
Please me resolve this error.
Thank You,
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.found this
http://groups-beta.google.com/group...rosoft+SQL-DMO+(
ODBC+SQLState:+42000)]+Error+1934:+[Microsoft][ODBC+SQL&rnum=4#43442
2e74f541c1b

DB Maintenance Plan optimization job aborts

My weekly SQL Server Database Maintenance Plan optimization job aborts
with the error listed below.
Please me resolve this error.
Thank You,
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.found this
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/facf07b869125687/434422e74f541c1b?lnk=st&q=[Microsoft+SQL-DMO+(ODBC+SQLState:+42000)]+Error+1934:+[Microsoft][ODBC+SQL&rnum=4#434422e74f541c1b

db log Timeout errors

Hi,
we've got a situation as below:
Error: database log truncated, db1
Error 9002 : The database log file for "temdb" is full. Backup the
transaction log for the database to free up some space
Any thoughts on these errors?
On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to grow by
10% unrestricted. Our Apps developers were getting some timeouts running
queries on DB1. It's possible they may be running some large queries that
cause these errors (or at least the second error). Any ideas on this?
What would be a probable solution?
Or they are harmless and can be ignored?
Many thanks,
SQL NewbieFor some operations, autogrow doesn't grow the file fast enough to keep up with the space usage
requirements. Hence a "full" error message. Reading your error message, this is in the tempdb
database. I suggest you pre-allocate space for tempdb. Use the ALTER DATABASE command for that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugh" <Hugh@.discussions.microsoft.com> wrote in message
news:80BF48AB-2D21-4DA4-9E47-B860F0859FA6@.microsoft.com...
> Some guru please help...pretty congeated.
> "Hugh" wrote:
>> Hi,
>> we've got a situation as below:
>> Error: database log truncated, db1
>> Error 9002 : The database log file for "temdb" is full. Backup the
>> transaction log for the database to free up some space
>> Any thoughts on these errors?
>> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to grow by
>> 10% unrestricted. Our Apps developers were getting some timeouts running
>> queries on DB1. It's possible they may be running some large queries that
>> cause these errors (or at least the second error). Any ideas on this?
>> What would be a probable solution?
>> Or they are harmless and can be ignored?
>> Many thanks,
>> SQL Newbie|||thanx mate.
can recommend some good materials on the concepts of how SQL server works?
Any samples of what the statement will be like?
Also how to know how much more space is required? Will it require restart?
Is anything loss due this error?
Any means to analyse and prevent similar occurences?
thanx,
An SQL newbie
"Tibor Karaszi" wrote:
> For some operations, autogrow doesn't grow the file fast enough to keep up with the space usage
> requirements. Hence a "full" error message. Reading your error message, this is in the tempdb
> database. I suggest you pre-allocate space for tempdb. Use the ALTER DATABASE command for that.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
> news:80BF48AB-2D21-4DA4-9E47-B860F0859FA6@.microsoft.com...
> > Some guru please help...pretty congeated.
> >
> > "Hugh" wrote:
> >
> >> Hi,
> >>
> >> we've got a situation as below:
> >>
> >> Error: database log truncated, db1
> >>
> >> Error 9002 : The database log file for "temdb" is full. Backup the
> >> transaction log for the database to free up some space
> >>
> >> Any thoughts on these errors?
> >>
> >> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to grow by
> >> 10% unrestricted. Our Apps developers were getting some timeouts running
> >> queries on DB1. It's possible they may be running some large queries that
> >> cause these errors (or at least the second error). Any ideas on this?
> >>
> >> What would be a probable solution?
> >> Or they are harmless and can be ignored?
> >>
> >> Many thanks,
> >> SQL Newbie
>
>|||You might want to get a hold of "Inside SQL Server 2000" by Kalen Delaney.
It is a big book but if you want to know how sql server works that is the
book.
--
Andrew J. Kelly SQL MVP
"Hugh" <Hugh@.discussions.microsoft.com> wrote in message
news:8B067416-65CF-45A6-B899-636CE6AF4CE1@.microsoft.com...
> thanx mate.
> can recommend some good materials on the concepts of how SQL server works?
> Any samples of what the statement will be like?
> Also how to know how much more space is required? Will it require
restart?
> Is anything loss due this error?
> Any means to analyse and prevent similar occurences?
> thanx,
> An SQL newbie
> "Tibor Karaszi" wrote:
> > For some operations, autogrow doesn't grow the file fast enough to keep
up with the space usage
> > requirements. Hence a "full" error message. Reading your error message,
this is in the tempdb
> > database. I suggest you pre-allocate space for tempdb. Use the ALTER
DATABASE command for that.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
> > news:80BF48AB-2D21-4DA4-9E47-B860F0859FA6@.microsoft.com...
> > > Some guru please help...pretty congeated.
> > >
> > > "Hugh" wrote:
> > >
> > >> Hi,
> > >>
> > >> we've got a situation as below:
> > >>
> > >> Error: database log truncated, db1
> > >>
> > >> Error 9002 : The database log file for "temdb" is full. Backup the
> > >> transaction log for the database to free up some space
> > >>
> > >> Any thoughts on these errors?
> > >>
> > >> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to
grow by
> > >> 10% unrestricted. Our Apps developers were getting some timeouts
running
> > >> queries on DB1. It's possible they may be running some large queries
that
> > >> cause these errors (or at least the second error). Any ideas on this?
> > >>
> > >> What would be a probable solution?
> > >> Or they are harmless and can be ignored?
> > >>
> > >> Many thanks,
> > >> SQL Newbie
> >
> >
> >|||thanx Andrew,
Pertaining to my problem. can any1 provide some samples of what the
statement will be like?
Also how to know how much more space is required? Will it require restart?
Is anything loss due this error?
Any means to analyse and prevent similar occurences?
thanx,
An SQL newbie
"Andrew J. Kelly" wrote:
> You might want to get a hold of "Inside SQL Server 2000" by Kalen Delaney.
> It is a big book but if you want to know how sql server works that is the
> book.
> --
> Andrew J. Kelly SQL MVP
>
> "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
> news:8B067416-65CF-45A6-B899-636CE6AF4CE1@.microsoft.com...
> > thanx mate.
> >
> > can recommend some good materials on the concepts of how SQL server works?
> >
> > Any samples of what the statement will be like?
> > Also how to know how much more space is required? Will it require
> restart?
> > Is anything loss due this error?
> >
> > Any means to analyse and prevent similar occurences?
> >
> > thanx,
> > An SQL newbie
> >
> > "Tibor Karaszi" wrote:
> >
> > > For some operations, autogrow doesn't grow the file fast enough to keep
> up with the space usage
> > > requirements. Hence a "full" error message. Reading your error message,
> this is in the tempdb
> > > database. I suggest you pre-allocate space for tempdb. Use the ALTER
> DATABASE command for that.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
> > > news:80BF48AB-2D21-4DA4-9E47-B860F0859FA6@.microsoft.com...
> > > > Some guru please help...pretty congeated.
> > > >
> > > > "Hugh" wrote:
> > > >
> > > >> Hi,
> > > >>
> > > >> we've got a situation as below:
> > > >>
> > > >> Error: database log truncated, db1
> > > >>
> > > >> Error 9002 : The database log file for "temdb" is full. Backup the
> > > >> transaction log for the database to free up some space
> > > >>
> > > >> Any thoughts on these errors?
> > > >>
> > > >> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to
> grow by
> > > >> 10% unrestricted. Our Apps developers were getting some timeouts
> running
> > > >> queries on DB1. It's possible they may be running some large queries
> that
> > > >> cause these errors (or at least the second error). Any ideas on this?
> > > >>
> > > >> What would be a probable solution?
> > > >> Or they are harmless and can be ignored?
> > > >>
> > > >> Many thanks,
> > > >> SQL Newbie
> > >
> > >
> > >
>
>|||> Pertaining to my problem. can any1 provide some samples of what the
> statement will be like?
There's an example in Books Online, I believe (I'm in a hurry right now).
> Also how to know how much more space is required?
Monitor tempdb size over time to get an idea of the space it needs for your particular applications,
schema, indexes, queries and workload.
> Will it require restart?
Tempdb is re-created every time SQL Server restarts. Applying the "template" size which you set
using ALTER DATABASE requires a re-start. You can also use ALTER DATABASE to change the size of your
running tempdb, but again, it is re-created at restart.
> Is anything loss due this error?
The statements that failed has ... failed.
> Any means to analyze and prevent similar occurences?
Monitor size, and your errorlogs/eventlogs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hugh" <Hugh@.discussions.microsoft.com> wrote in message
news:06CD159E-9ED4-4F20-9788-A40560615699@.microsoft.com...
> thanx Andrew,
> Pertaining to my problem. can any1 provide some samples of what the
> statement will be like?
> Also how to know how much more space is required? Will it require restart?
> Is anything loss due this error?
> Any means to analyse and prevent similar occurences?
> thanx,
> An SQL newbie
>
>
> "Andrew J. Kelly" wrote:
>> You might want to get a hold of "Inside SQL Server 2000" by Kalen Delaney.
>> It is a big book but if you want to know how sql server works that is the
>> book.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
>> news:8B067416-65CF-45A6-B899-636CE6AF4CE1@.microsoft.com...
>> > thanx mate.
>> >
>> > can recommend some good materials on the concepts of how SQL server works?
>> >
>> > Any samples of what the statement will be like?
>> > Also how to know how much more space is required? Will it require
>> restart?
>> > Is anything loss due this error?
>> >
>> > Any means to analyse and prevent similar occurences?
>> >
>> > thanx,
>> > An SQL newbie
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > For some operations, autogrow doesn't grow the file fast enough to keep
>> up with the space usage
>> > > requirements. Hence a "full" error message. Reading your error message,
>> this is in the tempdb
>> > > database. I suggest you pre-allocate space for tempdb. Use the ALTER
>> DATABASE command for that.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Hugh" <Hugh@.discussions.microsoft.com> wrote in message
>> > > news:80BF48AB-2D21-4DA4-9E47-B860F0859FA6@.microsoft.com...
>> > > > Some guru please help...pretty congeated.
>> > > >
>> > > > "Hugh" wrote:
>> > > >
>> > > >> Hi,
>> > > >>
>> > > >> we've got a situation as below:
>> > > >>
>> > > >> Error: database log truncated, db1
>> > > >>
>> > > >> Error 9002 : The database log file for "temdb" is full. Backup the
>> > > >> transaction log for the database to free up some space
>> > > >>
>> > > >> Any thoughts on these errors?
>> > > >>
>> > > >> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to
>> grow by
>> > > >> 10% unrestricted. Our Apps developers were getting some timeouts
>> running
>> > > >> queries on DB1. It's possible they may be running some large queries
>> that
>> > > >> cause these errors (or at least the second error). Any ideas on this?
>> > > >>
>> > > >> What would be a probable solution?
>> > > >> Or they are harmless and can be ignored?
>> > > >>
>> > > >> Many thanks,
>> > > >> SQL Newbie
>> > >
>> > >
>> > >
>>|||Some guru please help...pretty congeated.
"Hugh" wrote:
> Hi,
> we've got a situation as below:
> Error: database log truncated, db1
> Error 9002 : The database log file for "temdb" is full. Backup the
> transaction log for the database to free up some space
> Any thoughts on these errors?
> On db1, tempdb is 1314Mb with a log size of 28Mb. Both are set to grow by
> 10% unrestricted. Our Apps developers were getting some timeouts running
> queries on DB1. It's possible they may be running some large queries that
> cause these errors (or at least the second error). Any ideas on this?
> What would be a probable solution?
> Or they are harmless and can be ignored?
> Many thanks,
> SQL Newbie

DB Library error: Login incorrect

I have application that I received the error listed below from SQL Server
2000 database.
Please help me resolve this error message.
Thank You,
Error source: dbopen
DB Library error: Login incorrect
SQL Server message: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.try:
http://support.microsoft.com/kb/827422
-oj
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:12208B72-C604-4314-AF3C-57579401DABC@.microsoft.com...
> I have application that I received the error listed below from SQL Server
> 2000 database.
> Please help me resolve this error message.
> Thank You,
>
> Error source: dbopen
> DB Library error: Login incorrect
> SQL Server message: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>

DB Library error: Login incorrect

I have application that I received the error listed below from SQL Server
2000 database.
Please help me resolve this error message.
Thank You,
Error source: dbopen
DB Library error: Login incorrect
SQL Server message: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.try:
http://support.microsoft.com/kb/827422
-oj
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:12208B72-C604-4314-AF3C-57579401DABC@.microsoft.com...
> I have application that I received the error listed below from SQL Server
> 2000 database.
> Please help me resolve this error message.
> Thank You,
>
> Error source: dbopen
> DB Library error: Login incorrect
> SQL Server message: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>

dB is Loading

SQL & Win 2K
I have a dB that I restored and it got hung up at a phase
where it is '(Loading)'. I ran the script below, from a
post of Paul's from a while back, and an error message
was returned that 'Database 'xxx' cannot be opened. It is
in the middle of a restore.;' How can I get rid of this
dB?
alter database [xxx] set read_write with rollback
immediate
go
exec sp_removedbreplication [xxx]
go
use master
go
drop database [xxx]
go
TIA,
Larry...
Larry,
as far as I recall, my other solution was for a database stuck in RO mode.
For your case you could try
restore database xxx with recovery
Alternatively, you could restore the most recent backup with norecovery and
the logs with norecovery, apart from the last one which is with recovery.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I ran the command you suggested and QA returned...
File 'xxx' was only partially restored by a database or
file restore. The entire file must be successfully
restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any othere Ideas?
Larry..
|||Larry,
then it looks like your backup file wasn't fully
restored, so try restoring it again using with recovery.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||As always, thanks for your help.