List of SQL Server instances currently installed in your network

To get a list of SQL Server instances currently installed in your network, execute either of the below commands

Exec xp_cmdshell 'SQLCMD /L'
OR
Exec xp_cmdshell 'OSQL /L'


If you want to populate a text file with the results then, open the command prompt (Go to Run, Type cmd and click ok) and type this command and hit enter
SQLCMD /L > C:\ListOfServers.txt
This command will populate the text file ListOfServers.txt with the results obtained from SQLCMD /L

Identity Column

We all know about setting Identity to a column of a table.
By setting identity to a column, we mean that the column should be populated automatically by incrementing numbers based on the seed and increment option we give.
But all these days, i was setting the identity to a column of a table which had more than 2 columns.
Just think of having a table with only one column and setting identity to that Column.

Create Table IdentityTest
(Slno int identity(1,1))

When you run this query, it will create a table named IdentityTest with the column Slno with identity on it.
What next????
The next question would be, how to insert data to this table?
Any idea, on how to insert data to this table?
Here is how you insert data to this table..

Insert Into IdentityTest Default Values

And now after reading this post, you are now familiar with inserting data to a table which has only one column and identity set on it.  :)

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

1. Maintenance

2. Miscellaneous

3. Informational

4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC SHRINKFILE

DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:

DBCC dllname (FREE)

DBCC HELP

DBCC FREESESSIONCACHE 

DBCC TRACEOFF

DBCC FREESYSTEMCACHE

DBCC TRACEON

Some of the Informational DBCC Statements are:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS  

DBCC CHECKTABLE

DBCC CHECKDB

Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL

Index Recommendations

This Query when executed gives the recomendation to your indexes whether it has to reindexed or defraged.
This query also gives the fragmentation level of your indexes.

Select db_name(DB_ID()) as DBName,SS.name as SchemaName,SO.name as TableName, SI.name as Indexname,index_type_desc as IndexType ,avg_fragmentation_in_percent as FragmentationPercentage,
(case when avg_fragmentation_in_percent between 10 and 30 then 'Defrag'
when avg_fragmentation_in_percent > 30 Then 'Reindex'
Else 'Can be Ignored Currently'
End) as Recomendation
from sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), null, null, null, 'DETAILED') IPS ,
sys.indexes SI ,sys.objects SO , sys.schemas SS
where IPS.index_id=SI.index_id and IPS.object_id=SI.object_id and
SI.object_id=SO.object_id and SO.schema_id=SS.schema_id and
IPS.index_type_desc in ('NONCLUSTERED INDEX', 'CLUSTERED INDEX')
Order by Recomendation desc

SQL Server Performance Tuning Checklist

It's our duty as a DBA to ensure our SQL Server databases and their applications run at their full potential.
To achieve this we have to take care of our databases. In this process, Performance Tuning plays a very major role and slightly difficult to manage. It will be easy if we have a checklist which will be help us making our jobs easy.

Here is a checklist which conatins various parameters which we can look at when we are doing performance tuning on the SQL Server databases.

This list can be downloaded from here (Office 2007 Format).
For Office 2003 Format, Click Here.