March 31, 2015 at 11:51 am
Hi All,
when I run the below query I get results of Page Density. Now I created a Job and I want the output to be emailed to me and get it written to the Disk.
I created a .txt file on the server and I selected Outputfile to point to that text file and also checked append out put to existing file.
After I run the Job when I open the text file I am not seeing anything written to the Disk
CREATE TABLE #CONTIGTEMP (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
go
SET NOCOUNT ON
-- populate #CONTIGTEMP by running DBCC SHOWCONTIG with TABLERESULTS output
INSERT #CONTIGTEMP
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES')
go
PRINT ' '
(SELECT
LEFT(ObjectName,30) "Table Name",
LEFT(IndexName,30) "Index Name",
CountPages "Page Count",
ScanDensity "Scan Density %"
FROM #CONTIGTEMP
WHERE ObjectName NOT LIKE 'dt%' AND
ObjectName NOT LIKE 'sys%' AND
ObjectName NOT IN (select objectname from #CONTIGTEMP where indexid = 0)
AND ScanDensity <80 and
countpages > 1000)
UNION ALL
(SELECT
LEFT(ObjectName,30) "Table Name",
LEFT(IndexName,30)"Index Name",
CountPages "Page Count",
ScanDensity "Scan Density %"
FROM #CONTIGTEMP
WHERE ObjectName NOT LIKE 'dt%' AND
ObjectName NOT LIKE 'sys%' AND
ObjectName in (select objectname from #CONTIGTEMP where indexid = 0)
AND INDEXID <>0
AND ScanDensity <80 AND
countpages > 1000)
ORDER BY LEFT(ObjectName,30), ScanDensity ASC
go
DROP TABLE #CONTIGTEMP
I want the out put to be emailed as well I used
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileLily',
@recipients = 'Lily@Coffee.com',
@subject = 'Server -Fragmentation Check',
@query = ' Above Sql '
It doesnt work. How can I fix these issues?
April 3, 2015 at 10:42 am
Out Put file is working. It is Permissions issue.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply