Finding deatail Deadlock info IN SQl Server 2005 Standard Edtion

We are using SQL Server 2005 with SP2.Originally, we seemed to be having a lot of unnecessary lock escalation occurring to the page and table level.

I would like to track down all storedprocedure/queries or function that are being casue of deadlock in sql server 2005 with all deadlock input like transactionID ,command ran,loginID etc.But we have to need to find these information on SQL Server 2005 standard edtion.

Is there any tool,command or something like this which track down these information blindly in sql server 2005 without effecting system performance seriously.

Ahamd Drshen

Hi,

When executing DBCC TRACEON (1204,-1) in a command window, you're enabling the deadlock tracing option. (http://msdn2.microsoft.com/en-us/library/ms188396.aspx) When a deadlock occurs, you can find details in the logs. You can also try using the option 'with nolock' in your sql statements (SELECT * FROM Employees WITH (NOLOCK)).

Regards,

Remco

|||will it track all deadlock resource information SQL Server Errors log ?|||it will show all conflicting locks and its commands.|||

Use SQL Server Profiler with DeadLock Graph Event.

Or use stored procedures sp_create_trace and sp_trace_setevent for collecting profiler events without graphical interface.

@.eventId for DeadLock Graph = 148

Find the table name corresponding to a page

I am trying to find the table name associated with page#.
This is the query executed.
select * from master..sysprocesses where cpu > 2000
and this is the result.
14:1:3068040 [WAITRESOURCE]
[select * from sysindexes where id = 3068040 does not return any data]
TIA
Manoj Kumar
try select db_name(14) for the database
and select object_name(3068040) for the table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Manoj Kumar" <ManojKumar@.discussions.microsoft.com> wrote in message
news:0A0E5BE7-1969-436B-A116-D91E39D9305F@.microsoft.com...
>I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar
|||Manoj, a combination of sp_who2 and sp_lock will give you the locked
resource (or the corresponding current activity screens in EM).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Manoj, you can use DBCC PAGE to show information on page # 3068040.
In the DBCC page output - you will see an object_id that you can tie up to
sysobjects...
"Manoj Kumar" wrote:

> I am trying to find the table name associated with page#.
> This is the query executed.
> select * from master..sysprocesses where cpu > 2000
> and this is the result.
> 14:1:3068040 [WAITRESOURCE]
> [select * from sysindexes where id = 3068040 does not return any data]
> TIA
> --
> Manoj Kumar

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
Thanks
Hi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

Filtering SqlDataSource to show all vs. non-null records

Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (?) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance.

Bill

Construct a better SELECT that uses a parameter.

SELECT ...

FROM ...

WHERE (@.ShowAll=1)

OR (@.ShowAll=0 AND (col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL))

|||Sweet. Thank you much.

Filtering SQL statement with datepart

Hi!

Im making a page where I want to display this weeks information & news as a default setting.

What do I have to do to make it work (Im new to .Net but used to ASP 3 & MySql)?

My idea is to store the week number in a variable which is updated att Page_load and then
to use this variable in the following sql statement that gets news and info from the MsSql
database where weeknumber is = the defined variable.

So how do I store weeknumber in a variable?
AND
How do I use this variable in the SQL statement?

Weeknumber can be store in a Session variable.

There is a function in SqlServer called DateDiff() with many options, you can get the number of week between 2 date. than compare this nubmer with the Session variable.

Hope this help