February 10, 2009 at 7:59 am
If you are having to shrink files because you are running out of disk space then you need to get more disk space. Repeated shrink and grow of database files will harm your performance, whatever the reason is for why you are doing it.
If your log file is growing 'too big', how often are you doing a log backup? You may find that doing a log backup every 3 hours (or more often if needed) will stop your transaction log getting 'too big'.
If you are using Full Recovery or Bulk Logged Recovery, then you need to do log backups as well as database backups. Books Online (BOL) explains why this is necessary, and there are lots of forum posts on this subject you can find with Google.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 10, 2009 at 9:42 am
Are the transactional logs backed up regularly?
If not, do a transactinal log backup first (regular transactional log backup), then shrink log will work.
If the logs are not backed up, they will not be shrinkable, coz they are deemed as being in use.
February 11, 2009 at 11:31 pm
try this
dbcc opentran database
go
kill spid
go
alter database database set recovery simple
go
checkpoint
go
alter database database set recovery full
go
backup database pubs to disk = 'c:\database.bak' with init
go
dbcc shrinkfile (N'database_log' , 1)
go
May 12, 2009 at 7:52 am
Hi - I know this topic is a little dead, because it was posted in February, However I am going through this same problem now as we just completed a 2008 upgrade.
We backup our database nightly and take transaction log backups every half hour. One db we have is about 5 gigs and the log file is usually about 2 - 2.5 gigs. If a large transaction occurs though, like in loading a new table or something of that nature, the log will grow an equivalent size. As this is not the normal size of the log file, it makes sense to shrink it to conserve disk space. I know some folks would love to throw more disk space at the problem, but another philosophy is to mange the files. If you're shrinking your log file every day that's a problem, but if this is an occasional thing, that makes sense to me. Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.
It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.
I would love to hear if anyone comes up with a more reasonable work around.
m
May 12, 2009 at 8:04 am
mark (5/12/2009)
Hi - I know this topic is a little dead, because it was posted in February, However I am going through this same problem now as we just completed a 2008 upgrade.We backup our database nightly and take transaction log backups every half hour. One db we have is about 5 gigs and the log file is usually about 2 - 2.5 gigs. If a large transaction occurs though, like in loading a new table or something of that nature, the log will grow an equivalent size. As this is not the normal size of the log file, it makes sense to shrink it to conserve disk space. I know some folks would love to throw more disk space at the problem, but another philosophy is to mange the files. If you're shrinking your log file every day that's a problem, but if this is an occasional thing, that makes sense to me. Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.
It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.
I would love to hear if anyone comes up with a more reasonable work around.
m
Why would you need to manage the files? This should be a dedicated LUN for the log file only - with enough space to handle all of the transactions your system runs. Repeated shrinking and growing of the transaction log will cause performance issues.
If this is not a dedicated LUN - and you want to be able to 'manage' the file, then what happens when you run your transaction that needs additional space available in the log and there is no space on the disk to grow? I can tell you, your system will come to a screeching halt and no further transactions will be possible until you clear the transaction log and/or add space.
It really does not make any sense at all to be shrinking the log file.
Now, if there is an extraordinary event that you do not anticipate happening again (ever...), then yes it is okay to shrink. Again, if this happens on a regular basis you are much better off keeping the transaction log the max size it needs (actually, make it a bit larger).
Please review the article in my signature on managing transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2009 at 8:05 am
mark (5/12/2009)
Also, I don't think that your log file should exceed your database size, which will inevitably happen if you leave it alone.
Log file should never get that large if you're managing it properly. And by managing properly I mean regular log backups if you're in full recovery. If the log's still growing larger than you want, make the backups more frequent.
It seems ridiculous that you should need to change your recovery model in order to complete this task. This is not the way that it worked in 2005 and it seems a shame that you should have to jump through hoops to do this simple task.
You don't ever have to change recovery models. If you do, especially regularly, then there's something seriously wrong. If the log grows large due to a once off operation then, after the next log backup runs you'll be able to shrink it down. Not recommended regularly, but OK as a once-off operation.
The problem with truncate-only is that people ran it without realising what they were doing and they got themselves into trouble later. At least switching revery models makes it clearer that you're discarding the records in the transaction log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2009 at 8:40 am
Gail and Jeffery, In understand and agree with much of what you have to say. Regarding frequent backups of the log file, this really does not make a difference if your largest transactions are nightly bulk loads. For us, we wind up with one big transaction log after the night run and many small backup during the day. Once the log file has grown to a certain size, it will not shrink back to a normal size, it stays there thinking it will need that much room in the future. If this is true, then great. If the next time you need that much space is 3 to 6 months from now, you're wasting space for 3 - 6 months. Maybe that philosphy will resonate with some.
thanks for sharing your opinions,
m
May 12, 2009 at 9:37 am
mark (5/12/2009)
Gail and Jeffery, In understand and agree with much of what you have to say. Regarding frequent backups of the log file, this really does not make a difference if your largest transactions are nightly bulk loads. For us, we wind up with one big transaction log after the night run and many small backup during the day. Once the log file has grown to a certain size, it will not shrink back to a normal size, it stays there thinking it will need that much room in the future. If this is true, then great. If the next time you need that much space is 3 to 6 months from now, you're wasting space for 3 - 6 months. Maybe that philosphy will resonate with some.thanks for sharing your opinions,
m
I am going to restate my position on this - how are you wasting space if that space is already dedicated to the transaction logs? If it is not dedicated storage - then you run the risk of running out of space the next time you have that process run (in 3 to 6 months).
There is nothing wrong with having a larger transaction log - and no benefits to shrinking it every 3 months because of a normal quarterly process.
The normal size of the transaction log is the size it needs to be.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2009 at 9:55 am
I'm confused.
If you have nightly loads that make the log large, how is that space wasted? You need it every night. Shrinking during the day is silly. What else do you need that space for? If you use it during the day do you free it up at night?
If you need the space, perhaps you can run more frequent log backups at night to reduce the peak.
The log size should be set to the peak size needed between log backups, whether that's daily or for the next 6 months.
May 12, 2009 at 10:03 am
The issue of a large quarterly load raises a logical question - can any design changes be made to this load to reduce peak log usage?
A very large use of log space that cannot be reduced by a log backup implies that the load is putting a very large amount of data into a single transaction.
SQL Server reserves space in the log file for the estimated impact of a rollback if the transaction fails. Therefore if you load 100 GB in a transaction, SQL Server needs a log file at least 200 GB in size. If you got to a cat's whisker of doing the commit then the transaction failed, the rollback will almost certainly take longer to complte than the original transaction.
Very large transactions also need large amounts of lock space. This can cause space to be taken from the database buffer pool to satisfy the log buffer needs, causing the load to take longer.
It may be worth you examining how your problem load works. The impact of doing a large amount in a single transaction is not just log space, you also have increased run times and increased impact of failure. Both these last points are levers that can be used to get the load redesigned to use smaller transactions, and as a side-effect use less log space.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 12, 2009 at 10:59 am
mark (5/12/2009)
Gail and Jeffery, In understand and agree with much of what you have to say. Regarding frequent backups of the log file, this really does not make a difference if your largest transactions are nightly bulk loads.
Bulk logged recovery model? Won't change the size of the log backups but, if the operations are bulk loads, it will fix the growth of the log
Once the log file has grown to a certain size, it will not shrink back to a normal size, it stays there thinking it will need that much room in the future.
That I haven't seen. If the log is mostly empty, shrink file should shrink the file, regardless of size. Only time it won't is if the log has been created very large.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2012 at 1:44 pm
If you really want to shrink a log file and it is in use with transactions at the end of the log preventing shrinking, you can do so without affecting your ability to restore and without the need to switch to simple and back to full and then take another full backup. The way to do this is as follows:
BACKUP LOG [databasename]
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' WITH COMPRESSION;
USE [databasename];
GO
DBCC SHRINKFILE (logfilename_log, 0); -- substitute size in MB set set specific size like (logfilename_log, 1024) for 1 GB log file
GO
Joshua Perry
http://www.greenarrow.net
April 27, 2012 at 6:11 am
Hi,
I was the same problem and the table sys.sysdatabases, my DB was trasaction, but it not was.
I execute the procedure sp_removedbreplication, change the recovery model to simple, shrink logfile and backup database.
April 28, 2012 at 7:54 am
Posted Monday, February 09, 2009 1:49 PM
October 13, 2012 at 8:27 am
terryj30 (2/11/2009)
try thisdbcc opentran database
go
kill spid
go
alter database database set recovery simple
go
checkpoint
go
alter database database set recovery full
go
backup database pubs to disk = 'c:\database.bak' with init
go
dbcc shrinkfile (N'database_log' , 1)
go
Haha, it still works! My logfile shrunk from 10Gb to 2Mb
Many thanks
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply