Sql Server : Error Message : DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.

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,

_Sqltimes

Read Full Post »

Sql Server Errors: 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.

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

Read Full Post »

VMWare : “HostStorageSystem.ComputeDiskPartitionInfo” for object “storageSystem” on ESXi failed

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

 

Step 3: Enter root and password to log in.

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

Hope this helps,
_Sqltimes

Read Full Post »