Press "Enter" to skip to content

Category: Administration

Purging Lots of Backup History

David Wiseman needs to clear out a significant amount of backup history:

Recently, I encountered an issue running sp_delete_backuphistory on servers that hosted a large number of databases with frequent log backup & restore operations. The clean up task hadn’t been scheduled and the history tables had grown very large over several months. The msdb databases was also hosted on a volume with limited IOPs.

Attempting to run sp_delete_backuphistory under these conditions you will likely encounter these issues:

Click through for that list of issues, as well as a way of mitigating the problem. I’ve noticed this kind of pattern appears fairly often in Microsoft-provided cleanup procedures: the code works well until you reach a certain scale, at which point it falls over. It’d be great if the original sp_delete_backuphistory performed batch deletion from the get-go, but David shows us a way to get around the issue.

Comments closed

SQL Server Setup Config Files and Per-Version Maintenance

Aaron Bertrand diagnoses a problem:

We came across a new error during SQL Server setup that returned zero useful search results:

The setting ‘COMMFABRICPORT’ specified is not recognized.

I did not find too many mentions of this argument at all, never mind in that specific phrase – though now that I wrote this, it might start returning this post. Most mentions came from probably a single sample copy of ConfigurationFile.ini offered on a blog post from the ~2017 timeframe.

Read on to learn more about this, as well as short-term and long-term fixes for managing your installation config files.

Comments closed

New Features in Azure SQL MI Instance Pools

Djordje Marinkovic shows off what’s new:

When migrating small SQL Server instances to Azure it is often the case that a single SQL Managed Instance turns out to be overkill in terms of size and, consequently, cost. The oversizing problem can happen whenever very small instances are required, for example when an ISV company builds a multi-tenant app requiring a small SQL MI instance for each customer. In such cases the smallest size (4-vCores) for a single SQL MI can still turn out to be too large and too expensive for the given use case. This is where SQL MI pools (“instance pools”) deliver great value.

Click through for more information on instance pools, as well as new features for instance pools.

Comments closed

DBCC SHRINKFILE and tempdb

Tom Collins answers a question:

Question: I’m trying to delete a TempDB ndf file from the TempDB file definitions. It is no longer required ,but getting an error message :

DBCC SHRINKFILE: Page xxxxxxxx could not be moved because it is a work table page.

How can I get around this problem? There is no activity on the server

Read on for the answer.

Comments closed

The Impact of Auto-Close on Performance

Steve Stedman explains why Auto-Close should almost never be on for your database:

When the AutoClose setting is enabled, SQL Server will shut down the database after the last user disconnects. This means that every time a new connection is made, SQL Server must go through the entire process of starting the database again. This includes reading the database file, allocating memory, and performing any necessary recovery processes. This overhead can cause a noticeable delay for users as they connect, especially if the database is large or complex.

Read on for several other factors affecting performance. I will say that the best use case for Auto-Close is when you have a dev instance—especially on a local machine—with a large number of databases and a very limited amount of RAM available. Otherwise, if this is a server, I’m turning Auto-Close off. Even today, I’d rather just buy enough RAM for my developers than flip this switch.

Comments closed

Firewalls and TLS in SQL Server on Linux

I have a new video out:

In this video, we harden our SQL Server instance in two ways: by using a firewall to limit inbound traffic, and by using a certificate to force encrypted connections to SQL Server.

This was a video I enjoyed creating. It also shows the progress of SQL Server security: go back to 2005 (pre-SP1) and even SQL authentication over TDS was unencrypted by default. They fixed it so that the authentication would use a self-signed cert but the data you’d get back from query results was unencrypted. Nowadays, encryption is easy (if you’re okay with a self-signed cert) and some future version of SQL Server will make it mandatory.

Comments closed

Redgate State of the Database Landscape Results

Louis Davidson review the results:

Every year, Redgate surveys technologists to ask a big question (through lots of little questions, naturally.) This year’s question was about their current data platform configuration and usage. Just before it was released, I read the results, and I have to say, some of the things I learned amazed me…until I thought a bit more about it.

Read on for what amazed Louis and then check out the survey results yourself.

Comments closed

Databases with Transaction Logs Larger than Data

Jess Pomfret checks database sizes:

This week I needed a query to find any databases where the transaction log is bigger than the total size of the data files. This is a red flag, and can happen for a few reasons that would need further investigation. However, this post is just to share the query, partly for you, and partly for future Jess.

If you do want to read more about why this could happen and how to fix it, Brent has a good post and some queries here: Brent Ozar – Transaction Log Larger than Data File.

Click through for the script and a quick example.

Comments closed

Handling Orphaned Database Files with dbatools

Rod Edwards rounds up the orphans:

This may be an edge case issue, it may not. Or some may not know this is a potentially a thing. For any of the above questions, i’m not sure of the answer. I do know however, that it doesn’t involve morally suspicious fairy tales of any kind, flutes, or pastry products for that matter.

I also know that it’s something that could potentially be robbing disk space across SQL Estates so i’ll talk about it anyway and supply a simple way to fix this in one sweep using the magnificent DBATools.

Rod’s claim is no pastry products, but my counter-argument is that the command probably runs better if someone brings donuts in.

Comments closed

Adding a Service Principal to a Power BI Workspace

Marc Lelijveld reads the manual, disagrees with it, and stubbornly sets out to prove it right:

Today, I run into an issue by adding a service principal to a workspace. To my experience so far, this should be straight forward and a matter of adding with the right permissions in the workspace settings. However, for some reason I couldn’t get it to work. In this blog I will elaborate on what caused this issue and which tenant setting influences this.

Click through for the story behind this and the resolution to Marc’s issue.

Comments closed