?
Solved

Sharepoint SQL Server Database (WSS_Logging) stuck in "restoring" mode

Posted on 2016-09-13
12
Medium Priority
?
179 Views
Last Modified: 2016-09-14
I have noticed now for the last couple weeks when looking at SQL Server Management Studio that the WSS_Logging database is stuck in a "restoring" mode. How can I get this working normally again?
0
Comment
Question by:jb61264
  • 7
  • 5
12 Comments
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41796991
Are you using db Mirroring? If so, is this on the primary or secondary SQL server?

I have have had that happen before and although working with an expert dba all we could do was delete and restore. Luckily this was just a logging database and not a content db. I have had good luck (knock on wood) with this not happening on content databases.

Hope that helps...
0
 

Author Comment

by:jb61264
ID: 41797033
Not using db.Mirroring...in fact, I'm currently running sql server on the same sever (which I know is discouraged and is on my list to move to a different one soon)...after I figure this out.

I've read that wss_logging is for configuration information that feeds the central administration app with configuration "issues" so I wonder if it could just be "recreated"
0
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41797044
It provides diagnostic information, not so much configuration information. If it is stuck on restoring, it isn't working any way.

Your company may have an auditing and retention policy that may not make is possible just to delete without some type of approval.

The database WSS_Logging is the default name for the database used by the service application "Usage and Health Data Collection", in SharePoint 2010. (2013 is probably the same.) Before you do anything big with the database, like delete it, check with TechNet for the exact procedure.

Good luck...
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:jb61264
ID: 41797063
Thanks, no retention policies to worry about...do you think Sharepoint would recreate it if it were deleted? I checked and I do have a backup from back in August of that db, I'm thinking I could just restore with that copy as long as doing so doesn't mess anything up.
0
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41797077
You may not be able to delete it without stopping the service first. Currently I am not in a position to use anything but memory, so I can't say one way or the other exactly how to do it. Generally, service applications such as this can have their databases deleted and recreated. Worst case you would have to recreate the service application which would in turn recreate the database. But if you have access to Technet, it would be best to confirm all this there. Once I am back where I have references I will provide you with some definitive information, but that could be tomorrow at the earliest.

Maybe a real expert will add something definitive to this thread.

Have a good one...
0
 
LVL 20

Accepted Solution

by:
Walter Curtis earned 2000 total points
ID: 41798113
I was able to confirm that deleting the service application "Usage and Health data collection" will also remove the database, in your case "WSS_Logging". Then you can recreate the service application and a new database will be created and should work fine.

Note- the service application name shown above is the out-of-the box name. You may have a different name, but probably not. As far as recreating the service app goes, I am sure powershell can be used, and I love powershell, but the Configuration Wizard found in Central Admin also creates the service application and database, although the db is created with a typical ugly GUID. Powershell probably allows you to define the name. (I was in my test farm so I don't care about GUID's)

As mentioned in this link, it is okay to delete the current database that is malfunctioning for you -

https://social.technet.microsoft.com/Forums/lync/en-US/3c80d63d-7422-4c2e-9cfb-001df1f5e7f7/wsslogging-stuck-in-restoringhow-do-i-fix-can-it-be-recreated?forum=sharepointgeneral

Good luck. Hope that helps...
0
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41798212
Thanks,  hope you got it working as it should again...
1
 

Author Comment

by:jb61264
ID: 41798249
I haven't used PowerShell much at all (and know I probably need to start learning it more)...from the Central Admin gui I was able to remove the Useage and Health Data Collection Service App, but I don't see an option for creating it in the "New" dropdown list.
0
 

Author Comment

by:jb61264
ID: 41798255
0
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41798260
You can recreate the Service Application using the configuration wizard. In central admin, it is in the left navigation, (quick launch navigation.) Once there, you will see the icon for Farm Configuration. Click on that, then you go to another page and you will see start the wizard, click on that.

You will then see the service apps that can be installed. Toward the bottom is the information about the "Usage and Health data collection" service app. The rest is straight forward.
0
 

Author Comment

by:jb61264
ID: 41798283
Got it! thanks! last question, in that configuration wizard there are a number of items checked where the checkbox is gray...does this indicate items that are already installed? and if so, should I uncheck everything except for the Usage and Health data collection service? I don't want to uninstall stuff that is already installed and its not clear from the wizard if this would uninstall things that were unchecked
0
 
LVL 20

Expert Comment

by:Walter Curtis
ID: 41798340
Yes, the greyed out boxes are service applications that are already provisioned (installed). You will not install them new if you leave them checked, I think you can't uncheck them, it is a read only box I think.

Good luck..
1

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 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