Hello,
New install here. The Sqlwatch disk utilization job fails on step 3 every time.
running exec dbo.usp_sqlwatch_logger_disk_utilisation_table alone, shows me a list of databases it worked on, and then a failure after the last database (alphabetically) in our server.
Processing database: [dbname]
Executing command for database: [dbname]
Processed database: [dbname], @snapshot_type_id: 22. Invoked by: [usp_sqlwatch_logger_disk_utilisation_table], time taken: 219ms
Processing database: [SQLWATCH]
Executing command for database: [SQLWATCH]
Processed database: [SQLWATCH], @snapshot_type_id: 22. Invoked by: [usp_sqlwatch_logger_disk_utilisation_table], time taken: 187ms
Processing database: [tempdb]
2F9BBB27-3606-4166-B699-F794140711C7 Database (tempdb) excluded from collection (snapshot_type_id: 22) due to global exclusion.
Processing database: [lastdbname]
Executing command for database: [lastdbname]
Processed database: [lastdbname], @snapshot_type_id: 22. Invoked by: [usp_sqlwatch_logger_disk_utilisation_table], time taken: 391ms
Msg 50000, Level 16, State 1, Procedure dbo.usp_sqlwatch_internal_foreachdb, Line 168 [Batch Start Line 0]
Errors during execution (usp_sqlwatch_internal_foreachdb)```
Thoughts?
running it with the debug parameter set, everything seems fine to the end, then it croaks on calling the foreachdb procedure.
Processed database: [last db name], @snapshot_type_id: 22. Invoked by: [usp_sqlwatch_logger_disk_utilisation_table], time taken: 157ms
Msg 50000, Level 16, State 1, Procedure dbo.usp_sqlwatch_internal_foreachdb, Line 168 [Batch Start Line 2]
Errors during execution (usp_sqlwatch_internal_foreachdb)
I’ve realized the issue comes down to all my databases using SQL_Latin1_General_CP1_CS_AS however the server default is a case insensitive collation.
I’m not having much luck getting sqlwatch to install given this. I tried manually creating the SQLWATCH database with the matching SQL_Latin1_General_CP1_CS_AS collation, but then the dacpac wouldn’t install if the sqlwatch database was already found.
I pulled the source code into VS, but am not very savvy in Visual Studio, and am not finding any success on what things need changing to the SQL_Latin1_General_CP1_CS_AS collation so it can then be deployed.
Hi Andy, do you want to drop me an email, I will help you solve it ( I don’t know what the solution is but I will work with you, once we fixed it, I will update this thread for others)
Sure thing, thanks! Maybe we’ll end up with some decent instructions for other folks that have case sensitive databases! (Modifying the dacpac to allow for the sqlwatch database to already exist was a bit over my head, but then I wonder if the other scripts and stored procedures detect the collation of the sqlwatch database and “inherit” that, or just check the default for the whole server which doesn’t always represent the databases.
I dropped you a direct message.