0

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

2
  • 1
    The risk in entering MMSSQL's single user mode to make changes and fix a logon problem is about the same as entering Windows in safe mode to fix a logon problem. If you're careless or don't know what you're doing, you can really screw things up... but that's just the nature of using admin/root credentials. Jul 15, 2012 at 13:25
  • Yeah. "Hire a professional to do the job you obviously are not qualified to do" is the best advice.
    – TomTom
    Apr 8, 2014 at 5:16

1 Answer 1

1

The most likely cause is that the database and/or database log files have a maximum size defined and that size limit has been reached and autogrowth has not been enabled. As far as your level of access to the database, your Windows login rights have nothing to do with your rights to see and/or modify a database. You must be set up within SQL Server by an SQL Server administrator to have sufficient privileges to edit the configurations of a database.

If you can find someone with admin rights, that’s your safest bet. But if you have to go the other route, tread carefully and make sure you carefully review and understand the steps you are going to take before you begin. Once you have those rights, whether it’s given to you by an existing admin or by following the steps in your link, you can correct the autogrowth issue.

You must log in to answer this question.