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
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
804 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
11 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 250 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 175 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 75 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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