SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_perfmon]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_perfmon]
GO

Create procedure usp_perfmon @Applicationlogpath varchar(600) = 'd:\PerfMon.LOG'
as
--Objective: TO gather information from sysperfinfo table
--Created by: MAK
DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)
declare @size int
declare @fileexist int
declare @error varchar(1000)
declare @Cachehitratio varchar(100)
declare @SQLTargetMemory varchar(100)
declare @SQLTotMemory varchar(100)
declare @Freepages varchar(100)
declare @PageSplits varchar(100)
declare @transactions varchar(100)
declare @userconnections varchar(100)
declare @deadlocks varchar(100)
declare @date varchar(25)
declare @lockwait varchar(100)
declare @compilation varchar(100)
declare @totaldatagrowth  varchar(100)
declare @totalloggrowth  varchar(100)
declare @batchrequest  varchar(100)

set @Cachehitratio =''
set @SQLTargetMemory =''
set @SQLTotMemory =''
set @Freepages =''
set @PageSplits =''
set @transactions=''
set @userconnections =''
set @deadlocks =''
set @date =''
set @lockwait =''
set @compilation=''
set @totaldatagrowth =''
set @totalloggrowth =''
set @batchrequest =''

set @date =convert(varchar(25),getdate(),109)
--Cache hit ratio
SELECT @value = cntr_value FROM sysperfinfo (nolock)  WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM sysperfinfo (nolock)  WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= convert(varchar(100), (@value / @basevalue) *100)
--Free Pages
SELECT @value = cntr_value FROM sysperfinfo (nolock)  WHERE counter_name = 'Free Pages' and 
object_name = 'SQLServer:Buffer Manager'
set @Freepages= convert (varchar(100),@value )
--SQL Memory
select @value =cntr_value/1024  from sysperfinfo (nolock)  where object_name ='SQLServer:Memory Manager' and
counter_name ='Total Server Memory (KB)'
set @SQLTotMemory=convert(varchar(100),@value) 
select @basevalue=cntr_value/1024   from sysperfinfo (nolock)  where object_name ='SQLServer:Memory Manager' and
counter_name ='Target Server Memory(KB)'
set @SQLTargetMemory=convert(varchar(100),@basevalue) 
--Page Splits
select @value =cntr_value  from sysperfinfo (nolock)  where object_name ='SQLServer:Access Methods' and
counter_name ='Page Splits/sec'
set @PageSplits=convert(varchar(100),@value )
--# of transactions

select @value = cntr_value  from sysperfinfo (nolock)  where object_name = 'SQLServer:Databases'
and counter_name ='Transactions/sec' and instance_name ='_Total'
set @transactions= convert(varchar(100),@value )

--User Connections
select @value = cntr_value  from sysperfinfo (nolock)  where object_name = 'SQLServer:General Statistics'
and counter_name ='User Connections' 
set @userconnections=convert(varchar(100),@value )

--Deadlocks
select @value = cntr_value  from sysperfinfo (nolock)  where object_name = 'SQLServer:Locks'
and counter_name ='Number of Deadlocks/sec' 
set @deadlocks = convert(varchar(25),@value)

--Lock waits/sec

select @value= cntr_value  from sysperfinfo (nolock)  where object_name = 'SQLServer:Locks'
and counter_name ='Average Wait Time (ms)' 
set @lockwait = convert(varchar(25),@value)

--Database dataFile growth
select @value = cntr_value/1024 from sysperfinfo (nolock)  where object_name ='SQLServer:Databases' and
counter_name ='Data File(s) Size (KB)'  and instance_name ='_Total'
set @totaldatagrowth = convert(varchar(100),@value)

--Database Log File growth
select @value = cntr_value/1024 from sysperfinfo (nolock)  where object_name ='SQLServer:Databases' and
counter_name ='Log File(s) Size (KB)'  and instance_name ='_Total'
set @totalloggrowth =  convert(varchar(100),@value)

--declare @LogMB numeric(25, 2)
select @value = cntr_value from sysperfinfo (nolock)  where object_name ='SQLServer:SQL Statistics' and
counter_name ='Batch Requests/sec'  
set @batchrequest = convert(varchar(100),@value)

--Compilations/sec
select @value= cntr_value from sysperfinfo (nolock)  where object_name ='SQLServer:SQL Statistics' and
counter_name ='SQL Compilations/Sec'  
set @compilation= convert(varchar(100),@value)

--select distinct object_name,counter_name  from sysperfinfo

--select * from sysperfinfo
/*
print 'Cachehitratio'+ ','+
'SQLTargetMemory'+  ','+
'SQLTotMemory' + ','+
'Freepages' + ','+
'PageSplits' + ','+
'transactions'+  ','+
'userconnections' + ','+
'deadlocks' + ','+ 
'lockwait ' + ','+ 
'compilation' + ','+ 
'totaldatagrowth ' + ','+ 
'totalloggrowth ' + ','+ 
'batchrequest ' + ','+ 
'date '

print 
@Cachehitratio + ','+
@SQLTargetMemory+  ','+
@SQLTotMemory + ','+
@Freepages + ','+
@PageSplits + ','+
@transactions+  ','+
@userconnections + ','+
@deadlocks + ','+ 
@lockwait + ','+ 
@compilation+ ','+ 
@totaldatagrowth + ','+ 
@totalloggrowth + ','+ 
@batchrequest + ','+ 
@date 
*/
create table #fileexist (fileexist int, directory int, parent int)
insert #fileexist exec xp_fileexist @Applicationlogpath
select @fileexist = fileexist from #fileexist 
if @fileexist <> 1
begin
set @error = 'Cachehitratio'+ ','+'SQLTargetMemory'+  ','+'SQLTotMemory' + ','+
'Freepages' + ','+'PageSplits' + ','+'transactions'+  ','+'userconnections' + ','+
'deadlocks' + ','+ 'lockwait ' + ','+ 'compilation' + ','+ 'totaldatagrowth ' + ','+ 
'totalloggrowth ' + ','+ 'batchrequest ' + ','+ 'date '
EXEC master..Usp_WriteToFile @Applicationlogpath, @error

set @fileexist =1
end


create table #filedetails (name varchar(100),size int, date1 varchar(20),
time varchar(20), date2 varchar(10), time2 varchar(20), date3 varchar(20), time3 varchar(20),attr int)
insert #filedetails exec xp_getfiledetails @applicationlogpath

select @size =size from  #filedetails 

if @size <10
begin
set @error = 'Cachehitratio'+ ','+'SQLTargetMemory'+  ','+'SQLTotMemory' + ','+
'Freepages' + ','+'PageSplits' + ','+'transactions'+  ','+'userconnections' + ','+
'deadlocks' + ','+ 'lockwait ' + ','+ 'compilation' + ','+ 'totaldatagrowth ' + ','+ 
'totalloggrowth ' + ','+ 'batchrequest ' + ','+ 'date '
EXEC master..Usp_WriteToFile @Applicationlogpath, @error
end

set @error =@Cachehitratio + ','+@SQLTargetMemory+  ','+@SQLTotMemory + ','+@Freepages + ','+
@PageSplits + ','+@transactions+  ','+@userconnections + ','+@deadlocks + ','+ 
@lockwait + ','+ @compilation+ ','+ @totaldatagrowth + ','+ @totalloggrowth + ','+ 
@batchrequest + ',"'+ @date +'"'

EXEC master..Usp_WriteToFile @Applicationlogpath, @error




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO