Solved

Problem with reindexing SQL database for MS CRM

Posted on 2008-10-09
14
3,007 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now