How to check machine is physical for virtual server

This is used when doing audit to identify the server is Physical or virtual

There are many ways to find the machine type either Physical or Virtual 

Using GUI open the System information by typing msinfo32.exe in run command

Result Shown as below and for Physical servers

Result Shown as below and for Virtual servers

for Virtual  Machines "System Manufacturer" and "System Model", the screenshot displays "VMware Inc. " &  "VMware virtual platform"

From Command prompt also you can find by typing systeminfo in CMD

Also by using below dos command you can find 

systeminfo /s %computername% | findstr /c:"Model:" /c:"Host Name" /c:"OS Name"

Below are the two screens shots of Physical & Virtual Machine results

Physical Box:

Virtual Box:

How to remove Orphaned subscription

How to remove Orphaned subscription

TITLE: Microsoft SQL Server Management Studio

------------------------------

Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'. The subscription record does not exist at Subscriber.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeleteSubscriptionAtSubscriber&LinkId=20476

TITLE: Microsoft SQL Server Management Studio

------------------------------

Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeleteSubscriptionAt&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

'S608161RGSW14' is not defined as a Subscriber for 'AJGECOMVDBP235\GTI'.

Could not update the distribution database subscription table. The subscription status could not be changed.

Changed database context to 'Transactor'. (Microsoft SQL Server, Error: 20032)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=20032&LinkId=20476

Using below script you can drop the sucbscription  you need tun below script in publication server 

In below script 
MYTran is the publication database 
MYTranGTI_MY_PUB is publication name 
MYTranMI is the subscriber database name 

USE  MYTran

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

--enter your publication name

SET @publication = N'MYTranGTI_MY_PUB';

--enter subscriber name

SET @subscriber = N'MYTranMI';

USE MYTran

EXEC sp_dropsubscription

@publication = @publication,

@article = N'all',

@subscriber = @subscriber

,@ignore_distributor=1;

GO

Using below script you can drop the publication you need tun below script in publication server 

MYTran is the publication database 

MYTranGTI_MY_PUB is publication name 

USE MYTran
Go

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

--set your publication database here

SET @publicationDB = N'MYTran';

SET @publication = N'MYTranGTI_MY_PUB';

EXEC sp_droppublication

@publication = @publication

,@ignore_distributor=1;

-- Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N'publish',

@value = N'false';

GO

See Also

Remove SQL Replication in Publisher and Subscriber after changing server names

Table Space used and row count

Using below query you can find out the table space in a database and rows count of the table. 

Please change/add your tables list in WHERE condition  

SELECT T.NAME AS TableName, P.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB,  SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB 

 FROM  sys.tables T

 INNER JOIN      

    sys.indexes I ON t.OBJECT_ID = i.object_id

 INNER JOIN 

    sys.partitions P ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

 INNER JOIN 

    sys.allocation_units A ON p.partition_id = a.container_id

 LEFT OUTER JOIN 

    sys.schemas S ON t.schema_id = s.schema_id

 WHERE 

    T.NAME in ('Table1','Table2','Table3','Table4') 

    AND T.is_ms_shipped = 0

    AND I.OBJECT_ID > 255 

 GROUP BY 

    T.Name, S.Name, P.Rows

 ORDER BY 

    T.Name

 you can find using SSMS as shown in below. Right click on the database and click on the Reports --> select Standard Reports --> and select Disk Usage by Table. 

and results shown like below

Script to disable/enable all triggers (Database Level) in a database & Enable or Disable Server level triggers (SERVER LEVEL)

Use below script to disable or enable all triggers in MyDatabase  (Database level) 

To disable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

To enable all triggers, you can use following statement

use MyDatabase

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

The following example disables all DDL triggers that were created at the server scope. (Server Level Triggers) 

USE master

GO

DISABLE Trigger ALL ON ALL SERVER;

GO

USE master

GO

ENABLE Trigger ALL ON ALL SERVER;

GO

Information for SQL Server End of Mainstream support

Microsoft will offer a minimum of 10 years of support for Business, Developer, and Desktop Operating System (consumer or business) Software Products. Mainstream Support for Business, Developer, and Desktop Operating Systems will be provided for 5 years or for 2 years after the successor product (N+1) is released, whichever is longer. Microsoft will also provide Extended Support for the 5 years following Mainstream support or for 2 years after the second successor product (N+2) is released, whichever is longer.  

Products Released	Lifecycle Start Date	Mainstream Support End Date	Extended Support End Date
Microsoft SQL Server 2000 Service Pack 4	06-05-2005	08-04-2008	09-04-2013
Microsoft SQL Server 2005 Service Pack 4	13-12-2010	12-04-2011	12-04-2016
Microsoft SQL Server 2008 R2 Service Pack 3	26-09-2014	08-07-2014	09-07-2019
Microsoft SQL Server 2008 Service Pack 4	30-09-2014	08-07-2014	09-07-2019
Microsoft SQL Server 2012 Service Pack 2	10-06-2014	11-07-2017	12-07-2022
Microsoft SQL Server 2014 Standard/Enterprise	05-06-2014	09-07-2019	09-07-2024
Microsoft SQL Server 7.0 Service Pack 4	26-04-2002	31-12-2005	11-01-2011

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK"

If you are getting below issue : 

After creating linked server and when you try to run the query using ssms 

select * from  OpenQuery(ORACLE_LINK, 'select * from xxx_table_history') 

you get encounter below issue.  

Cannot create an instance of OLE DB db provider "oraOLEDB.Oracle" for linked server "ORACLE_LINK" 

or

Execute permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys' (Microsoft SQL Server, Error 229)

Resolution : 

Login to the server where you are getting this error go to 

Server Objects --> Linked servers --> Providers --> right click on [OraOLEDB.Oracle] and select properties 

In properties window click on ALLOW INPROCESS 

Script to shrink all database log files in SQL Server

The below script is useful when ever you want to shrink all log files of user databases ( mostly you may need to do in test & dev environments) to free up the disk space. if you ran below script you can get the  DBCC SHRINKFILE script for each database. Change the results to text option before executing below script shown in Pic :1. Copy the results

(Pic : 1)

in new window past the copied results shown in Pic : 2 and execute it to shrink the log file.

SELECT  CHAR(13)+CHAR(10) + 'USE ['+ DB_NAME(database_id)+']'+CHAR(13)+CHAR(10) + 'GO'+CHAR(13)+CHAR(10) + 'DBCC SHRINKFILE (N'''+name+''' , 0, TRUNCATEONLY)'+CHAR(13)+CHAR(10) + 'GO'  FROM sys.master_files where file_id = 2   

(Pic :2)