Linking Sysprocesses To Results Of DBCC Inputbuffer

May 23, 2008

Guys,
I need to send a group of people a list of specific processes running on the server, one of the requirements is to send them what's actually being ran on the machine. I have the information I was on the sysprocess tabke and the results of the DBCC Inputbuffer. Is there a way to link both result sets?

This is the criteria of the processes that neeed to be sent out to my users:

SELECT *
FROM master.dbo.sysprocesses p
WHERE last_batch < DATEADD(mi, -5, GETDATE())
AND dbo.fncGetNumLocks(p.spid, DB_ID('EngDataMart')) > 1
GROUP BY p.spid, p.loginame, p.hostname, dbo.fncGetNumLocks(p.spid, db_id('DBName'))
ORDER BY p.spid

Thanks,

View 1 Replies


ADVERTISEMENT

DBCC INPUTBUFFER Results Into Table

May 10, 2007

Does anyone know if there is a simple way to get the results of a DBCCINPUTBUFFER() request into a table? I have a process for monitoringactivity that will give me the results of sp_who2 into a temp table,and want to scroll through the active connections and get the inputbuffers into another table for review:Insert into #TmpWhoexec sp_who2 'active'Something like that with the dbcc command.I am using SQL 2000 SP4.Thanks,Tom

View 3 Replies View Related

DBCC INPUTBUFFER

Jun 13, 2002

if the inputbuffer contains more than 256 chars, it only reports out the first 256 characters of the buffer. that's because EventInfo is defined as a nvarchar(256) field.

is there any way to get the full inputbuffer...even if it's more than 256 chars? i'm trying to run this:

exec mn @parameter_number_1 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_2 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_3 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_4 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_5 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_6 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_7 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_8 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_9 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_10 = 'abcdefghijklmnopqrstuvwxyz'

and all i get is this:

EventType Parameters EventInfo
--------- ---------- -----------------------------
Language Event 0 exec mn @parameter_number_1 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_2 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_3 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_4 = 'abcdefghijklmnopqrstuvwxyz',
@parameter_number_5 = 'abcdefghijkl

is there any way to get the entire input buffer?

Thanks in advance,
mike

View 2 Replies View Related

Dbcc Inputbuffer

Oct 15, 2004

Hi folks.
DBCC INPUTBUFFER(79)
the statement returned is too short, not full. Can i get full STATEMENT that is sent by the user.

Howdy!

View 5 Replies View Related

DBCC Inputbuffer

Dec 24, 2007

Does anyone know if there is an equivalent way to get to the information that DBCC Inputbuffer returns using 2005s dynamic management objects? sys.dm_exec_requests only returns information that is currently executing...as DBCC inptubuffer returns the last statement executed for a session.

Thanks,
Tim

View 8 Replies View Related

Dbcc Inputbuffer Lock

Jan 14, 2000

We are running a Windows NT Terminal server 4.0 with SQL 7. The application we are using has two main connections to the database that may at times update the same table simultaneously. On two seperate occasions we have run into a lock that causes these processes to hang. They appear to still be running in the Task Manager, but have no CPU time. When we review the current activity on the SQL server under Object/locks one of the items listed is "tempdb.dbo.##lockinfo13(the spid)". The last TSQL command batch is "dbcc inputbuffer(13). We have tried to research what this dbcc inputbuffer does, and why there is a conflic here... but have come up empty. Can you help with any information on this dbcc?

View 2 Replies View Related

Store The DBCC InputBuffer Result

Jul 5, 2001

Hello,

I must get the content of dbcc inputbuffer (nn).
The structure of the result is well described in the BOL.

However, I don't find a way to store the result in temporary table or other stuff.
Thanks in advance.
Regards
Patrick

View 2 Replies View Related

Alternative To DBCC INPUTBUFFER/sys.dm_exec_sql_text?

Feb 19, 2008

Thanks for the help everyone.

Updated versions of the proc and view can be found here for now: http://www.sqlservercentral.com/Forums/Topic457115-146-1.aspx

View 10 Replies View Related

DBCC PAGE Results

Oct 4, 2005

Hello Ryan.

I run DBCC PAGE (dbname, 1, 136, 3) with trace 3604 on:

Server: Msg 8968, Level 16, State 1, Line 1
Table error: DBCC PAGE page (1:136) (object ID 0, index ID 0) is out of the range of this database.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

View 10 Replies View Related

Results After Dbcc Showcontig

Nov 15, 2007

Dear Experts,
just now i've read a great article from sql server performance.com
and understand several key factors related to performance.

