Learn SQL Server

Take Full Backups Of The System

Here's a T-SQL script that will create a SQL Agent job and perform a full backup of each database on the instance:

USE msdb
GO

-- Create SQL Agent job to backup all databases
DECLARE @jobId binary(16)
EXEC sp_add_job @job_name = 'Backup All Databases',
	@enabled = 1,
	@description = 'Job to backup all databases on the instance',
	@job_id = @jobId OUTPUT

-- Add a job step to perform a full backup of each database
DECLARE @dbName sysname
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
	DECLARE @backupPath nvarchar(255)
	SET @backupPath = 'C:\Backup\' + @dbName + '_' + CONVERT(varchar(8), GETDATE(), 112) + '.bak'

	DECLARE @jobStepId int
	EXEC sp_add_jobstep @job_id = @jobId,
		@step_name = 'Backup ' + @dbName,
		@subsystem = 'TSQL',
		@command = 'BACKUP DATABASE ' + @dbName + ' TO DISK = ''' + @backupPath + '''',
		@on_success_action = 1,
		@on_fail_action = 2,
		@job_step_id = @jobStepId OUTPUT

	FETCH NEXT FROM db_cursor INTO @dbName
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- Schedule job to run daily at 1am
EXEC sp_add_schedule @schedule_name = 'DailyBackupSchedule',
	@freq_type = 4,
	@freq_interval = 1,
	@active_start_time = 10000

EXEC sp_attach_schedule @job_id = @jobId,
	@schedule_name = 'DailyBackupSchedule'

EXEC sp_add_jobserver @job_id = @jobId,
	@server_name = @@SERVERNAME

This script creates a SQL Agent job called "Backup All Databases" and adds a job step to perform a full backup of each database on the instance (excluding system databases). The backups are saved to a specified directory, using the current date in the file name to differentiate backups from different days.

You will need to change the path of the backups from C:\Backups to whatever is best for your environment.

Finally, the job is scheduled to run daily at 1am, and attached to the SQL Agent server. You can modify the script to change the backup directory, schedule time, or any other options as needed.

Backup Logs

If you have any full-mode DB's you will also need to backup the logs.

Here's an example T-SQL job that loops through all of the databases on a SQL instance and takes a transaction log backup of all the databases in full recovery mode.

USE [msdb];
GO

DECLARE @databaseName NVARCHAR(128);
DECLARE @sqlCommand NVARCHAR(1000);

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE state = 0 AND recovery_model_desc = 'FULL';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @databaseName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlCommand = 'BACKUP LOG [' + @databaseName + '] TO DISK=''D:\SQLBackups\' + @databaseName + '_LogBackup_' + CONVERT(VARCHAR(10), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '') + '.trn'';';
    EXECUTE sp_executesql @sqlCommand;
    FETCH NEXT FROM db_cursor INTO @databaseName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

This script first selects all the databases on the SQL instance that are in full recovery mode and not in a restoring state. It then loops through each of these databases and generates a T-SQL command to backup the transaction log of each database to a specified location.

In this example, the location is D:\SQLBackups, the file name for each backup is based on the database name, the date, and the time of the backup.

Maximum Degree of Parallelism (MAXDOP)

The recommended maximum degree of parallelism (MAXDOP) setting for SQL Server based on the number of processors or cores in the system is documented by Microsoft in their official documentation as stated in the Microsoft documentation "As a general guideline, set the MAXDOP value equal to the number of processors available, up to a maximum value of eight."

This means that if you have a server with 8 or fewer processors or cores, you can set the MAXDOP value to the number of processors or cores in the system. However, if your server has more than 8 processors or cores, you should consider using a lower MAXDOP value than the number of processors or cores, depending on the workload characteristics. This is because using a high MAXDOP value on a system with many processors or cores can result in excessive parallelism, which can lead to performance degradation and resource contention issues.

You can find more information on the recommended MAXDOP setting in SQL Server and how to configure it based on the number of processors or cores in the system in the official Microsoft documentation here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15#recommendations-for-maxdop-value

To set the max degree of parallelism in T-SQL, you can use the following script:

EXEC sp_configure 'max degree of parallelism', 8; -- set the maximum degree of parallelism to 8
RECONFIGURE; 

Cost Threshold For Pallellism

The recommended setting for the Cost Threshold for Parallelism in SQL Server depends on the hardware configuration and workload characteristics of the system. However, Microsoft recommends adjusting the Cost Threshold for Parallelism if your system has a large number of processors or slower I/O subsystems.The default value for Cost Threshold for Parallelism is 5, which allows the optimizer to use parallel plans for queries that have an estimated cost of 5 or higher.

However, for systems with larger numbers of processors or slower I/O subsystems, a higher value might be appropriate to avoid excessive parallelism and optimize query performance.As a general rule of thumb, you can start by setting the Cost Threshold for Parallelism to a value between 25 and 50, and then adjust it based on the workload and performance monitoring. It is important to monitor the query performance and adjust the Cost Threshold for Parallelism accordingly to avoid excessive parallelism and optimize query execution.

In summary, the recommended setting for the Cost Threshold for Parallelism in SQL Server depends on the hardware configuration and workload characteristics of the system, but starting with a value between 25 and 50 is a good starting point. You should monitor the query performance and adjust the Cost Threshold for Parallelism as necessary to optimize query execution.

Mikes Take: The default is 5 I set this to 50

Auto Shrink

Microsoft does not recommend using the Auto Shrink feature in SQL Server as it can cause performance issues and database fragmentation. In fact, the official documentation states that "In general, you should avoid using auto-shrink. This setting can cause database fragmentation and can decrease performance."Here is the link to the official Microsoft documentation on the topic: https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-the-auto-shrink-database-option-transact-sql?view=sql-server-ver15.

Insted of using Auto Shrink, it is recommended to monitor the database size and manually manage the database growth and space allocation. This can be done by setting an appropriate initial size and growth rate for the database files, monitoring the database file size and growth, and performing regular maintenance tasks such as database backups and index maintenance.

Mikes Take: Turn It On - Yes it will run at the most inconvenient time and cause prodution slowdowns but this is better than running out of disk...because you are not managing the DB size to begin with.

You can find the setting by right clicking on database --> properties

Ram Settings

SQL Server will use all of the ram that you give it.. End Stop. Give SQL enough ram and 8-16 Gb for the OS.

The default is 214748364 or all avaible ram on the OS - Don't bother wit the minimum ram.