Solved

SQL Server Express DB Shrink..

Posted on 2009-05-06
17
1,262 Views
Last Modified: 2012-05-06
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 ??
0
Comment
Question by:bivesst2
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24321900
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.
0
 

Author Comment

by:bivesst2
ID: 24322724
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....
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24322745
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.
0
 

Author Comment

by:bivesst2
ID: 24322780
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 ??
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24322791
you can run it for .MDF and .NDF file as well
0
 
LVL 22

Expert Comment

by:dportas
ID: 24322843
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.
0
 

Author Comment

by:bivesst2
ID: 24322872
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..
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24322885
dportas,

I am agree with what you are saying but don't you think so author is facing exceptional situation?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:bivesst2
ID: 24322921
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...
0
 
LVL 22

Expert Comment

by:dportas
ID: 24322929
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.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 200 total points
ID: 24322930
well this is something nobody can't decide except you, all you have to do is, remove unwanted files and you may be with the luck. don't forget to have full backup.
0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 200 total points
ID: 24322950
Log files don't count towards the 4GB limit in Express. Shrinking the data files will NOT free up space in the database because it only removes unused pages (FREE space). It doesn't make the data smaller.

What you should do is check the server error log to find out exactlt why you are having problems. You say you are assuming that the DB has no space left but you don't say why you think that or exactly what the symptoms are. Just shrinking data files is unlikely to be the answer.
0
 
LVL 14

Assisted Solution

by:Jagdish Devaku
Jagdish Devaku earned 100 total points
ID: 24323641
hi,

you can do one thing...

install an evaluation version of sql server.

move the database to the evaluation version of sql server...

and before evaluation version license ends... take a license and use it...

(or)

remove the unnecessary data from the database...

and also try shrinking using the following query...

Dump Tran <db_name> with no_log
DBCCshrinkdatabase(<db_name>,30)

bye

0
 

Author Comment

by:bivesst2
ID: 24324074
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..
0
 

Author Comment

by:bivesst2
ID: 24324081
PS I tried a db shrink with SQL studio Management express to no avail !!!!
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24324635
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...

0
 

Author Comment

by:bivesst2
ID: 24331322
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....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now