Solved

SQL Server database backup file: 65Mb, database LDF file 70Gb and growing. Please help!

Posted on 2012-04-13
4
1,342 Views
Last Modified: 2012-04-13
OK, we have a website (which I have not been involved in building), which is eating up server space very fast.

When I attempt to restore the 65Mb backup file on my local system for example it gives the following error -

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server '****-PC\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'c:\' to create the database. The database requires 62259724288 additional free bytes, while only 8115404800 bytes are available. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


We urgently need to shrink this database down to a more manageable size and also to make a second copy of the database on the same server (and obviously the space issue is a major problem).

At the very least we need to stop it from increasing in size so much! There is obviously something wrong with the way the database is set up.

I would really appreciate your help on this.

Thanks in advance.
0
Comment
Question by:Ast35
[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
4 Comments
 
LVL 6

Expert Comment

by:stavros41
ID: 37842760
Run a full Log file backup, this should reduce the .ldf file down to a resaonable size.
0
 
LVL 1

Accepted Solution

by:
kaushys earned 500 total points
ID: 37842815
It is likely that the log file has not been backed up and not shrinked for a long time hence it is 70 GB. To verify this run the following query and if you see the wait description is 'LOG_BACKUP' then you will have to take a log backup before you can shrink the file.

select name, log_reuse_wait_desc from sys.databases

But on the server where the DB resides and the backup was taken if you do not have space to take a log backup for a 70 GB file, then just change the database model to simple recovery and shrink the file. Make sure you change it back to full recovery and take a full backup after this.

If the wait desc is anything else then please read the following article.
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Once you have taken a log backup or changed the recovery model to simple use a query like the following one to shrink the log file.


DBCC SHRINKFILE (N'AdventureWorksLT_Log' , 0, TRUNCATEONLY)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37842889
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24779598.html

This is with regards to SQL 2005 but it is pretty much the same for 2008.
0
 

Author Closing Comment

by:Ast35
ID: 37842902
Yep, that was it, thanks!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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