• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

SQL blocks status of blocking

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
TRACEYMARY
Asked:
TRACEYMARY
  • 9
  • 8
1 Solution
 
TRACEYMARYAuthor Commented:
I see the runnable status in sp_who2
but how do i see who this is.
0
 
Chris MangusDatabase AdministratorCommented:
traceymary,

You may want to check this information out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=106877&SiteID=1
0
 
TRACEYMARYAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
That be a good idea..........i got the database size now the tables.
Then  i monitor that and see which table grews..

Cheers
0
 
Chris MangusDatabase AdministratorCommented:
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
 
TRACEYMARYAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now