TSQL: Accuracy of DATETIME

Here’s something I didn’t know about the DATETIME data type in SQL Server….

SELECT CAST('2014-04-10 00:00:00.000' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.001' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.002' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.003' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.004' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.005' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.006' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.007' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.008' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.009' AS DATETIME)

The resultset will be as follows…

2014-04-14 00:00:00.000
2014-04-14 00:00:00.000
2014-04-14 00:00:00.003
2014-04-14 00:00:00.003
2014-04-14 00:00:00.003
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.010

The sharp eyed amongst you would have noticed some values milliseconds have been rounded. This is explained the the “accuracy section of the documentation for DATETIME

Rounded to increments of .000, .003, or .007 seconds

If this is an issue for your application then you should consider using DATETIME2.


TSQL: Table count per filegroup

Here’s a query that uses the SQL Server System Catalog Views to return a table count per table. I used this to check even table distribution in a data warehouse.

SELECT ds.name AS filegroup_name,
		COUNT(DISTINCT t.[object_id]) AS table_count
FROM sys.tables t
INNER JOIN sys.indexes i 
	ON t.[object_id] = i.[object_id]
	AND i.is_primary_key = 1
INNER JOIN sys.filegroups ds 
	ON i.data_space_id = ds.data_space_id
INNER JOIN sys.partitions p 
	ON i.[object_id] = p.[object_id]
	AND i.index_id = p.index_id
GROUP BY ds.name;

The resultset will look something like…

filegroup_name     table_count
file_1             5
file_2             5
file_3             5
file_4             5

CentOS: clvmd startup timed out

I received the following error, on CentOS 6.5, when configuring a High Availability cluster. This also cause the computer to freeze on the os boot.

clvmd startup timed out

The fix was found here and involved an edit of /etc/lvm/lvm.conf. This required a minor change to the library_dir line as the location of these libraries have changed. See below for the full list.

library_dir = "/lib64"
locking_type = 3
fallback_to_local_locking = 1
fallback_to_clustered_locking = 0
locking_dir = "/var/lock/lvm"

The database cannot be recovered because the log was not restored

A test restore of a SQL Server database had somehow been left in the “RESTORING” state. I attempted to bring the database online with

RESTORE DATABASE db_name;

But I was greeted with…

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To fix you need to drop and restore the database again…

DROP DATABASE db_name;
 
USE [master]
GO
-- Create db again...
CREATE DATABASE [db_name];
-- restore over with usual backup scripts...

Adding an ISCSI volume to the nodes

This post is part of a series that will deal with setting up a MySQL shared storage cluster using VirtualBox & FreeNAS. In this post we deal with the setup of an iscsi volume on two nodes. The text in bold below represent commands to be executed in the shell.

  • yum install iscsi-initiator-utils
  • iscsiadm -m discovery -t st -p 192.168.3.100 <- This command should show the LUNs advertised by FreeNAS.
  • /etc.init.d/iscsi restart
  • fdisk -l <- This should show new devices that are available. In this case it’s /dev/sdb
  • mkfs.ext3 /dev/sdb <- Format device.
  • mkdir /mnt/iscsi <- Where the device will be mounted.
  • mount /dev/sdb /mnt/iscsi
  • vi /etc/fstab (Add the following line without quotes “/dev/sdb       /mnt/iscsi      ext3     _netdev 0 0″
  • Reboot and ensure the mount is functional. Write a text file to test the filesystem.
  • Repeat all steps, apart from the mkfs.ext3 command, for node2.