Wednesday, January 2, 2013

Moving a table to a different file group (clustered index move)

Sometimes it becomes necessary to move tables to a different file group. This may be necessary if disk space runs out and a new one is added. In order to use the newly available space, a new file group needs to be created with ndf files assigned to it. Database objects can then be moved to this new file group residing on the newly available space. 

Non clustered indexes and clustered indexes can be moved to the new file group The process of moving a Non clustered index is fairly simple with the CREATE INDEX statement used in conjunction with the DROP EXISTING clause 

Clustered indexes however in many cases are created along with the primary key constraint. In such a case, the clustered index cannot be dropped and re-created without dropping the constraint first 

 Below is an example of a simple table created with a CLUSTERED PRIMARY KEY

CREATE TABLE Index_Drop_Test
( c1 INT NOT NULL
,c2 INT
CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED
)

When we attempt to move this table to another file group by dropping and re-creating the clustered index, the following error is encountered

 SQL Statement to drop and re-create clustered index (not the right approach to use DROP INDEX)

DROP INDEX PK_Primary
ON dbo.Index_Drop_Test

Error Message

Msg 3723, Level 16, State 4, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.Index_Drop_Test.PK_Primary'. It is being used for PRIMARY KEY constraint enforcement. 

Correct Approach

1) Drop the constraint

ALTER TABLE Index_Drop_Test
DROP CONSTRAINT PK_Primary

2) Re-create the constraint on the secondary file group


ALTER TABLE Index_Drop_Test
ADD CONSTRAINT PK_Primary PRIMARY KEY CLUSTERED (c1)
ON SECONDARY

Wednesday, December 19, 2012

Database Size in MB


SELECT SUM (size*.008) AS database_size_in_MB, DB_NAME (dbid) AS database_name
FROM sys.sysaltfiles
WHERE DB_NAME (dbid) IS NOT NULL
GROUP BY DB_NAME (dbid)

Wednesday, October 31, 2012

Query to find the latest backup date and size

SELECT DISTINCT a.*
FROM
(SELECT
d.database_name
,d.backup_finish_date
,(backup_size/1024/1024) AS backup_size_in_MBs
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id) a
INNER JOIN
(SELECT
 d.database_name
,MAX(d.backup_finish_date) AS max_backup_finish_date
FROM msdb.dbo.backupmediafamily a
INNER JOIN msdb.dbo.backupset d
ON a.media_set_id = d.media_set_id
WHERE [type] = 'D'
GROUP BY d.database_name) d
ON a.backup_finish_date = d.max_backup_finish_date
AND a.database_name = d.database_name

Monday, September 10, 2012

SQL Server Index Fragmentation Levels Using DMV (Fragmentation greater than 15%)



SELECT DB_NAME(database_id) AS database_name
, so.[name] AS table_name
, dmv.index_id
, si.[name]
, dmv.index_type_desc
, dmv.alloc_unit_type_desc
, dmv.index_depth
, dmv.index_level
, avg_fragmentation_in_percent
, page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dmv
INNER JOIN sysobjects so
ON dmv.[object_id] = so.id
INNER JOIN sys.indexes si
ON
dmv.object_id = si.object_id
AND dmv.index_id = si.index_id
WHERE dmv.avg_fragmentation_in_percent > 15
ORDER BY table_name, si.name



Thursday, August 23, 2012

Script to list all indexes in a database with their seek, scan, update count and their last seek, scan, update times


SELECT DB_NAME(database_id) AS [DB Name]
, OBJECT_NAME (dm.object_id) AS [Table Name]
, si.[name]
, user_seeks AS [User Seeks]
, user_scans AS [User Scans]
, user_lookups AS [User Lookups]
, user_updates AS [User Updates]
, last_user_seek AS [Last User Seek]
, last_user_scan AS [Last User Scan]
, last_user_lookup AS [Last User Lookup]
, last_user_update AS [Last User Update]
--, *
FROM sys.dm_db_index_usage_stats dm
INNER JOIN sys.indexes si
ON si.index_id = dm.index_id
AND dm.object_id = si.object_id
INNER JOIN sysobjects so
ON so.id = si.object_id
WHERE dm.database_id = DB_ID()
AND si.name IS NOT NULL
AND OBJECTPROPERTY (dm.object_id, 'IsUserTable') = 1
ORDER BY COALESCE (last_user_seek, last_user_scan, last_user_lookup, last_user_update)

Wednesday, August 15, 2012

How to register and de-register an SPN

To view all SPNs registered under a service account, usually the one under which SQL Services are running, type the following command in the CMD prompt

                                           C:> setspn -l [domain\account]

The above should display all SQL services running under the account, that have registered their SPNs in the active directory. The format of the displayed data is like the below example

                                 MSSQLSvc/[FQDN servername]:[port number]

The above result will also serve as means to verify if indeed the SQL Server has registered an SPN correctly. This can be done by cross verifying the server name and port number against the actual server

The port number that a SQL server is running on can be found from the error log. This has to match the port with which the SPN is registered with

De-registering a SPN:

SPN can be deleted using the following command

        c:\> setspn -d MSSQLSvc/[FQDN servername]:[port number] [domain\service account name]

SPN can be registered in a similar way using the following command

        c:\> setspn -a MSSQLSvc/[FQDN servername]:[port number] [domain\service account name]

Notice that the only difference between registering and de-registering a SPN is the switch option. -d for de-registering and -a for registering


Tuesday, August 14, 2012

List all trace events and trace categories

SELECT s2.[category_id] AS [Category_ID] , s2.name AS [Category_Name] , s1.trace_event_id AS [Trace_Event_ID] , s1.name AS [Event_Class_name] , s4.[trace_column_id] AS [Trace_Colum_ID] , s4.name AS [Column_Name] , s4.[type_name] AS [data_Type] FROM sys.trace_events s1 INNER JOIN sys.trace_categories s2 ON s1.category_id = s2.category_id INNER JOIN sys.trace_event_bindings s3 ON s1.trace_event_id = s3.trace_event_id INNER JOIN sys.trace_columns s4 ON s3.trace_column_id = s4.trace_column_id ORDER BY s2.[category_id] , s1.name , s4.name