SSIS GET DATE WITH OUT TIME

(DT_WSTR,30)(DT_DBDATE)(dateadd("d",-1,getdate()))

How to find how Cache/Bpool is being used and what objects are using the cache/Bpool

SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

Shrink Log File

USE OutboundScripts
GO
ALTER DATABASE OutboundScripts SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(OutboundScripts_log, 1)
ALTER DATABASE [OutboundScripts] SET RECOVERY FULL WITH NO_WAIT
GO

Find Indexes Not In Use

SELECT
o.name
, indexname=i.name
, i.index_id  
, reads=user_seeks + user_scans + user_lookups  
, writes =  user_updates  
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
    WHEN s.user_updates < 1 THEN 100
    ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
  END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id  
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()  
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

The ALTER TABLE Statement

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name

ALTER TABLE table_name
ALTER COLUMN column_name datatype

DBF Query

Select *
FROM openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;     
SourceDB=\\ordentry\PI-2000\ORDERV2\1670;SourceType=DBF','select * from ORDERS ')

SQL SERVER – Shrinking Truncate Log File – Log Full

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Table Size Query

The following query lists the tables and the space they use. This query 
is much faster (sub-second) than a standard SELECT COUNT(*) query since 
it uses the dynamic management views in SQL Server rather than scanning 
your data.

SELECT sum ( used_page_count ) * 8 as SizeKB,
  sum(row_count) as [RowCount], object_name ( object_id ) AS TableName
FROM sys.dm_db_partition_stats
WHERE index_id=0 or index_id=1
GROUP BY object_id
ORDER BY sum ( used_page_count ) DESC;

SQL SERVER – Find Last Date Time Updated for Any Table

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks')