Opsi Grup File dan File ALTER DATABASE (Transact-SQL)

Memodifikasi file dan grup file yang terkait dengan database. Menambahkan atau menghapus file dan grup file dari database, dan mengubah atribut database atau file dan grup filenya. Untuk opsi UBAH DATABASE lainnya, lihat MENGUBAH DATABASE.

Untuk informasi selengkapnya tentang konvensi sintaks, lihat Konvensi sintaks t-SQL.

Pilih produk

Di baris berikut, pilih nama produk yang Anda minati, dan hanya informasi produk tersebut yang ditampilkan.

* SQL Server *  

Instans Terkelola SQL

 

Sintaks

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ]
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ]
  | REMOVE FILE logical_file_name
  | MODIFY FILE <filespec>
}

<filespec>::=
(
    NAME = logical_file_name
    [ , NEWNAME = new_logical_name ]
    [ , FILENAME = {'os_file_name' | 'filestream_path' | 'memory_optimized_data_path' } ]
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
    [ , OFFLINE ]
)

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

Argumen

<>add_or_modify_files::=

Menentukan file yang akan ditambahkan, dihapus, atau diubah.

database_name Adalah nama database yang akan dimodifikasi.

TAMBAHKAN FILE Menambahkan file ke database.

KE FILEGROUP { filegroup_name } Menentukan grup file yang akan ditambahkan ke file yang ditentukan. Untuk menampilkan grup file saat ini dan grup file mana yang merupakan default saat ini, gunakan tampilan katalog sys.filegroups .

TAMBAHKAN FILE LOG Menambahkan file log ditambahkan ke database yang ditentukan.

HAPUS FILE logical_file_name Menghapus deskripsi file logis dari instans SQL Server dan menghapus file fisik. File tidak dapat dihapus kecuali kosong.

logical_file_name Adalah nama logis yang digunakan dalam SQL Server saat mereferensikan file.

Peringatan

Menghapus file database yang memiliki FILE_SNAPSHOT cadangan yang terkait dengannya akan berhasil, tetapi rekam jepret terkait tidak akan dihapus untuk menghindari pembatalan pencadangan yang mengacu pada file database. File akan dipotok, tetapi tidak akan dihapus secara fisik untuk menjaga cadangan FILE_SNAPSHOT tetap utuh. Untuk informasi selengkapnya, lihat SQL Server Pencadangan dan Pemulihan dengan Microsoft Azure Blob Storage. Berlaku untuk: SQL Server ( SQL Server 2016 (13.x) dan yang lebih baru).

MODIFIKASI FILE Menentukan file yang harus dimodifikasi. Hanya satu <properti filespec yang> dapat diubah pada satu waktu. NAME harus selalu ditentukan dalam <filespec> untuk mengidentifikasi file yang akan dimodifikasi. Jika UKURAN ditentukan, ukuran baru harus lebih besar dari ukuran file saat ini.

Untuk mengubah nama logis file data atau file log, tentukan nama file logis yang akan diganti namanya dalam NAME klausul, dan tentukan nama logis baru untuk file dalam NEWNAME klausul. Contohnya:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name )

Untuk memindahkan file data atau file log ke lokasi baru, tentukan nama file logis saat ini dalam NAME klausul dan tentukan jalur baru dan nama file sistem operasi dalam FILENAME klausul. Contohnya:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )

Saat Anda memindahkan katalog teks lengkap, tentukan hanya jalur baru dalam klausa FILENAME. Jangan tentukan nama file sistem operasi.

Untuk informasi selengkapnya, lihat Memindahkan File Database.

Untuk grup file FILESTREAM, NAME dapat dimodifikasi secara online. FILENAME dapat dimodifikasi secara online; namun, perubahan tidak berlaku sampai setelah kontainer direlokasi secara fisik dan server dimatikan dan kemudian dimulai ulang.

Anda dapat mengatur file FILESTREAM ke OFFLINE. Ketika file FILESTREAM offline, grup file induknya akan ditandai secara internal sebagai offline; oleh karena itu, semua akses ke data FILESTREAM dalam grup file tersebut akan gagal.

Catatan

<> opsi add_or_modify_files tidak tersedia dalam Database Mandiri.

<filespec>::=

Mengontrol properti file.

NAME logical_file_name Menentukan nama logis file.

logical_file_name Adalah nama logis yang digunakan dalam instans SQL Server saat mereferensikan file.

NEWNAME new_logical_file_name Menentukan nama logis baru untuk file tersebut.

new_logical_file_name Adalah nama untuk menggantikan nama file logis yang ada. Nama harus unik dalam database dan mematuhi aturan untuk pengidentifikasi. Nama dapat berupa karakter atau konstanta Unicode, pengidentifikasi reguler, atau pengidentifikasi yang dibatasi.

FILENAME { 'os_file_name' | 'filestream_path'' | memory_optimized_data_path'} Menentukan nama file sistem operasi (fisik).

' os_file_name ' Untuk grup file standar (ROWS), ini adalah jalur dan nama file yang digunakan oleh sistem operasi saat Anda membuat file. File harus berada di server tempat SQL Server diinstal. Jalur yang ditentukan harus ada sebelum menjalankan pernyataan ALTER DATABASE.

Catatan

SIZEParameter , MAXSIZE, dan FILEGROWTH tidak dapat diatur ketika jalur UNC ditentukan untuk file.

Database sistem tidak dapat berada di direktori berbagi UNC.

File data tidak boleh diletakkan pada sistem file terkompresi kecuali file adalah file sekunder baca-saja, atau jika database bersifat baca-saja. File log tidak boleh diletakkan pada sistem file terkompresi.

Jika file berada di partisi mentah, os_file_name harus menentukan hanya huruf kandar dari partisi mentah yang ada. Hanya satu file yang dapat diletakkan pada setiap partisi mentah.

'filestream_path' Untuk grup file FILESTREAM, FILENAME mengacu pada jalur tempat data FILESTREAM akan disimpan. Jalur hingga folder terakhir harus ada, dan folder terakhir tidak boleh ada. Misalnya, jika Anda menentukan jalur C:\MyFiles\MyFilestreamData, maka C:\MyFiles harus ada sebelum Anda menjalankan ALTER DATABASE, tetapi MyFilestreamData folder tidak boleh ada.

Catatan

Properti SIZE dan FILEGROWTH tidak berlaku untuk grup file FILESTREAM.

'memory_optimized_data_path' Untuk grup file yang dioptimalkan memori, FILENAME mengacu pada jalur tempat data yang dioptimalkan memori akan disimpan. Jalur hingga folder terakhir harus ada, dan folder terakhir tidak boleh ada. Misalnya, jika Anda menentukan jalur C:\MyFiles\MyData, maka C:\MyFiles harus ada sebelum Anda menjalankan ALTER DATABASE, tetapi MyData folder tidak boleh ada.

Grup file dan file (<filespec>) harus dibuat dalam pernyataan yang sama.

Catatan

Properti SIZE dan FILEGROWTH tidak berlaku untuk grup file MEMORY_OPTIMIZED_DATA.

Untuk informasi selengkapnya tentang grup file yang dioptimalkan memori, lihat Grup File yang Dioptimalkan Memori.

Ukuran SIZE Menentukan ukuran file. UKURAN tidak berlaku untuk grup file FILESTREAM.

Ukuran Adalah ukuran file.

Ketika ditentukan dengan TAMBAHKAN FILE, ukuran adalah ukuran awal untuk file. Ketika ditentukan dengan MODIFIKASI FILE, ukuran adalah ukuran baru untuk file, dan harus lebih besar dari ukuran file saat ini.

Ketika ukuran tidak disediakan untuk file utama, SQL Server menggunakan ukuran file utama dalam database model. Ketika file data sekunder atau file log ditentukan tetapi ukuran tidak ditentukan untuk file, Mesin Database membuat file 1 MB.

Akhiran KB, MB, GB, dan TB dapat digunakan untuk menentukan kilobyte, megabyte, gigabyte, atau terabyte. Defaultnya adalah MB. Tentukan bilangan bulat dan jangan sertakan desimal. Untuk menentukan pecahan megabyte, konversikan nilai menjadi kilobyte dengan mengalikan angka dengan 1024. Misalnya, tentukan 1536 KB alih-alih 1,5 MB (1,5 x 1024 = 1536).

Catatan

SIZE tidak dapat diatur:

• Ketika jalur UNC ditentukan untuk file
• Untuk FILESTREAM grup file dan MEMORY_OPTIMIZED_DATA

MAXSIZE { max_size| UNLIMITED } Menentukan ukuran file maksimum yang dapat ditumbuhkan file.

max_size Adalah ukuran file maksimum. Akhiran KB, MB, GB, dan TB dapat digunakan untuk menentukan kilobyte, megabyte, gigabyte, atau terabyte. Defaultnya adalah MB. Tentukan bilangan bulat dan jangan sertakan desimal. Jika max_size tidak ditentukan, ukuran file akan meningkat hingga disk penuh.

UNLIMITED Menentukan bahwa file bertambah hingga disk penuh. Dalam SQL Server, file log yang ditentukan dengan pertumbuhan tak terbatas memiliki ukuran maksimum 2 TB, dan file data memiliki ukuran maksimum 16 TB. Tidak ada ukuran maksimum ketika opsi ini ditentukan untuk kontainer FILESTREAM. Ini terus bertambah sampai disk penuh.

Catatan

MAXSIZE tidak dapat diatur ketika jalur UNC ditentukan untuk file.

FILEGROWTH growth_increment Menentukan kenaikan pertumbuhan otomatis file. Pengaturan FILEGROWTH untuk file tidak boleh melebihi pengaturan MAXSIZE. FILEGROWTH tidak berlaku untuk grup file FILESTREAM.

growth_increment Adalah jumlah ruang yang ditambahkan ke file setiap kali ruang baru diperlukan.

Nilai dapat ditentukan dalam MB, KB, GB, TB, atau persen (%). Jika angka ditentukan tanpa akhiran MB, KB, atau % , defaultnya adalah MB. Ketika % ditentukan, ukuran kenaikan pertumbuhan adalah persentase yang ditentukan dari ukuran file pada saat kenaikan terjadi. Ukuran yang ditentukan dibulatkan ke 64 KB terdekat.

Nilai 0 menunjukkan bahwa pertumbuhan otomatis diatur ke nonaktif dan tidak ada ruang tambahan yang diizinkan.

Jika FILEGROWTH tidak ditentukan, nilai defaultnya adalah:

Versi	Nilai default
Dimulai dengan SQL Server 2016 (13.x)	Data 64 MB. File log 64 MB.
Dimulai dengan SQL Server 2005 (9.x)	Data 1 MB. File log 10%.
Sebelum SQL Server 2005 (9.x)	Data 10%. File log 10%.

Catatan

FILEGROWTH tidak dapat diatur:

• Ketika jalur UNC ditentukan untuk file
• Untuk FILESTREAM grup file dan MEMORY_OPTIMIZED_DATA

OFFLINE Mengatur file secara offline dan membuat semua objek dalam grup file tidak dapat diakses.

Perhatian

Gunakan opsi ini hanya ketika file rusak dan dapat dipulihkan. File yang diatur ke OFFLINE hanya dapat diatur secara online dengan memulihkan file dari cadangan. Untuk informasi selengkapnya tentang memulihkan satu file, lihat MEMULIHKAN.

<Opsi filespec> tidak tersedia dalam Database Mandiri.

<>add_or_modify_filegroups::=

Menambahkan, mengubah, atau menghapus grup file dari database.

TAMBAHKAN FILEGROUP filegroup_name Menambahkan grup file ke database.

CONTAINS FILESTREAM Menentukan bahwa grup file menyimpan objek besar biner FILESTREAM (BLOB) dalam sistem file.

BERISI MEMORY_OPTIMIZED_DATA

Berlaku untuk: SQL Server ( SQL Server 2014 (12.x) dan yang lebih baru)

Menentukan bahwa grup file menyimpan data memori yang dioptimalkan dalam sistem file. Untuk informasi selengkapnya, lihat Pengoptimalan OLTP Dalam Memori - In-Memory. Hanya satu MEMORY_OPTIMIZED_DATA grup file yang diizinkan per database. Untuk membuat tabel memori yang dioptimalkan, grup file tidak boleh kosong. Setidaknya harus ada satu file. filegroup_name mengacu pada jalur. Jalur hingga folder terakhir harus ada, dan folder terakhir tidak boleh ada.

HAPUS GRUPFILE filegroup_name Menghapus grup file dari database. Grup file tidak dapat dihapus kecuali kosong. Hapus semua file dari grup file terlebih dahulu. Untuk informasi selengkapnya, lihat "HAPUS logical_file_name FILE," sebelumnya dalam topik ini.

Catatan

Kecuali FILESTREAM Garbage Collector telah menghapus semua file dari kontainer FILESTREAM, ALTER DATABASE REMOVE FILE operasi untuk menghapus kontainer FILESTREAM akan gagal dan mengembalikan kesalahan. Lihat bagian Menghapus Kontainer FILESTREAM nanti dalam topik ini.

UBAH FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =new_filegroup_name } Memodifikasi grup file dengan mengatur status ke READ_ONLY atau READ_WRITE, menjadikan grup file grup file default untuk database, atau mengubah nama grup file.

<> filegroup_updatability_option Mengatur properti baca-saja atau baca/tulis ke grup file.

DEFAULT Mengubah grup file database default menjadi filegroup_name. Hanya satu grup file dalam database yang bisa menjadi grup file default. Untuk informasi selengkapnya, lihat File Database dan Grup File.

NAME = new_filegroup_name Mengubah nama grup file ke new_filegroup_name.

AUTOGROW_SINGLE_FILE Berlaku untuk: SQL Server ( SQL Server 2016 (13.x) dan yang lebih baru)

Ketika file dalam grup file memenuhi ambang batas autogrow, hanya file yang tumbuh. Ini adalah default.

AUTOGROW_ALL_FILES

Berlaku untuk: SQL Server ( SQL Server 2016 (13.x) dan yang lebih baru)

Ketika file dalam grup file memenuhi ambang batas autogrow, semua file dalam grup file bertambah.

Catatan

Ini adalah nilai default untuk TempDB.

<>filegroup_updatability_option::=

Menyetel properti baca-saja atau baca/tulis ke grup file.

READ_ONLY | READONLY Menentukan grup file bersifat baca-saja. Updates ke objek di dalamnya tidak diperbolehkan. Grup file utama tidak dapat dibuat baca-saja. Untuk mengubah status ini, Anda harus memiliki akses eksklusif ke database. Untuk informasi selengkapnya, lihat klausa SINGLE_USER.

Karena database baca-saja tidak mengizinkan modifikasi data:

• Pemulihan otomatis dilewati saat startup sistem.
• Menyusutkan database tidak dimungkinkan.
• Tidak ada penguncian yang terjadi dalam database baca-saja. Ini dapat menyebabkan performa kueri yang lebih cepat.

Catatan

Kata kunci READONLY akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari menggunakan READONLY dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan READONLY. Gunakan READ_ONLY sebagai gantinya.

READ_WRITE | READWRITE Menentukan grup READ_WRITE. Updates diaktifkan untuk objek dalam grup file. Untuk mengubah status ini, Anda harus memiliki akses eksklusif ke database. Untuk informasi selengkapnya, lihat klausa SINGLE_USER.

Catatan

Kata kunci READWRITE akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari penggunaan READWRITE dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini digunakan READWRITE untuk digunakan READ_WRITE sebagai gantinya.

Tip

Status opsi ini dapat ditentukan dengan memeriksa kolom is_read_only dalam tampilan katalog sys.databases atau properti DATABASEPROPERTYEXPembaruan fungsi.

Keterangan

Untuk mengurangi ukuran database, gunakan DBCC SHRINKDATABASE.

Anda tidak dapat menambahkan atau menghapus file saat BACKUP pernyataan sedang berjalan.

Maksimum 32.767 file dan 32.767 grup file dapat ditentukan untuk setiap database.

Dimulai dengan SQL Server 2005 (9.x), status file database (misalnya, online atau offline), dipertahankan secara independen dari status database. Untuk informasi selengkapnya, lihat Status File.

• Status file dalam grup file menentukan ketersediaan seluruh grup file. Agar grup file tersedia, semua file dalam grup file harus online.
• Jika grup file offline, setiap upaya untuk mengakses grup file oleh pernyataan SQL akan gagal dengan kesalahan. Saat Anda membuat rencana kueri untuk SELECT pernyataan, pengoptimal kueri menghindari indeks nonkluster dan tampilan terindeks yang berada di grup file offline. Ini memungkinkan pernyataan ini berhasil. Namun, jika grup file offline berisi tumpukan atau indeks berkluster dari tabel target, pernyataan gagal SELECT . Selain itu, pernyataan , UPDATE, atau DELETE apa pun INSERTyang memodifikasi tabel dengan indeks apa pun dalam grup file offline akan gagal.

Parameter SIZE, MAXSIZE, dan FILEGROWTH tidak dapat diatur ketika jalur UNC ditentukan untuk file.

Parameter SIZE dan FILEGROWTH tidak dapat diatur untuk grup file yang dioptimalkan memori.

Kata kunci READONLY akan dihapus dalam versi Microsoft SQL Server yang akan datang. Hindari menggunakan READONLY dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan READONLY. Gunakan READ_ONLY sebagai gantinya.

Kata kunci READWRITE akan dihapus dalam versi Microsoft SQL Server yang akan datang. Hindari penggunaan READWRITE dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini digunakan READWRITE untuk digunakan READ_WRITE sebagai gantinya.

Memindahkan File

Anda dapat memindahkan data sistem atau file log yang ditentukan pengguna dengan menentukan lokasi baru di FILENAME. Ini mungkin berguna dalam skenario berikut:

• Pemulihan kegagalan. Misalnya, database berada dalam mode tersangka atau matikan yang disebabkan oleh kegagalan perangkat keras.
• Relokasi yang direncanakan.
• Relokasi untuk pemeliharaan disk terjadwal.

Untuk informasi selengkapnya, lihat Memindahkan File Database.

Menginisialisasi File

Secara default, file data dan log diinisialisasi dengan mengisi file dengan nol saat Anda melakukan salah satu operasi berikut:

• Membuat database.
• Tambahkan file ke database yang sudah ada.
• Tingkatkan ukuran file yang ada.
• Memulihkan database atau grup file.

File data dapat diinisialisasi secara instan. Ini memungkinkan eksekusi cepat operasi file ini. Untuk informasi selengkapnya, silakan lihat Inisialisasi File Database.

Menghapus Kontainer ALIRAN FILE

Meskipun kontainer FILESTREAM mungkin telah dikosongkan menggunakan operasi "DBCC SHRINKFILE", database mungkin masih perlu mempertahankan referensi ke file yang dihapus karena berbagai alasan pemeliharaan sistem. sp_filestream_force_garbage_collection akan menjalankan FILESTREAM Garbage Collector untuk menghapus file-file ini ketika aman untuk melakukannya. Kecuali FILESTREAM Garbage Collector telah menghapus semua file dari kontainer FILESTREAM, operasi ALTER DATABASE REMOVE FILE akan gagal menghapus kontainer FILESTREAM dan akan mengembalikan kesalahan. Proses berikut disarankan untuk menghapus kontainer FILESTREAM.

1. Jalankan DBCC SHRINKFILE dengan opsi EMPTYFILE untuk memindahkan konten aktif kontainer ini ke kontainer lain.
2. Pastikan cadangan Log telah diambil, dalam model pemulihan PENUH atau BULK_LOGGED.
3. Pastikan bahwa pekerjaan pembaca log replikasi telah dijalankan, jika relevan.
4. Jalankan sp_filestream_force_garbage_collection untuk memaksa pengumpul sampah menghapus file apa pun yang tidak lagi diperlukan dalam kontainer ini.
5. Jalankan ALTER DATABASE dengan opsi HAPUS FILE untuk menghapus kontainer ini.
6. Ulangi langkah 2 sampai 4 sekali lagi untuk menyelesaikan pengumpulan sampah.
7. Gunakan ALTER Database... HAPUS FILE untuk menghapus kontainer ini.

Contoh

A. Menambahkan file ke database

Contoh berikut menambahkan file data 5-MB ke database AdventureWorks2022.

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

B. Menambahkan grup file dengan dua file ke database

Contoh berikut membuat grup Test1FG1 file di database AdventureWorks2022 dan menambahkan dua file 5-MB ke grup file.

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

C. Menambahkan dua file log ke database

Contoh berikut menambahkan dua file log 5-MB ke database AdventureWorks2022.

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD LOG FILE
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

D. Menghapus file dari database

Contoh berikut menghapus salah satu file yang ditambahkan dalam contoh B.

USE master;
GO
ALTER DATABASE AdventureWorks2022
REMOVE FILE test1dat4;
GO

E. Memodifikasi file

Contoh berikut meningkatkan ukuran salah satu file yang ditambahkan dalam contoh B. Perintah UBAH DATABASE dengan MODIFIKASI FILE hanya dapat membuat ukuran file lebih besar, jadi jika Anda perlu membuat ukuran file lebih kecil, Anda perlu menggunakan DBCC SHRINKFILE.

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

Contoh ini menyusutkan ukuran file data menjadi 100 MB, lalu menentukan ukuran pada jumlah tersebut.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

F. Memindahkan file ke lokasi baru

Contoh berikut memindahkan file yang Test1dat2 dibuat di contoh A ke direktori baru.

Catatan

Anda harus memindahkan file secara fisik ke direktori baru sebelum menjalankan contoh ini. Setelah itu, hentikan dan mulai instans SQL Server atau ambil AdventureWorks2022 database OFFLINE lalu ONLINE untuk menerapkan perubahan.

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Memindahkan tempdb ke lokasi baru

Contoh berikut berpindah tempdb dari lokasinya saat ini pada disk ke lokasi disk lain. Karena tempdb dibuat ulang setiap kali layanan MSSQLSERVER dimulai, Anda tidak perlu memindahkan data dan file log secara fisik. File dibuat ketika layanan dimulai ulang di langkah 3. Sampai layanan dimulai ulang, tempdb terus berfungsi di lokasi yang ada.

1. Tentukan nama tempdb file logis database dan lokasinya saat ini pada disk.

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

2. Ubah lokasi setiap file dengan menggunakan ALTER DATABASE.

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

3. Hentikan dan mulai ulang instans SQL Server.

4. Verifikasi perubahan file.

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

5. Hapus file tempdb.mdf dan templog.ldf dari lokasi aslinya.

H. Menjadikan grup file sebagai default

Contoh berikut membuat Test1FG1 grup file dibuat di contoh B grup file default. Kemudian, grup file default diatur ulang ke PRIMARY grup file. Perhatikan bahwa PRIMARY harus dibatasi oleh tanda kurung atau tanda kutip.

USE master;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

i. Menambahkan Grup File Menggunakan ALTER DATABASE

Contoh berikut menambahkan yang FILEGROUP berisi FILESTREAM klausa ke FileStreamPhotoDB database.

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
  NAME= 'PhotoShoot1',
  FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot;
GO

Contoh berikut menambahkan yang FILEGROUP berisi MEMORY_OPTIMIZED_DATA klausa ke xtp_db database. Grup file menyimpan data memori yang dioptimalkan.

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db
ADD FILEGROUP xtp_fg
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db
ADD FILE
(
  NAME='xtp_mod',
  FILENAME='d:\data\xtp_mod'
)
TO FILEGROUP xtp_fg;
GO

j. Ubah grup file sehingga ketika file dalam grup file memenuhi ambang batas autogrow, semua file dalam grup file bertambah

Contoh berikut menghasilkan pernyataan yang diperlukan ALTER DATABASE untuk memodifikasi grup file baca-tulis dengan AUTOGROW_ALL_FILES pengaturan .

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
  SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

  SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
  INSERT INTO #tmpfgs
  EXEC (@query)

  UPDATE #tmpdbs
  SET isdone = 1
  WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
  WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
  BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

    SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

    PRINT @query

    UPDATE #tmpfgs
    SET isdone = 1
    WHERE [dbid] = @dbid AND fgname = @fgname
  END
END;
GO

Lihat juga

• BUAT DATABASE
• DATABASEPROPERTYEX
• JATUHKAN DATABASE
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• Objek Besar Biner
• DBCC SHRINKFIL
• sp_filestream_force_garbage_collection
• Inisialisasi File Database