July 7, 2010 at 10:22 am
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?
July 7, 2010 at 12:33 pm
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.
July 7, 2010 at 7:42 pm
Did you take a full backup of the database first ?
July 7, 2010 at 8:26 pm
>> 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.
July 7, 2010 at 8:32 pm
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! 🙂
July 8, 2010 at 1:32 am
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
July 8, 2010 at 2:23 am
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.
July 8, 2010 at 4:02 am
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."
July 8, 2010 at 4:25 am
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?
July 8, 2010 at 8:01 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply