Basically, in SQL Server there are two database files, one as physical file name and the other as logical file name. We will be much focusing on the logical file name of a database. Generally, when a databases is created, one primary data file, two secondary data files and one logical files are created. The logical files in a database helps holding the active transaction of a database. We should see that the logical files are always kept unique to resist from unnecessary confusions.
Usually, in SQL server while managing a database in ethical
form, just one logical file is created. In case if there are too much
transactions in a database, which keeps them filled always, then probabilities could
be to create a new logical file.
EXEC sp_helpdb <Database_Name>
Example:
USE SQLArena_Test
GO
sp_helpdb SQLArena_Test
Following the above
query can show the details of the database mentioned. In order to select the
logical file name from the database, use the below query:
select file_id, NAME as [LOGICAL_FILE_NAME],
PHYSICAL_NAME
from sys.database_files
In order to rename the
logical file names from the mentioned screenshot to a new name, let’s follow
the script below:
Rename SQLArena_Log to
SQLArena_Log_1, along with change in physical data files and indexes.
USE SQLArena_Test
GO
Alter Database [SQLArena_Test]
Modify file (Name=SQLArena_Data1, NEWNAME=SQLArena_Data_1)
GO
Alter Database [SQLArena_Test]
Modify file (Name=SQLArena_Log, NEWNAME=SQLArena_Log_1)
GO
Alter Database [SQLArena_Test]
Modify file (Name=SQLArena_Data2, NEWNAME=SQLArena_Data_2)
GO
Alter Database [SQLArena_Test]
Modify file (Name=SQLArena_Indexes,NEWNAME=SQLArena_Indexes)
0 comments:
Post a Comment