Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've got a SQL Server 2008 Cluster that has a 10GB drive used for a particular databases log file. I have a site on SharePoint that had a good few gigabytes of files a document library. Deleting the site sits there for a while in the browser and then gets an unknown error.

The SQL server sits there spinning away and quite rapidly fitting the log file until it runs out of space. I imagine that what is happening is that once the log file gets filled then the transaction aborts which is when SharePoint gives it's unknown error.

I don't have control over the drive size so cannot get the max size of the log increased. I've tried running a shrinkfile on the log and database before hand but it still doesn't make enough space to perform the site delete. I've deleted all the files within the document library (the only data really on the site) and also emptied the recycle bin. This didn't error but the database hasn't reduced in size (even after a shrinkfile) and the site still has the same problem when I try to delete it so I assume the files are still sitting in the database.

I'm now wondering whether I can lower the detail of the log somehow, or temporarily disable the logging on this database just to get this SharePoint operation to complete.

Any advice would be greatly appriciated.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

You cannot turn off logging. That's not how SQL works. Try moving the log file to a bigger drive, or setting the Recovery Mode to Simple (under database properties in SQL Server).

Shrinking the log file is also a bad idea as it fragments it and slows things down.

share|improve this answer
    
Shrinking the log file isn't bad, but shrinking data file (.MDF or .NDF) is - it causes massive fragmentation –  SuperCoolMoss Oct 31 '09 at 14:39
    
This is good to know. I'll avoid shrinkfile in future. I assume SQL Server will have it's own garbage collector style operation in process to clear up deleted stuff? –  Dan Revell Nov 1 '09 at 16:53
2  
The space gained from deleting objects remains as white space within the database. This white space will be reused instead of having to grow the database file (.MDF) further. –  SuperCoolMoss Nov 1 '09 at 18:20
    
Nicely put, SuperCoolMoss. –  user3914 Nov 1 '09 at 22:23
add comment

The other answers here have covered what you need to do with respect to the log file.

However, are you sure that the cause of the problem is the logfile? Or is it just the symtom.

If the logfile were full, would it not affect all operations, not just this specific one?

Do you have a very standard SharePoint installation or is it something that has been customised?

My guess is that a developer has programmed a "On Delete Site Event" that goes into an infinate loop, or has some other error in it.

share|improve this answer
    
It's a pretty standard SharePoint install. When I said it has a few gigabytes of files in it, I ment to say it has about 50GB of files in it, mostly word docs or cad drawings. This is what is taking the time I assume. The weird thing is that the database hasn't reduced in size when I deleted these files and emptyed the sites recycle bin. Why would SharePoint be holding onto these files still? –  Dan Revell Nov 1 '09 at 16:52
    
Depending on the settings of your database you may need to run DBCC SHRINKDATABASE or DBCC SHRINKFILE to reclaim the space from SQL Server –  Shiraz Bhaiji Nov 1 '09 at 17:08
    
Note, all the shrink operations have bad side affects: 1) they run single-threaded so take a long time, 2) they generate a lot of transaction log growth, 3) they fragment the indexes within the DB - possibly causing poor performance. –  SuperCoolMoss Nov 1 '09 at 18:16
    
@SuperCoolMoss: Agree with you, but is there any other way to get the space back? –  Shiraz Bhaiji Nov 1 '09 at 18:28
    
Hi Shiraz, no not easily - you can by moving tables between filegroups - documented here :sqlskills.com/BLOGS/PAUL/post/… I guess the question is whether Dan actually needs the released space? –  SuperCoolMoss Nov 1 '09 at 19:32
add comment

To add to Randolph Potter's answer - if the database's recovery mode is fully logged you should be running transaction log backups, trying running them more often e.g. every 5 minutes or less - this will allow the transaction log to be reused and shouldn't grow as large.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.