Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
potinenir
Starting Member
19 Posts |
 Posted - 2003-09-17 : 13:46:20
|
| Is there a way to drop a datafile from the primary filegroup after running the dbcc shrinkfile(file_name,emptyfile).There is a lot of fragmentation in the datafile and the file is not shrinking. The size of the datafile right now is 15 gig and no more than 5gig of data is present in the datafile . After running the above dbcc command the contents of the file will be moved onto the remaining data files in the filegroup and allows the file to be dropped . But this is not he case with the primary filegroup/datafile and it errors out. Is there any other way to drop the datafile or shrink the file to reduce the fragmentationThanks |
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-09-17 : 17:27:35
|
| Any suggestions?? |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-09-17 : 17:36:30
|
| First of all, you don't have to empty the file out... You can just shrink it to the desired size: DBCC SHRINKFILE (file_name, 5000) Secondly, if you are concerned about fragmentation, you can run DBCC DBREINDEX on your tables (but after that the size of the datafile will not change -- you still have to shrink it). If DBCC SHRINKFILE doesn't work even after reindex, you probably have a table where free space is not being reclaimed properly (this can happen if you are using text fields a lot). You will need to copy all of your data out, truncate the original table, and load the data back in. The file should shrink after that. Watch out for you foreign keys though.BTW, I know there is a KB article out there that talks about this in more detail. |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-09-17 : 18:09:28
|
| I did shrink the datafile and also moved out some tables. The remaining objects in the filegroup are the system tables (sysobjects ,sysfiles etc) and they are occupying the 15gig. i just want to group out system tables in the primary filegroup and the application tables in different filegroups. After moving out the application tables from the primary filegroup i ran the dbcc shrinkfile and it still occupies 15gig. There might be a high water mark at the 15gig limit which is not allowing to shrink more than that. So by mving the objects into a diff datafile in the same filegroup using the dbcc command i thought i can drop the datafile to reclaim the 15gig space. But the system is not allowing me to do that stating the datafile in primary filegroup cannot be dropped. Any ideas?And also where/how can i find out the article??Thanks, |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-09-18 : 07:25:39
|
| Any ideas?? |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2003-09-18 : 09:53:21
|
| Here's the article I was referring to:http://support.microsoft.com/default.aspx?scid=kb;en-us;324432Somehow, I doubt that your system tables could take up that much space... I think you need to generate a list of your table sizes, to see which table is hogging all the space. Also, you are sure that you're struggling with a data file and not the database log, right? |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-09-18 : 10:48:01
|
| Yes the log is just 100M. The datafile after itself is 15gig. As you said ,i'll check the sizes of the systables and see how much much space it occupies. Thanks for the link. I'm looking into it right now.Thanks, |
|
|
|
potinenir
Starting Member
19 Posts |
Posted - 2003-09-18 : 10:58:34
|
| I did the same what the document says . It works perfectly for non-primary filegroups/datafiles but not for the primary filegroup datafiles. It gives the error 5020 that the primary data or log file cannot be removed from the database. If i cannot remove a file which has no objects in a datafile (after running dbcc shrinkfile(name,empty_file) which runs perfectly moving the objects into another datafile in the filegroup) how can i reclaim the 15gig occupied the file??Thanks, |
|
|
|
|
|
|
|
|
dropping a datafile from the primary filegroup