Feeds:
Posts
Comments

Quick one today:

A few weeks ago, I ran into an interesting error message:

Error Message:

DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 2 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 

After looking into it a bit further, these are the conclusions.

  • SHRINKDATABASE runs on the entire database, unlike SHRINKFILE. So, when I issued the command, the command internally tried to SHRINK both data and log file. The shrink percentage parameter indicates the amount of “shrink” that it is attempting on the database (data & log file). If there is not enough free space inside the data or log file, then Sql Server throws this error out saying it could not proceed. If the expected free space is not available on both the files, the command errors out.
  • SHRINKDATABASE cannot reduce the data and log file size beyond the initially configured number. This could also be a factor.

So, first we need to understand how much free space is available, so I could shrink based on that. For that use the following command:

--
--  How much free space is available
--
DBCC SHRINKDATABASE (SampleDB, TABULAR)
GO

 

This give output in the following format:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
2 1 163392 1024 552 496
2 2 27408 64 27408 64

Once you know how much free space is available, then you could re-run the SHRINKDATABASE command with pertinent parameter values.

 

Note:

  • Running SHRINKDATABASE command on production systems is not advisable. Careful analysis needs to happen before any steps are taken.
  • Also, this (TABULAR) option is an undocumented feature, so it could change in future

Hope this helps,

Quick one today:

Recently, I ran into this interesting error message. The actions that resulted in this error are silly, but the message is interesting. The language used in the error message dignifies the actions to the same level as other valid error messages. But in my opinion these actions are silly and it is nice of Microsoft to be kind and gentle on us.

Code that resulted in this error:

--
--  Incorrect
--
PRINT 'Purged Ended on : ' + CONVERT(VARCHAR, DATETIME)
GO

Interesting error:

Msg 128, Level 15, State 1, Line 11
 The name "DATETIME" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Corrected code sample that works:

--
--  Correct
--
PRINT 'Purged Started on : ' + CONVERT(VARCHAR, GETDATE())
GO
Hope this helps,
_Sqltimes

Quick one today:

Earlier in the lab, during set of a new ESXi host, we ran into this cryptic error.


"HostStorageSystem.ComputeDiskPartitionInfo" for object "storageSystem" on ESXi "<ip address>" failed

Looks like ESXi host, was not able to correctly wipe the disk. So, we need to manually delete the partition to make it available for ESXi host. The following steps worked for us:

Step 1: Make sure SSH access is enabled on the ESXi host.’

Step 2: Connect to ESXi host using putty.

Putty to connect to ESXi host through SSH

Putty to connect to ESXi host through SSH

 

Step 3: Enter root and password to log in.

Login using Putty

Login using Putty

Step 4: Enter the command esxcfg-scsidevs -l to list the name of the disk that you want to wipe.

~ # esxcfg-scsidevs -l
mpx.vmhba1:C0:T0:L0
Device Type: Direct-Access
Size: 237824 MB
Display Name: Local DELL Disk (naa.6842b2b0189ae90019c1b00d08f81f9f)
Multipath Plugin: NMP
Console Device: /vmfs/devices/disks/naa.6842b2b0189ae90019c1b00d08f81f9f
Devfs Path: /vmfs/devices/disks/naa.6842b2b0189ae90019c1b00d08f81f9f
Vendor: DELL Model: PERC H700 Revis: 2.30
SCSI Level: 5 Is Pseudo: false Status: on
Is RDM Capable: false Is Removable: false
Is Local: true Is SSD: false
Other Names:
vml.02000000006842b2b0189ae90019c1b00d08f81f9f504552432048
VAAI Status: unsupported
naa.6842b2b0189ae9001c7e063a1131312b

Step 5: Run “partedUtil get” command to get the details of the disk

partedUtil get /dev/disks/naa.6842b2b0189ae90019c1b00d08f81f9f

66987 255 63 9876546456
1 20 9876546456 165 128

Step 6: Now lets delete this partition using “partedUtil delete” command.

partedUtil delete /dev/disks/naa.6842b2b0189ae90019c1b00d08f81f9f 1

After running this command successfully, you could not go back to vSphere Client and recreate data store.

Create New Disk/LUN ESXi Host

Create New Disk/LUN ESXi Host

Hope this helps,
_Sqltimes

Quick one today:

For one of our lab servers, we needed reconfigure an ESXi host from dynamic (default) to static IP address. Following are the steps, that worked for us. Not sure how, screenshots could be take from a ESXi console, so only text instructions for this post.

Go to the physical ESXi machine and perform the following operations.

  • At the ESXi console screen press F2 to go to Customize System/View Logs
  • Login with your credentials (root/password)
  • Using up/down arrows, select “Configure Management Network” and hit enter
  • Select “Network Adapters” and hit enter. Make sure the status says “Connected“.
  • Go to IP configuration and change from dynamic to static IP and hit enter
  • Using up/down arrows and enter key:
    • Enter a valid static IP address in your network.
    • Subnet mask
    • Default Gateway
    • Hit enter for <OK>
  • Select DNS configuration and hit enter to change its settings.
    • Enter Primary DNS Server
    • Alternate DNS Server
    • Enter valid HostName in your network
    • Hit Enter for <OK> (accepting the settings)
  • Press Esc to main screen.
  • Hit “Y” to accept/apply the changes and restart the host for changes to take effect.

Helpful hints: For Subnet mask, DNS, Default Gateway, etc, I ran ipconfig /all on a different physical machine next to this ESXi host (in the same network) and used the same IP addresses.

After restart, the ESXi host shows the new static IP address with Hostname.

Thanks for this post for teaching me.

Hope this helps,
_Sqltimes

Sometimes, in our environment, we have multiple instances of Sql Server running on the same machine. In default configuration, when named instances come up, they listen on some port number. This port number changes every time they restart.Check out the listening port in two ways:

  • Sql Server Error Logs
  • SCM >> TCP/IP properties window

See below.

Dynamic Configuration : Listening Port

Dynamic Configuration : Listening Port

Sql Browser to rescue. When you connect to a named instance without specifying port number in the connection string, the connection is still successful because SQL Browser running on the machine takes that incoming request and re-directs it to the named instance through appropriate port number.

Solution

If you want to make sure the named instances are listening on static port, that could be done through Sql Server Configuration Manager. Sql Server Configuration Manager >> Sql Server Network Configuration >> Protocols for <instance> >> Right Click on TCP/IP >>

Sql Instance Listening Port Properties

Sql Instance Listening Port Properties

 

In properties window go to “IP Addresses” tab, and scroll to the bottom (like the image below), and enter a port number in the “TCP Port” section. Make sure the “TCP Dynamic Ports” is empty.

Sql Instanc Set Static Port

Sql Instanc Set Static Port

Now, when you restart the instance, Sql Server will come back up while listening to this static port. See Sql Server Error Logs and TCP/IP properties window below.

Sql Instance Listening on Static Port

Sql Instance Listening on Static Port

To connect to this named instance listening on static port, we mention port in the connection string like the image below:
Connect To Static Port Named Instance

Connect To Static Port Named Instance

Hope this helps,
_Sqltimes

Quick one today:

When I restore databases from one instance to a different instance, i.e. from QA environment to Integration environment or to UAT, this issue of orphan users occurs.

Login are instance level principals and Users are database level principals. Each user account at the database level is mapped to a login at instance level. As part of this mapping, sid (security ID) of the login is mapped to

--
-- Gather details of login and users
--
SELECT uid, name, sid
FROM sys.sysusers
WHERE name = 'Copient_Logix'
GO

SELECT name, sid
FROM master.sys.syslogins
WHERE name = 'Copient_Logix'
GO
Login and User SID mapping

Login and User SID mapping

 

So, when you restore a database from one instance to another instance, this sid mapping will be incorrect; Resulting in orphan users at the database level. To correct this, we could take these steps.

Step 1:

Get a list of users that are orphans.

--
-- Gather list of orphan users
--
EXEC sp_change_users_login 'Report'
GO

Step 2:

--
-- Fix the orphan user
--
EXEC sp_change_users_login 'Auto_Fix', 'user'
GO

 

Fix_orphan_users

 

Hope this helps,
_Sqltimes

Quick one today:

Recently, during troubleshooting an issue with one of our customers, some patterns emerged that indicated the possibility of resource contention for their repeated timeout issues. Upon further digging in, the CPU usage was abnormal. The database server was on a Virtual Machine with 8 processors allocated. The CPU usage was only on one processor. The rest were idle. 0% usage.

