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

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
dlaffertyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QPRCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Justin SmithSr. System EngineerCommented:
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
Deepak ChauhanSQL Server DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
Justin SmithSr. System EngineerCommented:
What was the solution exactly?
0
Deepak ChauhanSQL Server DBACommented:
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
Justin SmithSr. System EngineerCommented:
I understand the problem.  What was the solution?
0
Deepak ChauhanSQL Server DBACommented:
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
Anthony PerkinsCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.