Truncate Log File
-
Hi all,
I have written a program using vb6 and SQL 2000. The program run day by day and it make the log file of DB growth suprisingly, I have try to reduce the size of log file by backup the log file but i am not successful, please advise me how to divide the log file into multiple file or how to reduce the size of it (cause it's 10 time bigger than the data file now).
Thanks and Regards
Tuan
-
Hi ,
DB wise:
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
Example:
Database name = MyDatabase
Database log file = MyDatabase_Log
DBCC SHRINKFILE(MyDatabase_Log, 1)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_Log, 1)
What i did was and via Enterprise Manager :
If you are not taking a back up of Transaction log,
Properties->Options->Check AutoShrink, Model: Simple, for decreasing the log file size. Log file in Simple Model will be cleared off after every successful transaction.
When it is time that u take back up, u can reset it and restart the job that takes the back up.
If the log file has grown very large, say 20GB etc, it will take another few transaction to get invoked and after some 1hr, you can see that it will be reduced to 40MB etc.
All the best,
Rosh.K.Mathews
-
I always use the next:
Backup LOG <Databasename> WITH NO_LOG
GO
DBCC SHRINKFILE (<Logfile>,<newsize MB>,TRUNCATEONLY)
GO
And it always works. If doesn't work you should think about detach and attach db with a new empty Log File.
Pancho
-
I always use the next:
Backup LOG <Databasename> WITH NO_LOG
GO
DBCC SHRINKFILE (<Logfile>,<newsize MB>,TRUNCATEONLY)
GO
And it always works. If doesn't work you should think about detach and attach db with a new empty Log File.
Pancho
-
Hi Pancho,
Attaching and dettaching is also a good option. But it has got 2 disadvantages.
1. You will have to reconfigure any replication set in your database.
2. The method is not permanent. You will have to do this again and again. Same is the case with DB Scripts.
If you go for the option in Enterprise Manager, you can forget about this issue and have a good night sleep
-
I'am totally agree with you, but some months ago i was in a situation, where i was trying so many ways to reduce the size of the log size and nothing works. That's why i try the dettach-attach as a final choice, after try another ways. Thats the way of my opinion, if there's not another choice, you can try that. I think the log File was damaged that time.
Thanks
Pancho
-
I have had this problem in the past. I ended up doing do-nothing transactions to get the "logical log" to wrap to the beginning. See "Shrinking the Transaction Log" in BOL.
In the related database, I created a new table with a large field (e.g., char(5000)) with a default value (e.g. ''). I used a loop that would do 1) insert many records, 2) delete the records, and 3) shrink. The log eventually wrapped.
In your case, you might want to start with a huge insert. After the log wraps once, you can then reduce the size of the insert.
I've assumed that simple recovery is in use during the above.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
Thanks for another option, i test with a no so big Log File and it works. Very helpful information in Bol to understand better the shrink process.
Pancho
-
This is a reply of Jeremy (Member of SQL Magazine Forum) whuch works best with me when I want to shrink a DB (I suugest you to Make Log Backup first):
SQL Server sometimes has a problem with shrinking the TLog. If you are not backing up the transaction log as a part of your data recovery plan, I recommend having truncate log on checkpoint enabled. This will help ease further issues.
For unruly database transaction logs, I use the following methodology:
1. First try to shrink the tlog using DBCC Shrinkfile (tlog_filename). After this has completed, run DBCC loginfo on the database of interest. If the last record returned has a status = 2 (active) and there are several entries before it with a status = 0 (inactive) you will need to run some transactions to push the active entry to the beginning of the log (step 2).
2. Run the following script
USE your database_name
GO
CREATE TABLE log_shrinker (char1 char(4000))
GO
DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)
BEGIN
WHILE (@i < 100)
BEGIN
INSERT log_shrinker VALUES ('a')
SELECT @i = @i +1
END
TRUNCATE TABLE log_shrinker
BACKUP LOG database_name WITH TRUNCATE_ONLY
END
This is a script which contains an infinite loop, which will need to be stopped manually. It will push the active entry to the beginning of the log so that the rest of the log can be truncated. I recommend running it for a minimum of 40 minutes for an 8GM Tlog and then stop it manually.
3. Try running BACKUP LOG database_name WITH TRUNCATE_ONLY
4. Now run DBCC Shrinkfile (tlog_filename) again, if the tlog hasn't already shrunk
5. If the Tlog is still huge, run the script in step (2) again. This should do the trick.
Note: This is a manual process which requires you to stop the infinite loop query. I have used it many times to shrink both live(production) databases as well as development databases which have grown too large. Sometimes the script in step (2) needs to be run for a longer time. You can periodically check the tlog using DBCC loginfo to ensure that the active entry in the log is moving to the beginning of the log file.
I Hope this helps!
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply