I have Microsoft Office Accounting running on an SBS 2003 Premium Server (with full SQL Server 2005).
I am having a problem with the TempDB file which I believe is running out of space.
As this is the SQL group, I won't go into the Office Accounting side of things, however I tried to create a sample company to see if that would work. The error I get there is:
===
2009-01-06 14:30:50.64 spid56 Starting up database 'SampleServiceCompany2008'
2009-01-06 14:30:50.96 spid56 Setting database option AUTO_CLOSE to OFF for database SampleServiceCompany2008.
2009-01-06 14:30:57.93 spid56 Error: 1105, Severity: 17, State: 2.
2009-01-06 14:30:57.93 spid56 Could not allocate space for object 'dbo.Large Object Storage System object: 450437288820736' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
===
I have also had this error when doing it:
===
009-01-06 14:26:50.43 spid56 Starting up database 'SampleServiceCompany2008'
2009-01-06 14:26:50.79 spid56 Setting database option AUTO_CLOSE to OFF for database SampleServiceCompany2008.
2009-01-06 14:26:56.92 spid56 Error: 17053, Severity: 16, State: 1.
2009-01-06 14:26:56.92 spid56 D:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\
===
I ran an sp_spaceused on TEMPDB with the following results:
===
database_name - TEMPDB
database_size - 11.44MB
unallocated space - 8.47MB
reserved - 1184KB
data - 512KB
index_size - 592KB
unused - 80KB
===
I'm not sure I understand this.
TEMPDB was set for 10% and Autogrow. I read somewhere that perhaps it was growing it fast enough to keep up with MOA so I set it to 10Mb and Autogrow but that resulted in immediate errors rather than it trying for a while and then failing.
I then set it to 20% and autogrow - same problem.
I have finally gave it an initial size of 20Mb for the database and 5Mb for the Log. When I did that I got an error "112 (error not found) while trying to expand the physical file". Error 112 looks like insufficient disk space (from some Googling). The C: drive has 6Gb free and the D: drive (where the TEMPDB database resides) has 68Gb free.
I'm very confused! Help?
I am not a SQL person, so if you have any suggestions please outline how I carry them out (as a lot of the SQL stuff I've read in forums while researching this kind of suggests you know exactly where sys.database is etc. - I don't :-))
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
As I said above, I can't set it to anything. I get an error 112. Happy to set it to an initial size of 1GB if SQL would let me. Autoshrink is set to False.
The TEMPDB settings weren't set by me but were set either SQL itself when the database was created or some other database creation utility that maybe I used to move the Office Accounting database (I cannot remember for the life of me how I set this up on the server).
It does indeed sound like the db should expand by itself.
Just to be sure, verify that there is not a hard-coded max size specified for the tempdb.
Go into Mgmt Studio, select "New Query", and run this command:
EXEC tempdb.dbo.sp_helpfile
Scroll to the right and make sure "MaxSize" is "Unlimited" and not a fixed amount.
I stopped the instance, deleted the tempdb files and started the instance again. The files were created as they should. I then went in and, as you suggested, changed the database and the log file to 1GB and it worked this time. Not sure why it worked this time and not before - problematic tempdb db / log files?
It unfortunately didn't fix the Office Accounting problem, but that isn't what I asked about!
"Your Suggestion" was in fact first suggested by me in http:#23306175
Err, no it wasn't.
You suggested restarting the SQL instance which will recreate the file if it already exists. The SQL instance had already been restarted several times prior to posting the question and the file always existed. My suggestion was to stop the SQL instance, delete the tempdb file and then start it again. This allowed me to change the file sizes to 1GB (as you suggested) which it wouldn't allow me to prior to deleting the tempdb file (regardless of how many times it had been restarted).
The key to solving the problem was deleting the tempdb file which is what actually solved the problem. Everything else that you had suggested prior to that had already been tried.
You were of great assistance. You helped solve the problem. I'm not sure where the issue is here.
I would have taken it as granted that before posting a question the SQL instance would have been restarted which is why I probably, subconsciously, didn't put it in there. Point taken.
I don't want to get into an argument over this so this is my last post on it. I thank you for your help. I've awarded 150 points for the question. If you don't feel that is sufficient and want to dispute that you can lodge an objection with the moderator.
"I would have taken it as granted that before posting a question the SQL instance would have been restarted which is why I probably, subconsciously, didn't put it in there. Point taken."
Although I often make assumptions, I don't make it a habit. I try to read peoples complaints fully before offering up a suggestion.
You are entitled to award points however you see fit. I guess to me it's a matter of "why not give the full 250".
Business Accounts
Answer for Membership
by: BrandonGalderisiPosted on 2009-01-06 at 08:18:39ID: 23305885
database_name - TEMPDB
database_size - 11.44MB
unallocated space - 8.47MB
11 MB is WAY!!!!! too small for tempdb. You should set it to 1GB (500 data/log) MINIMUM. Probably 2GB (1GB/1GB) to be safe. You also should not set it to auto shrink.