November 29, 2010 at 4:17 am
I have a SQL Server 2008 running on Windows 2008.
The main database is located on the D: drive and the total capacity of the disk drive is 435GB.
I have started to receive the following messages in the error log file:
"Could not allocate space for object 'dbo.WebProxyLog'.'IX_WebProxyLog_DateTime' in database 'w3proxy' because the 'PRIMARY' filegroup is full".
The database "w3proxy" is totally filled the D: dirve and there nothing else to delete on the D: drive to free some disk space.
At the moment I am unable to get an extra disk drive which I believe would solve the disk space problem.
But can anyone suggest any other way to free up the disk space until I can get an extra disk drive?
Thank you.
November 29, 2010 at 4:42 am
d.qureshi (11/29/2010)
I have a SQL Server 2008 running on Windows 2008.The main database is located on the D: drive and the total capacity of the disk drive is 435GB.
I have started to receive the following messages in the error log file:
"Could not allocate space for object 'dbo.WebProxyLog'.'IX_WebProxyLog_DateTime' in database 'w3proxy' because the 'PRIMARY' filegroup is full".
The database "w3proxy" is totally filled the D: dirve and there nothing else to delete on the D: drive to free some disk space.
At the moment I am unable to get an extra disk drive which I believe would solve the disk space problem.
But can anyone suggest any other way to free up the disk space until I can get an extra disk drive?
Can you truncate/drop any tables or indexes there?
Once and again I bump into databases that have backup copies of tables - just-in-case - unused indexes or huge log tables nobody looks at them.
_____________________________________
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.November 29, 2010 at 6:18 am
Your choices are limited. Add a file to the filegroup that's on another drive, or remove things from the existing file group. That's it.
If you don't have monitoring in place to watch file growth and free space, I'd strongly suggest getting that set up now.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2010 at 7:01 am
The log file (XXX.ldf) located on the C: drive.
The main database file(XXXX.mdf) located on the D: drive.
So they are on separate disks.
By the I have found a large 4GB ISO file (SW_DVD9_SQL_Stanadrd_Edtn_2008_R"_English_MLF_X16-29588.iso) on the D: drive but for some reason I am unable to delete. When I attempt to delete the ISO file it says it in use.
Any idea why I cannot delete the ISO file or is it required as part of SQL Server 2008 application?
November 29, 2010 at 7:06 am
ISO file should not be required by SQL server 2008. It seems like you have other program that show ISO as virtual drive and disk. If you do have that program, close it and try to delete it.
TO short term, you can additional data file on the drive where you have space available. This will allow your database to work again.
November 29, 2010 at 8:46 am
Can you give some examples how to create additional file on a C: drive where I have free disk space available?
I have looked in Task Manager to see if any program may have the ISO file open in the DVD drive but no joy. any idea what I else I can do to find the program and kill it?#
November 29, 2010 at 9:11 am
d.qureshi (11/29/2010)
Can you give some examples how to create additional file on a C: drive where I have free disk space available?
You might want to cozy up with a good book, like Dusan Petkovic's "MS SQL Server 2008: A Beginner's Guide". He covers creating new databases, adding files to databases, etc. Seriously, though, this sounds like a question where you haven't even started the homework.
Also, when it comes to SQL Server, Books On Line (hiding under F1 key), is your BEST friend. Answers all kinds of questions... you just gotta ASK.
Open SQL Server.
Right-Click on your database.
Select PROPERTIES.
Select FILES page.
Click the ADD button...
Add the file, and specify where it should go. (on a different drive). Make it non-primary, and when the primary fills up, the new data will go to the secondary file(s).
November 29, 2010 at 10:59 am
Thank you for your sound advice on doing some back ground reading on SQL Server principals. I shall take look at the books online documentation.
May 19, 2013 at 12:10 pm
1)Make that database to simple recovery mode.
2) shrink log file
3)increase growth percentage
May 20, 2013 at 8:24 am
if u turn in to simple recovery model and shrink log file at the time log chain will break so what u have to do is check all the table size it is possible do the archival in your database or add the file group in another drive and always make a alert job mointoring if drive reach 80 to 85%
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply