Archive for the ‘SQL Server’ Category

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 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 […]

Generate PK drops and creates using TSQL

Here’s just a couple of queries I used to generate PK drops and creates using the sys.key_constraints view. I wanted to do this for a database using Poor Mans Partitioning. Generate drops… ?View Code TSQLSELECT ‘ALTER TABLE ‘ + OBJECT_SCHEMA_NAME(t.[object_id]) + ‘.’ + + ‘ DROP CONSTRAINT ‘ + c.[name] + ‘;’ FROM sys.key_constraints […]