.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Index Rebuild failing with sql error msg 8966, level 16, state 2, line 20

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server
 

Working with a customer trying to help him re-index his database. Database is a little over a terabyte and I gave him a re-indexing script to run on his SQL Server 2005 Enterprise server.

FYI
It had been recommended by a 3rd party load testing company that all of our re-indexing be done in "offline" mode so the re-indexing was done in offline mode for this customer.

The first reindex attempt stopped with a "severe" error message in the "query window" when sql reached the largest table in the system, which I found out contains about 29 milliion rows. The previous indexes had been successfully rebuilt or reorganized based on the percentage of fragmentation.

I asked for the sql error logs and found the "severe" error message written to the log around the time of a "sql server restart". I asked the customer and he admitted to restarting sql server. I suspected that the reindex script had not completed but could not be sure.

I decided to approach it next by rebuilding the index on the huge table only. He called me the next day and told me the rebuild had failed. He sent me the error logs and he also informed me he had killed the index query after it had been running for over 24 hours.

I found some IO errors in the sql error logs referring to "15 seconds" had been exceeded a


View Complete Post


More Related Resource Links

Another ERROR Message Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'COLUMN'

  

Ok, I would like to ask you this if I may,

On another database I created a table named FICTION

and the table looks like this:

CREATE TABLE FICTION
 (BOOK_CODE CHAR(4) PRIMARY KEY,
 TITLE CHAR(40),
 PUBLISHER_CODE CHAR(3),
 PRICE DECIMAL(4,2) );

Now I am trying to change the data type in TITLE to CHAR(50)

ALTER

 

TABLE FICTION

MODIFY

 

COLUMN TITLE

Msg 50000, Level 16, State 1, Line 34

  

Hi,

I get the following error:

Msg 50000, Level 16, State 1, Line 34
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65669. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'capsqlextensions, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
System.IO.FileLoadException:
   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
   at System.Reflection.Ass

Msg 10301, Level 16, State 1, Line 1

  
Hi, I am trying to register Excel that should be located at: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 in order to: CREATE ASSEMBLY Excel FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.Office.Interop.Excel.dll' But there is no: 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.Office.Interop.Excel.dll' Where could I find that? Regards, Jos

Msg 15121, Level 16, State 10, XP_Cmdshell - A call to 'CreateProcessAsUser' failed with error cod

  
I have the XP_cmdshell issues for the past two days in SQL2005 SP2 environment. There is a domain account with sysadmin privilege. I have created a proxy account and gave the execute privilege for xp_cmdshell. If i try to run the following commmand EXEC master..xp_cmdshell 'dir *.txt' Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 An error occurred during the execution of xp_cmdshell. A call to 'CreateProcessAsUser' failed with error code: '1314'. But if i give sysadmin priv and then i try to execute. It worked. I cant give sysadmin priv so i m using a proxy account. Whether any one have solution for it.

Msg 8624, Level 16, State 13 - Internal SQL Server error.

  
Hi, I have a weird T-SQL 80 query full of nested subqueries full of aggregations tha thas been embedded in a view. if I do select * from the view I get: Server: Msg 8624, Level 16, State 13 Internal SQL Error.  If I run the same block of code from the query analyzer it runs fine. Also if I wrap exactly the same code in a SP it runs fine. I'm running this on a SQL Server 2000 Standard Edition SP4 8.00.2282. + I turned on the following Trace Flags: 3614, 9052, 9054, 9056, 9057, 9185, 9256. If I restore the same DB on a SQL Server 2005 and do the same select * it runs fine. So I'm getting the Error 8624 only on my SQL 2000 Standard edition  SP4 8.00.2282. Another thing I dont understand is also: why I can compile the view but if I try to visualize even the query plan I get the same error?!? Any suggestion will be appreciated. Thank you. Regards, Marco    

Backup failing - Error "operation is not valid due to the current state of the object" ...........Ca

  
Hi, I am having a problem backing up a site collection, when trying to backup the collection I get the following error  "operation is not valid due to the current state of the object"............. "Category Info :InvalidData" . I'm getting the same error whether I use CA "granular backup" or "Farm backup", and using either STSAMD or Powershell. I can backup CA or the mysite collection but not the Home Portal site collection. As this is a production system I am really worried that something is going to happen and I'll have no backup. Does anyone have any suggestions on what i can do to either fix the problem or an alternative way to backup the collection or farm.  P.S. I am not using the "UseSQLSnapshot" parameter. Any ideas ? 

Msg 102, Level 15, State 1, Line 12 INCORRECT SYNTAX NEAR ')'

  
CREATE Table Item(
ItemID    numeric(18,2)  PRIMARY KEY,
VendorID   numeric(18,0)  NOT NULL,
ItemDescription  Char(50)   NULL,
PurchaseDate  DateTime   NOT NULL,
ItemCost   numeric(18,2)  NOT NULL,
ItemPrice   numeric(18,2)  NOT NULL,
 CONSTRAINT Item_FK FOREIGN KEY (VendorID)
  REFERENCES Vendor(VendorID)
  ON DELETE CASADE
  
);      <<<<HERE IS THE ERROR

Msg 137, Level 15, State 2, Procedure AddCMEXperience, Line 49 Must declare the scalar variable "@Re

  

Hi folks, can someone please help me with this error?

Msg 102, Level 15, State 1, Procedure AddCMEXperience, Line 15
Incorrect syntax near ','.


Msg 137, Level 15, State 2, Procedure AddCMEXperience, Line 49
Must declare the scalar variable "@ReasonLeft".


Here is the Stored Procedure that is in question here

USE [ResponsedDatabase]
GO
/****** Object:  StoredProcedure [dbo].[AddNewEmployer]    Script Date: 10/03/2010 14:24:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AddCMEXperience]
(
    @MemberID bigint,
    @EmployerID bigint,
    @DateStarted smalldatetime,
    @IsContinuing bit,
    @DateLeft smalldatetime,
    @PositionStarted nvarchar(50),
    @PositionLeft nvarchar(50),
    @StartingSalary nvarchar(20),
    @EndingSalary nvarchar(20),
    @TimeWorked float,
    @JobResponsibilities text,
    @ReasonLeft, nvarchar(200)
)

AS

INSERT CMExperience
(
    MemberID,
    EmployerID,
 &nbs

Rebuild Index Error -1073548784

  

I am getting the following error on Rebuild Index Maintenance Task.

"Executing the query "ALTER INDEX [PK_cartrige] ON [dbo..." failed with the following error: "A severe error occurred on the current command.  The results, if any, should be discarded.
A severe error occurred on the current command.  The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

Where can i find more details on troubleshooting this error and find more details on the error. It seems to be a persistant problem since sql server 2005.

Details:

  1. -Maintenance Plan has been working well for months.
  2. -Std Edition Sql Server 2008
  3. -Db Integrity check successfull
  4. -sort result in tempdb is off
  5. -keep index online while reindexing is off
  6. -local server connection works for all other jobs prior to the task

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value when using JO

  

I am having a bit of time with a lab I am doing for school when I write the query this way there is no problems, it does what I want it to do. (I have tried to edit this to give the right formatting but it just is not working)

 

select

 employeeName
from

 employee
where

 employeeInitials in


(select

 employeeInitials
from

 service
where

 propertyId in


(select

 propertyID
from

 property
where

 propertyState = 'NY'

));

 

however, when I am answering the question to do that same query but using join instead of a sub I get an error.

 

select

 employeeName
from

 employee as

 emp join

 service as

 srv
on

 emp.employeeName = 

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'MODIFY'

  
OK, TRYING TO UPDATE [MODIFY] AN EXISTING TABLE NONAPPLIANCE

HERE IS MY STATEMENT:

ALTER TABLE NONAPPLIANCE
MODIFY DESCRIPTION CHAR(30);

IT IS GIVING ME AN ERROR:

Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'MODIFY'.

WHAT AM I DOING WRONG?

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'WHERE'.

  

Hi,

Recently received a trace file from customer of sql server which shows some errors in SQL statement but the front-end application is working fine.

I see SET FMTONLY ON and SET FMTONLYOFF and an additional WHERE clause. The statement looks like:

SET FMTONLY ON SELECT PLANCODE.SYSCODE, PLANCODE.CODE, PLANCODE.NAAM

Msg 8152, Level 16, State 14, Procedure HAM_User_Productive_Time, Line 20 String or binary data woul

  

I have this procedure and table architecture - My ultimate moto is to check the User Start time and user end time and if user is logged in into 2 pcs at once then capturing the min start time and max end time . If user start time is greater than users previous end time then creating a new entry into table.

Alter

 

Procedure dbo.HAM_User_Productive_Time

As

BEGIN

Declare

 

@Min Datetime

Msg 102, Level 15, State 1, Line 1 ,Incorrect syntax near '='

  

i am getting the error Msg 102, Level 15, State 1, Line 1 ,Incorrect syntax near '=' when i executing the query :-

insert

into author values(Ano=3,Author='Bannu',Addr='Delhi',Book='Hum-Tum')

Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'FROM'.

  

I am a student taking SQL classes and am having this error message with this SQL Script:

 

SELECT c.State, i.itemType,
        Cast(Year(o.orderDate) as Varchar) + N'-' +
        Cast((Month(o.orderDate) -1) / 3 + 1 as Varchar)
            as YearQuarter,
        Round((od.quantity * od.price)*(1-od.discount)
            as Sales
FROM Consumers c, Orders o, OrderLines od, inventory i
WHERE c.ConsumerId = o.ConsumerID and
        od.orderID = o.orderID and
        i.inventoryID = od.inventoryID;

 

any idea from that on what I am doing wrong? I'm thinking it's something simple.



Msg 10011, Level 16, State 1, Line 1

  
I received the error Msg 10011, Level 16, State 1, Line 1 when i try to add server role like sysadmin a login. kindly help
Omojuwa Raymond

Msg 10011, Level 16, State 1, Line 1

  
I received the error Msg 10011, Level 16, State 1, Line 1 when i try to add server role like sysadmin a login. kindly help
Omojuwa Raymond
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend