Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

10 Experts available now in Live!

Get 1:1 Help Now