November 26, 2007 at 2:44 am
When I run a query in my production DB (MSSQL 2005), after 1 hour and 20 minutes it stops, kills the DB and I get this error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The query runs fine in my Dev and Test DB's. All it does is read the database (named PA1) and output the number of table indexes that are over 90% fragmented.
The actual query is:
=======================================================
-- GET REQUIRED RECORDS AND PUT INTO TABLE (MISSING ObjectName use Object_id instead?)
DECLARE @db_id SMALLINT;
SET @db_id = DB_ID(N'PA1');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL , 'LIMITED')
WHERE avg_fragmentation_in_percent > 90.0 AND index_id > 0;
--INTO fraglist_temp;
END;
GO
=======================================================
Any ideas why I get the error and what I can do to prevent it?
Many thanks.
Paul
November 26, 2007 at 3:28 pm
Best I can do is give you some places to look I'm afraid.
1) Get your execution plan from your dev/test boxes and compare it to the prod box.
2) Run a checkdb on each of the databases on your prod server. You may have an index that is killing you.
3) Do you have a significatly larger set of databases on your prod server? (I'm not sure if sys.dm_db_index_physicial_stats runs longer on a larger DB or not but that might affect it too.)
One thing I can tell you though is that if you join to the sys.objects table of the DB you are checking you can get the object name.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 26, 2007 at 4:19 pm
I would check that all databases are in compatibility mode = 90.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply