riga1966,

You may want to run the query using SSIS to limit the batch size. 14 GB sounds like a pretty good sized transaction log. How large is the...

Hi riga1966,

How was the transaction log growing for a SELECT statement? My guess is that you had a concurrent DML or DDL transaction running in the BAP_Prod_2006 database.

If you...

Finishing my premature post :).

How is this query filling the transaction log when it is a SELECT statement? Which database is experiencing the transaction log issues? Is it...

How is this query filling the transaction logs when it is a SELECT statement?

Which database is experiencing the transaction log issues? Is it BAP_PROD_2006 or the database you are...

Paul,

paul.arnott noted earlier that you may want to investigate SQL Server 2005 Developer Edition. I would second that recommendation, it sounds like your problem may have been a functionality...

JKSQL,

Are you attempting to cleanup orphaned child rows, or are you deleting all instances of a given PK (guid) value throughout your schema (from children up to parent)?

If you are...

You could try this:

select ISNULL(s.login_name,s.nt_domain+'\'+s.nt_user_name) as Login_Nm,

DB_NAME(r.database_id) as Database_Nm,

r.status

from sys.dm_exec_requests r join

...

Is the 77% cost attributed to one particular table scan, index scan, or RID lookup?

If you have a test system available you could do the following (after restoring a backup of the prod database):

1. Drop non-clustered indexes.

2. Implement clustered PK indexes on...

I agree with Grasshopper that the query is the most likely cause, but I do have an additional question regarding the query plan generated by the SP.

Is the I/O originating...