May 8, 2010 at 4:15 pm
We have job that runs at the end of the week and do the Re-IndxIng .this Week,this job failed on a specific table.
and here is error message:"A floating point exception occurred in the user process. Current transaction is canceled."
I tried to run this job manually and again it failed.in that table we have 8 column which their data type is float.and previously we have had this problem.We had corrupted data,which generally the currpted data was in the format of "-1.#" in float columns,but this time I could not find them,Is there a way that I can find those currupted records and fix them.Because this job is extremly critical for us.
May 8, 2010 at 6:28 pm
Here is a similar issue that was resolved using DBCC CheckDB with Repair_Fast.
http://www.sqlservercentral.com/Forums/Topic374617-5-1.aspx#bm379178
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 10, 2010 at 12:27 pm
Thanks a lot for your reply.but here is the job that I was running and I was facing with problem.
Instead of this job when I run
DBCC CheckDB 'Order'
exec AdminDBReindex 'Order'
(Which the logic is the same as job ,excepts the SQL statments are not created dynamically)
I do not face any problem and there is not any error message.I was wondering why .If there is any problem in Tables Index structure,I should recieve error message again.
--------------Job------------------------------
declare @dbname varchar(100)
declare @checkdb varchar(100)
declare @logevent varchar(150)
--This creates a temporary table which has the list of current databases in a SQL server
Create table #dblist
(Dbname varchar(100) null,
dbsize int null,
remarks varchar(255) null)
insert into #dblist
exec sp_databases
--Starts the cursor
declare dbnamecursor cursor for
select dbname from #dblist
where dbname not in ('tempdb','model','Sales','ArchiveTempDB','LogChangedData')
open dbnamecursor
fetch next from dbnamecursor into @dbname
while @@fetch_status=0
begin
--performs the dbcc checkdb statement on the databases
set @checkdb='dbcc checkdb ('''+@dbname+''')'
exec (@checkdb)
--If the dbcc checkdb statement fails, then this process is aborted and the error is logged.
if @@error <> 0
begin
set @logevent=+ @dbname+ ' has failed the Database Consistency (DBCC CheckDB) Check. Please check the database'
EXEC master..xp_logevent 50001, @logevent, error
raiserror ('DBCC CheckDB has failed',19,1)
return
end
else
--If the dbcc checkdb statement is successful then it runs the usp_index on the database
exec AdminDBReindex @dbname--Which does REIndexing and Update Statistic
fetch next from dbnamecursor into @dbname
end
close dbnamecursor
deallocate dbnamecursor
May 10, 2010 at 12:36 pm
Your code isn't doing anything special. Can you run dbcc with the repair_fast option on that one db?
May 10, 2010 at 3:53 pm
As I said,When I run DBCC CheckDB 'Order',exec AdminDBReindex 'Order' instead of those dynamic code,I do not
recieve an error message.but When I run the job I recieve error message.Also When I try to DBCC CheckDB with Repair_Fast Option.I recieve an error,which said the database should be in single user mode.But this server is being replicated many place and If I change it's mode,it will fail the existuing replication
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply