Here is another script I frequently use to track the progress of an ongoing backup and restore operation.
Database file
Azure SQL DatabaseBelow is a script I commonly use to check for the free space in every file of a SQL Server database. The column headings are self-explanatory. Note that this can also be used for Azure SQL Database and Azure SQL Managed Instance.
Data Compression
Azure SQL DatabaseSQL Server Data Compression feature lets you compress tables and individual indexes. The compression does incur a few extra CPU cycles every time you read or write to that table or index, but this overhead is hugely compensated by much better IO performance. In most cases, increasing a little more work for the CPU for…
Azure SQL Database
Azure SQL DatabaseOne of the very common topic for support cases which we receive at Microsoft is around customers trying to understand the cause of high DTU for their Azure SQL Database. In this blog, I want to share the steps we follow, almost always, to approach a high DTU issue. The most important thing to understand…
Azure SQL
Azure SQL DatabaseI have personally been a huge fan of Query Store ever since it was released with SQL Server 2016. It helps me simplify performance troubleshooting as it asynchronously captures a history of queries, plans and run time statistics, and stores this information within the database itself – which means that this data persists across server…
Azure SQL
Azure SQL DatabaseBelow is a script you can use to update all statistics with a full scan in a SQL Server database. This comes in super-handy for Azure SQL Databases and SQL Server Express Editions, as we don’t support Maintenance Plans with both. Alternatively, if you wish to update only out-of-date statistics then consider implementing MSSQL Tiger…
Azure SQL
Azure SQL DatabaseBelow is a script you can use to rebuild all indexes in a SQL Server database. This comes in super-handy for Azure SQL Databases and SQL Server Express Editions, as we don’t support Maintenance Plans with both. Alternatively, if you wish to rebuild only fragmented indexes then consider implementing MSSQL Tiger Team’s AdaptiveIndexDefrag solution to…
Shrink Log
Azure SQL Managed InstanceAlthough we strongly discourage shrinking files of SQL databases, but it can be an absolute necessity at times. Below is a simple script you can use to shrink log files of all databases and quickly reclaim some space on the drive hosting log files (*.LDF).
Error
SQL ServerOne of my customer recently reported that they were not able to start SQL Server Agent after a new SQL installation. The first thing I did was to review SQLAGENT.OUT and below is how it looked like. 2019-02-24 01:40:52 – ? [100] Microsoft SQLServerAgent version 12.0.4100.1 (X64 unicode retail build) : Process ID 87642019-02-24 01:40:52…