Tag Archives: https://sqldbpool.com/

Clear/Remove SQL Server Services from Services.msc

Problem:- 
One of my friend was installing cluster SQL Server, installation was stopped in the middle of something and he was not able to remove the SQL Server from add/remove programs or SQL Server setup. He has deleted the registry, folder manually but still he can see the SQL Server Services in Services.msc. He called me for the issue, I have provided him the below solution and it will work.

Solution:
1) Go to command prompt and use SC command to delete service

sc delete sqlserveragent
sc delete mssqlserver

2) You have to also delete the services registry entry using regedit
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

How to take database out of emergency mode?

In SQL Server 2000

sp_configure ‘allow’ ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = status&(~32768) where name = ‘SQLDBPool’
GO
sp_configure ‘allow’, 0
GO
Reconfigure with override
go

IN SQL Server 2005/2008

ALTER DATABASE sqldbpool
SET online

How to open database in Emergency Mode?

In SQL Server 2000

sp_configure ‘allow’ ,1
GO
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = ‘SQLDBPool’
GO
sp_configure ‘allow’, 0
GO
Reconfigure with override
go

IN SQL Server 2005/2008

ALTER DATABASE sqldbpool
SET emergency 

Script to calculate DB size and available size


SELECT Db_name()                                                           AS
       dbname,
       name                                                                AS
       filename,
       size / 128.0                                                        AS
       currentsizemb,
       size / 128.0  CAST(Fileproperty(name, ‘SpaceUsed’) AS INT) / 128.0 AS
       freespacemb
FROM   sys.database_files; 

Disconnect Users

Many times we want to restore the existing database or do DDL operation and often we are getting error Database is in use.


ALTER DATABASE databasename SET single_user WITH ROLLBACK IMMEDIATE 

You required only DBO rights on the target database, even you don’t have sysAdmin rights it will work for that particular database.