June 14, 2010 at 2:26 am
Hi all, I want to be able to set-up a nightly task which checks the fragmentation levels of all DB's on a particular server and either place them into a table, or better still email the results to myself each morning .
Using SQL Server 2005 by the way if that helps, but being a nnod DBA I don't yet have the skills to create such a task or the code behind it.
Any help would be appreciated.
Cheers
June 14, 2010 at 4:36 am
For fragmentation you can use dm_db_index_physical_stats DMV ( google it for more description).
and for sending mail use sp_send_dbmail ( again same suggestion......google it ). and after that you are find yourself stuck somewhere. then WE ARE HERE 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 14, 2010 at 5:02 am
Cheers thanks for the pointers.
June 14, 2010 at 5:19 am
Hi Ok I have something that kind of works, see below.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#FragDetails]'))
DROP TABLE #FragDetails
USE RotherhamRecruit_Training
GO
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
INTO #FragDetails
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
Begin
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
DECLARE @From varchar(150)
DECLARE @To varchar(150)
DECLARE @cc varchar(150)
DECLARE @vcBody varchar(2000)
DECLARE @CurrDate datetime
DECLARE @MailServerName VARCHAR(100)
DECLARE @BodyType varchar(100)
DECLARE @iMsg int
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(500)
DECLARE @output varchar(1000)
SET @From = 'email here'
SET @To = 'email here'
SET @MailServerName = 'email server here'
SET @MailSubject = 'Fragmentation Levels ' + @@SERVERNAME
SET @BodyType ='HTMLBODY'
SET @AlertMessage = (SELECT * FROM #FragDetails)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @MailServerName
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @MailSubject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, @BodyType , @AlertMessage
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
End
drop table #FragDetails
GO
BUT when running the above I get the following error.
(89 row(s) affected)
Msg 116, Level 16, State 1, Line 31
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Which appreasr to be this line = SET @AlertMessage = (SELECT * FROM #FragDetails)
Any help please.
Cheers
June 14, 2010 at 5:39 am
Hi why cant you use sp_send_dbmail ? anyeways i dont have good hand on
sp_OASetProperty
See if this link can help you http://www.lazydba.com/sql/1__26049.html
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 14, 2010 at 6:26 am
Hi, get the same error using the other method, so it must be something about the sql code.
Every thing else seems to be ok
Cheers
June 14, 2010 at 6:47 am
below is the sql code that can help you
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name ,
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@subject = @subject,
@body = @body,
@body_format = 'TEXT',
@query = @query,
@attach_query_result_as_file = @attach_query_result_as_file,
@query_result_header = @query_result_header
Here in @query parameter , you just need to write your query in single quote ( dont use brackets as you did in your code)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 14, 2010 at 6:49 am
If I am not worng
sp_OASetProperty
will work in 2000 too while sp_send_dbmail is available from sql 2005 onwards. Hence this will have more flexibility with respect to version.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 14, 2010 at 7:19 am
Hi guys, ok here is what I have so far.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'gary.jenkinson01@rotherham.gov.uk',
@subject = 'Fragmentation Results' ;
@query = 'SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 20'
I do however get an error - see below.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@query'.
Am I missing someting here, as the code works correctly if run independatly of the email code?
It does seem such a simple solution if i can get it to work.....
Cheers
June 14, 2010 at 8:48 am
gary.jenkinson01 (6/14/2010)
Hi guys, ok here is what I have so far.EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'gary.jenkinson01@rotherham.gov.uk',
@subject = 'Fragmentation Results' ;
@query = 'SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 20'
I do however get an error - see below.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '@query'.
Am I missing someting here, as the code works correctly if run independatly of the email code?
It does seem such a simple solution if i can get it to work.....
Cheers
Gary,
Try below one. make sure you got 'Administrator' profile configured. Problem was with your query, after @Subject there is " ; " instead of a " , " and also I put 'DETAILED' in two quotes - ''DETAILED''. I tested and worked for me.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'gary.jenkinson01@rotherham.gov.uk',
@subject = 'Fragmentation Results' ,
@query = 'SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''DETAILED'') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 20'
SQL DBA.
June 14, 2010 at 8:55 am
Hi sorry, I had been looking all afternoon, and had sorted it myself, though goodness knows how, as I'm new to all this coding, so many thanks.
Even though the results don't match exactly what I'm seeing when running same code against Db, i'll have another visit later in the week.
Also just another question, if that's ok.
If I wanted to run the above code against a specific DB, where do I place the USE clause.
I.e USE DBName1 then SELECT statement?
Cheers
June 14, 2010 at 11:00 pm
gary.jenkinson01 (6/14/2010)
If I wanted to run the above code against a specific DB, where do I place the USE clause.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'gary.jenkinson01@rotherham.gov.uk',
@subject = 'Fragmentation Results' ,
@query = 'USE DatabaseName ; SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName,indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''DETAILED'') indexstats INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID AND i.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 20'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 15, 2010 at 9:43 am
check out http://ola.hallengren.com/. The script is excellant and if you don't want to actually do the reindexing you can set the appropriate parameter and just use this command in your sp_send_dbmail command to see the output.
Francis
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply