Solved

WSS 3.0 side-by-side on Small Business Server  - How do I shrink Huge logs

Posted on 2008-12-14
38
6,225 Views
Last Modified: 2012-06-09
SBS, WSS 3.0 and WSS 2.0 all on the same server....running out of space fast...URGENT!

SBS locks down acces to SQL DB's (or so it seams) yet my logs are eating up all my C:\ space?

Help?
0
Comment
Question by:wcross75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 18
  • 14
  • 2
  • +1
38 Comments
 
LVL 16

Assisted Solution

by:SQL_SERVER_DBA
SQL_SERVER_DBA earned 50 total points
ID: 23174674
set your recovery mode to simple so SQL Server automatically allocates the memory on large log files
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 23174699
SQL Server truncates the transaction log each time the database reaches a transaction checkpoint
0
 

Author Comment

by:wcross75
ID: 23174753
but how exactly do I set SQL Server recovery mode, run Scritps etc... guess I'm more f a beginner than I let on.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Zberteoc
ID: 23178589
In Management Studio right click on the server names > Properties > select Options > Recovery Mode change to Simple.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23178592
In Object Explorer that is.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23178594
And right click on the database name not on server name.
0
 

Author Comment

by:wcross75
ID: 23186720
Zebrteoc,

Thank you for responding ...I'm sorry I'm not seeing or do not understand the starting point for your directions...I need it spelled out more specifically...sorry.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23187006
If you're using SQL 2005 then in Management Studio you go in Object explorer and make sure you have estabilished a connection to your server. If you did that then expand the database folder, right click on the database name > Properties select Options and on the right side you will see a dropdown box for Recovery Model. Choose Simple and then click OK.
recovery-model.JPG
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 150 total points
ID: 23187025
You can use the sql code bellow as it was generated by doing what I described above:
USE [master]
GO
ALTER DATABASE [EE] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [EE] SET RECOVERY SIMPLE 
GO

Open in new window

0
 

Author Comment

by:wcross75
ID: 23187657
Thanks Zberteoc,
But, I can't use Manager because my DB's are configured not to allow remote access...
Any suggestions on how to reconfigure for remote access?  Then i think I can use your help here to resolve my problem.
Bill
 
 
 
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23188829
Do you have access to the server where database is? If yes access it, open the Management Studio and make the change.
0
 

Author Comment

by:wcross75
ID: 23189105
An SBS Sever does not have an install SQL Management Studio....
Bill
 
0
 

Author Comment

by:wcross75
ID: 23193473

Sorry for this being so difficult.  Obviously, I should bump this up to 500 points.
Is there some way to change the db remote access configs through Sharepoint Admin...or at the command line?
0
 

Author Comment

by:wcross75
ID: 23216537
I have install sqlcmd.exe (sqlcli) on my SBS server but when I attemp to run sqlcmd, I get a connection timeout error , suggeesting that its because SQL Server 2005 configuration as installed on SBS for Sharpoint 3.0 does not allow remote access.

How do connet to and reconfigure my MS SQL 2005 database on my SBS seerver to allow remote access via sqlcmd or SQL 2005 Manager Express?
I think I can fix my problem if someone can help me with this?
Bill

 
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23217941
0
 

Author Comment

by:wcross75
ID: 23218487
I've been working with this...but I do not have these installed...
  • Configuration Tools
  • SQL Server Surface Area Configuration
However, I do have SQLCMD and have established a connection using ..
sqlcmd -S \\ . \pipe\mssql$microssoft##ssee\sql\query -E
1>
Can you help me form here?
 I have three large "_log.LDF" files all over 100MB, one at 600MB
  • Sharepoint_config{ ....} _log.LDF (560 mb)
  • Wss_Content { ....}_log.LDF (110 mb)
  • Sharepoint_AdminContent{ ....} _log.LDF(110 mb)
Bill
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23218697
Help to do what?
0
 

Author Comment

by:wcross75
ID: 23218812
Do you actually read anything I send you?
0
 

Author Comment

by:wcross75
ID: 23218843
So is the final solution here have two parts..
(outstanding questions I need help with are in bold italic)

A. Fix the immediate problem (running out of storage space due to growing log files)

If I am following correctly, any *_log.LDF file can be moved to another drive and SQL will recreate it...
For example,  I have three large "_log.LDF" files all over 100MB...
    • Sharepoint_config{ ....} _log.LDF                     (560 mb)    
    • Wss_Content { ....}_log.LDF                             (110 mb)    
    • Sharepoint_AdminContent{ ....} _log.LDF      (110 mb)  
So, I can do the following to get rid of all three large files(?)
    1. Stop SQL (this can be done in SMS)  
    2. Move file (cut, paste all three listed above) to new drive...  
    3. Start SQL (this can be done in SMS)
And when I restart SQL, all three files will be recreated by SQL?

B.  Fix or prevent logs from growing huge in the future

Part of my problem here was that I do not have SQL 2005 Manager Express or SQLCMD or SQLCLI installed on my server...AND, that default SQL 2005 install did not allow remote access using SQL 2005 Manager Express.

However, once I located and installed SQLCI and SQLCMD on my server (in this order), I was able to you a pipe connect string as follows :
SQLCMD - S \\ . \pipe\mssql$microsoft##ssee\sql\query -E
to get here ..
1> {prompt...}

What SQLCMD compatible commands can I use to:
 1. Make recovery mode simple:
1>  USE [master]
2> GO
1> ALTER DATABASE [EE] SET RECOVERY SIMPLE WITH NO_WAIT
2> GO
1> ALTER DATABASE [EE] SET RECOVERY SIMPLE
2> GO
Should [EE] above be [master] instead?
2. Is this everything needed to prevent the problem inthe future?
Bill
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23219829
First, if you need answers be polite.

Second, you ask a question you gen an answer. For another answer open a new question. These are the rules.


Third, you want to change a database to simple recovery and not the master. "USE master" is there because the statements are run in the master database. In this light of course that you will replace [EE] with whatever database you need to change to simple recovery.

For how you can use SQLCMD to do that you have plenty of examples on the net.
0
 

Author Comment

by:wcross75
ID: 23222189
Zeberteoc,
Sorry. Guess I'm just frustrated, been 5 days now....(BTW, In some cultures...short responses are considered "short" and impolite too) ..
Second, since I have yet to get a "Resolution" to my first queestion, aren't we still trying to answer that original question ??? (How to fix growing log files)
Regarding [EE] question, thank you fo rthe clarification..
 The databases & logs I'm trying to shrink and/or change to SIMPLE recovery mode are:

SharePoint_AdminContent_07d8bb0f-8e06-46d6-a676-698971d9c203.mdf
SharePoint_AdminContent_07d8bb0f-8e06-46d6-a676-698971d9c203_log.LDF

SharePoint_Config_2d452c07-8a91-486d-aea2-89f47c2c4a99.mdf
SharePoint_Config_2d452c07-8a91-486d-aea2-89f47c2c4a99_log.LDF

WSS_Content_5d33d71c2cb84f1c8932b9c99e38af73.mdf
WSS_Content_5d33d71c2cb84f1c8932b9c99e38af73_log.LDF
I tried..
SQLCMD - S \\ . \pipe\mssql$microsoft##ssee\sql\query -E
1> USE [master]
2> GO
1> ALTER DATABASE [{myserver}\MICROSOFT##SSEE\SharePoint_Config_2d452c07-8a91-486d-aea2-89f47c2c4a99] SET RECOVERY SIMPLE WITH NO_WAIT
2> GO
 
And got the following response...
MSQ 5011, LEVEL 14, STATE 5, SERVER {myserver}\MICROSOFT##SSEE, LINE 1, USER DOES NO HAVE PERMISSION TO ALTER THE DATABBASE {myserver}\MICROSOFT##SSEE\SharePoint_Config_2d452c07-8a91-486d-aea2-89f47c2c4a99, OR THE DATABASE DOES NOT EXIST.

MSG 5069, LEVEL 16, STATE 1, SERVER {myserver}\MICROSOFT##SSEE, LINE 1 ALTER DATABASE statement failed.
What do I do next?
 Bill
0
 

Author Comment

by:wcross75
ID: 23227756
Original Problem : Huge and still growing SharePoint 3.0 Log Files on SBS side-by-side install
On a single SBS 2003 Premium Server that houses a Side-by-side Sharepoint Install (both WSS 2.0 and Sharepoint 3.0 via Windows Internal Database).
 
The Windows Internal Database data files installed on my SBS Server can be found in :
C:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\DATA\

The databases log files I'm trying to shrink and/or change to SIMPLE recovery mode are:
SharePoint_AdminContent_[strong part of name].mdf
SharePoint_AdminContent_[strong part of name]_log.LDF

SharePoint_Config_[strong part of name 2].mdf
SharePoint_Config_[strong part of name2]_log.LDF

WSS_Content_[strong part of name 3] .mdf
WSS_Content_[strong part of name 3]_log.LDF
The Solution is:
  1. Change Recovery Model for offending databases from "Full" to "Simple"
  2. Shrink Log File for offending Database
Optional activities (not described here)
  • after Shrinking log file, return Recovery Model to "Full"
  • Move offending database and log files to another drive.

I was able to install Microsoft SQL 2005 Server Manager Express directly onto the SBS 2003 Premium server via this Link to free download (and similar connect instructions) can be found here...
http://www.wssdemo.com/Blog/archive/2007/12/22/connecting-to-the-sharepoint-services-embedded-database-with-management-studio-express.aspx
Change Recovery Model to "Simple"
To start Microsoft SQL 2005 Server Manager Express, navigate the Windows Start menu as follows:
Start - All Programs - Microsoft SQL 2005 Server - SQL Server Manager Express
When prompted to connect to the Database for the SERVER NAME: type the following connection string.
\\.\pipe\mssql$microsoft##ssee\sql\query
Windows authentication (assume you are are or have administrator privileges/permissions)
Once connected, you should have an Object explorer pain on your left (like Windows Explorer Folders Panel) and a Detailed View to your right.
  1. First expand (click on [+] ) the Database Folder in the Object Explorer on the left..
  2. Locate the Database listing with the log file growing out of control....(I started with  SharePoint_Config....)
  3. Next, RIght Click on the offending database and in the resulting drop-down dialog box, locate and click on Properties. A separate Properties Window will pop-up..
  4. In the Properties window, on the left hand side, locate and select Options...
  5. In the right hand Options panel you will see the current configuration options established for the database when installed...
  6. Near the top of this Options Panel, you should see an entry for Recovery Model....mine said "full".  
  7. There you can navigate the value dropdown and select "simple".
  8. Then click the [OK] button at the bottom of the Options Panel to change/save your selection.
Reorg and Shrink Log Files
I first re-organize the file and then shrank it.
Again, using Microsoft SQL 2005 Server Manager Express,
To Reorganize:  
  1. First, from the Object explorer panel on the left, locate the offending database, click on it once then right-click to expose the dropdown menu
  2. Then high-light Tasks
  3. Then from the Tasks drop down menu, high-light Shrink
  4. Then from the Shrink drop down menu, high-light and click on Files
  5. Locate "File type" entry and change drop down selection to "log"
  6. Next locate "Shrink Action" and  click the radio button to select "Re-organize pages before releasing unused space"
  7. Next locate the "Shrink file to" value, high-light the number, type 30
  8. Then click the [OK] button at the bottom of the Shrink Pop Window to Re-organize the File needing shrinking.
To Shrink,
  1. Return from the Shrink Pop Up window (repeat steps 1 through 4 above)
  2. Then, locate "File type" entry and change drop down selection to "log"
  3. Next locate "Shrink Action" and  click the radio button to select "Release unused Space"
  4. Then click the [OK] button at the bottom of the Shrink Pop Window to Shrink the file needing shrinking.
-------------------------------------------
This fixed my problem...thanks to everyone for there suggestions and assistance.
Bill Ross
 


 
 
0
 

Author Comment

by:wcross75
ID: 23227788
Please Remove </A> above
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23228868
wcross75

I already gave you this solution when I explained you the exact same steps to change the recovery model to SImple; see ID: 23178589. Later II posted the message with the picture to be even more specific; see ID: 23187006. You then answered that you don't have MS and I gave you the codee to do the same thing. Now you installed it and you followed my steps.

When you tried with code you used the data files names and not the database names, hence the error, that db didn't existed.

Database is one thing and database file is another.

SharePoint_AdminContent_[strong part of name].mdf
SharePoint_AdminContent_[strong part of name]_log.LDF

are the database files.

In MS, however, when you expand the Databases folder you can see the database name only.

As far as I'm concerned I answered your question.
0
 

Author Comment

by:wcross75
ID: 23233344
Zberteoc,
I said you did provide some useful advise (and gave you points for that) and I thank you for that.
But, you did not provide a comprehensive solution (see my solution post) that worked for me. I tried to follow everything you offered and always ended up going to other posts, searching google for better, more specific solutions.
I found several of your responses incomplete, unclear, almost cryptic and always very generic (not specific to my configuration).
My continued replys and follow-up questions indicated that what you offered was not working for me.  Instead of clarifying working to clarify your solution, you shut down and lost interest in making sure my problem was solved by your solution.
Like I said, you helped ...I thank you for that.
Bill
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23234062
But you asked the question to be deleted so how "and gave you points for that" comes into place?

On the other hand you cannot expect to get always a response in detailed, perfectly accustomed to the problem, which sometimes can be quite general and complex. A good guidance is often equivalent to a good answer, however, in your case I still affirm that my answer was very clear and specific to your questions (how do I set a simple recovery). The problems is that you somewhat "extended" your question, which I agree sometimes happens on EE.
0
 

Author Comment

by:wcross75
ID: 23234554
Zberteoc:
This is the first question I have submitted here and I am learning how to use the tools here. I clicked on the Accept Multiple Solutions button to give you and another responder 100 pts each for offering "helpful" advise even though neither of you provided a working solution.  Then I tried to close the question...because I had summarized what I did to solve my problem in a final Solution Post.
Regardless, in the future, if I respond and offer a solution to someones question here in EE, I will over communicate and follow-up to be sure my solution resolved the problem.
I will consider the Author of the question as the "Customer" who ultimately has the power to determine whether or not my response works as a Solution or not..
So, respectfully, I'm afraid we will just have to agree to disagree...
What we had here was a failure to communicate....I was trying to tell you that your solution wasn't working. for me (perhaps I didn't understand it, or I need you to clarify it for me).. You where trying to tell me that you had given me all the neccessary information...
I asked the question ...tried what you recommended and in the end, over the course of 6 days, adding my own research, solved my own problem. That's just the way it is!
 
 
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23235035
As you said, this was your first question.

