Trying to figure out a problem at an office that has SQL Server 2005 installed on Windows SBS Server 2008. Here's the setup: It's an office, and the person who set this all up is nowhere to be found. I'm the best hope they have...
One of the programs they use on a workstation gives them an error of "Could not allocate space for object 'Billing' in database "MyDatabase" because primary filegroup is full" when trying to save an entry in their software.
I searched around for hours, looking for possible solutions. One was to check for available disk space, and another was to defrag.
I checked the hard drives on the server, and there is plenty of space free. I also defragged, which may have helped the problem somewhat. It's hard to say, because it seems like with the nature of the error, if you try over and over you might get
it to actually save.
My next step was to try to see if autogrowth was enabled on the database. This would seem to be a likely / possible solution, but I can't access the database!
If I run the SQL Management Studio, I can log in as my Windows user and view the list of databases. However, if I try to do anything (actually view the database, view the properties, add or edit users), I get errors that I don't have permission. For what
it's worth, I also tried runing Management Studio as Administrator, in case that would help. No difference, though.
Now, what I'm guessing is going on -- from my limited knowledge of SQL and from reading online -- is that though I'm logged in as a Windows administrator, that account does NOT have SQL access.
I do see a list of SQL users, including SA, but I again don't have permission to add one or to change the password on an existing one. And nobody at the office has any idea what the SQL passwords could be.
So... here's my thinking thus far:
1 - The "Could not allocate" error likely points to a database that needs to be allowed to autogrow. Especially since I verified there is plenty of free space and the HD has been defragmented.
2 - Enabling autogrow would be very easy to do if I had the proper access within SQL Management Stuido.
It sounds like it's a step-by-step guide for giving me the access I need to SQL. I'm guessing that if I followed this guide, I would be able to then log in to the SQL server via Management Studio with the proper permissions, and would be
able to enable autogrow (or simply view the status of the existing database), and hopefully solve the "Could not allocate space" problem!
So I guess I have a few questions:
1 - Would you guys agree with my "diagnosis"? Think I'm barking up the right tree?
2 - Is there any risk at all in hurting / disabling / wrecking the current SQL database or setup with me going through the guide to regain SQL access? I understand that per the guide, I would have to temporarily shut down SQL, so obviously it wouldn't be
accessible during that time. But it wouldn't be worth the risk if there's a chance I could mess anything up...
Like I said, the workstations ARE currently accessing the database somehow, but nobody knows with what login info or anything. Basically, it's set up, it works (usually), but if they had to reload the software, nobody would know how.
Any feedback would be appreciated!! The problem is such that it's not an emergency for them, but an annoyance. If I could fix it, it would be wonderful. But if not, I think they'll manage, especially as they are going to eventually stop using this software.
Defragmenting the disk won't help, because the issue isn't at the disk level. SQL Server defines the size of its data files, whether autogrow is enabled on the files, and what maximum size the file can grow to. If there is space on the drive, the files for
this database have likely hit their maximum size, and that is what needs to be addressed.
In Management Studio, the error that you don't have permissions to view/change things in the database is very important. Your login doesn't apparently have the permissions to perform these operations, and those permissions are controlled entirely within
SQL Server. Running Management Studio as an administrator changes how the program "Management Studio" runs; it will run as though you were logged on as the administrator account. Unfortunately, it won't change your permissions in the SQL Server.
As to your thinking so far:
1. You would seem to be correct here.
2. Agreed.
The step-by-step guide you referred to makes some points, but I think it was written for SQL Server 2008. In SQL Server 2008, the BUILTIN\Administrators group is not granted access to the SQL Server by default, but should have access when the SQL Server
is in a disaster recover mode.
SQL Server 2005 grants access to the BUILTIN\Administrators group by default. If you log on to the SQL Server as a Windows account that is a local administrator, you can test this. If you are able to log in, you should be able to change the filesize and
autogrow settings in the database in question.
If you are not able to log in, it is possible that the BUILTIN\Administrators group does not have access on this system. In that case, rebuilding the system databases would be the best course.
Marked as answer by Ming Xu - MSFT on Jul 19, 2012 02:58 PM
imekul
0 Points
1 Post
Could not allocate space for object ... primary filegroup is full. Trying to get this fixed!
Jul 10, 2012 06:26 PM|LINK
Trying to figure out a problem at an office that has SQL Server 2005 installed on Windows SBS Server 2008. Here's the setup: It's an office, and the person who set this all up is nowhere to be found. I'm the best hope they have...
One of the programs they use on a workstation gives them an error of "Could not allocate space for object 'Billing' in database "MyDatabase" because primary filegroup is full" when trying to save an entry in their software.
I searched around for hours, looking for possible solutions. One was to check for available disk space, and another was to defrag.
I checked the hard drives on the server, and there is plenty of space free. I also defragged, which may have helped the problem somewhat. It's hard to say, because it seems like with the nature of the error, if you try over and over you might get it to actually save.
My next step was to try to see if autogrowth was enabled on the database. This would seem to be a likely / possible solution, but I can't access the database!
If I run the SQL Management Studio, I can log in as my Windows user and view the list of databases. However, if I try to do anything (actually view the database, view the properties, add or edit users), I get errors that I don't have permission. For what it's worth, I also tried runing Management Studio as Administrator, in case that would help. No difference, though.
Now, what I'm guessing is going on -- from my limited knowledge of SQL and from reading online -- is that though I'm logged in as a Windows administrator, that account does NOT have SQL access.
I do see a list of SQL users, including SA, but I again don't have permission to add one or to change the password on an existing one. And nobody at the office has any idea what the SQL passwords could be.
So... here's my thinking thus far:
1 - The "Could not allocate" error likely points to a database that needs to be allowed to autogrow. Especially since I verified there is plenty of free space and the HD has been defragmented.
2 - Enabling autogrow would be very easy to do if I had the proper access within SQL Management Stuido.
That leads me to this link: http://blogs.technet.com/b/sqlman/archive/2011/06/14/tips-amp-tricks-you-have-lost-access-to-sql-server-now-what.aspx
It sounds like it's a step-by-step guide for giving me the access I need to SQL. I'm guessing that if I followed this guide, I would be able to then log in to the SQL server via Management Studio with the proper permissions, and would be able to enable autogrow (or simply view the status of the existing database), and hopefully solve the "Could not allocate space" problem!
So I guess I have a few questions:
1 - Would you guys agree with my "diagnosis"? Think I'm barking up the right tree?
2 - Is there any risk at all in hurting / disabling / wrecking the current SQL database or setup with me going through the guide to regain SQL access? I understand that per the guide, I would have to temporarily shut down SQL, so obviously it wouldn't be accessible during that time. But it wouldn't be worth the risk if there's a chance I could mess anything up...
Like I said, the workstations ARE currently accessing the database somehow, but nobody knows with what login info or anything. Basically, it's set up, it works (usually), but if they had to reload the software, nobody would know how.
Any feedback would be appreciated!! The problem is such that it's not an emergency for them, but an annoyance. If I could fix it, it would be wonderful. But if not, I think they'll manage, especially as they are going to eventually stop using this software.
Thank you so much for your time!
Luke
Jared Po - M...
Member
40 Points
5 Posts
Re: Could not allocate space for object ... primary filegroup is full. Trying to get this fixed!
Jul 17, 2012 03:18 PM|LINK
Defragmenting the disk won't help, because the issue isn't at the disk level. SQL Server defines the size of its data files, whether autogrow is enabled on the files, and what maximum size the file can grow to. If there is space on the drive, the files for this database have likely hit their maximum size, and that is what needs to be addressed.
In Management Studio, the error that you don't have permissions to view/change things in the database is very important. Your login doesn't apparently have the permissions to perform these operations, and those permissions are controlled entirely within SQL Server. Running Management Studio as an administrator changes how the program "Management Studio" runs; it will run as though you were logged on as the administrator account. Unfortunately, it won't change your permissions in the SQL Server.
As to your thinking so far:
1. You would seem to be correct here.
2. Agreed.
The step-by-step guide you referred to makes some points, but I think it was written for SQL Server 2008. In SQL Server 2008, the BUILTIN\Administrators group is not granted access to the SQL Server by default, but should have access when the SQL Server is in a disaster recover mode.
SQL Server 2005 grants access to the BUILTIN\Administrators group by default. If you log on to the SQL Server as a Windows account that is a local administrator, you can test this. If you are able to log in, you should be able to change the filesize and autogrow settings in the database in question.
If you are not able to log in, it is possible that the BUILTIN\Administrators group does not have access on this system. In that case, rebuilding the system databases would be the best course.