SQL email rows not aligning

Question

I have a job setup to email when the disk space falls below 20 %. Right now everything works great except when it comes to the results in the email. I have tried changing the query_result_width to every possible variation. I want to send it in plain text as some of the higher-ups that get the email for some reason only like plain text. The rows look like they are generated on every other line and the results, below is an example of the results.

ServerName                                          Drive
                          Free(MB)

MyServerName                                        C
                          5,468

Should look like:

ServerName                Free(MB)                  Drive

MyServerName              5,468                     C

Below is the script used, any help would be greatly appreciated.

    execute  msdb.dbo.sp_send_dbmail
        @profile_name = 'ProfileName',
        @recipients = '[email protected],
        @subject = 'WARNING: Disk space in one or more drives is below 20 % free space',
        @body_format = 'TEXT',
        @Body = 'These are the servers and drives with low free space',
        @query_result_width = 134,
        @query_result_header = 0,
        @query ='SET NOCOUNT ON;
                    SELECT "ServerName" as [ServerName], "Drive" as [Drive], "Total(MB)" as [Total(MB)], "Free(MB)" as [Free(MB)], "Used(MB)" as [Used(MB)], "Free(%)" as [Free(%)]
                    UNION ALL
                    SELECT [ServerName]
                    ,[Drive]
                    ,[Total(MB)]
                    ,[Free(MB)]
                    ,[Used(MB)]
                    ,[Free(%)]
                FROM [DBA].[dbo].[ServerDriveSpace]
                WHERE  [Free(%)] < 20'

Answer 1

Why don't you just add it as an attachment rather than as text in the email. The size would still be relativly small and it would give a much better appearance which also solves your alignment issue. Something like this....

DECLARE @tab char(1) = CHAR(9)
execute  msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName',
    @recipients = '[email protected]',
    @subject = 'WARNING: Disk space in one or more drives is below 20 % free space',
    @body_format = 'TEXT',
    @Body = 'These are the servers and drives with low free space',
    @attach_query_result_as_file = 1,
    @query_attachment_filename='DiskSpace.csv', 
    @query_result_width = 134,
    @query_result_header = 0,
    @query_result_separator=@tab,
    @query_result_no_padding=1,
    @query ='SET NOCOUNT ON;
                SELECT "ServerName" as [ServerName], "Drive" as [Drive], "Total(MB)" as [Total(MB)], "Free(MB)" as [Free(MB)], "Used(MB)" as [Used(MB)], "Free(%)" as [Free(%)]
                UNION ALL
                SELECT [ServerName]
                ,[Drive]
                ,[Total(MB)]
                ,[Free(MB)]
                ,[Used(MB)]
                ,[Free(%)]
            FROM [DBA].[dbo].[ServerDriveSpace]
            WHERE  [Free(%)] < 20'

Answer 2

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10) apply it(its mostly try and error)