Great script Grant.

Added to my briefcase.

This does the opposite, but could be a good starting point:

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

sp_spaceused

This will tell you how much unallocated space you have in the database.

Any available space in the files?

What's the default schema for the database user?

Ah, yes, I forgot about that option. Nice catch Chris.

It's a complex topic and there is no quick win.

Triggers will likely be a performance disaster, forget about it.

Trace could help, but again could turn into a performance nightmare and/or...

Try to restore them: that's the only way to know for sure. Find a test server (your workstation will do as well) and restore those backups.

There's no way you can get a sensible answer without posting the table definition, including current indexes. Please post the actual execution plan too.

There is no built-in CONVERT style that will handle the format DDMMYYYY. This means that you will have to code it yourself. This should do:

CONVERT(date, RIGHT(Date1,2) + SUBSTRING(Date1, 3,2) +...

Did you check the service status in the Configuration Manager?

"Access denied" is a filesystem error, not a SQL Server error.

If you detached the database, your windows user has exclusive permissions on the MDF and LDF files. Fix that first.

Join sysjobhistory with sysjobs on job_id to get the job name.

You don't want us to do the all the work, do you?

More suggestions:

1) Get rid of those NOLOCK hints: they won't make the query faster, but will potentially return incorrect data (reading rows multiple times or not at all).

2) Are you...

Your Patient table is horribly overindexed. That's what you get when you accept all the recommendations from the database tuning advisor.

I recommend that you look at the utilization of those...