Automate DMV capture report

• sqlcool

  SSC Eights!

  Points: 967

  More actions

  June 27, 2010 at 7:09 pm

  #141138

  Hi there

  I need to automate the following and send email to our team periodically (as per schedule)

  the format can be in a .csv file

  Schedule :- Every monday 10.00AM - 10.10AM

  I need to capture these info from DMV, when the scheduled(sql-job) runs

  1) Unused indexes

  2) Top 10 long running queries (CPU usage)

  3) Table size report

  Any ideas how to automate this? Do i need to write some T-SQL code or is there any setting in SQL-Server 2005 or 2008?

  Cheers

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 27, 2010 at 11:36 pm

  #1185511

  Is reporting services available?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  June 27, 2010 at 11:39 pm

  #1185514

  sqlcool (6/27/2010)

  ---

  Any ideas how to automate this? Do i need to write some T-SQL code or is there any setting in SQL-Server 2005 or 2008?

  USe sp_send_dbmail including Query parameter.

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  June 29, 2010 at 1:19 am

  #1186185

  What about job scheduled to run at the required time interval. The job can call a procedure that can collect the required data and also send the mail.

  "Keep Trying"