| Author |
Topic |
|
pras2007
Posting Yak Master
216 Posts |
 Posted - 2008-03-27 : 14:10:44
|
| Hello All,The PRIMARY" filegroup is full on my database, what are my options? What I have done so far is listed below:- Data Files (changed from “in megabytes” to “by percent” 10).- Transaction log (changed from “in megabytes” to “by percent” 10).I’ve changed the properties of the database and still I get PRIMARY" filegroup is full error. Does anyone know what action need to take place solve this problem? Please advice. Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-27 : 16:22:55
|
| How much free disk space do you have on the drive where the primary filegroup exists?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-27 : 19:53:03
|
| It looks like all databases are created in C: drive. We put all system database in D: drive and TEmpDB in seperate drive. Spread data files for user databases across different filegroup and also spread across different drives. Make secondary filegroup default so you don't compete for resources with system databases in Primary filegroup. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-27 : 22:23:42
|
| >> Make secondary filegroup default so you don't compete for resources with system databases in Primary filegroup.You mean system objects in each db?>>still I get PRIMARY" filegroup is full error.How big the file is? For 100gb file, sql takes a while to expand 10gb and app will timeout if can't walt. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-27 : 22:58:45
|
| I mean system database are stored in Primary FG by default except TempDB. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-27 : 23:34:18
|
| Every db has primary filegroup, and db objects are stored in filegroup. |
|
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-03-28 : 08:22:19
|
| Thanks for the response folks; I'm actually running out of space on the server. Therefore if I create a secondary filegroup would it solve my problem? Also if I drop some unwanted tables and deleted some records inside that database will that automatically decrease the primary filegroup size or I will need to run a script that shrink the size of the primary filegroup? Please advice. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-03-28 : 11:57:12
|
| Creating a secondary filegroup does not help if you are out of space. So either add disk space to the server or delete some data. If you delete some data, then you'll need to run DBCC SHRINKFILE (you can do it through the GUI as well) after you are to reclaim some of that space. But once the filegroup fills up again, you'll hit that error again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-28 : 23:16:54
|
| If server has another disk with more free space, you can create second data file in primary filegroup on that disk. |
|
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-03-31 : 15:00:21
|
| Thanks folks for the response. I ran the DBCC SHRINKFILE (1, TRUNCATEONLY) script and it seems that everything is okay for now. Meanwhile I will be buying additional hard drive. Was that the right script top run? Please advice. Thanks. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-31 : 23:07:54
|
| Which script? |
|
|
|
pras2007
Posting Yak Master
216 Posts |
Posted - 2008-04-01 : 07:54:06
|
| DBCC SHRINKFILE (1, TRUNCATEONLY) |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-01 : 22:36:07
|
| It's ok as long as db stays healthy. |
|
|
|
Dance Doll
Yak Posting Veteran
54 Posts |
Posted - 2008-04-04 : 13:25:24
|
| I had the same problem before. And I just added secondary file to the primary group. That's all. It works perfectly since then. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-04 : 16:05:13
|
quote:
Originally posted by Dance Doll
I had the same problem before. And I just added secondary file to the primary group. That's all. It works perfectly since then.
That will not help if you don't have any free disk space on the drive where the database files are located.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-04 : 16:13:29
|
| Maybe he added files to different drives. |
|
|
|
|
PRIMARY" filegroup is full