Solved

SQL Server Express DB Shrink..

Posted on 2009-05-06
17
1,289 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 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