Hi good morning

I have a sql server with 755Gb with 214 GB space.  During the weekend, there is a massive change in our sharepoint (management decided to restrcuture how document library file structure is and moving/deleting files etc..).  By doing so, our WSS_CONTENT db is now has 229Gb of data file usage and 93Gb of log file.  There is basically no unuse space in the data and log file.  I am thinking of doing a compressed backup of this wss_content.  Will there a chance that after backup, i can fit this 324 Gb of db to 214 Gb of space.  What i am not sure is what happen if i do a back up with compress and then still not enough space, how is the server react, will it hang or just say backup incomplete.  That haven't happen to me before.  

Or is there a way to handle this situation?  Any help/suggestion will be greatly appreciated.

thx, Ted.

  

Hi Ted,

The compression on backups is for the backup file itself and not for the database. This helps the backup and restore process because you're moving less data.

Generally speaking, regular maintenance and backups of the databases should help maintain the log file size.

SharePoint has jobs that will maintain the indexes and backups. I'm not sure what version you're running but a whitepaper on 2010 can be found here:

http://www.microsoft.com/en-us/download/details.aspx?id=24282 

Backup compression should just fail if it can't create the backup file. Chances are that it won't compress that much assuming that sharepoint means a lot of binary info. Only way to know is to try...

Ted,

Backup compression ill only affect backup it will have no affect on original data and log file.

If you are thinking of taking backup you cannot decide before compression what would be its size after compression you can make a guess and reach to tentative value.

You might as well try to shrink data file to reclaim space but please note don't make it as habit as data file shrinking causes massive index fragmentation so dont forget to rebuild index

Hi

Thank you for helping out.  I think i am going to do these:

a) do a transaction log backup

b) try to do a DBCC shrinkfile(mydb)

now my db's data file is 220Gb and log file is 94 Gb with my log file show almost 99% unused space 

c) switch to simple restore instead of full

and now i am backing up the whole db with compress

crossed my finger and hope at least i have a full copy... 

and thanks everyone for suggestion and helping out... will update if that that go thru ok...

Ted.

You dont need to run transaction log before shrinking

Also note that after you switch to simple from full you loose point in time recovery so after shrinking is complete change it backup to full recovery and then take full backup immediately after changing recovery model to full

First, setting to simple will break your backup chain, as noted by Shanky. Make sure that this is acceptable!

And if you decide to do this, set it to simple *before* the shrink. Else the log will grow with as much as data is moved. Can be huge size for the ldf file!

after you switch to simple from full you loose point in time recovery

You should pay particular attention to this! Simple recovery is not there out of convenience. It's not an option to turn on if the file grows too large. Instead you should be looking into regular maintenance.

Talk to your end users and especially your manager and skip level to ensure that losing data is OK in the event of needing to restore. More often then not people will say it is NOT ok to lose the data they just saved. :)

PS. Don't forget to FULLY document the conversation if they said Simple Recovery is

Hi

thanks for the suggestion and bringing up good points.  

And yes, it is truely not good to switch to simple since we lost point of time restore now.  But, after tossing around different options with our IT, we end up decided that this is what we have to do for now as our DB have always backup at 2AM and we only restore the copy from 2AM if needed to which we only happened once and not providing with point of time restore.

So after talking around, and decided to switch over to simple for now.  We will only provide a full restore from 2AM on this one.

Anyway, I make a full backup and it is 178 GB after compress and leave me with approx 40 GB left.

I am moving this full backup copy to another server drive and store it up.  

For now, I think I finally have a good full backup done on this sharepoint db since Monday. Great relieve that we have a full copy...