Archive for the ‘SQL Server’ Category

TSQL: Accuracy of DATETIME

Here’s something I didn’t know about the DATETIME data type in SQL Server…. ?View Code TSQLSELECT 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) […]

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. ?View Code TSQLSELECT 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 […]

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 ?View Code TSQLRESTORE 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, […]

What permissions have your users really got?

Here’s a TSQL script to audit the permissions of certain AD users access to a SQL Server instance. This script uses the EXECUTE AS LOGIN clause and the system function sys.fn_my_permissions. All databases on the SQL Server instance are queried and the script will output results containing the assigned user permissions. To get started all […]

Correct a log file with too many VLFs

The what and why of this post is explained here Transaction Log VLFs – too many or too few?. Presented here is a quick practical example of how you might correct this issue in a database log file. As a first step you’ll need to get the logical name of your log file for the […]