As part of that experience some lessons were gathered out of which today’s post emerges on CPU Affinity mask.

CPU Affinity Mask:

Sql Server uses all CPUs available through the Operating System. It creates schedulers on all the CPUs to make the best utilization of the resources for any given workload. Operating Systems, as part of its multitasking, it needs to switch process threads from processor to another. This is usually not detrimental for normal operations; But resource intensive operations like Sql Server this undercuts performance. Such Context Switching is not always a welcome scenario. To minimize we could configure the processors in a way that all the Sql Server load will be directed to a pre-selected group of processors. This is achieved using CPU Affinity Mask.

As you imagine, this is a HEX value. But we convert the HEX value into integer and configure Sql Server to lean towards (affinitize) a group of CPUs.

Let’s imagine a machine with 16 processors; When you assign all the processors to Sql Server and allow it to optimally utilize all the resources the settings look like this:

CPU Affinity Mask (default)

CPU Affinity Mask (default)

The options on the top convey that we are enabling Sql Server to fully manage the resource allocation with all the CPUs.

  • Automatically set processor affinity mask for all processors
  • Automatically set I/O affinity mask for all processors

I/O tasks like Lazywriter, CHECKPOINT, Log Writer, etc are directly affected by the dedicated CPUs allocated for I/O activities.

NUMA Noode0 and NUMA Node 1 are the CPU groups (either Hardware or Software configuration) to enhance hardware resource consumption. Each NUMA node has multiple CPUs with dedicated memory.

The ‘Processor Affinity’ Check box allows you to select individual CPUs, from each NUMA nodes, to SQL Server. Since the check box is not checked, none of the CPUs (from that NUMA node) have their affinity set (not allocated primarily for Sql Server).

Now, if you want to select all the processors from NUMA Node 0 to Sql Server, select that Check box. If you want to accomplish the same with T-SQL scripts like this:


--
-- Since these are advanced options, we first need to set 'Show Advanced Options' & RECONFIGURE
--
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

--
--  Integer value 15 translates to 0x000F (which shows that the last 4 bits are set to 1. Similarly 240 translates to 0x00F0
--
EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO
RECONFIGURE
GO

--
--  Once completed, reset the Advanced Options
--
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

Affinity Mask for each processor could be set using a 1 or 0 bit flag. 1 signifies the affinity being set. So each processor has an affinity mask bit flag that controls its affinity.

Let’s says you want to allocate all the 8 CPUs in NUMA Node 0 to Sql Server; First 4 with CPU affinity and last 4 for I/O affinity. This are the steps to follow:

Imagine setting 1 (bit) for each allocated CPU; Then convert that value into INTEGER.

In Binary the result looks like this 00001111; It translates to 0X0F in HEX. Which is 15 in integer. This is the value you want to set for ‘affinity mask’.

Now, let’s try the same logic for I/O affinity mask. Let’s set the last 4 bits to 1.

So, 11110000 in binary translates to 0xF0 in HEX and 240 in integer.

Putting it all together it becomes:

EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO
After setting CPU Affinity Mask

After setting CPU Affinity Mask

Since Following is the table from MSDN that shows other example:

These are affinity mask values for an 8-CPU system.

Decimal value Binary bit mask Allow SQL Server threads on processors
1 00000001 0
3 00000011 0 and 1
7 00000111 0, 1, and 2
15 00001111 0, 1, 2, and 3
31 00011111 0, 1, 2, 3, and 4
63 00111111 0, 1, 2, 3, 4, and 5
127 01111111 0, 1, 2, 3, 4, 5, and 6
255 11111111 0, 1, 2, 3, 4, 5, 6, and 7

Some more points:

  • One byte (8 bits) covers affinity mask for 8 CPUs. And two bytes for 16 CPUs, 3 bytes for 24; 4 for 32 CPUs.
  • For more than 32 processors, you want to use affinity64 for another 32 processors.
  • It is recommended not to use the same CPU for both CPU affinity and I/O affinity.
  • Changing CPU affinity mask does not require restart.
  • Any schedulers assigned to those CPUs will be shutdown once the current workload is completed. Any new workload, is assigned to new schedulers (or the assigned CPUs)

For more information read MSDN BoL article.

Hope this helps,
_Sqltimes
Follow

Get every new post delivered to your Inbox.