Thursday, April 30, 2015

CREATE LOGIN of windows user and GRANT SYSADMIN Access

Using below command you can create a windows login and grant SysAdmin access. Replace DOMAIN\LOGIN with windows login name 


USE [master]
GO
CREATE LOGIN [DOMAIN\LOGIN] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\LOGIN', @rolename = N'sysadmin'
GO

--
Thanks & Regards
Venkat Sangu

Wednesday, April 22, 2015

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:

Monday, April 20, 2015

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.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'.
------------------------------
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)

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';

Friday, April 10, 2015

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