Solved

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

Posted on 2016-09-13
12
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 18

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 18

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 18

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 18

Accepted Solution

by:
Walter Curtis earned 500 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 18

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 18

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 18

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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