YourSqlDba
Sources for YourSqlDba : Database maintenance solution that installs as a single SQL script
Install / Use
/learn @pelsql/YourSqlDbaREADME
YourSqlDba
Contact info Maurice Pelchat
To get the script of the most recent release of YourSqlDba - Go to Version History to get a specific script version and see details about changes between versions.
To display currently installed release of YourSqlDba, execute this query:<br/> select * from Install.VersionInfo()
Everything about YourSqlDba can be found in this OneNote Online documentation,
which requires nothing more than a web browser to navigate.
Pay attention to the landing page and the section describing what YourSqlDba does and how it works.
There is also, on the landing page, a QuickLinks table referencing frequently read and relevant pages.
YourSqlDba operates exclusively through SQL Agent jobs and Database Mail, both of which need to be configured. A helper stored procedure must be executed (see
Goals/QuickLinks table/Install.InitialSetupOfYourSQLDba) once per instance after downloading and running the YourSqlDba script. This procedure provides the necessary parameters to set up Database Mail, backup directories, and default behaviors. It also creates two SQL Agent jobs and schedules them to run as required. Future version updates do not require re-running this procedure.
Each of the two jobs has a single maintenance step. Both call the same main stored procedure (see
Goals/QuickLinks table/Maint.YourSQLDba_DoMaint) with different parameters depending on the job type. These parameters reflect some of those defined during installation (seeGoals/QuickLinks table/Install.InitialSetupOfYourSQLDba) and include many default values.Maint.YourSQLDba_DoMaintparameters are explained in detail in the online documentation.
YourSqlDba is essentially a large T-SQL script that automates database maintenance tasks for SQL Server. It creates, on the instance where it runs, a database named YourSqlDba packed with T-SQL modules (functions, stored procedures, and views). You do not need to be concerned with all of them, though some are useful tools for occasional day-to-day DBA work beyond regular maintenance.
Version history
Version 7.1.0.6
Fix for backup names too large for the name parameter of the backup command
Version 7.1.0.5
The yyyy-mm-dd date format used in the CREATE CREDENTIAL instruction was not universally supported. When connections language setting is configured for French, it caused an installation error. The date format YYYYMMDD that is now used is no more dependent on the connection language settings.
In the function that generates backup command, the parameter NoInit was mistyped for NoInt, causing error in log backup commands.
Version 7.1.0.4
If you got script 7.1.0.3 re-apply this script, as the provider upgrade of YourSqlDba "Mirror server" may be incorrect.
Fix for the small number of users who use group Managed Service Account to run the Database Engine service.
This type of account slightly reduces the rights granted to the relational engine for disk access. As a result, creating an assembly directly from its DLL file can fail.
A valid alternative is to import the binary content into SQL Server and create the assembly from that binary content.
Force Upgrade to MSOLEDBSQL provider When YourSqlDba "Mirroring" servers use the old "SQLNCLI" provider, they are forcefully deleted to be asked to be recreated with Mirroring.AddServer, which will then use the MSOLEDBSQL provider.
Doc reference on how to do a Mirroring.AddServer (ignore the error "cannot add YourSqlDbaRemoteServerCred because it already exists"
Code maintenance was also done to improve clarity. No features were added or changed.
Some tooling was added to help with code maintenance. Useful only for YourSqlDba maintainers. A permanent bookmark system is now used, based on special comments in the code of the form. The comment is the name of the bookmark.
-- @@MARK: Some comment explaining the purpose of this section of code
To add this tooling, in SSMS 22.3 or above (not tested in previous versions) goto Tools/External Tools, click add:
Via Tools/External Tools
|To Complete | Sample Value | |---|---| |Title |Goto-Mark | |Command |C:\Program Files\PowerShell\7\pwsh.exe | |Arguments |-NoProfile -ExecutionPolicy Bypass -File "C:\Github\YourSqlDba\Goto-Mark.ps1" "$(ItemPath)" "$(ItemFileName)" | |Initial directory |$(ItemDir) |
The PowerShell script (this tool) scans the current source, builds a table of these marks, and displays it in a grid window.
You can scroll through the list or search for a specific string. Once an item is selected, click OK. No text must be selected when doing so.
These comments highlight the architectural elements of YourSqlDba. Reading them helps provide an overview of the project. They also make it easier to locate those elements in YourSqlDba, which is a very large script.
Version 7.1.0.2
In mirroring mode, restore could block log backups. Added internal locking to prevent this.
Get script for version 7.1.0.2
Version 7.1.0.1
The restore queue is now cleared between full-backup executions. This change ensures that the full-maintenance job no longer repeatedly reports leftover error entries from previous maintenance cycles. At the end of a cycle, the job performs a final check but intentionally does not remove any queued items that are in an error state. Instead, YourSqlDba sends a message instructing the user to query the queue (via a SELECT statement) to identify failed restores; those entries are then removed at the start of the next maintenance run.
Get script for version 7.1.0.1
Version 7.1
This version achieves a long-sought goal: removing all external assembly dependencies from YourSqlDba. The script now builds its own assemblies from C# source code defined inside an inline table-valued function (iTvf). Since the script itself compiles and creates the assembly, it also signs it automatically — no binaries are imported from untrusted sources.
By enabling the script to compile, deploy, and secure the assembly autonomously, YourSqlDba takes a major step toward self-containment. This capability is derived from portions of my own library, S# (not yet published on GitHub). That library allows C# source code to be embedded directly within an inline function definition, enabling a complete set of T-SQL commands to create the assembly and expose its SQLCLR entry points in SQL Server.
Special thanks to Solomon Rutzky (srutzky@gmail.com) for his insights on assembly and module security, which helped finalize the design by adding a signature at creation time. Now, every DBA can review the relatively straightforward C# code without the risk of executing unsigned assemblies, significantly improving the overall security of YourSqlDba.
Another important benefit is that the database no longer needs to be set as TRUSTWORTHY, further increasing security.
This improvement was made possible by removing all reliance on Service Broker for mirror server operations.
Previously, Service Broker was used to provide a background thread for running restores in parallel with backups.
It has now been replaced with an automatically created, standalone SQL Agent YourSqlDba task dedicated to this purpose.
That task starts automatically when backups complete and stops itself five minutes after finishing the restoreQueue processing.
Version 7.1 lays the foundation for the new architecture of YourSqlDba, introducing these components gradually so the original and new architectures can coexist without compromising code quality. Upgrading is strongly recommended, especially for its security improvements.
With version 7.0, YourSQLDba.Maint.HistoryView (see Goals/QuickLinks table/Maint.HistoryView (V 7.0+)) received several improvements that enhance the visualization of multi-job interactions.
Events within a selected period are now displayed in chronological order and show simultaneous job activity.
Each time the log history switches jobs, columns indicating job lineage are highlighted to make these transitions easily identifiable.
`YourSQLDba.Maint.
