NetManaged
asked on
MS SQL 2008 DB Size Report for SpaceUsed in a DB file
Here is what I have so far that works.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@somebody.co m,
@query = 'USE TopsData
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;',
@subject = "Error Log",
@attach_query_result_as_fi le = 1
I want a 3rd result that tells me the SpaceUsed in the DB mdf file (CurrentSizeMB - FreeSpaceMB = UsedSpaceMB) without the transaction log data.
Soo...it will look something like below:
CurrentSizeMB FreeSpaceMB UsedSpaceMB
TIA
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@somebody.co
@query = 'USE TopsData
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;',
@subject = "Error Log",
@attach_query_result_as_fi
I want a 3rd result that tells me the SpaceUsed in the DB mdf file (CurrentSizeMB - FreeSpaceMB = UsedSpaceMB) without the transaction log data.
Soo...it will look something like below:
CurrentSizeMB FreeSpaceMB UsedSpaceMB
TIA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
EvilPostit,
NM, put it down to user error during copy/paste process.
Works great!!!
Thanks!
Greg
NM, put it down to user error during copy/paste process.
Works great!!!
Thanks!
Greg
ASKER
Using your first example (only modifed the e-mail address before the .com), I recieved the below result in the SSMS 2008 Messages pane:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'SpaceUsed'.
Msg 105, Level 15, State 1, Line 10
Unclosed quotation mark after the character string ',
@subject = "Error Log",
@attach_query_result_as_fi
Editing your first example to the below using only single quotes for SpaceUSed (ie. 'SpaceUsed'. The query editor was claiming incorrect syntax with two single quotes [''SpaceUSed''] and using double-quotes resulted in invalid column name), Looks like this:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'administrator@somebody.co
@query = 'USE TopsData
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB,
CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS SpaceUsed
FROM sys.database_files;',
@subject = "Error Log",
@attach_query_result_as_fi
This resulted in the following when running the query:
Msg 105, Level 15, State 1, Line 10
Unclosed quotation mark after the character string ',
@subject = "Error Log",
@attach_query_result_as_fi
Suggestions?