Manage Database files and file groups in SQLServer

SQLServer has three types of files

Primary – Primary data file contains the startup information of the database and also has information about other files in the database. The file extension is .mdf

Secondary – Secondary data file is used to store user data and these files are optional. The secondary files are used to spread data across multiple disks. The file extension is .ndf

Transaction log- Transaction log file holds the log information that is needed to recover the database. The file extension is .ldf

Every database has one primary file group and it is the best practice to create a user-defined file group for user data.

Create a user-defined file group:  Use below syntax to create user defined file group “USER_DATA”

USE master
GO
ALTER DATABASE TestDB
ADD FILEGROUP USER_DATA;
GO

ALTER DATABASE TestDB
ADD FILE
(
    NAME = USER_DATA1,
    FILENAME = ‘D: \DATA\user_data.ndf',
    SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB
),
TO FILEGROUP USER_DATA;
GO

You can also create a user-defined file group using Microsoft SQLServer Management Studio

1       . Right click on the database and choose Properties

2       . Go the FileGroups page, check on add and provide the file group name.

3       . Select the Default checkbox to make it as default file group

Modify User-defined/seconday file group as default:

USE master
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [USER_DATA] DEFAULT

Please note that all the user objects will create in default file group.

Resize the file or Modifying a file:  Use below syntax to increase the max size of the file

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
    (NAME = USER_DATA1, MAXSIZE = 2000MB);
GO

Add a data file to existing file group:  Many of the users will come across this If you primary data file drive is out of space. Create a new drive and add a new ndf file to the file group.

USE [master]
GO
ALTER DATABASE [TestDB] ADD FILE ( NAME = N'USER_DATA2', FILENAME = N'E:\SQLData\user_data2.ndf' , SIZE = 10MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB) TO FILEGROUP [USER_DATA]
GO

Moving a file to a new location: You must physically move the file to the new disk/direcotry before running this syntax.

USE master;
GO
ALTER DATABASE TestDB
MODIFY FILE
( NAME = USERd_ATA2,
    FILENAME = N’E:\SQLData\user_data2.ndf'
); GO

Make sure to stop and start the SQL Server or take the database OFFLINE and then ONLINE To implement the change.

Removing a file from database: Use following syntax to remove a file from database

USE master;
GO
ALTER DATABASE TestDB
REMOVE FILE USER_DATA2;
GO

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com/

How to move TempDB in SQLServer?

If the TempDB grows fast and does not have have enough space to grow then the best option is move the TempDB to another physical drive, which will help to improve database performance.

You will receive below error messages, if tempdb is full

Source: MSSQLSERVER
Event ID: 17052
Description: The log file for database 'tempdb' is full.
Back up the transaction log for the database to free up some log space

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Follow the below steps to move the tempdb to new location.

Step 1: Open a “New Query” Window and run the below command to get the path and names of the TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO

Or

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Step 2: In the following query Replace “NewPath” with new drive path and run the command to change the LDF and MDF file path.

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '}\templog.ldf');
GO

The tempDB definition has been changed, but no changes are made to until you restart SQLServer. Please stop and restart SQL Server and

Step 3: Stop and Start the SQLServer

Control Panel -- > Administrative Tools --> Services --> Right click on SQLServer (MSSQLSERVER) --> Restart

or

SQL Server Configuration Manager --> SQLServer 2008 Services --> Right Click on SQLServer (MSSQLSERVER) --> Restart

Now TempDB files created in new drive.

Step 4: Check the TempDB new file location patch

SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('TempDB');

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

How to Shrink Tempdb in SQLServer

Once in a while DBA’s will come across the situation that tempdb is full and no longer able to expand. You can avoid this situation by proactively monitoring the system with notification system setup for particular threshold.

To avoid this situation proactive monitoring is the best solution and it is always recommend to setup alerts for particular threshold.

It's highly recommended to disable "Autogrow" option for better performance. By default this option is enabled and it grows by 10% of its initial size. While performing auto growth, it will lock all applications and internal operations. This will also lead to fragmentation in the tempdb database.

Each time the SQL Server service starts, the tempdb is newly created by copying from the model database and inheriting some database configurations from it. The default size of tempdb is 8MB

You can try below solutions to shrink the databases.

Method 1: Shrink database using DBCC SHRINKDATABASE.

DBCC SHRINKDATABASE (tempdb,’’)
For ex:-
DBCC SHRINKDATABASE(tempdb,40)

This command shrinks the tempdb database as a whole with specified percentage.

Method 2: Shrink the tempdb data file or log file

Use tempdb
GO
--shrink the data file
DBCC shrinkfile (tempdb_data,’< target_size_in_MB’>)
GO
--shrink the log file
DBCC shrinkfile (tempdb_log, ’)

Ex:-
DBCC shrinkfile (tempdb_data, 5000);

Method 3: shrink the tempdb data file or log file using T-SQL Command

-- Shrink the data file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_data', SIZE = target_size_in_MB)

-- Shrink the log file
ALTER DATABASE tempdb MODIFY FILE (NAME = 'temp_log', SIZE = target_size_in_MB)

Where target_size_in_MB= Desired target size for the data/log file

Errors while shrinking tempdb
If you have any active transactions in tempdb then you may receive below consistency errors.

1. Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.

This error may not be an indicative of any corruption, but it causes the shrink operation to fail.

2. Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.

This 8909 error indicates tempdb corruption. You can clean up the consistency errors by restarting the SQL Server. If you still see any errors then restore the database from valid backup.

3. DBCC SHRINKFILE: Page 1:456120 could not be moved because it is a work file page.

This error indicates that Page could not be moved because it is a work file page. You can release the cached objects by running “DBCC FREEPROCCACHE”. Now try shrink tempdb once again to release the free space.

Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

Shrink Database fails with Error “Backup, file manipulation operations”

In some cases users may get below error while trying to shrink their databases manually or using SQLServer management studio.

Executing the query "DBCC SHRINKDATABASE(N'OperationsManagerDW', 10, TR..." failed with the following error: "Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Possible reason:
The backup is running when user are trying to perform the database shrink. Please note that below operations may not be performed at the same time as a backup operation

• File management operations such as the ALTER DATABASE statement with either ADD FILE or REMOVE FILE clauses.
• The file truncation phase of shrink database or shrink file.

Please note that if backup is running and one of the above operations is attempted, then operation fails immediately. When user is trying to add or remove a file and if backups is started then backup will wait for a timeout period, then fail.

If user is trying to shrink database or file while a backup is running, the shrink stops without truncating the file. If backup started after shrink database operation then backup waits for shrink operation to complete.

Action:
Reissue the statement after the current backup or file manipulation operation is completed.

Regards,
Satishbabu Gunukula
http://sqlserver-expert.com