Azure Monitor SQL Insights – Preview
Azure Monitor SQL insights (Preview) Comprehensive and reliable monitoring is a top priority for all SQL customers. The Azure SQL and Azure Monitor teams are proud to announce the preview of Azure Monitor SQL insights: a new, Azure-native monitoring experience for almost any SQL deployment in Azure.Public Preview: Shrink for Azure SQL Database Hyperscale
Update: On 29 January 2025 we announced the General Availability for shrink in Hyperscale. For more details, please read the GA announcement. If you are using Hyperscale in Azure SQL Database, you know that it is a powerful tier that lets you rapidly scale up and scale out your database according to your needs along with autoscaling storage. However, there could be situations where storage is scaled up automatically and then due to some business needs, a significant amount of data is removed/purged and a lot of free space is left within the database Today, we are pleased to announce that database and data file shrink is available in the Hyperscale tier in preview. Now you can reduce the allocated size of a Hyperscale database using the same DBCC SHRINK* commands that you might be familiar with. This allows you to reduce the size of the databases and free up unused space to save storage costs. How to use shrink in Hyperscale? Using shrink is easy and straightforward. You use the same set of commands which you might have used in other tiers of Azure SQL Database or in SQL Server. First, identify a Hyperscale database with substantial allocated but unused storage space. For definitions of allocated and used storage space, see Azure SQL Database file space management - Azure SQL Database | Microsoft Docs. Azure portal also provides this information. You can also capture the current used, allocated, and unused space in each database file by executing the following query in the database. DECLARE @NumPagesPerGB float = 128 * 1024; SELECT file_id AS FileId , size / @NumPagesPerGB AS AllocatedSpaceGB , ROUND(CAST(FILEPROPERTY(name, 'SpaceUsed') AS float)/@NumPagesPerGB,3) AS UsedSpaceGB , ROUND((size-CAST(FILEPROPERTY(name, 'SpaceUsed') AS float))/@NumPagesPerGB,3) AS FreeSpaceGB , ROUND(max_size / @NumPagesPerGB,3) AS MaxSizeGB , ROUND(CAST(size - FILEPROPERTY(name, 'SpaceUsed') AS float)*100/size,3) AS UnusedSpacePercent FROM sys.database_files WHERE type_desc = 'ROWS' ORDER BY file_id A shrink operation can be initiated using either command to shrink the entire database, or DBCC SHRINKFILE command for individual data files. We recommend using DBCC SHRINKFILE, because you can run it in parallel on multiple sessions, targeting different data files. DBCC SHRINKDATABASE is a simpler choice because you only need to run one command, but it will shrink one file at a time, which can be time-consuming for larger databases. If shrink operation fails with any error or canceled, the progress it has made so far is retained, and the same shrink command can be simply executed again to continue. Once shrink for all data files has completed, rerun the earlier query (or check in the Azure portal) to determine the resulting reduction in the allocated storage size. If there is still a large difference between used space and allocated space, you can rebuild indexes to reduce the total number of used data pages. This may temporarily increase allocated space further, however shrinking files again after rebuilding indexes should result in a higher reduction in allocated space. For more details about Azure SQL Database space management, see the following documentation article: Database file space management - Azure SQL Database | Microsoft Learn Known behaviors / limitations Database shrink is a long-running operation. For larger databases, it may span multiple days. To avoid shrink getting interrupted, we recommend using a client that is unlikely to get disconnected from the database. While shrink is running, used and allocated space for the database in the Azure portal might not be reported. Running SHRINKFILE with a target size slightly higher than the used space in the file tends to have a higher success rate compared to setting it to the exact used space. For instance, if a file is 128 GB in total size with 50 GB used and 78 GB free, setting the target size to 55 GB results in a better space reduction compared to using 50 GB. When executing DBCC SHRINKFILE concurrently on multiple files, you may encounter occasional blocking between the sessions. This is expected and does not impact the outcome of shrink. Shrinking of the transaction log file in the Hyperscale tier is not required as it does not contribute to the allocated data size and cost. Executing DBCC SHRINKFILE(2) has no effect on the transaction log size. Shrink is currently in preview mode and has the following limitations: Shrink is not allowed on unencrypted databases. Any such attempt raises the following error: Msg 49532, Level 16, State 1, Line 1 DBCC SHRINKFILE for data files is not supported in a Hyperscale database when the database is not encrypted. Enable transparent data encryption and try again. To find the encryption state of the database, execute the following query: SELECT db_name(database_id) AS 'database_name' ,encryption_state_desc FROM sys.dm_database_encryption_keys WHERE database_id = db_id() If encryption state is other than ENCRYPTED then shrink will not start. Conclusion We hope that you will find shrink useful and beneficial for your Hyperscale databases. We welcome your feedback and suggestions on how to improve it. You can contact us by adding to this blog post and we’ll be happy to get back to you. Alternatively, you can also email us at sqlhsfeedback AT microsoft DOT com. We are eager to hear from you all!Microsoft Copilot in Azure extends capabilities to Azure SQL Database (Public Preview)
We are excited to announce the introduction of new Copilot skills for Azure SQL Database! Microsoft Copilot in Azure can now help you streamline the design, operation, optimization, and health of Azure SQL Database-driven applications. It improves productivity in the Azure portal by offering self-help for database administration and natural language to SQL conversion.
A Brief Comparison of Database, Data Warehouse, Data Mart and Data Lake and these services in Azure.
Every organization needs to process data. Choosing whether, a data mart, data warehouse, database, or data lake is the best option for your organization will depend on the type of data, its scope, and how it will be used.Announcing Azure Data Tech Groups v2.0: Big News for User Groups and Members
Two years ago, Microsoft publicly launched the new Azure Data Community webpage, and created Azure Data Tech Groups utilizing Meetup. Prior to this, many user groups used PASS resources to host their websites, virtual meetings, manage members and more. Over the following months, we onboarded qualified user groups to the new Azure Data Meetup network and began providing Microsoft Teams access to help groups meet virtually through the pandemic. All of our community efforts were to empower the community, provide you with resources to help you continue to connect with one another, learn and develop your skills, and grow in your careers. V 1.0 of Azure Data Tech Groups had one great big limitation: groups that didn't use Meetup had to start or we weren't able to include them. This had the greatest impact on the community in LATAM, the Middle East and APAC, as they're less likely to utilize Meetup. This latest initiative will expand access to our network to more groups globally, groups that use their own website, LinkedIn, HDX or various ways to connect with members.
