Solved

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

Posted on 2012-04-13
4
1,265 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
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

757 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

21 Experts available now in Live!

Get 1:1 Help Now