SQL Server Performance Forum – Threads Archive

Dropping file from PRIMARY filegroup

My primary filegroup was growing too big so i decided to create a new filegroup and transfer most of the tables over there. But, the primary filegroup was still at 50 GB of size in a RAID 1 container, so I decided to create a new file for PRIMARY in a RAID 10 container, and I proceeded to execute: DBCC SHRINKFILE (1, EMPTYFILE) RTFM says it will empty the file in order to drop it from the database. Sure it did transfer the tables from the 50 GB file to the new file. So I proceeded to drop old 50 GB file: ALTER DATABASE [DB_NAME] REMOVE FILE [PRIMARY_FILE_1] I get this error: Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database. But that does not make sense because the PRIMARY filegroup is still in place with now 2 files. I just want to keep the new one and drop the old one. I cannot even shrink it. Any clues?

Check if there is any statistics in Primary, because that could be one of the razons. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

That will make sense if we are moving to a different filegroup, but I want to keep the filegroup and just change the files inside it (migrating from old to new). In any case, where can I search for filegroup statistics?

This scrip will help: select si.rows as ‘filas’, SO.Name as Tabla, SI.name as ‘Index’, SFG.groupname as ‘Filegroup’
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId
order by si.rows desc, SO.Name , SI.name, SFG.GroupName Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

The SQL was helpful but I can only see the objects within each FILEGROUP. My problem is this: PRIMARY FILEGROUP has 2 files: File 1: 50 GB
File 2: 20 GB I issued: DBCC SHRINKFILE (1, EMPTYFILE)
in order to move all that from File 1 into File2. I just want to drop File1 and keep File2 but I get this error: Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database. The file is still 50 GB and I need to free up the space. It does not work. Thanks Oscar

Ok, now I understand.
You can’t drop file1 and keep file2, because both belong to Primary.
One way is to create a new filegroup , move file2 to new one and then delete both file1 and file2 Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi Oscar, also moved here from SQL Server Central? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Can you get your hands on Inside SQL Server 2000? If so, you should read Chapter 5: Databases and database files. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Database Files A database file is nothing more than an operating system file. (In addition to database files, SQL Server also has backup devices, which are logical devices that map to operating system files, to physical devices such as tape drives, or even to named pipes. I won’t be discussing files that are used to store backups.) A database spans at least two, and possibly several, database files, and these files are specified when a database is created or altered. Every database must span at least two files, one for the data (as well as indexes and allocation pages), and one for the transaction log. SQL Server 2000 allows the following three types of database files: Primary data files Every database has one primary data file that keeps track of all the rest of the files in the database, in addition to storing data. By convention, the name of a primary data file has the extension MDF. Secondary data files A database can have zero or more secondary data files. By convention, the name of a secondary data file has the extension NDF. Log files Every database has at least one log file that contains the information necessary to recover all transactions in a database. By convention, a log file has the extension LDF. Each database file has five properties: a logical filename, a physical filename, an initial size, a maximum size, and a growth increment. The properties of each file, along with other information about the file, are noted in the sysfiles table (shown in Table 5-1), which contains one row for each file used by a database. <hr height="1" noshade id="quote"></blockquote id="quote"> I’m not sure, but I guess the second file you’ve added has the ndf extension. If you need to move data to a different location, probably the easiest way is to recreate to clustered index on that new location. As the leaf level of such an index actually contains the data, all data will move there, too. –Frank http://www.insidesql.de</a>

Hi Frank, yes I moved from SQL Server Central (not mozilla firefox friendly plus their always sign-in policy). It seems than I cannot drop any file related to the Primary file group. I am recreating all the clustered primary keys now so they are stored in another filegroup. Then I will see If at least I can shrink them.

]]>