?
Solved

How do I manage the SharePoint Config Database, the LDF file is over 15GB, and the SQL managment Studio doesnt show the Database

Posted on 2012-03-31
11
Medium Priority
?
822 Views
Last Modified: 2012-04-15
I have an SBS 2008 Server with Sharepoint installed, I cannot manage the database in SQL to truncate or shrink the .LDF file that is 15GB.  I have SQL 2005 managment studio express but dont know how to open the .MDF file or .LDF file.  My SQL managment studio only lets me open ,the SBSMonitoring and a few other Databases we have in office.  Once I open them I can shrink it or back it up and that should eliminate the LDF file.

Thanks
0
Comment
Question by:dlafferty
[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
11 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 1000 total points
ID: 37792516
My guess is that your account (that you are using to connect to the server in management studio) does not have the necessarry permissions to view the SP databases, that or you are not connecting to the correct SQL instance.

That said....
SQL log files (LDFs) grow for a reason, index rebuilds, bulk inserts, mass updates or deletes... none of which sound plausible for a SP config database.
My point is that shrinking a log file is temp solution (if you can call it a solution), the log file may well need to re-grow to do it's "thing" and autogrowth is a CPU intensive operation.

1st question is, is the log being backed up regularly? If not then it will continue to grow.

You are entering a terrority that requires a good working knowledge of SQL Server.

When you say you cannot open it, do you mean it is visible but you cannot expand it's contents?
0
 
LVL 38

Assisted Solution

by:Justin Smith
Justin Smith earned 700 total points
ID: 37793034
You don't have access to your SharePoint instance?  Or you don't know how to connect to it?  Last time I worked on an SBS environment, it was using the internal database instance.  Since your file is 15 GB, I'm guessing you are running the internal instance as well.  If so, you connect to it with this connection:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query


Once connected, I would edit the properties of each of your databases and change the recovery model to SIMPLE.  This will keep the log truncated and prevent it from growing out of control.  However, if you are running incremental backups of the databases during the day, you won't be able to do this.  

To shrink the huge log file, run a query against the database:  dbcc shrinkfile('databasename_log',1000)

This will shrink the file to 1 GB.  Make sure you change the recovery model to SIMPLE, wait 10 minutes, then run the query.  Keep an eye on the file after  you run the query.  If it doesn't shrink, wait another 10 minutes and run again.
0
 
LVL 15

Assisted Solution

by:Deepak Chauhan
Deepak Chauhan earned 300 total points
ID: 37793678
I think you can not open this file in sql server as you say your file size is 15GB and
sql server Express edition limit is 4GB so my dear i suggest you open it in standard or enterprise edition if you don't have you can download evaluation edition for 180 days then you can shrink this file using

1 DBCC SHRINKDATABASE(N'DBNAME' ) or

2 DBCC SHRINKFILE (N' Logfile_name' ,1 ) this will shrink your file to one mb

if you are use dbcc  then  please change recovery model from FULL to SIMPLE

If you can not change recovery model than you can take a full backup of this db and after that a transaction log backup .
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37793999
Database files are not readable by anything else than the SQL Server engine and both DATA and LOG files are required to connect a specific database to a SQL Server instance.  SQL Server is not Access or word.
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 37824390
What was the solution exactly?
0
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37824497
Achilles problem was that Dlafferty has a data and log file which size was more than 15Gb. But he was trying with sql server 2005 express edition is limited to maximum 10 GB.
0
 
LVL 38

Expert Comment

by:Justin Smith
ID: 37824523
I understand the problem.  What was the solution?
0
 
LVL 15

Expert Comment

by:Deepak Chauhan
ID: 37824584
use standard /enterprose/ developer edition of sql server to open more than 10Gb mdf / ldf files

database size limit...................=
sql server 2005 express limit is 4 GB max.

sql server 2008 express limit is 4 GB max

sql server 2008 R2 express limit is 10 GB max. and

sql server standard / enterprise. developer edition limit is unlimited
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37825882
deepakChauhan
Dlafferty has a data and log file which size was more than 15Gb. But he was trying with sql server 2005 express edition is limited to maximum 10 GB.
Be careful with the misinfromation.  The SQL Server 2005 Express Edition only supports data up to 4 GB not 10GB.  Also, that has no bearing on the size of the data and Transaction log files.  They can be 100 GB each and you can still use the database, provided that the data does not exceed 4GB with SQL Server 2005 Express Edition and 10GB for SQL Server 2008-R2 Express Edition.

use standard /enterprose/ developer edition of sql server to open more than 10Gb mdf / ldf files
Again that is not correct.  Please check your facts first.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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