How to find memory available free using SQL Server 2008?

Whenever we get performance issues, we look how much memory is left available for that we have to login Windows box and have to look on task manager.Using SQL Server DMV also we can find. Pls run below query to find out the memory details.

SELECT total_physical_memory_kb/1024 as TotalMemoryMB, available_physical_memory_kb/1024 AvailableMemoryMB,
(total_physical_memory_kb - available_physical_memory_kb)/ 1024  as UsedMemoryMB
FROM sys.dm_os_sys_memory

If you want to find out, how much memory is using each database in SQL Server 2008 running below query you can find out.


SELECT  CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS DatabaseName, cast((count(*) * 8.0)/1024.0 as decimal(10,1)) as UsedMemoryMB 
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY database_id DESC

Clear the Buffers in Database


DECLARE @intDBID INT; SET @intDBID = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE name = 'AdventureWorks'); -- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID); 
dbcc dropcleanbuffers 
dbcc freeproccache

Configure Logshipping for SQL SERVERVER 2008

--LOG SHIPPING USING T-SQL

select @@SERVERNAME

--backup and restore the database from primiry server to secondary server initially

backup database bbaa to disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

restore filelistonly from disk='\\10.50.11.149\Backup\bb\bbbb.bak'

go

--restore the database

restore database finalTest from disk='\\10.50.11.149\Backup\bb\bbbb.bak' with move 'bbbb' to 'L:\aaaabbbb\bbaa.mdf', move 'bbbb_log' to 'L:\aaaabbbb\bbaa.ldf',norecovery

go

--primary --> bbaa

--secondary --> AB

--1) adding primary database 

use master

exec sp_add_log_shipping_primary_database @database='bbaa',

@backup_directory='\\10.50.11.149\Backup\bbSource',

@backup_share='\\10.50.11.149\Backup\bbSource',

@backup_job_name='bbBackup_job',

@backup_compression=1,

@overwrite = 1

go

-- 2) adding job schedule for backup job

use msdb

exec sp_add_jobschedule

@job_name='bbBackup_job',

@name='backupSchedule',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=1

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='bbBackup_job',

@schedule_name='backupSchedule'

go

-- 4) enable/disable

use msdb

exec sp_update_job @job_name='bbBackup_job',

@description='backing up...',

@enabled=1

go

--5) add job alert

use master

go

exec sp_add_log_shipping_alert_job;

--6) adding information about secondary database to primary database

use master

exec sp_add_log_shipping_primary_secondary @primary_database='bbaa',

@secondary_server='BETASQL2K8',

@secondary_database='finalTest'

go

----- primary over ----

--start secondary

--1) adding details about primary server in secondary server

use master

exec sp_add_log_shipping_secondary_primary @primary_server='BETASQL2K8',

@primary_database='bbaa',

@backup_source_directory='\\10.50.11.149\Backup\bbSource',

@backup_destination_directory='\\10.50.11.149\Backup\bbDestination',

@copy_job_name='copyJob1',

@restore_job_name='restoreJob1',

@overwrite = 1

go

--2) adding job schedule for copy job

use msdb

exec sp_add_jobschedule

@job_name='copyJob1',

@name='copylog',

@freq_type=4,

@enabled=1,

@freq_interval=1,

@freq_subday_type=0x4 ,

@freq_subday_interval=2

go

-- 3) attaching schedule

use msdb

exec sp_attach_schedule @job_name='copyJob1',

@schedule_name='copylog'

go

--4) adding job schedule for restore job

use msdb

exec sp_add_jobschedule

@job_name='restoreJob1',

@name='restorejob',

@enabled = 1,

@freq_type = 4,

@freq_interval = 1 ,

@freq_subday_type = 0x4,

@freq_subday_interval = 5

go

-- 5) attaching schedule

use msdb

exec sp_attach_schedule @job_name='restoreJob1',

@schedule_name='restorejob'

go

--6) adding secondary database

use master

exec sp_add_log_shipping_secondary_database @secondary_database='finalTest',

@primary_server='BETASQL2K8',

@primary_database='bbaa',

@overwrite = 1,

@restore_mode=1,

@disconnect_users=1

go

--7) update copy and restore jobs

use msdb

exec sp_update_job @job_name='copyJob1',

@description='copying...',

@enabled=1

go

use msdb

exec sp_update_job @job_name='restoreJob1',

@description='restoring...',

@enabled=1

go

--startind job (backup job)

use msdb

exec sp_start_job @job_name='bbBackup_job'

go

SELECT @@version

Configuring Replication From Sql Server 2008.

--enabling replicatio for a database

use [AVehicles]

select * from cycles

exec sp_replicationdboption @dbname='AVehicles',

@optname='publish',

@value='true'

go

--adding a publication

use [AVehicles]

exec sp_addpublication @publication='final_pub',

@status='active',

@allow_pull='true',

@allow_push='true',

@independent_agent='true',

@immediate_sync='true',

@allow_sync_tran='true'

go

--adding article

use [AVehicles]

exec sp_addarticle @publication='final_pub',

@article='ar3',

@source_object='cycles',

@pre_creation_cmd='drop',

@force_invalidate_snapshot=1

go

--adding subscription

use [AVehicles]

exec sp_addsubscription @publication='final_pub',

@subscriber='BETASQL1',

@destination_db='Database_Pub',

@subscription_type='push'

go

--adding publication snapshot

use [AVehicles]

exec sp_addpublication_snapshot @publication='final_pub'

go

--starting snapshot

use [AVehicles]

exec sp_startpublication_snapshot @publication='final_pub'

go

--adding rows to articles

select @@SERVERNAME

sp_helpsubscription