Compress Database Backup Files with 7-Zip

7-Zip is a free and open-source file archiver, a utility used to place groups of files within compressed containers known as “archives”. It is developed by Igor Pavlov and was first released in 1999. 7-Zip uses its own 7z archive format, but can read and write several other archive formats. ZipFiles.cmd The following script look... » read more

Database Backup Log

Sources: https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

Database Dictionary with Extended Property

You can use tools like SQL Doc (Red Gate) to generate database dictionary. Add extended property to table and column when you create them. Adding extended property to table. Update existing extended property to table. You can also update the extended property of table and column by clicking on the table or column -> Properties... » read more

SQL Server Network Packet Size

The network packet sizeĀ option sets the packet size (in bytes) that is used across the whole network. Packets are the fixed-size chunks of data that transfer requests and results between clients and servers. The default packet size is 4,096 bytes. Note: Do not change the packet size unless you are certain that it will improve... » read more

Check Database Replica Status for Always On

synchronization_state Data-movement state, one of the following values. 0 = Not synchronizing. For a primary database, indicates that the database is not ready to synchronize its transaction log with the corresponding secondary databases. For a secondary database, indicates that the database has not started log synchronization because of a connection issue, is being suspended, or... » read more

Error 9002. The transaction log for database is full due to AVAILABILITY_REPLICA error message in SQL Server

Error: The transaction log for database ***** is full due to ‘AVAILABILITY_REPLICA’ Cause: This occurs when the logged changes at primary replica are not yet hardened on the secondary replica.  Fix: Make sure Always On setup is working properly. Start the SQL services on secondary replica server and resume data movement for the always on... » read more

Remove Database Backup Files

Using SQL Job via Powershell script to remove backup files older than x days. Create the job… Powershell script to remove all .bak files… The following script delete all files… The following script delete .7z file from one folder and .bak from another folder on the same drive. Note: Make sure certain account have modify... » read more

Update Database Statistics

Statistics are used by the Query Optimizer to create execution plans based on statistical distribution of required values, the information is stored in BLOBs (binary large objects). Outdated or non-existent statistics can lead to poor performing queries or sub-optimal execution plans. This runs the UPDATE STATISTICS command against all user defined tables in the current... » read more