How to compact WSS databases with SQL Express

iVenture_Solutions
iVenture_Solutions used Ask the Experts™
on
I have a Windows Small Business Server 2008 that has a 50GB log file (LDF) for a WSS 3.0 "config" database. It's strange because WSS isn't used that much by the users and none of the old MDF and LDF files for WSS are any larger than 70MB.

Pretending I know very littel about WSS and SQL maintenance... how do I compact this log file? Since I can't access the WSS database in SQL Server Management Studio Express?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
To Shrink the WSS database can be done in the same way you would in any other SQL DB, but there are a few steps you need to follow within SharePoint before you can do this.

Steps
 
1. In Central Admin -- Operations -- Quiesce Farm
2. Place time in Minutes to Start Quiesce and click on Start Quiescing
3. Monitor Status on this page until Status shows Quiesced
The steps above will stop all activities on the farm/
4. First check out the properties first for our SharePoint database files.
5. Run the SQL command to shrink the database data file (DBCC SHRINKFILE)
6. Run the SQL command to shrink the database Log file (DBCC SHRINKFILE)
7. In Central Admin -- Operations -- Quiesce Farm
8. Reset Farm
9. Monitor Status on this page until Status shows Normal
10. Farm back up.

Author

Commented:
Assuming the files are named the following, where do I go to run the SQL commands and what is format for the SQL compacting command:
- SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6.mdf
- SharePoint_Config_29c26fca-17b8-48c1-9704-b869932abcb6.ldf

My apologies but my SQL experience is limited and has only been GUI based thus far.

Author

Commented:
BTW, I'm not seeing any options in Central Admin with the word Quiesce in them.

Author

Commented:
NM. I got it. Thank you.
Ok the options are in Central Admin -- Operations -- Quiesce Farm

If you haven't enabled the auto-shrink setting, there may be times when your database has been cleared of significant amounts of data, and you'd like to reclaim the space on disk that the partially empty MDF file is taking up. To do this, you can use the DBCC SHRINKDATABASE command

To shrink the database follow the below command line taken from http://msdn.microsoft.com/en-us/library/Aa258287
DBCC SHRINKDATABASE
Shrinks the size of the data files in the specified database.

Syntax
DBCC SHRINKDATABASE
( database_name [ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)

Arguments
database_name

Is the name of the database to be shrunk. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

target_percent

Is the desired percentage of free space left in the database file after the database has been shrunk.

NOTRUNCATE

Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

TRUNCATEONLY

Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

To Shrink the SQL Log Files you would use the DBCC SHRINKFILE command;
Follow this KB Article;
http://support.microsoft.com/kb/272318
 I hope this helps.
 
Regards
 
Bobby Habib
SharePoint Technical Architect / Development Lead
 
 
 
 

operations.jpg

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial