Removing Stopped Databases

Question

Post reply

Removing Stopped Databases

geelsu

Old Hand

Points: 333
More actions
March 9, 2021 at 5:08 pm

#3855589
Greetings all, first post....
In doing backups with our Cohesity system it reports errors with a few databases which lead me to investigate the cause. Basically these are stopped databases on the Microsoft Server 2016 / MSSQL 2016 server systems. The SQL Configuration Managers shows them as stopped. These particular databases were moved to other servers. The SQL Management Studio also has memory of them on the old server in its list when I use Connect -->Database Engines. Of course selecting the oldsever\db just times out. How do I remove these stopped database references? I am not sure how Cohesity is seeing the stopped databases, but it thinks they are there.
Kind regards and thanks.
- This topic was modified 3 years, 8 months ago by geelsu.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

SQL Server Configuration Manager does not list databases, as far as I can see. So what do you mean by this:

The SQL Configuration Managers shows them as stopped

?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

geelsu Old Hand Points: 333 More actions · Answer 2

Yes. Well it shows the Service for the database stopped i.e. with the SQLServer Agent for that database stopped.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Do you know how SQL Server is structured?

There is a thing called an 'instance' and the services associated with it (SQL Server, SQL Agent and others) run the instance.

Databases are hosted on an instance. If the SQL Agent service is stopped on that instance, it means that no SQL Agent jobs will run on that instance. But if the SQL Server service is running, the instance could still be online.

If your backup system relies on SQL Agent, then start the SQL Agent service.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

geelsu Old Hand Points: 333 More actions · Answer 4

LOL. You are getting over my head already, but I get the gist of what you are saying.

I see in the Config Manager.....

SQL Server (CONFLUENCE) Stopped

SQL Server (EPO) Running

SQL Agent (CONFLUENCE) Stopped

SQL Agent (EPO) Running

I guess I could shut this server down and see if Confluence and EPO still works.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

OK, that looks like two instances on the same server. One called CONFLUENCE and one called EPO.

EPO looks active, CONFLUENCE not.

I recommend that you don't shut down the server until you know more about the potential effects!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

geelsu Old Hand Points: 333 More actions · Answer 6

Ok. Thanks for your wisdom. I will hold off since my day is about over. Tomorrow is a better day for me to break something!!!!!!

geelsu Old Hand Points: 333 More actions · Answer 7

I got the history on this. Someone tried to setup mirroring and AAG on the databases, but failed. Ultimately, the task was abandoned and that is why I have these disabled databases. So there is some remnant of the failed instance still hanging around but unused. The Cohesity backup is seeing it somehow and tries to back it up, but of course it can't.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

These aren't databases. They are instances. A SQL Server install is an instance, and it can contain multiple databases. If these instances are not being used, and not ever started, then you need to uninstall them. Use setup to "remove an instance".

geelsu Old Hand Points: 333 More actions · Answer 9

Where/What is setup? Is that in the Management Studio?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 10

The SQL Server setup program from the installation media.

Sodtke SSC Eights! Points: 982 More actions · Answer 11

geelsu wrote:

Greetings all, first post....
In doing backups with our Cohesity system it reports errors with a few databases which lead me to investigate the cause. Basically these are stopped databases on the Microsoft Server 2016 / MSSQL 2016 server systems. The SQL Configuration Managers shows them as stopped. These particular databases were moved to other servers. The SQL Management Studio also has memory of them on the old server in its list when I use Connect -->Database Engines. Of course selecting the oldsever\db just times out. How do I remove these stopped database references? I am not sure how Cohesity is seeing the stopped databases, but it thinks they are there.
Kind regards and thanks.

@geelsu, Cohesity requires sysadmin access to the SQL instances, the Cohesity agent installed on the server (physical/VM) as well as having the SQL instance registered within the Cohesity UI as a Source. With those prerequisites, Cohesity can "see" everything and will back up all databases within the instance(s) if you've selected the option to Auto-Protect the SQL instance. To remove the databases from the backup job, you will have to manually select the databases within the protection job.

Hope that helps.

geelsu Old Hand Points: 333 More actions · Answer 12

Thank you all for all your excellent input. I learned a lot from each of you. I actually found a KB article at Cohesity which closely addressed this issue. The Cause, Cohesity reports, is:

This error will continue to be reported as long as the SQL Server is listed in the registry. Cohesity searches for available SQL Server instances by checking the location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL. If the instance is present, then Cohesity attempts to protect it.

I backed up the registry and removed the dead instances. After talking with someone who knew the history of these systems, I am 99% confident these registry entries are just leftover entries from the failed AAG (SQL Server AlwaysOn Availability Group) attempt on the instances.

I used the sc command to remove the DISABLED services which also cleaned up the listing in the SQL Configuration Manager.

The SQL Server Management Studio still connects to the various active instances and no errors or database issues seem to be occurring, though I will continue to monitor for a few days. The dead instances still appear in the Management Studio's ->Object Explorer -> Connect -> Database Engine -> Connect To Server menu, but that is probably another registry entry.

My next backup of the instances with Cohesity produced no errors.

VoldemarG Hall of Fame Points: 3883 More actions · Answer 13

also if you administering that server and often need to lookup what services are started you can use command line

NET START command.

If you want to see all services related to SQL SERVER ONLY, you can run a net start command also from a SQL Server Query:

  
-- enable xp_cmdshell extended system stored procedure if it is not
/* 
EXEC sp_configure 'show advanced options', 1;
GO
Reconfigure;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
Reconfigure 
*/
--Run 'net start' command from SQL Server
--1) Create the table
CREATE TABLE #cmdNetStartRun
(outputvalue VARCHAR(3000)
 ,outputDate DATETIME2 DEFAULT GETDATE());

--2) Run the cmd and insert the output into the table
INSERT INTO #cmdNetStartRun(outputvalue)
exec xp_cmdshell 'net start';

--3) Select from the table

SELECT outputvalue, outputDate
FROM #cmdNetStartRun
WHERE outputvalue LIKE '%SQL%';

--4) Drop the Table
DROP TABLE #cmdNetStartRun;

Likes to play Chess