September 25, 2002 at 8:12 am
I use the 'complete backup' provided by maintenance plans to backup several databases on a nightly basis. The transaction log on these databases continues to grow larger despite the nightly backup. The nature of the database does not demand the transaction log be backed up, however, do I need to perform a transaction log backup to get the log to shrink ? Or is there some other database or backup option that I need to set to get the log file to shrink ?
Thank you.
September 25, 2002 at 8:29 am
If you truly don't want to keep any transaction log information, to keep the transaction log small your might consider changing your "Recover Model" to "Simple" if you are in SQL 2000, or using "trunc. log on chkpt" option if you are in SQL 7.0.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 26, 2002 at 4:54 am
quote:
(...) log file to shrink ?
Why don't you try to run a schedule job with the 2 steps:
BACKUP LOG [<BD_name>] WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('<Log_name>', 50 ,TRUNCATEONLY)
where 50 is the size of the log that you want (in M ).
Schedule the job to run in a daily basis.
Hope that helps
Luis
September 26, 2002 at 9:23 am
Another alternative is to delete the transaction log after sucessfully detaching the database. Don't know if there is a convenient time to take the database off line, but when you want to shrink the log file, you can take the database off line by running sp_detach. This closes the database down. Then the log file can be deleted (although I rename it first just to be sure.) Then the database can be attached by running sp_attach_db stored procedure and not specifiying the log file. The database will attach and come back online with a log file size of 504 kb.
A note of caution. Make sure sp_detach_db works without any errors. Also be sure to rename the log file first after detaching so if you do have any problems running the sp_attach, you do have the original log file
that can be used to re-attach. If there is a problem during the sp_detach and the log file gets deleted, you may be scrambling for a full backup to restore as you may not be able to bring your database back online.
Please see the following link that describes this in more detail:
September 26, 2002 at 9:36 am
Considering I am making a full backup - why wouldn't the transaction log be cleaned out at that time ? Why do I need to do something extra ?
September 26, 2002 at 9:55 am
"truncating" the active portion of the log is not "shrinking" anything. you still need to shrink the physical file. BOL has a good definition of the "virtual" and "logical" log definitions, and shows the physical space usage and activity. ( I use the updated "BOL2")
September 26, 2002 at 8:09 pm
The transaction log is getting emptied, it is just not getting shrunk. As was previously mentioned, you have to use the DBCC ShrinkFile command.
Its is a "one off" thing. You have to shrink the log and then set the "simple" option (in SQL 7, its truncate on checkpoint) in the database properties. This option will prevent it from growing large in the future. The other option is to perform regular transaction log dumps which can be easily configured with a maintenance plan.
September 26, 2002 at 10:29 pm
MAybe using the simple recovery model is not a good advice. More in a production db.
You should check why the log file is growing (with profiler maybe) and you could also run during the day more logs backups.
September 27, 2002 at 12:04 pm
The other thing to keep in mind is SQL 7 has known issues with shrinking a bloated LOG due to the virtual logs. There is a script on this site to force the shrink. Finally, consider what happens if you truncate the log file and make sure does not conflict with you data reliability needs. If the data is backuped nightly and has a fully recoverable source then truncation will be ok. But if changes constantly happen and they may be lost if DB goes done between backups then you may not want to set the truncate to occurr automatically but run a script to do when backup occurrs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 5, 2002 at 12:45 am
Hi
I read all replies posted on this topic.
even i have same typical problem.
I also have a shared Db server with more than 200 Db being(accessed/updated by diff customers).
In such a case it is very difficult to identify and somtime followup customer that which squery in their code is bad and enforce them to change it.
also identifying the output of sql profiler is a bit tedious one.
Can any body suggest any other free utility which helps to identify long running queries..
does any body has tried to script the detach , attcah process i found in ..
http://www.swynk.com/friends/krishnan/tranlogshrink.asp
i am doing this manually for My major dbs whose logs grows in size of 1 Gb in a month..
also the above article says
"make sure before detaching that ensure no processes are using the database files.) "
is i need to script the above process -- then Can i kil processes related to a db using some procedure..?
This issue is really very critical as Logs eat up lot of space and I am unable to answer my director Technical that why is this behavior continuing..
Thanks
Deepa
October 5, 2002 at 9:37 pm
If you have space shortage issue and customer does not require recovery to any point in time on database failure, you can simply set the database recovey mode to 'simple' in 2000 and trunc. log on chkpt' in 7.0 as suggest by Gregory Larsen.
You can also setup alter to monitor the growing of transaction log and fire a job to truncate the log anytime the log size exceeds the threshold.
Profiler is very good tool to help you to identify the poor performance queries. By configure the filter properly, you will be easily to find out which customer's queries that have to be tuned.
October 8, 2002 at 9:00 am
Detaching to shrink the log file is totally unnecessary.
Truncate the log on a regular basis either by doing a transaction log backup or setting 'truncate log on checkpoint' to true if you do not require log backups.
Run DBCC Shrinkfile and then on your next truncate the file will shrink (if there is sufficient empty space in the file).
Remeber both Truncation and shrinking need to be repeated regularly to prevent the log file form growing where the amount of transactions is not relatively constant.
The problem in SQL 7 with the log file refusing to shrink usually ( & luckily) only raises its head if there is a sudden change in the amount of data recorded in the log.
Make sure you understand the SQL Server backup and restore startegies if you are working with production databases. Its too important to mess up.
I know I have repeated a lot of what has been said already but it is one of those important topics that is repeatedly asked about so you cannot repeat the answer too often 🙂
Nigel Moore
======================
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply