iamari
asked on
How do i truncate a log file manually in sql 7?
i need to truncate this log file, 8Gb
documentation says i have to back it up and select the truncate on checkpoint option.
only thing is, from the server manager, when i do the backup, the Transaction Log radio box is greyed out
documentation says i have to back it up and select the truncate on checkpoint option.
only thing is, from the server manager, when i do the backup, the Transaction Log radio box is greyed out
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your best bet is to to set the recovery model for the database to full...this way you can back up the logs for a better disaster recovery model. This will allow you to then back up the logs as part of a maintenance plan or just back them up plainly otherwise if the model is set to simple...no log backups allowed. Then you can also set the in the databases properties , the option to auto shrink for the database itself.
Let me know how this works for you...with logs that big you might want to back them up frequently.
Let me know how this works for you...with logs that big you might want to back them up frequently.
>> the Transaction Log radio box is greyed out
This means that the option "Truncate Log on Checkpoint" (SQL Server 7) is activated, thus every transaction is truncated automatically upon commit...
>>truncate this log file, 8Gb
I guess that rather "truncating" the log file, you want to "shrink" the log file ?!!
For this, you need a different sql statement, or if you really want to shrink it down, do the following:
* close any connections to the database
* detach it (use sp_detach_db) in query analyser
* rename/delete the .ldf file(s) in Windows explorer
* attach the data (use sp_Attach_db), specifying only the .mdf file(s). This step will recreate the transaction log file with a small size :-) in query analyser
* set the max size of the log file to say 20% of the actual database size, which should be sufficient if the option "truncate log on checkpoint" is set, use Enterprise manager for this
Of course, you might perform a full backup of the database before doing these steps ... but I didn't run into problems with a good db.
CHeers
This means that the option "Truncate Log on Checkpoint" (SQL Server 7) is activated, thus every transaction is truncated automatically upon commit...
>>truncate this log file, 8Gb
I guess that rather "truncating" the log file, you want to "shrink" the log file ?!!
For this, you need a different sql statement, or if you really want to shrink it down, do the following:
* close any connections to the database
* detach it (use sp_detach_db) in query analyser
* rename/delete the .ldf file(s) in Windows explorer
* attach the data (use sp_Attach_db), specifying only the .mdf file(s). This step will recreate the transaction log file with a small size :-) in query analyser
* set the max size of the log file to say 20% of the actual database size, which should be sufficient if the option "truncate log on checkpoint" is set, use Enterprise manager for this
Of course, you might perform a full backup of the database before doing these steps ... but I didn't run into problems with a good db.
CHeers
yap...before truncating log file, better try to shrink it..maybe this is a solution to your problem, and is a better choice..if you ask my opinion...
You can shrink it wither in Enterprise manager, or using an sql statement like:
DBCC SHRINKFILE (N'logfilename',1000) --this shrinks to 1 GB, only of course if there is less than 1GB used space in the log file..
Check before runnig this statement how much space is used in the log file
(in Enterprise manager-> right click on the database name-> select All Tasks-> select Shrink-> push the button 'Files-> select the log file and in the window you will see the total space and the used space)
Sugestion: create a job that periodicaly shrinks the log files.. (EG monthly)
good luck,
xenon
You can shrink it wither in Enterprise manager, or using an sql statement like:
DBCC SHRINKFILE (N'logfilename',1000) --this shrinks to 1 GB, only of course if there is less than 1GB used space in the log file..
Check before runnig this statement how much space is used in the log file
(in Enterprise manager-> right click on the database name-> select All Tasks-> select Shrink-> push the button 'Files-> select the log file and in the window you will see the total space and the used space)
Sugestion: create a job that periodicaly shrinks the log files.. (EG monthly)
good luck,
xenon
Nope - you need to truncate first - it can't shrink past active entries.
From enterprise manager you can do both but better to use the commands.
If you don't need the tr log backups then set the database to simple recovery mode. This will truncate for you.
Then use dbcc shrinkfile on the log file.
This should shrink the file - if not you have to move the active potion to the start of the file.
Don't create a job that periodically shrinks the file - you will just be slowing down the system. If your log needs to be large then leave it that size otherwise it will just grow again which is a resource intensive task.
You should only shrink the file after an unusual growth - like a one off process or because it wasn't set up properly.
From enterprise manager you can do both but better to use the commands.
If you don't need the tr log backups then set the database to simple recovery mode. This will truncate for you.
Then use dbcc shrinkfile on the log file.
This should shrink the file - if not you have to move the active potion to the start of the file.
Don't create a job that periodically shrinks the file - you will just be slowing down the system. If your log needs to be large then leave it that size otherwise it will just grow again which is a resource intensive task.
You should only shrink the file after an unusual growth - like a one off process or because it wasn't set up properly.
depends on what kind of application he has... for an application that has not many updates/inserts... but instead is more a reporting tool, the log files will grow very large, even if they don't use just a few mega (1-15 MG)... which by shrinking you improve a lot the database activity...
So a job that makes the shrink periodicaly (lets say every 3-4 days) it will improve a lot... The extra cost of growing the log file is very small in my opinion even if its made once a day ..... (this is only if the space used in the log file is never more than 10% of the space used by the log file..)
what do you think about this, nigelrivett ?
So a job that makes the shrink periodicaly (lets say every 3-4 days) it will improve a lot... The extra cost of growing the log file is very small in my opinion even if its made once a day ..... (this is only if the space used in the log file is never more than 10% of the space used by the log file..)
what do you think about this, nigelrivett ?
ASKER
many thanks to adwiseman and nigelrivett, shrank the sucker to 20Mb
In order to use the above replace <databasename> with actual db name no<> and same goes for logfile name.
Use <databasename>
DBCC SHRINKFILE('<logfilename minus the .ldf>', 1)
That's the logical file name or file ID - not the physical name minus the .ldf.
sp_helpfile should give the logical (and physical file names) and file id.
sp_helpfile should give the logical (and physical file names) and file id.
If you want to actually recover the disk space then you have to shrink the file.