Press "Enter" to skip to content

Category: Administration

Azure SQL Database Deployment Account Errors

Published 2017-08-03 by Kevin Feasel

Steve Jones troubleshoots an issue with Azure SQL Database:

 I’ve had most builds work really well. I tried a number of things, but kept getting a few items in the build. There were login errors or network errors, both of which bothered me since I could manually log in with SSMS from the same machine as my build agent.

I suspected a few things here, one of which was the use of named pipes for the Shadow database and TCP for Azure SQL Database.

Eventually, I decided to fall back with msbuild, ignoring VSTS, and make sure all my parameters were correct.

Read on for the rest of the story.

Comments closed

Resizing A Linux Partition

Published 2017-08-02 by Kevin Feasel

Steve Jones shows how to add disk space to a Linux partition:

While working with some SQL Server 2017 tests, I ran out of disk space. I tend to size my VMs around 40GB, and that works for some things, but I’ll run out of space.

I needed to expand the VMWare disk. That doesn’t mean Linux sees the space directly, and I had to figure out how to make the partition bigger. I could have added another disk, but I wanted to work through this process. I learned I needed to have an inactive partition, so I download gparted on a live cd and booted to that.

Steve uses the GUI approach; in the comments, David Klee links to his CLI approach.

Comments closed

Using Startup Stored Procedures

Published 2017-08-02 by Kevin Feasel

John Morehouse explains how to set up a startup stored procedure, as well as some of the risks involved:

Startup procedures automatically execute whenever SQL Server is started.  Where would you use this?  One example is if you had an application that required the use of a global temporary table, you could use a startup procedure to create it.  This would allow the table to be immediately accessible to any application that requires it.

Another possibility is that you could “warm up” the cache so that data is already cached when an application needs it.  This would help prevent that initial I/O hit the first time the query is called, thus potentially improving initial performance.

Click through for details.  I’ve created a couple of these, but they rarely come to mind and that’s one of the big problems:  if there is an issue, there’s probably quite a bit of investigative work that would take place before somebody remembers that hey, these things exist.

Comments closed

Moving SQL Server Data Files

Published 2017-08-01 by Kevin Feasel

Jana Sattainathan walks us through the process of moving a SQL Server data file from one drive to another:

Space got tight on a drive and I knew that there was space on another drive. I had already set this particular database with multiple secondary file groups/files (.ndf files) instead of a huge and single .mdf file (which would have made the whole thing a lot harder).

The procedure to relocate data files is extremely simple

Click through for Jana’s seven salvos of administrator success.

Comments closed

Filesystem Enumeration DMV

Published 2017-08-01 by Kevin Feasel

Erik Darling points out a new DMV in SQL Server 2017:

SQL Server 2017 RC1 dropping recently reminded me of a couple things I wanted to blog about finding in there. One that I thought was rather interesting is a new iTVF called dm_os_enumerate_filesystem. It looks like a partial replacement for xp_cmdshell in that, well, you can do the equivalent of running a dir command, with some filtering.

The addition of a search filter is particularly nice, since the dircommand isn’t exactly robust in that area. If you’ve ever wanted to filter on a date, well… That’s probably when PowerShell got invented.

If I run a simple call to the new function like so…

Click through to see Erik use the new function.

Comments closed

Troubleshooting MSDTC

Published 2017-07-31 by Kevin Feasel

Jeff Mlakar has a troubleshooting guide for the Distributed Transaction Coordinator:

Microsoft Distributed Transaction Coordinator (MSDTC) is a Windows service that coordinates transactions spanning multiple databases. It is a transaction manager that allows applications to include several different sources of data in one transaction. MSDTC coordinates committing the distributed transaction across all servers enlisted in the transaction.

An example here would be a process on one machine calling a stored procedure on another machine which in requires data that changes together in a transaction.

The MSDTC service is running on each of the servers to manage the successful commit (or rollback) of the transaction across all servers enlisted in the transaction.

My MSDTC experience is generally negative, that this is more trouble than it’s worth.  But sometimes you’ve got to use it, and when you do, it’s nice to have the skinny on it.

Comments closed

Moving TempDB In Linux

Published 2017-07-28 by Kevin Feasel

David Klee shows how to migrate the tempdb database when running SQL Server on Linux:

We previously created a folder at /var/opt/mssql/data/tempdb01 for these files. Moving them is straightforward, once you know the file system structure. The following commands will move them to the new location, and I also add additional files to equal the four vCPUs I have on this SQL Server VM. The file growth is my model database’s default of 64MB for this instance. Do as you would normally do with SQL Server on Windows with tempdb file counts and separation of duties for your workload.

Read on for the process.  As a general spoiler, the “how to do this in Linux” answer is usually pretty close to the same as the “how to do this in Windows” answer, at least once you get into Management Studio.

Comments closed

The Risks Of Clearing The Procedure Cache

Published 2017-07-28 by Kevin Feasel

Erin Stellato explains two downsides to running DBCC FREEPROCCACHE or anything else which clears query plans:

Ideally, you should remove only what’s absolutely necessary.  Using DBCC FREEPROCCACHE is a sledgehammer approach and typically creates a spike in CPU as all subsequent queries need to have their plans re-generated.  Glenn gives examples on how to use each statement (and others) in his post Eight Different Ways to Clear the SQL Server Plan Cache, and I want to show you one more thing that happens when you clear a plan (or all plans) from cache.

For this demo script, I recommend running it against a TEST/DEV/QA environment because I am removing plans from cache which can adversely affect performance.

There are reasons to run these commands, but ideally, you should be as precise as possible in clearing plans out of the cache.

Comments closed

Sharepoint And MAXDOP=1

Published 2017-07-27 by Kevin Feasel

Daniel Glenn has a public service announcement on Sharepoint maximum degree of parallelism:

For my first install test, I was using a service account that did have administrative rights on SQL as well. I looked at the setting in SQL and SharePoint 2016 did change the Maximum Degree Of Parallelism setting to 1. So, the story is, as of now anyway (we are dealing with Preview software), SharePoint Server 2016 requires MAXDOP=1.

Kidding about Sharepoint aside, if you do have Sharepoint in your environment, it’s worth knowing how to configure it correctly.

Comments closed

Tracking DDL Events

Published 2017-07-27 by Kevin Feasel

Kenneth Fisher has a simple database trigger to track certain data definition language events:

A couple of notes before testing the code. The event groups I’m using will pull CREATE, ALTER and DELETE events for those objects. For a more complete list of events (you might want to add service broker events for example) go here. Also I’m using ORIGINAL_LOGIN because it will return who made the change even if they are impersonating someone else.

For my test, I created a user that only has db_DDLADMIN on the database. That means it can make DDL changes but can’t insert, update, delete or even run a select against any table in the database. That’s why I grant INSERT to public for the logging table.

It’s a good way of knowing when unexpected changes happen, too.

Comments closed