April 27, 2009 at 9:10 am
A little over a week ago our system administrators did some work on a server on which we have a test MS SQL Server 2005 running. This server is on a SAN. This maintanance caused the server to miss a few pings. The MS SQL Server 2005 interpreted this as a system shutdown and shut itself down.
Ever since then we have not been able to start the MS SQL Server. It keeps giving the following error:
2009-04-27 16:31:44.91 spid9s Error: 17053, Severity: 16, State: 1.
2009-04-27 16:31:44.91 spid9s D:\DATA\tempdb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.
There isn't much space left on D (about 350MBs), but tempdb.mdf exists on this disk so I would think it wouldn't need any extra space.
I also notice the following error:
2009-04-27 16:31:50.46 spid9s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.
2009-04-27 16:31:50.46 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
I have no idea where this comes from.
The database files are the only things on this disk of significant size, so deleting files to create space is not an option. Our plan was to temporarily add disk space and start the MS SQL Server, but we are currently low on available space and will have to wait for a few weeks for this.
My big question is, does anyone have any idea why the MS SQL Server seems to need more space than that already in use by database files? Why would the MS SQL Server decide, at startup, that it needs to modify the file?
April 27, 2009 at 9:29 am
tempdb database is actually rebuilt by SQL Server at startup time; message suggests space needed to build it is not available on your storage subsystem.
Make the required amount of space available.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 27, 2009 at 9:34 am
ddonck (4/27/2009)
I also notice the following error:2009-04-27 16:31:50.46 spid9s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.
2009-04-27 16:31:50.46 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
You may want to see if the service account for SQL Server has the necessary permissions to the folder where the tempdb database is; if for some reason the rights on that folder were changed, SQL wouldn't be able to create tempdb at startup.
Another thing to check would be the initial size you have specified for tempDB. If that is higher than what is available on the drive, that could also be the problem.
April 27, 2009 at 9:41 am
My guess is that someone altered the starting size of tempDB. If I recall, such changes only happen when SQL restarts.
You should be able to start SQL if you use the -f switch from the commandline. It starts SQL in mimimal config which should allow you to check and if necessary change the size of tempDB.
From the commandline:
sqlservr.exe -m -f
then one connection can be made (make sure it's you). I prefer to use sqlcmd for this as it ensures that management studio's additional windows don't grab that sole allowed connection.
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
April 27, 2009 at 9:44 am
You also need space for an error log. Not much, but if the disk is full...
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 27, 2009 at 9:48 am
Because tempdb is recreated during a restart - you could delete the current tempdb files and let SQL Server recreate them during startup.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2009 at 2:47 am
Many, many, many thanks for all the replies. Starting up in single user mode turned out to be our way in. I shrinked one of the largest databases and could then start the MS SQL Server normally.
After some searching I found the cause. Earlier this month someone used dbcc shrink to shrink tempdev from 1024 MB to 512 MB. This was done to make room for large database. What we are used to, is that shrinking a database file also changes it's initial size. As it turns out, it does not do this for tempdev (it does for templog and all other databases I've ever used it on). So, when the databaseserver rebooted it reverted back to 1024 MB, which was more than could fit on the disk.
I wonder if this is intended behaviour?
Anyways, we're up and running, without having to wait for additional disk space. Thanks a million to all.
April 28, 2009 at 3:00 am
Hi Ddonck,
I have a small doubt ..how you shrink the DB without starting the SQL Server?
April 28, 2009 at 3:02 am
I did not shrink without starting. I started in single user mode (starting with a small tempdb, something I did not know before hand) and then shrunk a database.
April 28, 2009 at 3:04 am
Thanks for the reply
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply