What is Trace Flag? Enable / Disabling Trace Flag

Trace flag is a directive used to " set specific server characteristics or switch off a perticular behavious" -- is a setting that in some way or another alter the behaviour of various SQL Server functions

Trace flags are used to generate internal information regarding SQL Server's activities, primarily so that Microsoft developers can troubleshoot their code.

Trace flags are enbled in SESSION or GLOBAL levels

How to check whcih trace flags are enabled in the server globally

DBCC  TRACESTATUS(-1) 

Above command will display the status of all trace flags that are currently enabled globally.

How to check whcih trace flags are enabled in the server for current session

DBCC  TRACESTATUS()

Above command will display the list of all trace flags that are enabled for the current session 

You can enable/implement Trace Flags in several ways

• using SQL Server Configuration Manager
• using Registry Editor
• using Query Analyser

Implementing Trace Flag using SQL Configuration Manager 

This is also know as Enabling Trace Flag using –T Startup Option

Adding trace flag in Startup Parameters 

-T startup option to specify that the trace flag be set on during startup. Saparate each trace flag by semi-colon(;). -T startup option enables a trace flag globally 

You can't enable a session level trace flag  by using start up option

  

Implementing Trace Flags using Registry (REGEDIT.MSC)

This is not a recomended method. please avoid using it in production box.

• Goto Run and type regedit.msc and press endter
• Goto below location in registry
• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Add the trace flag by right click on the Parameters folder and click on New and then String Value
• Provide the name as SQLArg3 and then right click on the key value to modify the value.
• Now provide the value as -T1222 

Implementing Trace Flags using Query Analyser 

Using DBCC TRACEON command you can enable the trace flags.All trace flags can't be enabled using DBCC TRACEON e.g. DBCC TRACEON (835), this can be enabled during the server startup only.

You can enable Session Scope or Global Scope using this command

Session Scope : Using below command we can enable session level trace flag

DBCC TRACEON(1222)

Global Scope :  Using below command we can enable global level trace flag

DBCC TRACEON(1222,-1)

Disabling Trace flag

Remove from start-up parameters 

• Go to SQL Server configuration manager and to sql server properties 
• Go to Advanced Tab and click on drop box of start-up parameter and remove the parameter -T1222
• Restart SQL Server services 

Remove from Registry 

• Go to regedit.exe and 
• Browse HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\Parameters
• Remove the string value which has -T1222
• Restart the SQL Server services 

Remove using Query Analyser 

use below command to turn off session level trace flag

DBCC TRACEOFF(1222)

use below command to turn off global level trace flag

DBCC TRACEOFF(1222,-1)

Caution : 

Do not use trace flags in production environment without testing it on non production environments, trace flags should be used under the guidance of Microsoft SQL Server support.

Installing SQL Server – reboot required check fails

Not able to install SqlServer 2008 says Restart computer failed

SQL Server Installation Rules and "System Reboot Required" Error

I get the error message the prerequisites  "Restart the computer is required".

When installing SQL Server some times you may run to situation for "Reboot required check failed" failed issue in prerequisites validation screen.  To skip this failure without restarting machine. 

Goto run and type regedit.exe to open registry 

in registry 

Goto:  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SessionManager , and instead of "EXPANDING" Session Manager, "SELECT" it and you will see the entry called, PENDING FILE RENAME OPERATION 

Take backup of registry value first.

Then, If PENDING FILE RENAME OPERATION has any values just remove it and clear the value

Reboot the System and again go to PENDING FILE RENAME OPERATION and see if there is any values still available.

--

Thanks & Regards
Venkat Sangu

How to check the database size in PDW

Using below DBCC command via NEXUS query editor or SQL Server DATATOOL or Visual Studio

USE DATABASE_NAME

dbcc pdw_showspaceused

You can view the size in Mega Bytes  

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

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