?
Solved

SQL blocks status of blocking

Posted on 2006-05-10
17
Medium Priority
?
657 Views
Last Modified: 2012-05-05
i have a lot of status with blocking
when i do dbcc inputbuffer all i get is
RPC Event      0      sp_prepexec;1

any advice
0
Comment
Question by:TRACEYMARY
  • 9
  • 8
17 Comments
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16651270
I see the runnable status in sp_who2
but how do i see who this is.
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 16651307
traceymary,

You may want to check this information out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106877&SiteID=1
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16651382
how do i see the sql code
 i see the command as SQL
 but nothing else...i want to see what the process is doing.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16651460
You would need to set up a trace in SQL Profiler to be able to see what sp_prepexec is processing in that instance.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16651465
I just killed off the one that had blocked and it fine.
but i like to know how i can find the command it was doing?
all i had was select
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16652222
I would set up a trace in SQL Profiler and then fire off the process / processes / application that's causing the blocking.  Then, analyze the trace output to determine what is going on.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16653322
i had the trace on for deadlocks i saw deadlock but still i could not determine what it was trying to do ..i.e database table ?
i have to do research how to figure out...i like to know what im killing before i do it.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16653390
You did the right thing by using DBCC INPUTBUFFER first.  You can also check DBCC OUTPUTBUFFER which may give more clue.

When analyzing your trace you may want to save the trace data to a SQL Server table and then query it for the SPID that you see that is locking the table.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16653685
im looking around now...i used the profiler..............i did not add all columns so then it was too late...but i will next time.

I also saw that the dbcc inputbuffer if it returns RPC then use profiler..........

Tough finding the exact problem...............suppose its too late now to go back now the lock gone ...no history anywhere?

Gets a bit hairy when every one shouting and can't get access...........grins.

   
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16653787
Locks can be pretty hard to nail down sometimes but when you do find the source of this one you'll have several new tools in your tool belt that you can use in the future.  

Good luck with it...
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16658706
I just looked at my growth data and the data size grew 3 gigs yesterday from the previous night...that must have been what the server was doing...?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16659664
Is that normal for your database?  Depending on where that 3 gigs of data was going it could very well have been what was keeping your server busy.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16661224
No....i been measuring the results every night......
and it was
data size   data used  data free space  data free prt date
28500      26832        1668                    5.85             5/8/2005
28500      26953        1547                    5.43             5/9/2005

then suddenly
31350      27314        4036                  12.87              5/10/2005
31350      27822        3528                  11.25              5/11/2005

thats a big jump 1 and 1/2 gigs.....in a day......and its still at 11.25%
i was expected 100 mg growth a day according to my log but a whooping 1 1/2 gig is holly molly...
whats going on.........

Im not sure what i can be checking....
Thanks for responding i know this is closed..


0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16661361
I have a simple script that I run that tells me how many rows of data I have in a table and the size of the table in megabytes.  If you'd like to use it to see which table is growing I will post the script here.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16661491
That be a good idea..........i got the database size now the tables.
Then  i monitor that and see which table grews..

Cheers
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16662127
Here is my script.  It's ugly and fairly non-commented but it works well.  You can choose to run sp_updatestats before you run the script to get the most accurate results.

-- sp_updatestats

Declare @include_system_tables bit, @myTableName varchar(20), @SQL nvarchar(500)
Set @include_system_tables = 0

SELECT Table_Name
, (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.Table_Name)) AS Row_Count
, Total_Space_Used_MB
Into #T1
FROM (SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name
, CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024)/1024)) AS Total_Space_Used_MB
FROM sysindexes i (NOLOCK)
INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND ((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255) --AND Total_Space_Used_MB = 0
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))) as a
--ORDER BY Total_Space_Used_MB DESC
ORDER BY Row_Count DESC

-- Get the column counts
Select myInner.Table_Name, Count(sc.id) As Number_Columns
Into #T2
From dbo.syscolumns sc
Inner Join
(SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name, i.id
FROM sysindexes i (NOLOCK)
INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((o.type IN ('U', 'S')) OR o.type = 'U')
AND ((OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)) myInner On sc.id = myInner.id
Group By myInner.Table_Name

Select #T1.*, #T2.Number_Columns
From #T1
Inner Join #T2 On #T1.Table_Name = #T2.Table_Name

Drop Table #T1
Drop Table #T2



0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16662220
Smiles.....I like ugly code...lol...

I get it a run set it up..........run it then do a restore from the previous night and see where the "table " grew...

Thats a work around to see the problem..

Thanks i think i have enough to get on with.....be a few days on the restore...

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question