On this site rather than a customer you are a beneficiary of a voluntary service offered for free by amiable people. :o)

Cheers.

0
 

Author Comment

by:wcross75
ID: 23235253
Zberteoc:,
I'm a paid subscriber...don't know about the free part...but I undestand what your saying...I'll be better next time.
Bill
0
 

Author Comment

by:wcross75
ID: 23248750
Ultimately...an answer to my question was established and solution deployed....I awarded points to multiple experts.....
 
Bill
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 23250871
I chime in late, but for me, the underlying question was this:
>How do I shrink Huge logs<

which was NOT answered. changing the db recovery mode from full to simple will only make it won't grow further, but not shrink it.

to explain:
all transactions are written to the transaction log, which is used like a circular buffer.
while in simple recovery mode, each internal slot of the log can be reused immediately once the transactions are completed (commit/rollback), the log file will only grow in the early busy hours of the database, and then stay more or less stable in size.
in full recovery mode, the slots will remain used, so the log file will grow. to clear the slots, use
BACKUP LOG yourdatabase TO ....
statement. the log backup is required regularly (for example hourly), to avoid the log grows endlessly.
in this mode, you can later restore not only full backups, but also apply the log backups in the restore so you can restore to a point in time you wish.

the only problem that can occur in both situations is a stuck unclosed tranactions:
DBCC OPENTRAN
will help to reveil that information


