Transaction Log
-
I am new to SQL administration. I have a Transaction log that has grown to over 34 GB. I have corrected the job that runs daily, but, I am needing to know how to truncate the log. HELP.
What is the correct syntax to truncate a transaction log.
This is what I typed and gives the error "incorrect syntax near WITH"
BACKUP LOG dbname
TO DISK = 'c:\temp'
[WITH]
{NO_LOG | TRUNCATE_ONLY}
-
If you are not doing transaction log backups, you need to set your recovery mode to simple and just run
USE database(whatever database it is)
CHECKPOINT
DBCC SHRINKDB()
in Query Analyzer.
To get your command to run, you don't need anything after the temp'. This is just going to do a backup of the log file though, so it doesn't sound like this is what you really want.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Derrick is correct. unless you are getting "transaction log full" errors, in which case the NO_LOG option will work.
-
Hi Shelley,
if you only want to cut the log you can try
BACKUP LOG dbname WITH TRUNCATE_ONLY
then use the DBCC SCHRINK DATABASE to limit the filesize of the logfile.
DBCC SHRINKDATABASE (dbname, TRUNCATE ONLY)
HTH
Joachim
-
You should only use the Backup Log Truncate Only if you know you have a good backup of the currect db or if you are out of disk space and thus can not backup the db.
Using the Truncate Only will remove ALL commited/checkpointed transactions from the log.
It would be worthwhile backing up the log as well first.
-
I use
BACKUP LOG dbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('dbname_log', 1)
-
I run most of my databases in simple recovery mode, but still occasionally have problems with the logs getting pretty full. I use this to dump the log:
dump transaction dbname with no_log
You can also check how much of the log space is used with this.
dbcc sqlperf(logspace)
-
Ensure that you have a good backup of your database. Modify and run the following script:
/************************************************************************/
/* Creation Date: <21 April 2004> */
/* Copyright: Assessment Technologies Institute */
/* Written by: Rick Lowrey */
/* */
/* Purpose: <To reduce the size of production db logfile size> */
/* */
/* Input Parameters: <N/A> */
/* */
/* Output Parameters: <N/A> */
/* */
/* Return Status: <N/A> */
/* */
/* Usage: <N/A> */
/* */
/* Local Variables: <@cmd NVARCHAR(4000)> */
/* */
/* Called By: <Run ad hoc or placed in a scheduled job PRN> */
/* */
/* Calls: <N/A> */
/* */
/* Data Modifications: <N/A> */
/* */
/* Updates: */
/* Date Author Purpose */
/* <this section is used to track changes to the script> */
/* */
/************************************************************************/
/* Take production database offline */
USE master
EXEC sp_dboption '<yourdatabasename>', 'offline', 'TRUE'
/* Detach production database */
EXEC sp_detach_db '<yourdatabasename>', 'TRUE'
/* Rename production database logfile */
DECLARE @cmd NVARCHAR(4000)
SET @cmd = 'RENAME <pathtoyourdatabaselogfile> <newlogfilename>'
EXEC master..xp_cmdshell @cmd
/* Attach production database using original datafile. SQL will not see the original large
logfile and will create a new, default sized logfile in the same directory as the datafile */
EXEC sp_attach_db @dbname = N'<yourdatabasename>',
@filename1 = N'<pathandnameofyourdatabase.mdf>'
/* Take production db back offline */
USE master
EXEC sp_dboption '<yourdatabasename>', 'offline', 'TRUE'
/* Detach production db again */
EXEC sp_detach_db '<yourdatabasename>', 'TRUE'
/* Move new logfile from current location to where it needs to be */
SET @cmd = 'MOVE <currentpathandnameofdatabaselogfile.LDF>
<newpathandnameofdatabaselogfile.LDF>'
EXEC master..xp_cmdshell @cmd
/* Delete old humongous logfile */
/* May remark this step out and */
/* test database before deleting */
/* old logfile */
SET @cmd = 'DEL <pathandnameofrenamedlogfile.LDF>'
EXEC master..xp_cmdshell @cmd
/* Attach production db once again with correct file locations */
EXEC sp_attach_db @dbname = N'<yourdatabasename>',
@filename1 = N'<pathandyourdatabasename.MDF>',
@filename2 = N'<pathandyourdatabaselogfile.LDF>'
-
Thank everyone so much for the help. I was able to correct my problem. You are all appreciated very much. Your knowledge is just amazing. Thanks for the mentoring.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply