Using the Blocked Process Report in SQL Server 2005/2008 (SQLServerCentral.com 10/30/2008)

[SQLServerCentral.com]

A community of more than 922,000 database professionals and growing In this issue Featured Articles Using the Blocked Process Report in SQL Server 2005/2008 SSIS Configuration Files - SQL School Video Database Recovery Models in SQL Server Constraint Yourself! Editorial The October 2008 Energy Update Featured Script The Ultimate Table Searcher advertisement Free ebook on the 'Best of Exchange Server 2007' published by Sybex A book for Exchange SysAdmins who, amongst other things, make sure your email works! 350 pages on getting the most from Exchange Server. Download a free copy. New SQL Response makes it easy for Mike. "I now have one place to look to see if my production servers are healthy and what routine maintenance is required. Keep up the good work, Red Gate!" Mike Lile, DBA, K2B, Inc., Release Candidate tester. Experience no-nonsense monitoring. Download your free trial now. Featured Articles Using the Blocked Process Report in SQL Server 2005/2008 Jonathan Kehayias from SQLServerCentral.com When excessive blocking occurs in SQL, performance degrades. Learn how to use the Blocked Process Report to rapidly resolve excessive blocking scenarios in SQL Server 2005/2008. More » SSIS Configuration Files - SQL School Video Brian Knight from JumpStartTV This edition of SQL Server examines the various options available in Integration Services configuration files. More » Database Recovery Models in SQL Server Additional Articles from SQL Server Performance This article discusses in detail the three database recovery models and how to choose the appropriate recovery model More » Constraint Yourself! Additional Articles from SimpleTalk In his first article for Simple-Talk, Joe Celko demystifies the use of Constraints, and points out that they are an intrinsic part of SQL and are a great way of ensuring that a business rule is done one way, one place, one time. More » Editorial - The October 2008 Energy Update The election in the US is next week and I'm not sure who to vote for. I really think we need to treat the energy situation in this country as a crisis and attack it the way we attacked the space program in the 60s: set a goal for the next decade and drive everyone towards it. We could substantially reduce our dependence on oil if we really attacked the problem. I mean all oil, not just foreign oil, but our overall use of fossil fuels. Actually I think every country should do this, but especially the larger companies like the US and China that use a tremendous amount of energy and can afford to research all types of alternatives to fossil fuels. One of the things I really would like to see more of in the US is wind power. T Boone Pickens, show in the video linked here, has some interesting ideas. I don't think he's always right, and don't really admire his story, but I think he sees this as a problem in the US and he's trying to do something. And I don't believe he's just looking to earn more money; I think he believes in the project. There are a lot of people that aren't thrilled with wind power and I can understand issues. Over the last month we've had less wind here at the ranch, which means if we had a wind generator, we'd be getting less power. Since we aren't using less power, we would definitely need another source during those calm days. There's a solution using compressed air, though it's more for large scale wind plants. I'm skeptical of how well underground caverns might store compressed air, but it's probably worth some pilots to get a better idea of the efficiencies and feasibility of this solution. I also saw this new turbine that has a ring around it, which should reduce the noise from the blades. It's not cheap ($10k installed), but it doesn't require some of the clearances of other turbines. This is the type of thing that we ought to start requiring on more buildings. At least I think it's worth the aesthetic compromise. GE is also testing new materials and shapes for turbines. This is the type of research that governments should foster and support, and then license the technology to companies to build products with. Google is spending money on a variety of projects, from floating data centers to a car sharing program to a plan for generating much of the power in the US from renewable sources. I'm not sure I completely agree with the details they have, but more people should have their passion to reduce the power they use. How green can we be? I have no idea, though I have faith in humans to get it done if we need to. I just wish more people would believe that we need to. Kalen Delaney, noted SQL Server expert and author, talks a bit about the challenges of going green. She brings up some great points about simple things that could help. To me, we have to walk a fine line between enforcing standards and allowing innovation, something that isn't done too well by many companies. Steve Jones from SQLServerCentral.com » Join the debate, and respond to today's editorial on the forums The Voice of the DBA Podcasts The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes! Windows Media Podcast - 48.2MB WMV iPod Video Podcast - 36.5MB MP4 MP3 Audio Podcast - 7.4MB Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com. I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it. » To submit an article, rant or editorial, log in to the Contribution Center Question of the Day Today's Question: In SQL Server 2008, if you are running Profiler in a database set to 80 compatability mode, are are the security requirements for showplan data determined? By Steve Jones Think you know the answer? Click here, and find out if you are right. This question is worth 2 points in this category: Profiler. We keep track of your score to give you bragging rights against your peers. We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center. Inside Microsoft SQL Server 2005: T-SQL Querying is the highly rated book by Itzik Ben-Gan, Lubor Kollar, and Dejan Sarka that brings all you need to know about T-SQL. Grab your copy today from Amazon! Yesterday's Question of the Day In a SQL Server 2008 Thesaurus file, if you have overlapping terms, for example "SQL" and "SQL Server", which term is applied as a rule? By Steve Jones Answer: SQL Server Explanation: When there are overlapping terms, the longest rule is applied, in terms of physical length. So SQL Server would be applied in this case. Ref: SQL Server 2008 Full-Text Search: Internals and Enhancements - http://msdn.microsoft.com/en-us/library/cc721269.aspx#_Toc202506231 » Discuss this question and answer on the forums Featured Script The Ultimate Table Searcher Jesse Roberge from SQLServerCentral.com Searches for tables who's names contain the search string. Gives Size and Rows information as well. More » Database Pros Who Need Your Help! Here's a few of the new posts today on the forums. To see more, visit the forums. SQL Server 2005 : Administering Transaction Replication Error - I have suddenly started getting this errors which is stopping from data being pushed to Subscriber. Is there any way troubleshoot... How to check how much database spased used on the server and do I need to increase size on my server? - How to check how much database spased used on the server and how do I know if I need to... Linked Server - I am having error on my linked server. from x to y. OLE DB provider "SQLNCLI" for linked server "x" returned... configure the new box for reporting services? - Hi All, We have 3 production servers, which need reporting services.We have decided to go for a separate 4th server to... T-SQL Code Optimizers - We recently tried optimizing a complex view with a third party tool. The results were terrific, with a 96% reduction... Clustering - Dears, i have 6 databases and 3 servers. i need and advice on how to cluster them i want to divide the... SQL Server Authentication Modes - Dear Forum Members, Our application is desinged on [b].Net Framework, which uses SQL Server 2000[/b]. During installation our application creates the... SQL DB Performance - Hi All, In our Production Server one SQL Database is having 2 GB of size running slow and its is a... SQL Performance - Hi, Over the last 2-3 weeks i have been battling with performance on one of my servers. I looked at a... sql auditing triggers - I have to write DDL trigger in sql 2005, using CLR feature. Can anyone provides help in this regard Problem DB Mirroring - Hi to all, i'm a junior dba and i have a problem with implementing a database mirroring. In a unique server with... I/O Bound - What's the best way to find if a 64-Bit Windows 2003 based Server is suffering from I/O issues. I will really... How to Create Linked Server for a MySQL database using SQL Server Management Studio - It took me about a day to figure this out, so I thought I'd try to save someone else the... SQL Server 2005 : Backups backup with different credentials - Is it possible for me to have SQL Services logged in as a local admin account on Server A (SQL... When can a CHECKPOINT occur during a BU? - We have a VLDB that takes over 15 hours to perform a full BU. An intense processing job kicked off... Regarding TempDb - Hi, my tempdb is not getting free after server restart also.Here are the details. Before restart Tot Size unallocated tempdb 15000.00 MB 5499.19 MB After restart tempdb 15000.00... Regarding Backup - Hi, We have database of size 80 Gb,we take full backup of the database daily,its finishing within 3:30 Hrs daily.But particulary... BackUp - I am new in sql server so I have a problem with backup, i like to know the type of... Red Gate SQL Backup vs. Quest Lite Speed backup - As our SQL server environment continues to grow, we are starting to look at 3rd party backup solutions. The two... Unable to start execution of step 1 - I'm trying to create a backup plan and when I run it, I get the error: "Unable to start execution of... SQL Server 2005 : Business Intelligence SSIS Package Execution on 64 bit system. - I recently aquired a 64 bit machine and installed 64 bit developer edition of SQL Server. Upon executing SSIS packages, i... Reporting Services: Sparklines and Bullet Graphs? - Hello All, For a new reporting project compact "Tufte-like" and very clean and simple data visualizations as sparklines and bullet graphs... SQL Server 2005 : CLR Integration and Programming. Visual Basic code to write the last time a Table was updated in a DB - I want to show the last Time and Date that a Table in a Database was updated. I'm writing this... SQL Server 2005 : Data Corruption dbcc allow data lose problem with increasing log - Hi Everyone I do have server problem when i make a dbcc checkdb allow data lose script. the log of the... Need help! Cannot re attach suspect database - I had a database marked as suspect Heres the logs on why it became suspect: 2008-03-13 02:53:09.42 spid16 Starting up database 'mydatabase'. 2008-03-13 02:53:16.87... Failed SP2 install causing SQL service to fail... - Hey guys, Setting up some new machines and after installing 2005 Standard I moved all the system databases as per http://msdn2.microsoft.com/en-us/library/ms345408.aspx I... SQL Server 2005 : Development Selecting records on condition that others exist. - I am attempting to produce a claims ratio for a book of insurance. In general I can do this. What I... Updating Value of a particular XML node in a col where data type is xml - We have a table 'A' and its has a col 'A' with data type xml. The contents are in xml form... Slow access of data via a linked server connection - My manager has designed a new architecture for our development and user test environments that I don't think will work.... Regarding a while loop - Hi please help me i was trying to update using while loop Here is my code .... declare @y char(3) declare... execute SQL server query from Dos command prompt/ batch file. - Hi All, I need to wirte a batch file, which connects to SQL server, executes a simple Query and output the... SQL Server 2005 : Working with Oracle Oracle view and query question - i am writing a view to pull data from a table field1 field 2 field3 field4 Data for the above fields... SQL Server 2005 : SQL Server 2005 Compact Edition How to Execute SQL Stored Procedures using PHP - Hello, I have a stored procedure that I want to run from a web page using PHP, I have used odbc_prepare,... SQL Server 2005 : SQL Server 2005 General Discussion query with dates - I'm writing a query that is allowing the user from my web page to do a search with selecting parameters... Select Statement: Generate results from last 6 months - Hi All, I know I am close but still not quite there...:crazy: I want to generate some rows, specifically the last 6... SELECT INTO TWO TABLES ? - Hi, I have a little question. I have imported data from a csv file. Here are 4 values. These are imported... Exporting Tables in MSSQL 2005 - I am a bit new to MSSQL 2005 and I am trying to Export a table using the SSIS Import... Receiving and collating data in e-mails - Hi all. This one is an interesting one, and I'm fairly confident it can be done without too much trouble actually,... using 32-bit ODBC driver on 64-bit system - Hi would appreciate some advice if possible please. We have SQL2005 64-bit running on Win2008 64-bit. We have had to install a 32-bit... NET Runtime Optimization Service Failed after SQL Server 2005 SP2 is Installed - Can any one shed some light how how to correct this? Event Type: Error Event Source: .NET Runtime Optimization Service... Need work around to lower initial size and shrink database - One of my customers's admins set the initial size of their database to several gigabytes. That was fine for their... SQL Server 2005 : SQL Server 2005 Security Deny viewing of System Metadata and tables to users connecting via ODBC - Good afternoon, I have the following issue: I created an ODBC connection to a Server on our network. The ODBC connection... SQL Server 2005 : SS2K5 Replication gaps in transactional replication - Hi, We are replicating from SQL2005 to SQL2005. Distributor is SQL2005 too, separate box. There are no errors in replication. We remove... Query Timeout Expired when attempting to apply snapshot - Hi all, I had an issue whereby i was attempting to apply a snapshot from the file system of server A... Columns not propogating - I have a transactional replication set between two servers, but not all the rows appear to be properly synched. In... SQL Server 2005 : SQL Server 2005 Performance Tuning SPROC with variables runs like a dog compared to same SPROC with constants - why?! - Hello all! I have written this code that runs like a dog and I can't find out why!! [quote]ALTER PROCEDURE [dbo].[getDiaryEntries] ( @StartDate... Change Where clause causes HUGE performance problem - HI All, I have a query that when I just it from WHERE x like '%abc%' to WHERE x like '%xyz%' The query... SQL Server 2005 : SQL Server 2005 Integration Services Conditional flow in my Data Flow? - Is it possible to have a conditional flow in my Data Flow? If there was an Error Output I want to... help in modifying script - I have following ActiveX script to delete files older than one day from folder C:\Test _____________________________________________________________ Dim fso,fol,f set fso = CreateObject("Scripting.FileSystemObject") Set fol = fso.GetFolder("C:\Test\") For... Exports to Excel - I am having trouble with Excel exports. I want to be able to run the same export more than once... Export / Import table data - Last night we had to rebuild a small database because some tables where causing problems. There were two tables that... SSIS: trouble parameterising SQL - hi i have a large bit of sql and i thought "i shall parameterise this" created my variable and stuck my... Deleting files using foreach loop and File System Task - Hi, I've created a foreach loop container, in the 'Collection' tab I specified the enumerator, the folder from which to... SSIS Package Protection Level - Hi, I am in the process to promote in production a bunch of packages, after migrating form sql2000 to sql2005. I... Db2 Connection Problems SSIS (OLEDB) - I'm using the IBM DB2 connect client software (Version 8.2) and SQL Server 2005. In the SSIS package I have... SQL Server 2005 : T-SQL (SS2K5) SMALL HELP EGD ORDER BY - I HAVE A QUERY THIS WAY .. select convert(varchar(12),datepart(m,date)) + '/' + convert(varchar(4),datepart(yyyy,date)) as date, count(*) as total from TBL1 where status = 'OUT' group by convert(varchar(12),datepart(m,date)) + '/' + convert(varchar(4),datepart(yyyy,date)) order by... recreating table with columns that have spaces in the names - I have a customer on either SQL 2000 or SQL 7 that's running old software I'm trying to review for... Collation Error - How do i resolve collation errors. Msg 468, Level 16, State 9, Line 23 Cannot resolve the collation conflict between "Latin1_General_CI_AI" and... text data extraction with a sql query - Hi, i use a sql 2005 database where we stored phone call historic notes. The detail of the phone call is... NEED HELP with QUERY TO GENERATE REPORTS. - I need to produce small reports as per Users request. Here is my table structure[code]CREATE TABLE [dbo].[PROV]( [PROV_KEY] [int] IDENTITY(1,1) NOT NULL, [VEND_ID]... Re: WHERE clause evaulation and resulting execution plans - Re: WHERE clause evaulation and resulting execution plans When I execute the following queries with @brand_id = NULL, the second query has... Need help on performance and integrity - Hi Everyone, I have sample code below Item#1 which will create the sample data for testing and Item #2 which is... SQL-Server 2005: Change Ordinal Column Position - Hello, does anyone know, if it is possible to change the ordinal position of a column? I tried the following: update syscolumns set... SQL Server 2005 : SQL Server Newbies Ports on a named instance. - Hi Folks, I had a strange issue the other day where the port on a named instance had changed on me. I... Stored procedures and Case or if statements - I am creating a stored procedure that will check an input variable and based on that execute another stored procedure.... Can I run a second proc independent of the first proc? - Hi Experts, I use a CRM application that uses ActiveReports to enable report development. I have a procedure that takes a... SQL Server 7,2000 : Administration Segmentation fault in tempdb - We have an accounting software (not a good one!!!) using SQL Server 2000. When the users run reports, it creates... Adding a processor to SQL Server - Has anyone had experience with adding a processor to a server running a SQL Server instance? We are wanting to... can't start Sql server 2000 service, how to troubleshooting ? - Error log: 2008-10-27 15:02:33.80 server Copyright (C) 1988-2002 Microsoft Corporation. 2008-10-27 15:02:33.80 server All rights reserved. 2008-10-27 15:02:33.80 server Server Process ID... Getting access to MSDE version server - I have a SQL Server 2000 MSDE instance which was installed and configured by a person no longer with our... database connection for primavera p6 - sql server with pmdb$primavera database is running ok ,but connection with project management module is not establishing. not trusted server... SQL Server 7,2000 : Backups LiteSpeed makes corrupt DoubleClick backups when over 4 GB? - Dear All, I have just noticed something horrible on my site. After backing up databases with LiteSpeed for some months we... SQL Server 7,2000 : SQL Server Newbies Setting Insert records alert - How do I create an sql script that that would inform me if the table I have created has more... Pivot, Crosstab report - Hi Im trying to create a pivot table, with little success, atm im using this code.. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE... SQL Server 7,2000 : Performance Tuning Why we need to use UPDATE STATISTICS ? - Hi, I have a some questions here - 1. Why we need to use UPDATE STATISTICS ? 2. When we need to do... SQL Server 7,2000 : Replication Error 208: Invalid object name ''msdb.dbo.MSdistpublishers'' - Hi Guys, We have a server setup as a distribution server, which manages the replication to & from 3 other SQL servers. Last... SQL Server 7,2000 : T-SQL Need help with syntax - Greetings all. I have a situation where I save an Excel file as tab delimited text, then BCP it to... OPENQUERY DYNAMIC SQL PROBLEM - Hi I'm having a problem updating an Oracle table using a linked server from SQL Server 2000, and hope someone can... Conversion syntax error - I have a table with accounting posts by "job number" (workstage), taken from our accounting system. In the past, these... convert a varchar(4000) to a int !? - Hi, i'm a beginner with sql server and t sql ! i have a store procedure who take some parameters! one of these... Assigning current of _Cursor to local @variable - Hello, I have this nice cursor (yes, I know you should never use them but I feel I must broaden my... bcp Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file - I use domain account to login query analyser, i have admin right , and I can access the folder "d:\W3Root\Test", but... query to find space usage of each column in a table - We a table of abnormal size and need to find usage by each column in the table. Below is the structure... Career : Certification MCDBA vs MCITP:DBA ?? - Hi, For DBAs managing both platforms (MSSQL 2000/2005) , does anybody still recommend doing the MCDBA track first, then MCITP:DBA? Or, just... Career : Employers and Employees Did you buy SQL Server for home use? - I'd like to do some learning on my home computer with SQL Server 2000 and 2005. I realize I can... Career : Resumes and Job Hunters Freelance SQL Server Opportunity - I was looking for an experienced person to take a look at the SQL 2005 setup on new server and... Programming : XML Parsing large XML string in stored procedure - I have a SQL Server 2000 data table with 1 field that contains an XML string. I am currently using... SQLServerCentral.com : Anything that is NOT about SQL! Looking for reviews please! - All, I'm putting together my DBA / Author's website and including copies of my published articles. I'd like, if possible, to include... Dangers of using MYSQL...What are they? - We are a Microsoft SQL Server shop when it comes to our database systems, but now word has reached the... Reporting Services : Reporting Services restoring encrypted content - Hi, I had a problem with reporting services not starting and I thought reinstalling it would be the answer as I... Quick Question (Changing SSRS URL) - Hi guys, Can anyone here provide some directions as to how to change the default SSRS url http://servername to some other... SSIS package as datasource for SSRS report - I created an SSIS package that delivers an ADO.NET recordset as its output, and it works fine stand-alone. I then... Expand All & Collapse All options in a drill down report - Hi, I have a report with drill down implemented to 3 levels. I want to provide options to Expand all items and... Monthname: Is there a bug in this function - Can someone tell me if there is a bug/issue with the SSRS built in function MonthName The following expression in the... Reporting Services : Administration Install Second Intance of RS on Same Box - Currently we have a test box for reports. This box has both RS and SQL. Now I am asked to... Reporting Services : Report Development How can I insert the parameters I create into my page header? - I am a newbe working with SSRS in Sql Express 2008 from within Visual Studio 2008 Professional. I am able... SSRS wont allow different charting types in the same plot???? - Hi , SSRS wont allow different charting types in the same plot???? For ex: Y1-axis: Scatter graph Y2-axis : Line graph Can i achieve... Data Warehousing : Integration Services 2005 SSIS SQL Destination Using Windows Username Instead Of Connection String User - For the past two days, I've been troubleshooting an issue we have with an SSIS package that was just created.... Microsoft Access : Microsoft Access Run SQL Stored Proc from access - Can anyone enlighten me? I am in the process of migrating a database from Access to SQL. I have put all... Incurable ODBC timeout - Hi. I have an Access 2000 database with a linked table pointing to SQL Server 2000.  When running a query in... To be removed from this list, please manage your subscription at http://www.sqlservercentral.com/MyAccount or send a blank message to leav...@sqlservercentral.com. We have had issues with the automated email, so if your email is not recognized, please send a polite note to the webmaster. If you have any problems leaving the list, please contact the webm...@sqlservercentral.com. This transmission is ©2000, 2007 Red Gate Network. All rights reserved. If you have any issues or problems, you may contact us at: Red Gate Software, Cambridge, CB40WS, United Kingdom Fax: 904-282-7845