1

I have a database I restored on a new hosting space. I worked a lot in this db before publish it. The mdf file is 37MB, the log file is 427 MB. I could have disk space problems with the hosting service, could I truncate the log file and then restoring the database again? And how could I do that?

Or maybe someone has suggestions on how to proceed?

Many thanks for your time.

1
  • Belongs on Server fault. But I will suggest you read about transaction log backups.
    – HLGEM
    Nov 11, 2010 at 21:21

2 Answers 2

2

SQL Server 2008 removed BACKUP LOG WITH TRUNCATE_ONLY because it breaks the backup chain when using the Full Recovery Mode.

Unless you are really making backups of the log (which make sense only for databases of many GB), you should change to the Simple Recovery Mode (you can do that from SQL Server Management Studio), which saves only the log needed for restoring a transaction (while the full recovery mode allows to reconstruct data from the log backups).

With Simple Recovery Mode when the logs grows you only need to do:

DBCC SHRINKDATABASE(database,0,TRUNCATEONLY)

or

DBCC SHRINKFILE(DatabaseLog, 1)
0

backup the database and restore it on your host.
don't just copy the mdf and ldf files.

2
  • I haven't copy the files, I uploaded the bak file and they restored for me. Do you think I should ask them to delete the mdf and ldf files?
    – opaera
    Nov 11, 2010 at 21:23
  • 1
    Backup and restore don't work because the log is saved also in the backup, at least in Full Recovery Mode, so when the database is restored the log is restored too. Nov 11, 2010 at 23:19

You must log in to answer this question.