Processes blocking RESOURCE MONITOR, normal behaviour?

Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor? Is it fatal if some processes are blocking RESOURCE MONITOR? How can one end up in such situation, is it normal or a bug somewhere?

The server is a 64-bit Windows server having SQL Server 2005 SP1.

Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.

The details if the processes as seen from Actívity Monitor (I only have screenshots so I can't copy-paste...):

The main process:

Process id: 69

status: suspended

open transactions: 1

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 578

Wait type: ASYNC_NETWORK_ID

CPU: 1375

Physical IO: 22

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 0

Two "subthreads", there are five similar.

Process id: 69

status: suspended

open transactions: 0

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 35293046

Wait type: CXPACKET

CPU: 4875

Physical IO: 2214

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 1

Process id: 69

status: suspended

open transactions: 0

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 35293031

Wait type: CXPACKET

CPU: 4875

Physical IO: 2210

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 2

The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.

Alright Chap,

You need to look at the "BLOCKED BY" rather than "BLOCKING" column.

The BLOCKING=1 means that this process is blocking another process.

Looking at the info you provided, the SPID 69 is not being blocked by any process.

Hope that helps.

Jag

|||

Hi

First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.

On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.

|||

Jag Sandhu wrote:

Alright Chap,

You need to look at the "BLOCKED BY" rather than "BLOCKING" column.

The BLOCKING=1 means that this process is blocking another process.

Looking at the info you provided, the SPID 69 is not being blocked by any process.

You are right, 69 is not blocking anything. I'm not interested in what 69 is blocking.

The problem is that 69 IS blocking SPID 1. SPID 1 is a system process, whose significance I don't know. During the problem 69 had been blocking 1 for a long time and the SQL server had been quite jammed. I was suspecting that the jamming was because the system process 1 couldn't do anythin being blocked by 69.

|||

Dhericean wrote:

First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.

Thanks for the information, I actually was unaware of the issue. This time I am not using a context connection, so the KB-entry is not valid in my case? I have two SQL Server instances and the CLR stored procedures run on instance A and use a "normal" SQL Server connection (instead of context connection) to connect to the server B.

Dhericean wrote:

On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.

OK, thanks for this information too. I find this CXPACKET issue also very strange since our database is in practise idle most of the time. And when this issue I reported happened the connection 69 had been in CXPACKET state for a while (don't know details, but maybe at least minutes). Shuoldn't the CXPACKET state change to something else after a while? Can this have something to do with the RESOURCE MONITOR process being blocked by process 69?

|||

Hi JM_F,

When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.

The values are 1 or 0 for Yes or NO respectively.

You have to use SP_who2 or DMV - sys.dm_exec_requests

and look for spid 69 in the BLOCKED by column.

regards

Jag

|||

Jag Sandhu wrote:

When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.

The values are 1 or 0 for Yes or NO respectively.

Hello Jag,

If I open Activity Monitor and click help, the following comes:

Blocked By

Process ID (SPID) of a blocking process.

Blocking

Process ID (SPID) of processes that are blocked.

This very clearly states that the value of blocking column contains the process ID. You say it contains 0 or 1. Are you sure of this?

JM

|||

Hi JM,

To confirm, please see the books online topic - Activity Monitor (Process Info Page).

Process ID

SQL Server Process ID.

User

ID of the user who executed the command.

Database

Database currently being used by the process.

Status

Status of the process (for example, running, sleeping, runnable, and background).

Open Transactions

Number of open transactions for the process.

Command

Command currently being executed.

Application

Name of the application program being used by the process.

Wait Time

Current wait time in milliseconds. When the process is not waiting, the wait time is zero.

Wait Type

Indicates the name of the last or current wait type.

Resource

Textual representation of a lock resource.

CPU

Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.

Physical IO

Cumulative disk reads and writes for the process.

Memory Usage

Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.

Login Time

Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.

Last Batch

Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the displayed time is that at which SQL Server startup occurred.

Host

Name of the workstation.

Net Library

Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. .

Net Address

Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.

Blocked By

Process ID (SPID) of a blocking process.

Blocking

Indicates whether this process is blocking others. 1 = yes; 0 = no.

Execution Context

Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.

|||

Jag Sandhu wrote:

To confirm, please see the books online topic - Activity Monitor (Process Info Page).

Blocking

Indicates whether this process is blocking others. 1 = yes; 0 = no.

Interesting... I found the entry you pointed in MSDN (http://msdn2.microsoft.com/en-us/library/ms178520.aspx). However the help page in my local installation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/12f87b09-bf20-4a69-8333-e67419472337.htm) contains the description I sent before. So these descriptions don't match.

Can I assume this a bug in SQL Server local documentation?

I have SQL Server 2005 SP 1 on Windows XP.

regards,

JM

|||MS does a very good job of keeping sql bol on msdn2 current. You should download the latest one from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx|||

Hi JM,

Please get the latest bol.

By the way, BLOCKING thing is even same for SQL 2000. So its always been like that.

regards

Jag

process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
lot of time to run. The store proc is having simple select statement with
multiple case statements.
I checked the sysprocesses table and noticed that the process is blocking
itself.
What could be reason and how to solve this issue'
ThanksSo you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?
Roy Harvey
Beacon Falls, CT
On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>What could be reason and how to solve this issue'
>Thanks|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
> What could be reason and how to solve this issue'
> Thanks|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.
*mike hodgson*
http://sqlnerd.blogspot.com
Omnibuzz wrote:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Process Blocking Problem

We are running SQL Server 7.0 Service Pack #4, running on Windows 2000 Server.
We have a problem with process blocking. At some points the T-SQL statement runs restricting all our users from accessing the DB.
The statement is:

SET FMTONLY ON
exec sp_execute 1
SET FMTONLY OFF

Only when it finishes running, then users are allowed to access the db again.
It is triggered automatically. Does any know what condition on the server is causing this statements to run and what can be done to prevent it. Thanks so much for your help.Look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/sqlquerproc.asp

It explains what sp_execute does.
Do you have a lot of dynamic sql or embedded sql rather than SPs?|||Also look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/adoapp.asp|||What I found out from the Microsoft website that sp_execute is a command that runs a prepared statement using the sp_prepare command.
So what happened is I've watched the SQL for the condition to repeat but it did not. However what I've noticed that our of nowhere at time sp_prepare runs without any parameters, causing the CPU utilization go up to high 90%.
What is causing this sp_prepate to run, as it done automatically.
Thanks for your help.

Process blocking itself since SP4

Hi,

Since installing SP4 we seem to encounter issues where a process seems to block itself. Has anyone seen this issue. Is this a feature?

Thanks,
Dave

Please refer to the following for more details. This may be the reason

The original posting can be found in many places, for example: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/23496/SP4-reports-queries-blocking-on-themselves

There is also a KB article on this: http://support.microsoft.com/default.aspx?scid=KB;EN-US;906344
Thanks

Process blocking itself

Im ahving a problem running a sql server 2000 query, in that the
process is blocked by itself and doesn't finish, even after waiting 20
hours for it.
The table has 40 million records and im running something like :
UPDATE table SET field2 = db.myfunction(field1)
The function isnt doing anthing with tables, it justs does some string
manipulation on the parameter string.
Someone said it might be dur to parallelism (multiple processes
running at the same time causing the process to lock up / block
itself), but even if i try :
UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
it doesnt make any difference.
Hope someone can help, thanks,
PaulHow do you know that the process is "blocked by itself"? Are you seeing
this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
and of itself, is not a cause for concern, it is just a change in the way
sysprocesses reported waits (I think it started working this way in 2000
SP4). Please see the following KB article for more information:
http://support.microsoft.com/kb/906344
"puma75" <paulroskilly@.gmail.com> wrote in message
news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
>|||On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> How do you know that the process is "blocked by itself"? Are you seeing
> this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> and of itself, is not a cause for concern, it is just a change in the way
> sysprocesses reported waits (I think it started working this way in 2000
> SP4). Please see the following KB article for more information:
> http://support.microsoft.com/kb/906344
> "puma75" <paulroski...@.gmail.com> wrote in message
> news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
>
> > Im ahving a problem running a sql server 2000 query, in that the
> > process is blocked by itself and doesn't finish, even after waiting 20
> > hours for it.
> > The table has 40 million records and im running something like :
> > UPDATE table SET field2 = db.myfunction(field1)
> > The function isnt doing anthing with tables, it justs does some string
> > manipulation on the parameter string.
> > Someone said it might be dur to parallelism (multiple processes
> > running at the same time causing the process to lock up / block
> > itself), but even if i try :
> > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> > it doesnt make any difference.
> > Hope someone can help, thanks,
> > Paul- Hide quoted text -
> - Show quoted text -
Thanks.
In Enterprise Manager under Management > Current Activity > Locks/
Process Id it says "spid 73 (blocked by 73)"
It just seems very slow that it hasn't finished after 20 hours and it
didnt seem to be doing anything, little cpu usage, so thinking that it
was deadlocked seemed like a good explanation, must maybe not.|||> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
It's certainly not deadlocking because otherwise SQL Server would have
detected it and terminated one of the deadlock participants.
Perhaps, the UPDATE is not the only SQL statement, and there is still
runaway parallelism going on. I'd check the query plan to be see whether
there is any prallelism.
Linchi
"puma75" wrote:
> On 8 Oct, 19:42, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
> > How do you know that the process is "blocked by itself"? Are you seeing
> > this in sysprocesses, or are you guessing? Usually a spid self-blocking, in
> > and of itself, is not a cause for concern, it is just a change in the way
> > sysprocesses reported waits (I think it started working this way in 2000
> > SP4). Please see the following KB article for more information:
> >
> > http://support.microsoft.com/kb/906344
> >
> > "puma75" <paulroski...@.gmail.com> wrote in message
> >
> > news:1191868345.918936.134610@.57g2000hsv.googlegroups.com...
> >
> >
> >
> > > Im ahving a problem running a sql server 2000 query, in that the
> > > process is blocked by itself and doesn't finish, even after waiting 20
> > > hours for it.
> >
> > > The table has 40 million records and im running something like :
> >
> > > UPDATE table SET field2 = db.myfunction(field1)
> >
> > > The function isnt doing anthing with tables, it justs does some string
> > > manipulation on the parameter string.
> >
> > > Someone said it might be dur to parallelism (multiple processes
> > > running at the same time causing the process to lock up / block
> > > itself), but even if i try :
> >
> > > UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> >
> > > it doesnt make any difference.
> >
> > > Hope someone can help, thanks,
> >
> > > Paul- Hide quoted text -
> >
> > - Show quoted text -
> Thanks.
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
>
>|||On Oct 8, 1:32 pm, puma75 <paulroski...@.gmail.com> wrote:
> Im ahving a problem running a sql server 2000 query, in that the
> process is blocked by itself and doesn't finish, even after waiting 20
> hours for it.
> The table has 40 million records and im running something like :
> UPDATE table SET field2 = db.myfunction(field1)
> The function isnt doing anthing with tables, it justs does some string
> manipulation on the parameter string.
> Someone said it might be dur to parallelism (multiple processes
> running at the same time causing the process to lock up / block
> itself), but even if i try :
> UPDATE table SET field2 = db.myfunction(field1) OPTION (MAXDOP 1)
> it doesnt make any difference.
> Hope someone can help, thanks,
> Paul
a few suggestions:
- check if you have triggers on that table.
- try to do your updates in batches, of, say, 10K rows.|||Aaron Bertrand [SQL Server MVP] wrote:
> > How do you know that the process is "blocked by itself"? Are you
> > seeing this in sysprocesses, or are you guessing? Usually a spid
> > self-blocking, in and of itself, is not a cause for concern, it is
> > just a change in the way sysprocesses reported waits (I think it
> > started working this way in 2000 SP4). Please see the following KB
> > article for more information:
> >
> > http://support.microsoft.com/kb/906344
puma75 wrote:
> In Enterprise Manager under Management > Current Activity > Locks/
> Process Id it says "spid 73 (blocked by 73)"
> It just seems very slow that it hasn't finished after 20 hours and it
> didnt seem to be doing anything, little cpu usage, so thinking that it
> was deadlocked seemed like a good explanation, must maybe not.
In this case, I think it's exactly what Aaron said: you are seing that
the process is waiting for I/O or network latches (belonging to that
process). I bet the query is accessing a lot of data and the HDD led is
light-up almost contiuously.
Try to rephrase the query (to optimize it) or try adding appropriate
indexes (if necessary). Also, try updating the statistics before
running the query.
--
Razvan Socol
SQL Server MVP

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advanced
Hi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:

> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>
|||Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
file[vbcol=seagreen]
|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas

"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
file[vbcol=seagreen]

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advancedHi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:

> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>|||Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
>
file[vbcol=seagreen]|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas
"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
>
file[vbcol=seagreen]

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advancedHi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:
> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>|||Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas
"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >