shrinking a transaction log
-
I want to shrink my transaction log. The log is backed up regularly. Immediately after a backup I tried to use EM to shrink the log. The size did not change. I thought the shrink would work right after a log backup. I don't understand why it didn't??? Should I run a command instead of using EM?
-
Hi,
You can try in QA:
backup log yourdb truncate_only
dbcc shrinkfile (file_id, targetsize, truncateonly)
Sometimes it can be quite stubborn, so good luck
Ritch
"I didn't do anything it just got complicated" - M Edwards
Edited by - Ritch on 08/18/2003 08:12:51 AM
"I didn't do anything it just got complicated" - M Edwards -
Thanks, that seemed to work!
The SQL Server 2000 Resource Kit says the log should not need to be shrunk periodically. It says "you should further investigate what is causing the log to fill up, rather than focus on constant maintenance with the shrink command"
But it offers no advice on how to do this. Would I have to actually look at the log files to find out what is causing a lot of growth?
-
Hi,
The type of recovery model you have set affects the space, i.e. bulk logged uses the least amount. Check BOL for more, and the pros and cons of different recovery models.
Cheers
Ritch
"I didn't do anything it just got complicated" - M Edwards
Edited by - Ritch on 08/18/2003 09:37:54 AM
"I didn't do anything it just got complicated" - M Edwards -
Maybe there is an uncommited transaction in the log. IF this is stored near the end of the log file, then shirinking the file will only chop off the bit after that point. You could aslso try issuing a CHECKPOINT to ensure that the log is flushed before truncating it.
In EM, make sure that you check the box to move pages to the beginning of the file, as this will also affect the file size after shrinking.
Tony Bater
Tony -
If your db is replicated using transactional replication, the log won't shrink unless all replicated transactions are marked as complete.
Check BOL for system sproc sp_repldone and the associated commands at the bottom of the page, but use this command with due care as noted in the BOL topic.
Good luck.
Vik
-
Another thing that can make shrinking the tran log difficult is if the active portion of the log is near the end of the physical file. SQL Server will not shrink beyond the active portion of the file, regardless of committed/uncommitted transactions. The solution here is to get SQL Server to roll the log back over to the beginning by backing up the log, shrinking it, running a dummy query that will put entries into the trasnaction log, then backing up the log and shrinking again. Repeat as necessary. I have had to repeat this several times for some logs. I use a query that creates a table, adds a value, and updates the value 1500 to 15000 times, then deletes the table. This usually rolls the log back to the beginning of the file just fine.
Chris
Doesn't know nuthin about nuthin, but don't tell nobody. 😉
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
-
If you can shut down the database I've always found that the quickest solution is immediately after a full backup to use sp_detach_db, delete the physical log file and then use sp_attach_db.
EXEC sp_detach_db '<DB_Name>', 'true'
EXEC sp_attach_db @dbname = N'<DB_Name>',
@filename1 = N'<Path_T0_Physical_Data_File>'
By attaching only the data file a blank log file of the minimum size is created. This method is very fast whereas at least some of the other methods used will have a very noticable effect on db server performance.
In my experience I could shut the DB down, detach and attach in under 30 seconds. This went over better with my users than giving slow performance for some indeterminate period of time.
(On more than one occasion I did this without even telling the users. Just kicked them out during a lull and ran the script. Bad DBA! BAD!)
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek) -
If the transaction log is being backed up regularly , shouldnt the SQL server be overwriting the commited transactions ? if you have an adequate size for the transaction log, why shrink it ? I think there can sometimes be performance degradations in shrinking the log all the time
I have a job that says that if the log is reaching it's maximum to backup the log that way the transaction log can be overwritten. Plus you can roll back if needs be from the transaction log..
Someimes i have also found that after you do a shrink on a DB, you need to refresh the database . just my 2c's 🙂
------------------------------
Life is far too important to be taken seriously -
To shrink a log file,
you should call sp_dboption
before using DBCC shrinkfile
(see the script below)
It's usually enough.
/*
shrink a log file
WARNING: change the database name and the logical name of the log file (found in sysfiles).
database: MyDB
log file: MyDB_Log
*/
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'TRUE'
USE MyDB
go
DBCC SHRINKFILE ('MyDB_Log', TRUNCATEONLY )
USE master
go
EXEC sp_dboption 'MyDB', 'trunc. log on chkpt.', 'FALSE'
USE MyDB
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply