Send mail job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Send mail job

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-04 : 09:12:59

Hello guys, need more help from your side
I need to find the following thing on database on weekly basis and it should have to create one txt file and mailed to team

My requirement is to find
database size
Current_date,database_name, data_allocated, data_used, log_allocated,
(log_pc_used * log_allocated / 100) as 'log_used'

disk space

Current_Date, Drive, TotalMb, (TotalMb - FreeMb) as 'UsedMb', FreeMb

it should have to genrate one text file which will always change date.

Please help.

Yogesh V. Desai. | SQLDBA|

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 09:21:47

cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88840

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 09:23:41

[code]DBCC UpdateUsage(0) WITH NO_INFOMSGS

DECLARE @DbSize BIGINT,
@LogSize BIGINT,
@ReservedPages BIGINT

SELECT @DbSize = SUM(CONVERT(BIGINT, CASE WHEN Status & 64 = 0 THEN Size ELSE 0 END)),
@LogSize = SUM(CONVERT(BIGINT, CASE WHEN Status & 64 = 0 THEN 0 ELSE Size END))
FROM dbo.SysFiles

SELECT @ReservedPages = SUM(a.Total_Pages)
FROM sys.Partitions AS p
INNER JOIN sys.Allocation_Units AS a ON p.Partition_ID = a.Container_ID
LEFT JOIN sys.Internal_Tables AS it ON p.Object_ID = it.Object_ID

SELECT DB_NAME() AS DbName,
LTRIM(STR(8192.0 * (@DbSize + @LogSize) / 1048576.0, 15, 2) + ' MB') AS DbSize,
LTRIM(STR(CASE WHEN @DbSize >= @ReservedPages THEN 8192.0 * (@DbSize - @ReservedPages) / 1048576.0 ELSE 0.0 END, 15, 2) + ' MB') AS [Unallocated space][/code]

E 12°55'05.25"
N 56°04'39.16"

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-05 : 07:23:40

Hi getting the following error messages.

Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.Partitions'.
Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.Allocation_Units'.
Msg 208, Level 16, State 1, Line 9
Invalid object name 'sys.Internal_Tables'.

Yogesh V. Desai. | SQLDBA|

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources