asked on

SQL Server Express DB Shrink..

I installed a Moss2007 with SSE and now its 4GB limit is reached, I installed the Sql Server Managment Studio Express and have the option to shrink my content DB , can I do that while users are hitting the sharepoint site or do I need to wait for a quiet period and dump the lan cable out of it then run it.
I'm not sure but I thought it would put locks on it while it does it but I dont really know.
Main prob is because DB full , I am trying to delete a large 800MB file off sharepoint to free some space temporarily but it wont let me delete it, I assume because no DB space left ??

RiteshShah

well, I have shrink transaction log files many times during peak hours and didn't face any problem but I guess, it would be great if you do it in low traffic hours.

bivesst2

ASKER

I ran the shrink but obviously it didnt free up anything as I cant delete files from the site now , I assume it trys to put them in recycle bin which the db has no size to accomadate this, I cant even delete them from explorer view , so I'm stuffed am I , I was hoping to get rid of a 1 Gig file which would free up enuff space to let it run till a weekend to upgrade to sql server....

RiteshShah

Please have a look my script at

http://www.sqlhub.com/2008/05/sql-server-truncate-transaction-log.html

shrink your transaction file and then datafile with the way I described.

bivesst2

ASKER

my Transaction logs appear very small , I am running sql Express which has a 4GB limit when installed, my sharepoint WSS content DB seems to have reached the 4GB limit, can this be run on the WSS Content DB with no problems or is it only for transaction logs ??

RiteshShah

you can run it for .MDF and .NDF file as well

dportas

Be careful. Using BACKUP LOG with the TRUNCATE_ONLY option will *invalidate* the sequence of log backups taken to that point. That means you are at high risk of losing data until you next do a FULL backup. Note that the TRUNCATE_ONLY option is removed and does not work in SQL Server 2008. My advice is not to use it, and Microsoft seems to agree.

Frequently shrinking the log is not a good idea because it causes file-level fragmentation and harms performance if and when the database grows again. Determine the correct size for your log files, set them to that size and then leave them alone. Shrinking should be used in exceptional circumstances only.

bivesst2

ASKER

I am very basic with sql , How do run the script , and as what,
and with the script is this the correct format if the db is called wss_content_109983453hr483
use wss_content_109983453hr483
dbcc shrinkfile(wss_content_109983453hr483, 1)
backup log with truncate_only
dbcc shrinkfile(wss_content_109983453hr483, 1)
why is the shrinkfile command run twice.........thanx for your input..

RiteshShah

dportas,

I am agree with what you are saying but don't you think so author is facing exceptional situation?

bivesst2

ASKER

Exactly , I am at a situation of trying to get my sharepoint running again until I can do the sql server standard upgrade to remove 4GB limit , but at moment no one can upload to and I dont want to do upgrade yet and I have a 1GB file that I can remove of site which will let it run probably for another few weeks , so I after quick fix to get it running then upgrade to unlimited sql db size...

dportas

I just wanted to give all the information so that the author can decide for himself. Some people may not realise what the TRUNCATE_ONLY option actually does.

ASKER CERTIFIED SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jagdish Devaku

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bivesst2

ASKER

Yep , I've looked at my content db in Sql server managment studio express and it shows it as max size 4096MB , my application log is giving an 1827 wihich when I looked it up said my DB is at its maximun size and also MOSS states the install I done installs sql server express with a 4gb limit, so I reckon the finger points squarely at the DB is maxxed out to the limit, consequently, I cannot delete data out of it, for what reason I dont know but this is whats frustrating me as Iif I could I would delete some stuff or content to free some space to enable usres to upload to it again temporarily till I can bring it offline to upgrade to a full version sql, but it wont let me delete, I 'm assuming the bloddy db must need space to remove content to the recycle bin which there is none so I'm stuffed I assume until I attempt a sql upgrade which I dont want to do right now..

bivesst2

ASKER

PS I tried a db shrink with SQL studio Management express to no avail !!!!

Jagdish Devaku

ok... as you said that you dont want to buy sql server right now...

then why can't you get and install the evaluation version sql server.... and use it...

you can download it from following link....

http://technet.microsoft.com/hi-in/bb738031(en-us).aspx

bye...

bivesst2

ASKER

Yeah I got a licensed version of sql , just that my site I cant put off air till weekend as 1600 users use it to open apps and hyperlinks to internal files so I just couldn.t upgrade as I need to leave it online until weekend.....I thought I might've been able to find a fast way to keep it functional bit it seems microsoft built in a fall over option that once its reached it limit , ya pretty well stuffed, without going through a major upgrade....