still i have one doubt that what will be the best ratio between
extents scanned and extent switches.

here i'm pasting some of my table result. please suggest me how is the performance of the table

table 1

- Pages Scanned................................: 85618
- Extents Scanned..............................: 11283
- Extent Switches..............................: 85405
- Avg. Pages per Extent........................: 7.6
- Scan Density [Best Count:Actual Count].......: 12.53% [10703:85406]
- Logical Scan Fragmentation ..................: 49.93%
- Extent Scan Fragmentation ...................: 46.31%
- Avg. Bytes Free per Page.....................: 2707.1
- Avg. Page Density (full).....................: 66.55%


table2

- Pages Scanned................................: 60648
- Extents Scanned..............................: 7650
- Extent Switches..............................: 60639
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [7581:60640]
- Logical Scan Fragmentation ..................: 50.93%
- Extent Scan Fragmentation ...................: 64.01%
- Avg. Bytes Free per Page.....................: 2672.5
- Avg. Page Density (full).....................: 66.98%

thankyou very much


Vinod
Even you learn 1%, Learn it with 100% confidence.

View 3 Replies View Related

URGENT! - DBCC Keeps Running Without Any Results?

May 30, 2001

One of our databases seems to be looking dodgy as some scheduled jobs are failing, but DBCC CHECKDB is no use since it has been running for over 1/2 hour without giving me any results, just the spinning globe.

How do I find out what is wrong without resorting to backups?

View 3 Replies View Related

DBCC DBREINDEX - Unexpected Results

Jul 28, 2004

I have lost the reference but I read somewhere that when running DBCC DBREINDEX against a clustered index, all the secondary indexes on the table are automatically re-indexed as well. I did a test of this on a small table and it seemed to confirm this. However, now I've put this into practice, I am finding that it doesn't seem to work this way. I noticed that having run DBCC DBREINDEX against a table's clustered index (DBCC DBREINDEX ('tablename', 'clusteredIndexName', fill_factor)), the secondary indexes were not automatically re-indexed - as born out by the fact that they remained badly fragmented.

First of all, do the dba's who read this beleive it is correct that DBCC DBREINDEX run against a clustered index will automatically rebuild the secondary indexes too? If so, why wouldn't it work in all cases?

Clive

View 12 Replies View Related

DBCC DBREINDEX Unexpected Results

Aug 9, 2004

Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?

Clive

View 1 Replies View Related

Dbcc Showcontig Results...analyze Please

May 21, 2008

Dear All,
i've used the DBCC showcontig command against my table table103
but i dont know how to analyze the results of fragmentation levels. please give me some explanations or some good links.....

the results are:
DBCC SHOWCONTIG scanning 'TABLE103' table...
Table: 'TABLE103' (1899257921); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 13
- Extent Switches..............................: 18
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 15.79% [3:19]
- Logical Scan Fragmentation ..................: 90.00%
- Extent Scan Fragmentation ...................: 92.31%
- Avg. Bytes Free per Page.....................: 3281.4
- Avg. Page Density (full).....................: 59.46%

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 10 Replies View Related

How To Capture Results Of A DBCC SHRINKDATABASE?

Feb 28, 2008

So, basically I'm trying to do an insert into exec(dbcc shrinkdatabase)


Code Snippet

DECLARE

@SQL VARCHAR(1024)

,@DBName VARCHAR(512)

SET @DBName= 'admin'

IF OBJECT_ID('tempdb.dbo.#ShrinkDB') IS NOT NULL

DROP TABLE #ShrinkDB

CREATE TABLE #ShrinkDB

(

DbId INT

,FileID INT

,CurrentSize BIGINT

,MinimumSize BIGINT

,UsedPages BIGINT

,EstimatedPages BIGINT

)

SET @SQL=

'

INSERT INTO #ShrinkDB

(DbId,FileId,CurrentSize,MinimumSize,Usedpages,EstimatedPages)

EXEC(''DBCC SHRINKDATABASE(' + @DBName + ')'')

'

EXEC(@SQL)

SELECT * FROM #ShrinkDB


and receive the following:

Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.

I've tried adding a begin tran and commit tran around it, doesn't help ...

Is there any way around this? Is there any other way to capture the output of a shrink database from a procedure perspective?

Thanks

View 4 Replies View Related

SQL Server 2012 :: How To Determine Metadata On DBCC Results

May 26, 2015

I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.

I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.

I tried

SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')

but I got back

Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.

View 1 Replies View Related

Fixing My Table Based On Dbcc Showcontig Results

Jul 20, 2005

Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.

View 2 Replies View Related

How Could Load Results Of [DBCC Showcontig (@TableName) With TableResults] To A Table

Feb 19, 2008

Hi:

how could I load "DBCC showcontig (@TableName) with tableResults" to a table?
I created a table and let insert to it via above "DBCC...."
but problem is the results with "DBCC execution completed. If DBCC printed error messages, contact your system administrator.", or how could I mute this output line for each table ?
thanks
David
--=========================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -------------------- -------------------- ----------------- ----------------- ---------------------- -------------------- -------------------- -------------------- ---------------------- ---------------------- ---------------------- -------------------- -------------------- ---------------------- ----------------------
sysproxylogin 37575172 clust 1 0 0 0 0 0 0 0 0 0 0 0 100 0 0 0 0

View 1 Replies View Related

Greater Inputbuffer?

Aug 9, 2001

I'm trying to sniff or view a fairly large query that get's passed to my sql server.

The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.

Is there another method or a way to increase the size of this so I can see the whole query?

Thanks

- jeff

View 3 Replies View Related

Sysprocesses

Jul 20, 2005

Hi everyone,I'm a bit new til ms sql server and hope that anyone here can answer aquestion I have. I'm running a ms sql server 7.0. The server isaccessed by a application written in Access (help..) and sometimes Iexperience that the server performance is slowing down. When I do asp_who I find alot of the processes (or sids) with the statussleeping? Can anyone please tell med what this means, and is it ok forme to kill these processes? Since this is happening quite often Iwould like to make a script that automatically kills all sleepingprocesses, is this ok?Looking forward to hearing from anyone!B'regSql server newbee

View 2 Replies View Related

Script: Get Inputbuffer For Spid's Holding Locks

Jul 20, 2002

Here's a quick script that will return the inputbuffer of processes that are holding more than a certain threshold of locks (by default, 10, but it's easy to modify). Handy for tracking down what query is holding those 1400 locks.

Yes, it uses a cursor. I'd welcome amendment by anyone who has the insight on converting it to pure set-based.

Cheers
-b

CREATE PROCEDURE sp_lock4 AS
set nocount on

DECLARE @tSpids table(spid int PRIMARY KEY CLUSTERED,count int)
DECLARE @iSpid int,@iCount int

insert into @tSpids (spid,count)
select convert (smallint, req_spid) As spid,
count(*) as count

from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u

where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
group by converT(smallint,req_spid),'dbcc inputbuffer(' + cast(req_spid as varchar(4)) + ')'
having count(*)>10
order by count(*) desc

DECLARE cLoop cursor for
select spid,count from @tSpids

OPEN cLoop

FETCH NEXT FROM cLoop INTO @iSpid,@iCount
WHILE @@FETCH_STATUS=0
BEGIN
select 'spid ' + cast(@iSpid as varchar(4)) + ' has ' + cast(@iCount as varchar(5)) + ' locks.'
exec ("dbcc inputbuffer (" + @ispid + ")")
FETCH NEXT FROM cLoop INTO @iSpid,@iCount
END

CLOSE cLoop
DEALLOCATE cLoop

return (0) -- sp_lock
GO

View 1 Replies View Related

Sysprocesses.waittype

May 24, 2004

Hi,

The column waittype of master..sysprocesses table contains binary numbers.
I am looking for their codes. I know that Microsoft has published these codes for SQL Server 7 (here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;244455 ), but I need the codes of SQL Server 2000.

And yes, I've checked spt_values - they are not there.


Any help will be appreciated,
Thanks,
kukuk.

View 4 Replies View Related

How To See All Sysprocesses In SQL 2005?

Sep 11, 2007



My application was written in SQL 2000 where a non-dbo account could see all logged in users. Is there some way I can do this in SQL 2005?
Here is the query I run in SQL 2000:
SELECT spid
FROM master.dbo.sysprocesses, master.dbo.syslogins
WHERE master.dbo.sysprocesses.sid = master.dbo.syslogins.sid
AND master.dbo.sysprocesses.dbid = db_id()
AND LOWER(master.dbo.syslogins.name) IN ('grnl_security','log_security')


Right now, my application (where many users log in with gnrl_security) can only see thier own SPID; but I need to see all logged on SPIDS.

I have tried adding a SELECT permission to the new sysprocesses view for user grnl_security.

View 5 Replies View Related

Incorrect Hostname In Sysprocesses

Jun 27, 2000

On SQL Server 6.5, we tried to track down the owner of a process,which was hogging the CPU. Using Currently Activity Detail, we went to the hostname that seemed to be the culprit, but the users had not accessed SQL Server in a few days. Where is SQL Server get the hostname from? We are on an NT4.0 network, using DHCP.

View 1 Replies View Related

Sysprocesses Says: Wait Forever

Sep 30, 2004

I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.

Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.

I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.

I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.

So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...

So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.

What should I do?

View 6 Replies View Related

Joining Sysprocesses With Fn_get_sql

May 16, 2006

ok, I have a requirement where I need to get a list of sql commands currently being blocked.
This is very easy to do via stored procedure, and I have it working well using a vb.net console app to fire it off.

Trouble is, I need to ship it to different offices on an ad hoc basis. I don't want to install a stored procedure on each site because it'll be a one off job and there is not likely to be anyone available who would know how to even install a new sp. So, I thought I'd try and pull back the sql commands via a select statement, joining together sysprocesses and the fn_get_sql udf. This returns a table, so I presumed I could join the two together using a subquery via the sql_handle with something like this :

SELECT sql_handle,
(
SELECT top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
) as sqlcommand

FROM master..sysprocesses

The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.

Anyone any ideas? I'm using SQL Server 2000 sp3.

View 6 Replies View Related

SysProcesses Login Time

Apr 1, 2008

Hi,

We have written various applications that have a connection table for managing users logins, we have a connect stored proc that inserts the SIPD and the login time from sysprocesses.

From time to time we clean up our connections table by queryng against the SPID and login_time in sysprocesses and clear it if these don't match. This has been fine for a few years, howver we have found recently with a client that with some SPID's in sysprocesses that SQL server is changing the login time, this is unexpected behaviour.

The client is using SQL server 2005 and this has caused us problems, we have worked around it for now but wondered if anyone new why on this particular setup why the login time would change even though they have not disconnected.

We have other clients using SQL 2005 but have never seen this issue before, I wondered if there was a setting we have not seen that can cause this.

Thanks

View 4 Replies View Related

Sysprocesses Memusage Sleeping

Nov 3, 2005

I was trying to find the bottle neck on our server while we wererunning a couple very large updates.I ran the following query and noticed there were a couple sleepingspid's that were really eating up the memory. One had a value of16000.I guess my question is was this query still holding on to that memoryeven though the query had finished? Why didn't SQL Server clean it up?select spid,substring(Loginame,1,10) Login,memusage,substring(hostname,1,15) Host,program_name,cmd,statusfrom master..sysprocessesorder by memusage desc

View 1 Replies View Related

What Happened To Sql_handle In Sysprocesses?

Sep 13, 2007

In SQL Server 2000 you can monitor active processes by looking at the sql_handle column in sysprocesses. In SQL Server 2005 BOL says sql_handle ...


Represents the currently executing batch or object.


In SQL 2000 a non-zero value in sql_handle indicated an active process. You could not simply rely on the status column to check for an actively running process. This is not the case in 2005. I ran a profiler trace to confirm that just because sql_handle reports a non-zero value does not mean the corresponding process is active. Does anyone know why this has changed and how we are now to interpret sql_handle in 2005? Why are non-zero entries reported for processes that are not currently running?


Thanks, Dave

View 1 Replies View Related

SQL SERVER 2000 - Table SYSPROCESSES

Mar 8, 2004

Hello friends,

Despiste what it is mentioned in B.O.L., in the Table SYSPROCESSES, the column KPID has almost always the value 0 !!!

If anyone knows how I can find the right link between a SPID and the corresponding KPID, I shoud be very happy to know to.

It concerns SQL SERVER 2000 under WINDOWS 2000 SP3.

Thanks in advance for any help about that ...

View 1 Replies View Related

Finding Active Users From Sysprocesses

Jul 20, 2005

I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1

View 2 Replies View Related

NET_ADDRESS In The Master.dbo.sysprocesses Table

Oct 24, 2006

Does anyone knows how the field NET_ADDRESS in the master.dbo.sysprocesses table is encoded ?
May I extract the IP address or MAC address from that field ?

View 9 Replies View Related

DBCC Execution Completed. If DBCC Printed Error Messages, Contact Your System Adminis

Mar 26, 2007

Hi All,

I am playing with DBCC command to check the contsrainst on a perticular table (DBCC CHECKCONSTRAINTS ('myTable') WITH ALL_CONSTRAINTS), it always gives the following result:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

nothing more than that, anyone can help please?

Cheers,
Riaz

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved