February 15, 2011 at 2:07 pm
Hello I have a new job and they threw me in to learn SQL I have a few error that all tell me my primary file group is full. I do not know how to fix this issue. I am not a programmer so I just need help. We are running SQL 2005 Express I know that it has a 4g cap and i9 do not thing the old tech set up a secondary file group to save to so everything on this server is saving it the primary file group. And there is a lot if images are being saved. So if anyone can help with a step by step. I really do not want to lose this job lol
February 15, 2011 at 3:36 pm
The size cap on express is per database, not per filegroup, so if that's what you're running into, another filegroup won't help at all.
If that is what you're running into, you can upgrade to SQL 2008 R2 express, which has a 10GB limit, but beware possibly breaking applications. You can look at archiving and deleting old data. Otherwise buy a licence for a full edition of SQL and upgrade.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 8:54 am
Thanks so how can I clean out the old data.I still do not know how to go about doing that on the SQL server I am sorry this is all new to me.
February 16, 2011 at 9:58 am
That completely depends on what data is in the database. There's no simple answer to that. Is there no SQL person there?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 10:09 am
Nope.. the last guy was not a SQL person but he was the one who set all of this up. Most if not all of the errors are because of image files . the primary file group is full is the only error for now. All the errors are from one database at lest they all are pointing to the one. So there is no way to clean up the primary file group of a the one database? When it is giving me the error primary file group is the the only file group for the server. I was going and looking threw all the databases and the all say there file group is primary. they know I do not know much but they want me to fix it.
February 16, 2011 at 1:36 pm
Tell them to get someone in who at least knows SQL, preferably knows the database structure as well. Since you know neither, anything you do could easily break things while you're trying to fix them, and I'm quite willing to bet you have no backups there.
Yes, the error says 'primary filegroup', but if you are at tha max allowable size for Express (4GB), adding filegroups won't help. The hard limit on a SQL 2005 Express database is 4 GB. Want larger? Pay for a licence for a full version of sQL.
To clear up older data, there has to be some way of identifying what images are older data, which can be safely deleted without impacting whatever application is using is, without causing any other problems, without any legal implications. A volunteer on the other side of the world is not perhaps the best person to rely on for that. Ideally you want someone who was involved in the development of that app, as they will know the details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 2:13 pm
I am not a volunteer I am a network admin not a DBA:(. Yes they have back up's. all the images are dated
February 16, 2011 at 2:33 pm
i would have them create you new box sql2008 express
restore it to that an see if that fixes the problem and the apps still work
It if works use it and if there are issues just go back to the old box
the images unless they are stored in the database itself wont help with the 4 gb limit
if it is not a production box changing to developer version would get rid of the limit otherwise upgrade to standard
February 16, 2011 at 3:12 pm
Thanks I will see if they will do that.
February 17, 2011 at 2:26 am
Just bear in mind that if you do convert a database to SQL 2008 R2 (which is the one with the 10GB limit), you will not be able to go back to SQL 2005. Database upgrade is a one-way process.
Are the images stored in the database?
If so, check and confirm with management (or whoever asked you) what the oldest images are that they still need.
Have a play around with management studio, get the schema (the create table definition) for the table with the images.
I suggest then that you post a new thread, post the table definition and ask how to delete rows older than a certain date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2011 at 10:00 am
They are going to think about it going to 2008 R... does the 4g cap is that talking about the size of a sync or how much data that can at one time be saved to the database/ primary filegroup?
February 17, 2011 at 10:42 am
The total size of a database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2011 at 2:35 pm
They are not going to upgrade to 2008 r. to much money.. I do have a DBA that is going to help me rebuild this crap shoot SQL server.. on another note if i split a database in two. would that fix to and if no ok no biggy if yes i would have to set up MS access to pull and save to that new database.
thank you for all the help
February 17, 2011 at 4:32 pm
Too expensive? SQL Express 2008 (the one with the 10GB limit) is free.
It's only if they want a full version of SQL (without a size limit) that they need to pay, and in that case they could just get 2005 workgroup or standard edition
Splitting DB will almost certainly break any application you're using. Unless you know that application backwards and know that it can handle two different databases (permissions, connection strings, etc), don't go that route. It's non-trivial even for someone who knows SQL well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 18, 2011 at 8:04 am
OK thanks for that. the app is MS access 2007.. and were can I get SQL 2008 free:w00t:? So I can show them
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply