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
793 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
Comment Utility
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
Comment Utility
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:deepakChauhan
deepakChauhan earned 75 total points
Comment Utility
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 38

Expert Comment

by:Justin Smith
Comment Utility
What was the solution exactly?
0
 
LVL 15

Expert Comment

by:deepakChauhan
Comment Utility
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
Comment Utility
I understand the problem.  What was the solution?
0
 
LVL 15

Expert Comment

by:deepakChauhan
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now