Solved

Problem with reindexing SQL database for MS CRM

Posted on 2008-10-09
14
3,038 Views
Last Modified: 2012-05-05
I am seeing the error below in my logs and have tried to run the DBCC Checkdb command on the database.  I could use assistance from someone who knows SQL much better than me.

The error:
****
Host CLARITYSBS: error while processing organization f610b1b2-084e-42c8-a48f-a4bbc5cd7b60. Exception: System.Data.SqlClient.SqlException: Unable to find index entry in index ID 4, of table 1724585232, in database 'Clarity_MSCRM'. The indicated index is corrupt or there is a problem with the current update plan. Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.
A severe error occurred on the current command.  The results, if any, should be discarded.
****

I then open SQL Studio and run the following command:
***
ALTER DATABASE Clarity_MSCRM SET SINGLE_USER
Dbcc Checkdb('Clarity_MSCRM', Repair_Rebuild)
ALTER DATABASE Clarity_MSCRM SET MULTI_USER
***

I then get the following result:
***
DBCC results for 'Clarity_MSCRM'.
Msg 8921, Level 16, State 1, Line 2
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
DBCC results for 'sys.sysrowsetcolumns'.
There are 6763 rows in 69 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
....
Msg 1105, Level 17, State 2, Line 2
Could not allocate space for object 'dbo.SORT temporary run storage:  140737741324288' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
***

I cannot find the tempdb referenced above.  I have looked at the database and its set to unlimited size, along with the log files.  

SQL is not my strong area, so I'm limited to figureing this out.  

Thx,
Jason
0
Comment
Question by:jtcomstock
  • 9
  • 5
14 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 22684136
Hi,

Firstly, if tempDB is full, then its likely you have some very full disks. The easiest way I've found to check all disks at once is to look at the Windows Defrag screen. Just as your coffee cup is full when at 95% capacity, treat your disks as full when at 80% capacity.

Second, on SQl 2005, tempdb is under the system databases.

Your tempdb may have a limited size to avoid running out of disk space.

Get back to me with these results and we can dig further, although I suspect that you'll be able to figure it out from there.

Cheers
  David

PS Easiest way to empty tempdb is to restart SQL
0
 

Author Comment

by:jtcomstock
ID: 22694694
I've been working on this today to get more detail.  Disk space wise I'm in good shape,  I have 28% free on the volume being used.  I'm seeing the following errors in the SQL Server Log:

10/11/2008 12:41:55,spid58,Unknown,The transaction log for database 'MSCRM_CONFIG' is full. To find out why space in the log cannot be reused<c/> see the log_reuse_wait_desc column in sys.databases
10/11/2008 12:41:55,spid58,Unknown,Error: 9002<c/> Severity: 17<c/> State: 2.
10/11/2008 12:40:59,spid57,Unknown,SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x63349e39; actual: 0x53dcffe9). It occurred during a read of page (1:9451) in database ID 13 at offset 0x000000049d6000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Clarity_MSCRM.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.

I have done the following:

- Run DBCC CHECKDB Repair_Rebuild and Repair_Allow_Data_Loss
- Restared SQL and the server a few times

Also, I've looked at the sys.databases table per the above message and don't see why this would be full.  The database is set to grow and is only 18.5 Mb.  

tempdb is 8.6 Mb and has space available for for 6.6 Mb.

My lack of knowledge with SQL Server is making this a challenge.  

Let me know what other info you need.  
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22695020
Hi,

In all the databases of interest (MSCRM_Config, Clarity_MSCRM, tempdb), run the following:

select *
from sys.database_files

and report the results.

Cheers
  David
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:jtcomstock
ID: 22699880
David,

Attached are the results.  I was given the option to export to .csv and then converted them to .pdf to upload here.  The headings did not export.  Please let me know if you need to have them.  

I looked at the results, but am not sure what I'm looking for.  I look forward to your insight.  

Jason

ResultsforClarityMSCRM.pdf
ResultsforMSCRM-Config.pdf
ResultsForTempdb.pdf
0
 

Author Comment

by:jtcomstock
ID: 22712310
Any thoughts?
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 22714011
Hi,

There does appear to be room in all your databases.

I'm intrigued by the full-text file in ClarityMSCRM. I haven't worked with fulltext indexes before.

Size is in 8K pages.

The data file for ClarityMSCRM is only 300MB.

Suggestion:
In the database properties screen, increase the files sizes to the following:
tempdb
data 45MB
log 15MB

MSCRM-Config
data 45MB
log 15MB - and make unlimited growth!

ClarityMSCRM
data leave at approx 300MB
log 50MB

Question:
After running the Run DBCC CHECKDB, does it report any errors?

Cheers
  David
0
 

Author Comment

by:jtcomstock
ID: 22715462
I was able to adjust the size of tempdb and ClarityMSCRM.  I get the error message below when I try to access the properties of the MSCRM_Config database.  It won't let me into the properties to change the size.  

Suggestions?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Database 'MSCRM_CONFIG' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3282&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476

0
 

Author Comment

by:jtcomstock
ID: 22715526
I took that database offline and then was able to get into properties.  I tried changing it to 45 and 15 and get the same error message.  I tried other combinations that were larger and still get the same error.  
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22717873
Hi,

In Object explorer, is there any status on the mscrm_config database, like offline, or loading or suspect or single user or anything?

Cheers
  David
0
 

Author Comment

by:jtcomstock
ID: 22725903
No.  It doesn't show single user.  It did show offline when i tool it offline, but that icon changed back to the default icon as shown on other databases.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 22729007
Hi,

Any chance that you have a usable backup? I think that you might need to go back to backup.

Cheers
  David
0
 

Author Comment

by:jtcomstock
ID: 22737258
No a recent one.  I could try to apply what I have, but am not sure how they would work with the current database.  It appears that CRM has three databases.  
0
 

Author Comment

by:jtcomstock
ID: 22737437
I have a backup for that file dated 9/5, which would be fine with me.  I keep getting the message below:

******

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Microsoft SQL Server, Error: 9002)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3233&EvtSrc=MSSQLServer&EvtID=9002&LinkId=20476
******
I have restarted SQL Server a few times to clear this database and the size is set to 50Mb.  The database is only 8.5 Mb.  Suggestions to deal with tempdb?
0
 

Author Comment

by:jtcomstock
ID: 22799959
Problem resolved.  The issue of tempdb being full was the issue.  The volume with the SQL data had 7.58 Gb free.  When I would try to grow tempdb to 25Mb, it would say there is not enough physical room, there was 7.58 Gb free.  Anyway, I moved the CRM database to another volume and had 8.10 Gb free.  CRM started running.  Go figure that .5 Gb would do the trick.  

Thanks for sticking with me.  

J
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question