Archive for the ‘Server Maintenance’ Category

Before a filegroup can be removed all files associated with it need to be deleted.

Before a file can be deleted all objects associated with it need to be removed.

Just removing a file not a filegroup

If you have multiple files on a filegroup and you just want to get rid of one you can run this command.

DBCC ShrinkFile(logical_file_name,EmptyFile);

This will move any objects in the file onto the other files in the filegroup. You can then delete the file.

ALTER DATABASE DatabaseName REMOVE FILE logical_file_name;

Removing a file and a filegroup

To do this you will have to make sure that here are no objects associated with the file group. This could be Tables etc, Indexes, or even Blob objects.

This bit of code will show you the objects on a file group.

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]

FROM sys.indexes i

INNER JOIN sys.filegroups f

ON i.data_space_id = f.data_space_id

INNER JOIN sys.all_objects o

ON i.[object_id] = o.[object_id]

WHERE i.data_space_id = f.data_space_id

AND o.type = ‘U’ — User Created Tables

I got this from:

If there are objects you want to keep you will need to move them to another filegroup. To move a non-clustered index you will need to drop and recreate it on the new file group. To move a table you need to drop and recreate the clustered index.

Once the filegroup has no user objects empty you can run the same commands for removing a file as – shrinkfile and remove file.

Then you will need to run the code below to drop the filegroup:

DATABASE [databaseName] REMOVE FILEGROUP [Filegroupname];

Remember: you can not drop the Primary filegroup.


I have used this on most of my servers so that when I click restore or Backup database it doesn’t go off to a directory under programme files on the C drive and goes to the root of the backup directory I use.

SQL 2005 version

This part of the script will display the current default backup directory.
DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’,
@key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer’,
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory as BackupDirectory
This script will set G:\Maintenance\userData as the new default directory.
EXEC master..xp_regwrite
@key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer’,

SQL 2000 version

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’,
@BackupDirectory=@BackupDirectory OUTPUT
SELECT @BackupDirectory as BackupDirectory
EXEC master..xp_regwrite

This goes through all databases and finds any indexes which have fragmentation greater than 15% and reindexes them. The loop near the beginning is because sys.dm_db_index_physical_stats needs a database ID. If you do not pass a database Id and leave it as null it will go thrugh all database – which will cause a problem if it is not writeable (read_only, offline, In Recovery)

To use the null options the account running the command needs: VIEW SERVER STATE

This task will re-index and index where fragmentation is greater than 15% provided that there aare more than 50 pages used in the index. If there are less than this the chances are the index isn’t being used and Query Analyser will perform a table scan.

I took the initial idea of using this function to populate a table for reindexing from a blog but can’t remember which. I have adapted it to suit the tasks I wanted to perform.

I find STATISTICS_NORECOMPUTE = OFF a little unintuitive. It means do not not recomputed stats – or in English: recompute the stats.

On most servers this tasks takes between 5 and 20 minutes to run and I can run it every night. There are a few though where it is run instead on a weekly basis.

I also use an extra line after AND ps.index_type_desc <> ‘heap’ to exclude some databases. This is used along with AND [Name] NOT IN (‘LIST OF DBNAMES TO EXCLUDE’); The names should be like this (‘dbname’,’dbname2’). This is where the there is a database that does a lot of ETL work during the night so it is best to run a separate re-index job for that once it has finished.

If speed is a problem you can replace
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, ‘detailed’)
FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, NULL)
This will then use the default Limited for gather statistics on the indexes – of course the faster version does not get such accurate data back.

use tempdb;

if not object_id(‘indexData’) is null
  drop table [indexData];

CREATE TABLE [dbo].[indexData](
[dbName] varchar(250) Not NULL,
[di] int Not NULL,
[oi] varchar(150) NOT NULL,
[Frag] int not null,
[pages] int not null,
[iType] varchar(250) not null,
[oName] varchar(250) NULL,
[schemaName] varchar(250) NULL);

DECLARE @dbCountID as int;
SET @dbCountID = 0;
DECLARE @topID as int;
SELECT @topID = Max(database_id) FROM master.sys.databases;
While @dbCountID < @topID
   SELECT TOP (1) @dbCountID = database_id FROM master.sys.databases
   WHERE database_id > @dbCountID
   AND is_Read_Only = 0 and state_desc = ‘ONLINE’

   INSERT INTO [indexData]([dbName],[di],[oi],[Frag],[pages],[iType])
   SELECT Distinct DB_NAME ( @dbCountID ), @dbCountID,    ps.object_id,ps.avg_fragmentation_in_percent,ps.Page_Count,ps.index_type_desc
   FROM sys.dm_db_index_physical_stats (@dbCountID, NULL, NULL, NULL, ‘detailed’) ps
   LEFT OUTER JOIN master.sys.databases AS sd
   ON ps.database_id = sd.database_id
   Where (ps.database_id > 4) AND sd.is_Read_only = 0 AND sd.state_desc = ‘online’
   AND ps.Page_Count > 50
   AND ps.avg_fragmentation_in_percent > 15
   AND ps.index_type_desc <> ‘heap’
   AND sd.database_id = @dbCountID;
DECLARE @dbName varchar(250);
DECLARE dbCursor CURSOR Local Fast_Forward FOR
SELECT Distinct [dbName]
FROM [indexData];

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName;
    DECLARE @sql as Varchar(500)
    SET @sql = ‘Use [‘ + @dbName + ‘]’
    Set @sql = @sql + ‘ Update tempdb..[indexData] ‘
    Set @sql = @sql + ‘ SET oName = object_Name(oi), ‘
    Set @sql = @sql + ‘ schemaName = (select schema_Name(sobj.schema_id) from sys.objects sobj where sobj.object_id = oi) ‘
   Set @sql = @sql + ‘ WHERE dbName = ”’ + @dbName + ”’;’;
   Exec (@sql);
   Use tempdb;
   FETCH NEXT FROM dbCursor INTO @dbName;
CLOSE dbCursor;
DECLARE @dbName2 varchar(5000);
DECLARE dbCursor2 CURSOR Local Fast_Forward FOR
SELECT Distinct ‘[‘ + dbName + ‘].[‘ + schemaName + ‘].[‘ + oName + ‘]’ as databaseObject
FROM [indexData]
WHERE oName not like ‘perfValuesForProcess%’;

OPEN dbCursor2;
FETCH NEXT FROM dbCursor2 INTO @dbName2;
     DECLARE @sql2 as Varchar(8000);
     Set @sql2 = ‘ALTER INDEX ALL ON ‘ + @dbName2
     Set @sql2 = @sql2 + ‘ REBUILD WITH (SORT_IN_TEMPDB = ON,       STATISTICS_NORECOMPUTE = OFF);’;
     FETCH NEXT FROM dbCursor2 INTO @dbName2;
CLOSE dbCursor2;
Use tempdb;
Drop Table [indexData];

MSDN sys.dm_db_index_physical_stats (