Category: Administration

Lori Brown has a challenge:

To some of you out there, that many databases might not be a big deal.  To me, it is a big deal.  That many databases can cause many admin tasks to take a very long time.  One such task is to review all databases to make sure that they are not growing their files by 1MB or 10 Percent, especially the transaction log files.  I find those default settings to not be a good thing generally, and have learned to set files to grow by specified amounts if auto growth needs to occur.  I also try to manually grow data files during maintenance periods but we all know that auto growth happens.

So…how do we find out if our databases have 1MB or 10 Percent file growth set up?

Click through for a script which finds default-sized databases, as well as a script to fix them.

Nisarg Upadhyay shares a few techniques to look for whether a table is currently in use:

In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add a new column or change the data type of a column, we export the data of the existing table in the backup table. Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that populates the list of unused tables and email the list to the DBA Team for review.

The best way to decommission any table is to rename it first, and if it does not break the functionality of the application, we can drop the table and its dependencies. We decided that after a review of the unused table completes; we will rename the tables and later drop them.

Click through for the techniques and a script which checks tables. It does look fairly reasonable, with the concern being if you restart the SQL Server instance or look at a table which is only accessed at a particular time of year.

Aaron Bertrand has a word of warning:

After releasing Cumulative Update #7 on September 2nd, Microsoft has discovered an issue with snapshot generation that was critical enough for them to pull the CU.

Click through for Aaron’s advice.

Ameena Lalani walks us through NUMA and automatic soft NUMA in SQL Server:

Modern processors have multiple cores per socket. Each socket is represented, usually, as a single NUMA node. The SQL Server database engine partitions various internal structures and partitions service threads per NUMA node. With processors containing 10 or more cores per socket, using software NUMA to split hardware NUMA nodes generally increases scalability and performance. Prior to SQL Server 2014 (12.x) SP2, software-based NUMA (soft-NUMA) required you to edit the registry to add a node configuration affinity mask, and was configured at the host level, rather than per instance. Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), soft-NUMA is configured automatically at the database-instance level when the SQL Server Database Engine service starts. Please read this  documentation and this documentation for more understanding.

Read on for more info.

Greg Larsen takes us through the ins and outs of synonyms in SQL Server:

Once a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.

I’ll admit that I don’t really think about synonyms much and have used them at most a couple of times in my career. I can see where they’d be useful, but that comes at the risk of something going wrong and people not even realizing they exist.

Josh Simar is on a mission:

 while ago, I asked in the twitterverse if there was a Postgres equivalent for the great sp_WhoIsActive script. While I didn’t get a flat-out no (which I wasn’t expecting) I didn’t get anyone pointing me in the direction of something pre-done and did get some advice that I should take it on.

Well it took quite a while and it’s still nowhere near as robust as the MSSQL version but as a first stab I have created the pg_WhoIsActive function.

While doing it I basically said that I want a 1 to 1 equivalent as much as possible but to get it out quick I had one major rule for the POC.

Check it out.