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
801 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:deepakChauhan
deepakChauhan 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SharePoint 2013 with K2 5 23
SELECT query on two levels (detail and summary) 13 47
SSRS 2013 - Creating a summarized report 19 32
SQL Server 2012 r2 - Varible Table 3 23
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

808 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