[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1412
  • Last Modified:

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

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
Ast35
Asked:
Ast35
1 Solution
 
stavros41Commented:
Run a full Log file backup, this should reduce the .ldf file down to a resaonable size.
0
 
kaushysCommented:
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
 
Lee SavidgeCommented:
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
 
Ast35Author Commented:
Yep, that was it, thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now