April 22, 2013 at 8:23 am
Great, thanks for the quick reply.
--
Adam Machanic
whoisactive
April 22, 2013 at 3:25 pm
gazzer (4/21/2013)
Hi, thanks a great deal, that helped me out and it is now working.I had originally set-up to use in another Database, but then went with the default, and forgot to change the code.
On another note, I set this up on our production server (both test/live SQL 2008 R2).
when I run on LIVE I get the following error messages.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column \'owner_sid\', table \'msdb.dbo.sysjobs\'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Any ideas?
Thanks again.
I'm not positive this is the issue, but the SQL Jobs are created as user "sa". If that user doesn't exist on your system, you may encounter that error.
April 22, 2013 at 3:26 pm
We updated the script to 2.3.6, it's available for download on the dbWarden Sourceforge page.
Changelog:
Simplified usp_LongRunningQueries to use DMV's to gather session information.
Altered QueryHistory table to accomodate changes in usp_LongRuningQueries
Altered rpt_HealthReport to use new QueryHistory schema
Altered rpt_Queries to use new QueryHistory schema
A release with bug fixes will be along shortly. We are currently addressing the arithmetic error encountered in the usp_FileStats proc which affects usp_Checkfiles and rpt_HealthReport.
April 22, 2013 at 4:17 pm
This is great work! Thank you very much for sharing your work.
I ran into the "Arithmetic overflow error converting expression to data type int" on the dba_CheckFiles job and traced it back to usp_CheckFiles. It was caused by a log file set to grow by 2 GB. I updated the usp_CheckFiles to CAST growth as a BIGINT for the calculation of the FileGrowth column on the #FILESTATS table.
(CASE WHEN SF.[status] & 0x100000 = 0 THEN CONVERT(NVARCHAR,CEILING((CAST(growth AS BIGINT) * 8192)/(1024.0*1024.0))) + '' MB''
Thanks again and keep up the great work.
Tim
April 22, 2013 at 4:24 pm
T_Peters (4/22/2013)
This is great work! Thank you very much for sharing your work.I ran into the "Arithmetic overflow error converting expression to data type int" on the dba_CheckFiles job and traced it back to usp_CheckFiles. It was caused by a log file set to grow by 2 GB. I updated the usp_CheckFiles to CAST growth as a BIGINT for the calculation of the FileGrowth column on the #FILESTATS table.
(CASE WHEN SF.[status] & 0x100000 = 0 THEN CONVERT(NVARCHAR,CEILING((CAST(growth AS BIGINT) * 8192)/(1024.0*1024.0))) + '' MB''Thanks again and keep up the great work.
Tim
Thank you very much for helping find that bug! I'll get an updated version out shortly with that fix.
April 22, 2013 at 8:58 pm
The arithmetic overflow issue is fixed, an updated version (2.3.7) is on the SourceForge page.
April 23, 2013 at 10:40 am
With the latest version, 2.3.7, the Health Report is failing for me with the following error.
Job Name: dba_HealthReport
String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
When running the procedure from the job I get the error below.
EXEC [dbWarden].dbo.rpt_HealthReport @Recepients = NULL, @cc = NULL, @InsertFlag = 1, @IncludePerfStats = 1
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8152, Level 16, State 13, Procedure rpt_HealthReport, Line 955
String or binary data would be truncated.
The statement has been terminated.
This is caused by a long, 146 characters, file name. I'm running this on a dev box that backs up to the default folder.
File name: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SQLAdministration\SQLAdministration_backup_2013_04_23_000001_3038395.bak
I changed the length of the Filename column in the #BACKUPS table in the rpt_HealthReport from NVARCHAR(128) to NVARCHAR(MAX) and it worked great after that.
/* BackupStats */
CREATE TABLE #BACKUPS (
ID INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_BACKUPS
PRIMARY KEY CLUSTERED (ID),
[DBName] NVARCHAR(128),
[Type] NVARCHAR(50),
[Filename] NVARCHAR(MAX),
Backup_Set_Name NVARCHAR(128),
Backup_Start_Date DATETIME,
Backup_Finish_Date DATETIME,
Backup_Size NUMERIC(20,2),
Backup_Age INT
)
Thanks again and the HTML report looks really good.
Tim
April 23, 2013 at 3:01 pm
Thanks Tim, I added in your changes and just posted a 2.3.8 update on the Sourceforge page.
Chanagelog
- QueryHistory table - added Formatted_SQL_Text column. (There are now two columns storing the SQL_Text.
One will show the raw text as it's stored, the new column will show it formatted. For example, If someone is executing a stored procedure, you will see the parameters passed to the proc instead of the CREATE proc syntax like you would in the raw text.)
- usp_LongRunningQueries - Adjusted INSERT based on schema changes to QueryHistory, Added Formatted_SQL_Text.
- rpt_HealthReport - Modified FileName length in #BACKUPS from NVARCHAR(128) to NVARCHAR(255)
April 23, 2013 at 3:26 pm
Looks great Michael, I think my NVARCHAR(MAX) was a little overkill 😛
Tim
April 24, 2013 at 7:40 am
(once again in the right forum)
Hi,
first of all a big "Thank You" for this script!
This is exactly what I'm seraching for.
On the first two server the Reports work fine but the third one has Replication configured and the Report was empty.
I've found two points to correct.
- added some Coalesce in the HTML Creation part of "Replication Publisher"
- there was a warning: Null value is eliminated by an aggregate or other SET operation.
this could be solved by adding an isnull around the Max of ja.start_execution_date and ja.stop_execution_date
One question: the health report is quite long on our server so it would be a good idea to have a quickreport of the changes from one day to another. Do you think this is possible with the data that are collected right now?
Thanks again and best regards,
Volker Bachmann
April 24, 2013 at 8:43 am
Thanks for the fixes, I'll get them added today. As for the paired down report, what did you have in mind for what's displayed? I like the idea. I've been able to generate a couple long ones too, especially with a lot of schema changes...
April 25, 2013 at 12:32 am
for the quick Report:
- databases without backup in the last day
- errors from the sql server log (no warnings and dbcc checkdb without Errors...)
- Connections, Buffer Hit Cache Ratio and CPU Usage is good
maybe configurable via a new settings table? 🙂
Any other suggestions?
Thanks again.
Best Regards,
Volker
April 25, 2013 at 4:11 pm
dbWarden 2.3.9 has been released! A lot of fixes in the health report and reporting of tempDB and Log file growth. Thanks to Volker.Bachmann and Matt Monroe for their work!
Note - There is a new proc, usp_CheckFilesWork. It helps simplify reporting on tempDB and log file growth
Changelog:
- usp_JobStats and rpt_HealthReport - Added COALESCE to MAX(ja.start_execution_date) and MAX(ja.stop_execution_date)
- rpt_HealthReport - Added COALESCE to columns in Replication Publisher section of HTML generation.
- rpt_HealthReport - Added MIN() to MinFileDateStamp in FileStats section
- rpt_HealthReport - Fixed JOIN in UPDATE to only show last 24 hours of Read/Write FileStats
- rpt_HealthReport - Fixed negative file stats showing up when a server restart happened within the last 24 hours.
- rpt_HealthReport - Expanded WitnessServer in #MIRRORING to NVARCHAR(128) FROM NVARCHAR(5)
- usp_CheckFilesWork - New proc - Re-factored code out of usp_CheckFiles
- usp_CheckFiles - Factored out duplicate code into usp_CheckFilesWork
April 25, 2013 at 4:36 pm
I strike the gold today.
Thank you very much for the efforts and sharing with the community. Have been thinking of doing the similar thing but always got pushed out by other things.
April 29, 2013 at 4:54 am
Hello, first of all thanks for this amazing solution.
I'm getting a particular error on one server while trying to run the Health Report:
Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Conversion failed when converting the nvarchar value '9 lockTimeout=' to data type int. [SQLSTATE 22018] (Error 245). The step failed.
Microsoft SQL Server 2005 - 9.00.5000.00 (X64) (SP4)
Any assistance you can provide would be greatly appreciated.
Matt
Viewing 15 posts - 76 through 90 (of 186 total)
You must be logged in to reply to this topic. Login to reply