> I have three large "_log.LDF" files all over 100MB, one at 600MB
well, that is NOT large, at least not in my consideration. you should leave it there, once you clarified the recovery mode of your database (ie applied the regular tlog backups if it's in full recovery mode)
if the file(s) are on the wrong disk, move them (!! special procedure with either backup/restore OR detach/attach, but that's another question, that you should fine hundreds of time on EE or described carefully on msdn.microsoft.com)

now, to the original question: how to shrink?
DBCC SHRINKFILE  is the command to use:
http://msdn.microsoft.com/en-us/library/ms189493.aspx

you specify the log file in your case, and the target size (which should NOT be too low, otherwise the first thing sql will do is make it larger again :)
note: with full recovery mode, the first time you try the command the effect might be none, due to the fact that the slots at the end of the file are still "in use". if this is the case, you have to "wait" for some transactions (and the t.log backups, if in full recovery mode), and retry.

note2: do NOT plan to shrink neither the db nor the log on a regular basis. it would be a waste of resources.
however, you can plan to watch the size regularly, and in case it got out of hand, check why (DBCC OPENTRAN)

good luck
0
 

Author Comment

by:wcross75
ID: 23252009
Thanks Vee,
Now that it is all said and done , my real problem was the small ammount of primary boot drive space allocated with the default win 2003 SBS install (8 gb).  Especially after upgrading Sharepoint 2.0 to 3.0 via side-by-side method.
If I where to rewrite..redo all this...the question would be...
How to make more space on primary drive of a default install of Windows 2003 SBS 8 gb C Drive)
The solution would include....
  1. Moving default data folders from Drive C to another partion/drive MSOFT TECH How to Link
  2. Moving Sharpoint 2.0 dbs/logs to from Drive C to another partion/drive (see link above)
  3. Moving Sharpoint 3.0 dbs/logs to from Drive C to another partion/drive How to Move WSS 3.0 databases (cut and paste these commands into SQL Manager Express if you like)
  4. Change Recovery Mode to simple (see directions on how to do this here - using SQL Manager Express).
  5. Reorg and Shrink Sharepoint 3.0 Transaction/Log files (see directions on how to do this here - using SQL Manager Express).
  6. Pluss perhaps, Change Recovery Mode Back t full? (see instructions in step 4 to change back to full)
SQL Server DBA, Zberteoc and angellII were all very helpful...
So, please advise....
  1. how do I edit this string of posts (where needed)
  2. award points
  3. close this question
Thnak you,
Bill
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23252154
1. how do I edit this string of posts (where needed)

the "how to change" the recovery mode was explained in your comment html://#23222189 with the alter database statement. note that you don't need to change the recovery mode from full to simple and then back to shrink the file. the "how to shrink" command was given by my comment above http://#23250871 .

2. award points

it's basically your choice, as long at it is logical.

3. close this question

by awarding (as by now, you should have the full picture), the question get's closed (though comments can still be added).
0
 

Author Closing Comment

by:wcross75
ID: 31525839
Thank yo for your assistance...I split the points among you. I will get better asking and responding in the futre.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23256777
I was absent for the last days on EE but I notice that the question is finally closed.

I agree with angelIII that the question was not accurately answered and his contribution is welcomed however a solution was provided and just for clarification very often on EE an original question deviates to subsequent ones and the answer to those are actually the solution looked for.

Kind of situation here where at some point the very clear question was asked in ID: 23174753:

"but how exactly do I set SQL Server recovery mode, run Scritps etc... guess I'm more f a beginner than I let on."

I answered to THAT specific question, which actually can be considered a solution from the perspective that even though it doesn't shrink the files at least will keep them from growing, which was the REAL problem here from my understanding, the possibility of running out of space.

Now I don't say that I gave a complete answer but at least a partial one so my objection was to simply deleting the question. If a response is not complete the author can always give either a lesser score like B or C, or less points to the expert.

I am glad that the problem was clarified and, mostly, that my help was useful.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23256851
One more little thing, the confusion here might be also because the "actual question" was kind of in the title rather than in the Question body. :o)

I usually tend to not pay enough attention to the title as most of the cases the question and the important details are posted in the body.

Cheers!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

687 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