Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
danesh1
Starting Member
7 Posts |
 Posted - 2010-11-29 : 06:49:54
|
| 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" has totally filled up 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. D.Qureshi |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-29 : 06:56:22
|
| How big is the log file?If you haven't put anything in place to back it up or truncate it will keep increasing in size and could be filling the disk.Otherwise your options are to reduce the size of the database file on the d drive, reduce/delete other files or create another database file on another drive.Given that the drive is already full I suspect you would have trouble reducing the size but you could try doing a shrink.Do you have another drive with any space? Can you delete reduce the size of any files on that drive?How big is the log?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
danesh1
Starting Member
7 Posts |
Posted - 2010-11-29 : 13:29:50
|
| The transaction logs (xxx.ldf) are located on the C: drive and only 12Gb in size.The database is on D: drive which has a capacity of 435Gb. The problem is the database has grown to 435Gb on the D: drive and there is no fre space left. On the D: drive there is an ISO SQL Server Installation file which is taking up 4Gb but I am unable to delete it. When I try to delete it says open by System. I have used process Explorer to find out which process has got it open but cannot find it.I did think about shrinking the large database file but I thought shrinking is not recommended.The C: drive has 45Gb free disk space available.Could you give me some guideline examples how I would use C: drive to create another database file on it?D.Qureshi |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-29 : 13:50:36
|
| As a temporary solution you can add a filegroup and file on the C: drive.use sp_spaceused to find your biggest tables, take one that's about 20GB or so, and recreate the clustered index on the new filegroup.This will buy you a little time, but you need to add disks, and soon.Also, look for anything on the D: drive that isn't SQL Server files and move them off the server, or delete them. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2010-11-29 : 15:04:36
|
| Do you have the .iso file mounted with some mounting software? -Chad |
|
|
|
danesh1
Starting Member
7 Posts |
Posted - 2010-11-30 : 10:26:18
|
| The .ISO has been dismounted finally to give us a bit of time.Would exporting the database to another drive free any disk space?D.Qureshi |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-30 : 11:43:31
|
| of course it would. you have another local drive? |
|
|
|
danesh1
Starting Member
7 Posts |
Posted - 2010-11-30 : 12:30:09
|
| We are hoping to adjust the level of alerts we will need to look through. We will also monitor the amount of disk space being utilised and carryout house keeping procedure to export part of the database to a flat text file and then remove records using SQL statements to reclaim the space back. We will do this on a weekly basis until we have an idea how fast the database is growing in size.D.Qureshi |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-30 : 13:08:07
|
| Wow. How about purchasing some disks? |
|
|
|
|
|
|
Could not allocate space for object