Category: DB Articles

Important Trace Flags for DBA
A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server.

You can enable the trace flag from Startup Parameters, Session and Global Session level.
```
DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global
```
Trace Flag 610 : Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading
Trace Flag 4199 : enable query optimizer fixes
Trace Flag 1222 : writes the deadlock information in XML format into error log
Trace flag 835 : Enable the Lock Pages in memory for standard edition
Trace flag 1118 : Directs SQL Server to allocate full extents to each tempDB objects
Trace flag 1204 : Write information about the deadlock in text format
Trace flag 1211 : Disable the lock escalation based on the memory pressure
Trace flag 3226 : Prevents successful backup operation being logged
Trace flag 3014 : Returns more information to error log about backup operation
Trace flag 3502 : Write information about the checkpoint in the error log
Trace flag 3505 : Disables automatic checkpoint
Trace flag 3004 : Returns more information about the instant file initialization
Trace flag 1806 : Disables the instant file initialization
February 5, 2013
Get the List Of DBCC Commands & Syntax
If you don’t remember all the DBCC commands “NO Issue” execute the DBCC HELP command to get the list of all the DBCC.
```
DBCC HELP('?')
```
checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checktable
cleantable
dbreindex
dropcleanbuffers
free
freeproccache
freesessioncache
freesystemcache
help
indexdefrag
inputbuffer
opentran
outputbuffer
pintable
proccache
show_statistics
showcontig
shrinkdatabase
shrinkfile
sqlperf
traceoff
traceon
tracestatus
unpintable
updateusage
useroptions

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Don’t remember syntax, execute below command to get the syntax.
```
DBCC HELP('checktable')
```
January 8, 2013
Script to get the SQL Agent Properties

You can use the sp_get_sqlagent_properties undocumented stored procedure to retrieve the SQL Agent properties of a SQL Instance. It is available in SQL Server MSDB database. This procedure only works if the SQL Agetn Service is started.

January 8, 2013
Script to check – Login Name and Password are same
It is always a risk if the user name & password is equal. You can run the below script to find out the list of User/Login name & password which are equal.
```
select  
cast(@@SERVERNAME as varchar(150)) as SQLInstanceName
,name as [LoginName]
,'Password is same as Login Name' [Description]
from sys.syslogins
WHERE PWDCOMPARE (name,password) = 1
```
November 27, 2012
Steps to add Log Shipping monitor into an existing SQL Server

Problem
I have a requirement to add the Log Shipping Monitor for an existing installation. I have heard you can only complete this by rebuilding the Log Shipping infrastructure. Is that true? Are there any other options? In this tip I will explain how we can add the Log Shipping monitor to a SQL Server 2005, 2008, 2008 R2 or 2012 environment without rebuilding the Log Shipping installation.

Solution
http://www.mssqltips.com/sqlservertip/2799/steps-to-add-log-shipping-monitor-into-an-existing-sql-server/

November 16, 2012