May 10, 2004 at 8:23 am
Hi all,
I have a problem with the log file being way too large. It is almost 4 gig in size and the database file is only 2 gig. The transaction log from my understanding only be 1/2 the size of the DB file. I have the recovery model set to full and have been doing full backups every night along with transaction log backups with truncate only. Using the statement below:
Truncating the transaction log:
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
I have also manually tried to truncate the log using enterprise manager by selecting clear all transaction entries from the log option. None of this seems to be truncating the log and it is still 4 gig in size. As a matter of fact the log file is not physically updated. I verified i am looking at the correct file that the DB resides as well. Does anyone have any ideas as to why the log file is not shrinking in size after it is truncated? Maybe it is being cleared but the file size is remaining the same. I know I can back up the log then delete and have SQL server recreate the log file but I would prefer to do this a clean way.
Thanks,
nszczerpanski
May 10, 2004 at 9:30 am
Try running a dbcc shrinkfile command after the log is truncated. if you specify truncateonly that will release all of the empty space back to the os.
From BOL
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
May 10, 2004 at 9:37 am
check out the 3rd or 4th post down in
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=113369#bm113437
do a backup and then shrink.
the size of the transaction log will grow the 'busier' your database is. it should be a reasonable size but i don't know if sticking to half the size of your database is right ...
cheers
dbgeezer
May 10, 2004 at 3:53 pm
By using BACKUP LOG ...WITH TRUNCATE ONLY, you are defeating the purpose of the full recovery model. If you really don't need transaction log backups, consider changing to the simple recovery model, which will help keep your transaction log smaller once you have shrunk it. However, most DBAs recommend using the full recovery model along with transaction log backups. This provides you with the best protection in an online transaction processing environment. About the only time I recommend NOT using the transaction log backups is when the database is completely reloaded in a batch process, making point-in-time recovery unnecessary.
Steve
May 11, 2004 at 9:36 am
This is what i use
BACKUP LOG dbname TO DISK = 'C:\Bakup.log' -- To Backup the Log file
BACKUP LOG dbname WITH TRUNCATE_ONLY -- To Truncate the Log File
DBCC SHRINKFILE (file_name, reqd_file_size) -- To shrink the log file
Size of db in my case is 1.8 gig, and i try and maintain a log file under a gig.
Sunil
May 11, 2004 at 11:22 am
Are you running replication with this database? If you are, and the distribution agent has stopped for some reason, the log file will continue to grow until the distribution agent runs. I had a database that I dropped the replication job on but something went wrong and it 'thought' it still needed to log every transaction.
You might try this in the database in question:
select name, replinfo from sysobjects where xtype = 'u'
and replinfo > 0
If anything is > 0, then that table is being logged for replication.
May 11, 2004 at 12:22 pm
Do the first backup log command a few time, and then try shrinkfile command. It will be shrinked to your desired size.
Also, the second command 'truncate only' is useless and redundant. You dont have to issue it. Hope this helps.
May 12, 2004 at 10:22 pm
Try a Database Maintenance Plan. :-); and install a "SQL check" (free from IDERA) and watch the screen on your desktop pc.
Jimmy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply