A Fine Slice Of SQL Server
Paul Codding tells us what’s coming in the next version of Ambari:
Ambari Log Search (Tech Preview) has been one of our most popular features, and in this release has seen UI, and backend refreshes based on customer feedback. Log Search is planned for GA with the next major Ambari release, Ambari 3.0 in which the UI will be simplified, and the backend will have more robust log retention and scaling capabilities.
There are some interesting changes, so read the whole thing.
Comments closedYou are probably reading this post because you have experienced the pain yourself and I dont want to waste anymore of your time and get right to the steps involved in breaking up a huge database or a datafile
Check the space situation on your host
Get the space usage by files for the big database/datafile in question
Decide on number of files to add/location
Add multiple secondary datafiles
Distribute data from big datafile into the new datafiles using EMPTYFILE option
Shrink the big datafile and set a maximum size
Change the default filegroup
Read on for Jana’s step-by-step approach.
Comments closedNate Johnson has a script he uses to automate installation of SQL Server on a new server:
We can then use this file in the
ConfigurationFileargument ofsetup.exefrom the SQL Server install media. To put a little more color on that: the.inifile is really just a collection of command-line arguments tosetup.exe; you could also list them all out in-line, but that would be tedious and silly. Here’s a couple major selling points of creating your own config file:
Slipstream updates (SP’s, CU’s), instead of having it go out to MSFT update servers (or *aghast* sticking with the original RTM bits, you heathen you!)
Specify drive letters / default file locations: sure, this may be considered old-hat if you’re running super slick storage, but I still find it makes management a bit easier if I know where my MDFs, LDFs, TempDB, & backups will always be.
Take advantage of 2016’s better TempDB setup options (# files, size & growth)
Read the whole thing.
Comments closedJeff Mlakar walks through some advanced MSDTC troubleshooting:
Sometimes when a VM is cloned from a template (VMware or Hyper-V) the CID can be duplicated between the two machines. Evidence of this can be seen in the Event Viewer (Event ID 4101). This means the two MSDTC services will not be able to communicate with each other.
A possible error message is:
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction
The MSDTC feature of the Windows operating system requires unique CID values to ensure that MSDTC functionality between computers works correctly. Disk duplicate images of Windows installations must have unique CID values or MSDTC functionality may be impaired. This can occur when using virtual hard disks to deploy an operating system to a virtual machine.
This burned me in the past. Jeff has several scenarios that he walks us through, so if you’re using the Distributed Transaction Coordinator, definitely check this out.
Comments closedSteve 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 closedSteve 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 closedStartup 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 closedSpace 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 closedErik 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 forxp_cmdshellin that, well, you can do the equivalent of running adircommand, 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 closedJeff 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