problem getting rid of a log file

Question

problem getting rid of a log file

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
July 7, 2010 at 10:22 am

#141303

i restored a database to ship to a vendor. the db is 42GB and half of it is the logs. trying to cut it down before i FTP it.
trying to get rid of the secondary log file and it's not working. i run back up log, dbcc shrinkfile emptyfile and alter database and it still won't remove the log, shrink it or whatever due to some data in there that i don't know what it is.
any advice how to shrink it?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 1

Did you try running the DBCC Shrinkfile with TruncateOnly after or before the EmptyFile?

ISTR going through this and I had to run both of them (don't remember the order) before SQL finally allowed me to remove the log file. But it was a long time ago.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Did you take a full backup of the database first ?

Ray Mond SSCertifiable Points: 7159 More actions · Answer 3

>> run back up log, dbcc shrinkfile emptyfile and alter database and it still won't remove the log, shrink it or whatever due to some data in there that i don't know what it is.

If your database is running in full or bulk-logged recovery models, you may need to back up your transaction log first prior to attempting to shrink it.

>> i restored a database to ship to a vendor.

So this database is not the actual production database? If you do not plan to ship additional transaction log backup files to your vendor, or if the contents of the active transaction log is not important, you could try detaching the database. Then just send the data file, and your vendor can use the sp_attach_single_file_db/CREATE DATABASE ... FOR ATTACH syntax to recreate the database.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

SQL Marathoner SSC-Addicted Points: 417 More actions · Answer 4

DBCC Shrinkfile with TruncateOnly is applicable only to data files.

You may need to truncate your transaction log before you are able to empty that log file. Try this:

backup log [yourdb] with no_log -- suppose you don't need the log records anymore

Then do what you did before: DBCC Shrinkfile with emptyfile then alter database to remove that log file.

Good luck! 🙂

Amit kulkarni-491236 Default port Points: 1492 More actions · Answer 5

shrinking the log file involves following steps:-

For sql server 2005 version :-

Use [dbname]

go

Backup log[dbname] with truncate_only

go

dbcc shrinkfile(2,1) /* shrinking log file (file_id = 2) to 1 MB */

go

For sql server 2008 version :-

1) set recovery model to simple

2) execute dbcc shrinkfile(2,1)

3) set the recovery model back to full if previously it was a full recovery model.

Sometimes if both of these options doesnt work thn you can use the following steps,

( Note plz take full backup of DB before proceeding for these steps)

1)detach the database

2) rename or move the log file to different location

3) retach the db and under the log file menu you will see msg :- Notfound. Just select that file and click on remove and attach the DB.

4) A new log file size of 1MB will created.

5) once the DB is fine, you can delete the old log file.

Regards,

Amit kulkarni

Ray Mond SSCertifiable Points: 7159 More actions · Answer 6

Amit, I think you should qualify your suggestions by stating that they are only applicable if the transaction log is not critical to the user. You really shouldn't be using the TRUNCATE_ONLY option, changing the database's recovery model, and getting SQL Server to create a new transaction log file, if having a recoverable chain of transaction log backups is important to the user.

Thanks.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

free_mascot One Orange Chip Points: 27169 More actions · Answer 7

IF YOUR DATABASE IS TEST THAN ONLY PROCEED.....

As this may have data loss.

Make sure you do have the backup of the database

You can simply do the following steps:

1. detach of the database

2. rename the logfile

3. attach the mdf file without logfile.

You will have small size of the logfile.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 8

Good point about the TRUNCATEONLY issue being only data files. Maybe that's what I was working on, I don't remember.

Alen, are you getting a specific error message when you try to delete this file?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

the database in question is for Symantec's anti-virus management product. they needed a copy for a case we had open so i restored it to QA and tried to shrink it before sending it to them. in the end they said we don't need it.

i tried to get rid of the log anyway and nothing seems to work. could be some corruption. few months ago it was on a different server that had hardware issues. i had to move it to another server and run dbcc checkdb. if it continues to be a problem i might just recreate the log file in the future.