Daily DBA Cheatsheet

1. Creating linked server with local name

EXEC master.dbo.sp_addlinkedserver @server = N'mylocalname', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'servername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'mylocalname,@useself=N'False',@locallogin=NULL,@rmtuser=N'dblink',@rmtpassword='########'

2. SQL Server Data Type Conversion Chart

http://www.microsoft.com/en-us/download/details.aspx?id=35834

3. Filtered Index

I want to create a unique key with multiple null. To fulfil this, I can create unique filtered index.

Create Unique NonClustered Index NonClustomer_Emailaddress on Customer
(emailaddress)where emailaddress is not null

4. sys.processes -  Find how many processes running on a instance

SELECT DB_NAME(dbid) as 'Database Name', COUNT(dbid) as 'Total Connections' FROM master.dbo.sysprocesses WITH (nolock)WHERE dbid > 0GROUP BY dbidSELECT
@@MAX_CONNECTIONS AS 'Max Allowed Connections'

5. Server Edition & Version

select
SERVERPROPERTY('Edition'),
SERVERPROPERTY('ProductLevel'),
SERVERPROPERTY('BUildClrVersion'),
SERVERPROPERTY('ProductVersion')


6. Row Count ( The following code is from http://www.sqlservercentral.com/articles/T-SQL/67624/)

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name, 
 ddps.row_count 
FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 
 AND o.is_ms_shipped = 0 
ORDER BY o.NAME 

7. DBCC

The following DBCC, DMVs and sp commands help me.

dbcc inputbuffer(SPID)

dbcc sqlperf(logspace)

sp_who2

sp_configure

sp_spaceused

select * from sys.sysprocesses

SELECT file_id, name, physical_name, (size * 8 /1024.0) AS SizeMB FROM sys.database_files

select * from sys.masterfiles

msdb.dbo.sp_help_job

exec master.dbo.xp_sqlagent_enum_jobs 1,garbage