{"id":5970,"date":"2014-06-26T00:00:00","date_gmt":"2014-06-26T00:00:00","guid":{"rendered":""},"modified":"2025-09-12T10:40:40","modified_gmt":"2025-09-12T17:40:40","slug":"azure-automation-your-sql-agent-in-the-cloud","status":"publish","type":"post","link":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/","title":{"rendered":"Azure Automation: Your SQL Agent in the Cloud"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">The ability to schedule regular maintenance or administrative jobs makes life significantly easier for anyone that manages one or more databases.&nbsp; SQL Server Agent affords this ability to those that utilize SQL Server on premise or in a virtual machine on Azure.&nbsp; However, for those that prefer the PaaS offering of Azure SQL Database, it does not take long to recognize that SQL Server Agent is not an available feature.&nbsp; As a result, many have <a href=\"https:\/\/feedback.azure.com\/forums\/217321-sql-database\/suggestions\/1079035-add-sql-server-agent-or-its-ability-to-create-cust\">expressed<\/a> <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/windowsazure\/en-US\/62127a81-8078-4926-9395-2ed7b5e07f4a\/how-to-create-a-scheduled-task-for-sql?forum=ssdsgetstarted\">the<\/a> <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/windowsazure\/en-US\/e86f3cab-2c08-410e-837c-34c5566ae45d\/monitoring-schema-size-in-sql-azure?forum=ssdsgetstarted\">desire<\/a> for us to deliver this functionality in various forums.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Recently <a href=\"https:\/\/blogs.technet.com\/b\/in_the_cloud\/archive\/2014\/04\/15\/announcing-the-microsoft-azure-automation-preview.aspx\">introduced<\/a> as a <a href=\"https:\/\/account.windowsazure.com\/PreviewFeatures?fid=automation\">public preview service<\/a>, <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/automation\/\">Azure<\/a> <a href=\"https:\/\/azure.microsoft.com\/en-us\/documentation\/services\/automation\/\">Automation<\/a> brings a powerful, much needed PowerShell Workflow execution service to the Azure platform.&nbsp; Those once difficult maintenance tasks are now possible to automate, and conveniently encapsulated within the common Azure portal experience.&nbsp; Simply author a PowerShell Workflow (called a \u201crunbook\u201d in Azure Automation), upload it to the cloud, and schedule when you want the runbook to execute. It\u2019s that simple.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Given this context, it begs the question &#8212; can Azure Automation play the role of SQL Server Agent for Azure SQL DB?&nbsp; The short answer is yes.&nbsp; In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/en-US\/62127a81-8078-4926-9395-2ed7b5e07f4a\/how-to-create-a-scheduled-task-for-sql?forum=ssdsgetstarted\">customer requested scenario<\/a> of truncating tables when a particular database approaches its maximum size capacity.&nbsp; Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There have been a few <a href=\"https:\/\/azure.microsoft.com\/en-us\/documentation\/articles\/automation-create-runbook-from-samples\/\">getting<\/a> <a href=\"https:\/\/blogs.technet.com\/b\/keithmayer\/archive\/2014\/04\/04\/step-by-step-getting-started-with-windows-azure-automation.aspx\">started<\/a> <a href=\"https:\/\/blogs.technet.com\/b\/cbernier\/archive\/2014\/04\/08\/microsoft-azure-automation.aspx\">guides<\/a> for Azure Automation, so we will cut right to the chase (if you do have any Azure Automation specific questions, please post them <a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/windowsazure\/en-US\/home?forum=azureautomation&amp;filter=alltypes&amp;sort=lastpostdesc\">here<\/a>).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Below is an example of a runbook that iterates through the database for a given logical server and obtains an array of the database names as well as their current size (runbook can be downloaded here [<a href=\"https:\/\/gallery.technet.microsoft.com\/scriptcenter\/Azure-Automation-Your-SQL-30f8736b\">link<\/a>]).&nbsp; Then the runbook will query each of the respective database for their maximum size and, if the database\u2019s size is within a specified range of the maximum size, the runbook will truncate a given table.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nworkflow Remove-DataFromSqlDbTable \n{\n    param\n    (\n        # Fully-qualified name of the Azure DB server \n        [parameter(Mandatory=$true)] \n        [string] $SqlServerName,\n\n        # Credentials for $SqlServerName stored as an Azure Automation credential asset\n        # When using in the Azure Automation UI, please enter the name of the credential asset for the \"Credential\" parameter\n        [parameter(Mandatory=$true)] \n        [PSCredential] $Credential\n    )\n\n    inlinescript\n    {\n\n        # Setup credentials   \n        $ServerName = $Using:SqlServerName\n        $UserId = $Using:Credential.UserName\n        $Password = ($Using:Credential).GetNetworkCredential().Password\n\n        # Setup threshold for % of maximum DB size\n        $Threshold = 0.8\n\n        # Create connection to Master DB\n        $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection\n        $MasterDatabaseConnection.ConnectionString = \"Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;\"\n        $MasterDatabaseConnection.Open();\n\n        # Create command to query the current size of active databases in $ServerName\n        $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand\n        $MasterDatabaseCommand.Connection = $MasterDatabaseConnection\n        $MasterDatabaseCommand.CommandText = \n            \"\n                SELECT \n                       database_name,\n                       storage_in_megabytes [SizeMB]\n                FROM \n                       [sys].[databases] as db\n                INNER JOIN\n                       [sys].[resource_usage] as rs\n                ON\n                       rs.database_name = db.name\n                WHERE\n                       [time] = (SELECT Max([time]) FROM [sys].[resource_usage] WHERE database_name = db.name)\n                GROUP BY \n                       database_name, storage_in_megabytes\n            \"\n        # Execute reader and return tuples of results \n        $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()\n\n        # Proceed if there is at least one database\n        if ($MasterDbResult.HasRows)\n        {\n            # Create connection for each individual database\n            $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection\n            $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand\n\n            # Iterate through each database under $ServerName\n            while($MasterDbResult.Read())\n            {\n                $DbName = $MasterDbResult[0]\n                $DbSize = $MasterDbResult[1]\n\n                # Apply conditions for user databases (i.e., not master DB)\n                if($DbName -ne \"Master\")\n                {\n                    # Setup connection string for $DbName\n                    $DatabaseConnection.ConnectionString = \"Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;\"\n                    $DatabaseConnection.Open();\n\n                    # Create command for a specific database $DBName\n                    $DatabaseCommand.Connection = $DatabaseConnection\n                    $DatabaseCommand.CommandText = \"SELECT DATABASEPROPERTYEX ('$DbName','MaxSizeInBytes')\"\n\n                    # Execute query and return single scalar result \n                    $DbResultBytes = $DatabaseCommand.ExecuteScalar()\n                    $MaxDbSizeMB = $DbResultBytes\/(1Mb)\n\n                    # Calculate $TargetDbSize\n                    $TargetDbSize = $MaxDbSizeMB * $Threshold\n\n                    # When the current $DbSize is greater than a percentage ($Threshold) of the $MaxDbSizeMB\n                    # then perform a certain action, in this example, truncate a table on that database\n                    if($DbSize -gt $TargetDbSize) \n                    {\n                        Write-Output \"Perform action on $DbName ($DbSize MB > $TargetDbSize MB)\"\n\n                        # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data\n                        # that can be truncated to save space on the database.\n\n                        $DatabaseCommand.CommandText = \"TRUNCATE TABLE [dbo].[ExampleTable]\"\n                        $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()\n                    }\n                    else\n                    {\n                        Write-Output \"Do not perform action on $DbName ($DbSize MB <= $TargetDbSize MB)\"\n                    }\n\n                    # Close connection to $DbName\n                    $DatabaseConnection.Close()        \n                }\n            }\n        } \n\n        # Close connection to Master DB\n        $MasterDatabaseConnection.Close() \n    }    \n}\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Once uploaded to Azure Automation, the runbook can be modified, tested, executed on-demand, or linked to a schedule.&nbsp; In fact, the entire runbook can be developed in the Azure Portal.&nbsp; Azure Automation provides a convenient authoring experience complete with intellisense, syntax coloring, and text search capabilities.&nbsp; Shown in the screenshot below is a schedule that executes the previously described runbook every night at midnight.<\/p>\n\n\n<figure class=\"wp-block-image size-full\"><img decoding=\"async\" src=\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp\" alt=\"graphical user interface, text, application\" class=\"wp-image-8680 webp-format\" title=\"Screenshot\" data-orig-src=\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp\"><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">As you can see, Azure&nbsp; Automation provides a very extensible PowerShell Workflow execution engine and job scheduler.&nbsp; Even beyond the functionality I\u2019ve already demonstrated, one can also use the Azure PowerShell cmdlets that ship in Azure Automation to perform higher-level SQL DB tasks, such as provisioning new databases or SQL servers, from within Azure Automation runbooks.&nbsp; Whether it is database provisioning, capacity management, index maintenance, or increasing\/decreasing the performance level of a database, together Azure Automation\u2019s features afford cloud dev\/ops the primitives needed to automate their Azure SQL DB management and maintenance tasks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Not an Azure Automation user yet? <a href=\"https:\/\/account.windowsazure.com\/PreviewFeatures?fid=automation\">Sign up for the preview<\/a> and then check out the <a href=\"https:\/\/azure.microsoft.com\/en-us\/documentation\/articles\/automation-create-runbook-from-samples\/\">Getting Started guide<\/a>.<\/p>\n\n\n<pre class=\"prettyprint\">\u00a0<\/pre>\n<p>\u00a0<\/p>","protected":false},"excerpt":{"rendered":"<p>In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity.  Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ms_queue_id":[],"ep_exclude_from_search":false,"_classifai_error":"","_classifai_text_to_speech_error":"","_alt_title":"","footnotes":"","msx_community_cta_settings":[]},"categories":[1482],"tags":[],"audience":[],"content-type":[1511],"product":[393],"tech-community":[],"topic":[],"coauthors":[97],"class_list":["post-5970","post","type-post","status-publish","format-standard","hentry","category-management-and-governance","content-type-best-practices","product-automation","review-flag-1680286580-543","review-flag-1680286581-295","review-flag-1680286584-658","review-flag-1-1680286581-825","review-flag-8-1680286581-263","review-flag-lever-1680286579-649","review-flag-new-1680286579-546","review-flag-on-pr-1680286585-35","review-flag-publi-1680286584-566"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog<\/title>\n<meta name=\"description\" content=\"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog\" \/>\n<meta property=\"og:description\" content=\"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\" \/>\n<meta property=\"og:site_name\" content=\"Microsoft Azure Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/microsoftazure\" \/>\n<meta property=\"article:published_time\" content=\"2014-06-26T00:00:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-09-12T17:40:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png\" \/>\n<meta name=\"author\" content=\"Microsoft Azure\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@azure\" \/>\n<meta name=\"twitter:site\" content=\"@azure\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Azure\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\"},\"author\":[{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/author\/microsoft-azure\/\",\"@type\":\"Person\",\"@name\":\"Microsoft Azure\"}],\"headline\":\"Azure Automation: Your SQL Agent in the Cloud\",\"datePublished\":\"2014-06-26T00:00:00+00:00\",\"dateModified\":\"2025-09-12T17:40:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\"},\"wordCount\":557,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png\",\"articleSection\":[\"Management and governance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\",\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\",\"name\":\"Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog\",\"isPartOf\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png\",\"datePublished\":\"2014-06-26T00:00:00+00:00\",\"dateModified\":\"2025-09-12T17:40:40+00:00\",\"description\":\"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.\",\"breadcrumb\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage\",\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp\",\"contentUrl\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp\",\"width\":1028,\"height\":225,\"caption\":\"graphical user interface, text, application\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Blog home\",\"item\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Management and governance\",\"item\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/category\/management-and-governance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Azure Automation: Your SQL Agent in the Cloud\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#website\",\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/\",\"name\":\"Microsoft Azure Blog\",\"description\":\"Get the latest Azure news, updates, and announcements from the Azure blog. From product updates to hot topics, hear from the Azure experts.\",\"publisher\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization\",\"name\":\"Microsoft Azure Blog\",\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2024\/06\/microsoft_logo.webp\",\"contentUrl\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2024\/06\/microsoft_logo.webp\",\"width\":512,\"height\":512,\"caption\":\"Microsoft Azure Blog\"},\"image\":{\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/microsoftazure\",\"https:\/\/x.com\/azure\",\"https:\/\/www.instagram.com\/microsoftdeveloper\/\",\"https:\/\/www.linkedin.com\/company\/16188386\",\"https:\/\/www.youtube.com\/user\/windowsazure\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/person\/c702e5edd662b328b49b7e1180cab117\",\"name\":\"shakir\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g7664e653ea371ce16eaf75e9fa8952c4\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g\",\"caption\":\"shakir\"},\"sameAs\":[\"https:\/\/azure.microsoft.com\"],\"url\":\"https:\/\/azure.microsoft.com\/en-us\/blog\/author\/shakir\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog","description":"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/","og_locale":"en_US","og_type":"article","og_title":"Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog","og_description":"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.","og_url":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/","og_site_name":"Microsoft Azure Blog","article_publisher":"https:\/\/www.facebook.com\/microsoftazure","article_published_time":"2014-06-26T00:00:00+00:00","article_modified_time":"2025-09-12T17:40:40+00:00","og_image":[{"url":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png","type":"","width":"","height":""}],"author":"Microsoft Azure","twitter_card":"summary_large_image","twitter_creator":"@azure","twitter_site":"@azure","twitter_misc":{"Written by":"Microsoft Azure","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#article","isPartOf":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/"},"author":[{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/author\/microsoft-azure\/","@type":"Person","@name":"Microsoft Azure"}],"headline":"Azure Automation: Your SQL Agent in the Cloud","datePublished":"2014-06-26T00:00:00+00:00","dateModified":"2025-09-12T17:40:40+00:00","mainEntityOfPage":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/"},"wordCount":557,"commentCount":0,"publisher":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization"},"image":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage"},"thumbnailUrl":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png","articleSection":["Management and governance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/","url":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/","name":"Azure Automation: Your SQL Agent in the Cloud | Microsoft Azure Blog","isPartOf":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage"},"image":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage"},"thumbnailUrl":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.png","datePublished":"2014-06-26T00:00:00+00:00","dateModified":"2025-09-12T17:40:40+00:00","description":"In this blog, I\u2019ll show you how to leverage the Azure Automation service to accomplish a customer requested scenario of truncating tables when a particular database approaches its maximum size capacity. Although targeted towards a specific scenario, the highlighted example provides the framework to enable one to accomplish countless Azure SQL DB scenarios that one would normally need a SQL Server Agent to perform.","breadcrumb":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#primaryimage","url":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp","contentUrl":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2014\/06\/Screenshot_thumb.webp","width":1028,"height":225,"caption":"graphical user interface, text, application"},{"@type":"BreadcrumbList","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/azure-automation-your-sql-agent-in-the-cloud\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog home","item":"https:\/\/azure.microsoft.com\/en-us\/blog\/"},{"@type":"ListItem","position":2,"name":"Management and governance","item":"https:\/\/azure.microsoft.com\/en-us\/blog\/category\/management-and-governance\/"},{"@type":"ListItem","position":3,"name":"Azure Automation: Your SQL Agent in the Cloud"}]},{"@type":"WebSite","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#website","url":"https:\/\/azure.microsoft.com\/en-us\/blog\/","name":"Microsoft Azure Blog","description":"Get the latest Azure news, updates, and announcements from the Azure blog. From product updates to hot topics, hear from the Azure experts.","publisher":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/azure.microsoft.com\/en-us\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#organization","name":"Microsoft Azure Blog","url":"https:\/\/azure.microsoft.com\/en-us\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2024\/06\/microsoft_logo.webp","contentUrl":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-content\/uploads\/2024\/06\/microsoft_logo.webp","width":512,"height":512,"caption":"Microsoft Azure Blog"},"image":{"@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/microsoftazure","https:\/\/x.com\/azure","https:\/\/www.instagram.com\/microsoftdeveloper\/","https:\/\/www.linkedin.com\/company\/16188386","https:\/\/www.youtube.com\/user\/windowsazure"]},{"@type":"Person","@id":"https:\/\/azure.microsoft.com\/en-us\/blog\/#\/schema\/person\/c702e5edd662b328b49b7e1180cab117","name":"shakir","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g7664e653ea371ce16eaf75e9fa8952c4","url":"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9342c7c05bb16548741bc5cd3a3e3b7ee0c8e746844ad2cc582db5beb5514c6f?s=96&d=mm&r=g","caption":"shakir"},"sameAs":["https:\/\/azure.microsoft.com"],"url":"https:\/\/azure.microsoft.com\/en-us\/blog\/author\/shakir\/"}]}},"msxcm_display_generated_audio":false,"msxcm_animated_featured_image":null,"distributor_meta":false,"distributor_terms":false,"distributor_media":false,"distributor_original_site_name":"Microsoft Azure Blog","distributor_original_site_url":"https:\/\/azure.microsoft.com\/en-us\/blog","push-errors":false,"_links":{"self":[{"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/posts\/5970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/comments?post=5970"}],"version-history":[{"count":1,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/posts\/5970\/revisions"}],"predecessor-version":[{"id":46173,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/posts\/5970\/revisions\/46173"}],"wp:attachment":[{"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/media?parent=5970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/categories?post=5970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/tags?post=5970"},{"taxonomy":"audience","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/audience?post=5970"},{"taxonomy":"content-type","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/content-type?post=5970"},{"taxonomy":"product","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/product?post=5970"},{"taxonomy":"tech-community","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/tech-community?post=5970"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/topic?post=5970"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/azure.microsoft.com\/en-us\/blog\/wp-json\/wp\/v2\/coauthors?post=5970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}