transaction log file size
-
HI. I have a database whose transaction log file size was larger than i thought was needed. so i detached the database, and deleted the log file. when i reattached the database, the log file size was of course at the default size as when you create a new log file. But no sooner the next day the log file size was just as big as the day i deleted it and recreated it. I run a maintenance plan every night and the log file is backed up. what is causing the log file to go back to it's large size each time i do this? does it have something to do with my maintenance plan and the indexes?
I'm confused and I know it's a simple explanation.
Thanks for ANY help.
Juanita
-
If you are using the full/bulk-logged recovery models, you might need to perform more frequent transaction log backups to keep the size of the trx log to a manageable level. Or you could switch to the simple recovery model if feasible.
You need not detach/attach the database to reduce the trx log file size. You can use the DBCC SHRINKFILE command.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
If your maintenance plan includes 'Reorganise data and index pages' option, your transaction log will blow out as SQL Server reorganises the data. Under the covers, SQL server will run the command DBCC INDEXDEFRAG and DBCC SHRINKDATABASE amongst other things.
Try turning off the 'Reorganise data and index pages' and 'Remove unused space from database fiiles' options in your Maintenance plans and see if your transaction log still grows unusually large.
The transaction log grows depending on the amount of data insert/update/deletes that occur in your database. Evertime something is added/changed/deleted in your database, SQL Server will record the action. When you do a transaction log backup, these actions are backed up, and marked as allowed to be overwritten. Maybe increase the frequency of your transaction log backups (usually hourly is fine).
Open up Sql Books Online and read up about the transaction logs, backups, and the DBCC commands.
Julian Kuiters
juliankuiters.id.au -
Hi Julian
Where do I find these options
- Reorganise data and index pages
- Remove unused space from database files
paul -
Under the Optimizations tab in the properties of your database maintenance plan. They should be the first and third tick boxes.
Julian Kuiters
juliankuiters.id.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply