Ragavachari Barathan

Clear Cache in MS Sql Server

DBCC FREEPROCCACHE

--TO Clear Buffer from Buffer Pool

DBCC DROPCLEANBUFFERS

exec ReindexTables

Memory Details

-Memory Details

============

WITH VAS_Summary

AS ( SELECT Size = VAS_Dump.Size,

Reserved = SUM(CASE ( CONVERT(INT, VAS_Dump.Base) ^ 0 )

WHEN 0 THEN 0

ELSE 1

END),

Free = SUM(CASE ( CONVERT(INT, VAS_Dump.Base) ^ 0 )

WHEN 0 THEN 1

ELSE 0

END)

FROM

(

SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) [Size],

region_allocation_base_address [Base]

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address <> 0x0

GROUP BY region_allocation_base_address

UNION

SELECT CONVERT(VARBINARY, region_size_in_bytes) [Size],

region_allocation_base_address [Base]

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address = 0x0

) AS VAS_Dump

GROUP BY Size

)

SELECT SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB],

CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB]

FROM VAS_Summary

WHERE Free <> 0

TO DISABLE FIBER MODE (LIGHHTWEIGHT POOLING)

--TO DISABLE FIBER MODE (LIGHHTWEIGHT POOLING)

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'lightweight pooling', 0;

RECONFIGURE;

OpenDataSource and OpenRowSet Example

--ACCESS SQL SERVER FROM QUERY ANALYSER

SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=192.168.1.3;User ID=sa;Password=shore').[Save].dbo.Material

--ACCESS MSDB DATABASE

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','D:\pacetam\Dbase\Trn0109.mdb'; 'admin';'',Trn0109)

To enable to access datasource and datarowset

--To enable to access datasource and datarowset using MS Sql Server

=================================================

sp_configure 'show advanced options', 1

RECONFIGURE

sp_configure 'Ad Hoc Distributed Queries', 1

RECONFIGURE

Get Version in MS Sql Server

--Get Version

SELECT SERVERPROPERTY('productversion'),

SERVERPROPERTY ('productlevel'),

SERVERPROPERTY ('edition')

Best Regards,

Ragavachari B

Shrink Database

To shrink database using MS Sql Server

============================

use [db_name]

--Delete data log

Alter database [db_name] set recovery SIMPLE

DBCC SHRINKFILE ( db_name,TRUNCATEONLY)

--To Clear Cached plan