I just was reading about Auto Shrink and it appears that I will have to set this database option if I want the transaction log to shrink since I am using transaction log backups. This may explain why my transaction log grew so tremendously when I was rebuilding indexes.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
I think I have to set the database to Auto Shrink 1
- Thread starter bessebo
- Start date
mrdenny
Programmer
- May 27, 2002
- 11,595
When ever indexes are rebuilt the transaction log will grow dramatically (especially for large tables). If you do this on a regular basis don't bother setting the auto shrink. If you do you'll just be adding additional load to the disks, processors and SQL Server for no reason as it will be growing the log again the next time the indexes are rebuilt.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
(Not quite so old any more.)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
- Thread starter
- #3
Denny,
I am noticing that my transaction log (the used amount) keeps growing even though I perform transaction log backups every 15 minutes. In SQLServer 7 the used portion would go down after each time the log backup occurs. Now, in SQL 2000, it looks like I have to perform a DBCC SHRINKFILE('log file','amount to shrink to') after each transaction log backup if I want the transaction log to go down. Is this the typical practice?
I am noticing that my transaction log (the used amount) keeps growing even though I perform transaction log backups every 15 minutes. In SQLServer 7 the used portion would go down after each time the log backup occurs. Now, in SQL 2000, it looks like I have to perform a DBCC SHRINKFILE('log file','amount to shrink to') after each transaction log backup if I want the transaction log to go down. Is this the typical practice?
mrdenny
Programmer
- May 27, 2002
- 11,595
Odds are you had the AutoShrink setting enabled on the SQL 7 server. This option probably isn't set on the SQL 2000 server. If you shrink the file after each transaction log backup you will end up with a heavly fragmented disk, and additional load being placed on the server which will end up serving no purpose. Best practice is to have the log be a little bit larger than it will need to be, and leave the log at that size so that the server isn't constently growing and shrinking the file on the disk which will cause file fragmentation.
If you want to have this happen you can enable the auto shrink option on the database.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
If you want to have this happen you can enable the auto shrink option on the database.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
- Thread starter
- #5
Denny,
It appears that the only thing that will decreas the size of the log is a Full Backup and then performing a backup log with INIT right afterwards. I ended up allocating a lot of space for the transaction log and letting it grow. Then after each full backup I will clear it out. Thanks for your help...
It appears that the only thing that will decreas the size of the log is a Full Backup and then performing a backup log with INIT right afterwards. I ended up allocating a lot of space for the transaction log and letting it grow. Then after each full backup I will clear it out. Thanks for your help...
-
1
- #6
First, you can only shrink the data and log file down to their original size or the size of the data in them (whichever is larger).
Second, even backing up the transaction logs won't immediately shrink the log files. It can take a while. It's all based on where the active portion of the file is.
Refer to the BOL for more information on shrinking databases and the active portion of the log.
-SQLBill
Posting advice: FAQ481-4875
Second, even backing up the transaction logs won't immediately shrink the log files. It can take a while. It's all based on where the active portion of the file is.
Refer to the BOL for more information on shrinking databases and the active portion of the log.
-SQLBill
Posting advice: FAQ481-4875
- Thread starter
- #7
SQLBill,
This is really strange. Every night I perform a full backup then immediately afterwards (if successful) I perform a Backup Log with INIT. In SQL7 this has always reduced the USED amount of the Transaction Log down to an insignificant amount. This morning I am noticing that the used portion of the transaction log has not been reduced.
My philosophy is to set a limit on the transaction log size so that it will never grow to the point of consuming the whole disk drive. Should I be concerned that the used portion of the transaction log has not been reduced after a full backup and a backup log with init? I am almost thinking that this may be an Enterprise Manager issue of not refreshing in SQl 2000 (although the used amount of the log is growing very slightly so it is changing). I read somewhere that implementing service pack 4 may help with some issues related to EM. Any comments about the fact that the used portion of my transaction log has not reduced from yesterday (our first day with SQl 2000)? Thanks for all of your help.
One other thing Bill, what do you think of the PASS conventions? Have you been? I am contemplating going in September 27-30 in Texas...
This is really strange. Every night I perform a full backup then immediately afterwards (if successful) I perform a Backup Log with INIT. In SQL7 this has always reduced the USED amount of the Transaction Log down to an insignificant amount. This morning I am noticing that the used portion of the transaction log has not been reduced.
My philosophy is to set a limit on the transaction log size so that it will never grow to the point of consuming the whole disk drive. Should I be concerned that the used portion of the transaction log has not been reduced after a full backup and a backup log with init? I am almost thinking that this may be an Enterprise Manager issue of not refreshing in SQl 2000 (although the used amount of the log is growing very slightly so it is changing). I read somewhere that implementing service pack 4 may help with some issues related to EM. Any comments about the fact that the used portion of my transaction log has not reduced from yesterday (our first day with SQl 2000)? Thanks for all of your help.
One other thing Bill, what do you think of the PASS conventions? Have you been? I am contemplating going in September 27-30 in Texas...
Enterprise Manager does not always show the most accurate data. It requires either a refresh, or the running of DBCC UPDATEUSAGE. Whenever I shrink the database or do an index defrag, I always run DBCC UPDATEUSAGE afterwards.
Don't be concerned that the log hasn't shrunk....it all deals with where the active portion of the log is. It can't shrink that portion.
LOG:
..........xxxxxxxxxx
unused active
The above can't be shrunk since the active portion is at the end of the file.
Log:
xxxxxx............
active unused
The above can be shrunk since the active portion is at the beginning of the file.
Refer to the BOL, use the Index tab, enter Transaction Log. Click on the entry for Architecture, select the entry for Shrinking the Transaction Log.
PASS2005- never been to one, but I'm going to this one.
-SQLBill
Posting advice: FAQ481-4875
Don't be concerned that the log hasn't shrunk....it all deals with where the active portion of the log is. It can't shrink that portion.
LOG:
..........xxxxxxxxxx
unused active
The above can't be shrunk since the active portion is at the end of the file.
Log:
xxxxxx............
active unused
The above can be shrunk since the active portion is at the beginning of the file.
Refer to the BOL, use the Index tab, enter Transaction Log. Click on the entry for Architecture, select the entry for Shrinking the Transaction Log.
PASS2005- never been to one, but I'm going to this one.
-SQLBill
Posting advice: FAQ481-4875
- Thread starter
- #9
Understood Bill. Makes sense. Ya know I don't care so much about shrinking the transaction log, since I like to keep a finite value in there for its size and not allow it to go past that size. I am more concerned about reducing the Used portion of the transaction log (if that makes sense). I will take your advice and research more in BOL. Are you running SP4 and do you recommend it? I am just starting to research today what fixes are in there.
Nope, not running it and I can't yet.
We mainly use our SQL Server for a specific vendor application and they don't support their product on SQL Server SP4 yet.
If your database(s) are the result of a specific vendor product, alway check with the vendor before upgrading. It might not be supported.
-SQLBill
Posting advice: FAQ481-4875
We mainly use our SQL Server for a specific vendor application and they don't support their product on SQL Server SP4 yet.
If your database(s) are the result of a specific vendor product, alway check with the vendor before upgrading. It might not be supported.
-SQLBill
Posting advice: FAQ481-4875
mrdenny
Programmer
- May 27, 2002
- 11,595
I'm currently in the process of deploying SP4 to our SQL Servers. We've deployed to our dev servers at this point. Next week we are installed on QA, and 2 weeks later we are putting it on Production. So far (knock on wood) it's working fine.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
- Thread starter
- #12
Denny,
Can you enlighten me as to fixes/enhancements with SP4?
SQLBill,
The vendor of our ERP system is so far behind that they usually say we will help you out if you have problems but make sure you have it running in a Test environment before upgrading. That is their typical tag line...
Thanks
Can you enlighten me as to fixes/enhancements with SP4?
SQLBill,
The vendor of our ERP system is so far behind that they usually say we will help you out if you have problems but make sure you have it running in a Test environment before upgrading. That is their typical tag line...
Thanks
mrdenny
Programmer
- May 27, 2002
- 11,595
Here is the list of new bugs which are fixed in SP4 Keep in mind if you have over 4 GB of RAM you will need to install this after installing SP4.
This also includes all the fixes from SP1, SP2, SP3 and all hot fixes in between.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
This also includes all the fixes from SP1, SP2, SP3 and all hot fixes in between.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
- Thread starter
- #14
mrdenny
Programmer
- May 27, 2002
- 11,595
I've never been to PASS.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(Not quite so old any more.)
sqlbill, denny and bessebo
It was very interesting to read the conversation. I am facing the same problem. I am a newbie and did not realize to control the transaction log. So our company server went down because the disk got full.
My network adminstrator under time constraints deleted all of the log on the server and restored the server for the last weeks restore point (we back up weekly). Since then i am having a lot of problems with DTS. When ever i run DTS, it says that there is no space for the transaction log backup the transaction log or increase the disk space and the DTS fails.
This are the steps i was considering to correct the problem in future.
1. Use DBCC shrinkfile command.
2. Check the shrink transaction log button.
3. Limit the auto grow size to certain MB's of space.
Can you guys help me on this. I want to know how to delete the existing transaction log to free up the space. The size of the datafiles is 100MB i was planning to allocate 40MB of space for transaction logs. What are the best practice steps for keeping control of transaction logs.
Thanks
Dwight
It was very interesting to read the conversation. I am facing the same problem. I am a newbie and did not realize to control the transaction log. So our company server went down because the disk got full.
My network adminstrator under time constraints deleted all of the log on the server and restored the server for the last weeks restore point (we back up weekly). Since then i am having a lot of problems with DTS. When ever i run DTS, it says that there is no space for the transaction log backup the transaction log or increase the disk space and the DTS fails.
This are the steps i was considering to correct the problem in future.
1. Use DBCC shrinkfile command.
2. Check the shrink transaction log button.
3. Limit the auto grow size to certain MB's of space.
Can you guys help me on this. I want to know how to delete the existing transaction log to free up the space. The size of the datafiles is 100MB i was planning to allocate 40MB of space for transaction logs. What are the best practice steps for keeping control of transaction logs.
Thanks
Dwight
CONT....i was trying different options to get it work and i tried detach and attach database. After i did that i am not able to connect to any of my database infact the console tree does not open. Gives me an error
A Connection could not be estatblished to server.
Reason: Cannot open user defined database.Log in failed.
Because the database i tried to attach and detach was my default database. Where did i go wrong in attaching db and how to set it right.
THANKS FOR YOUR HELP. THIS WOULD MAKE MY DAY.
Dwight
A Connection could not be estatblished to server.
Reason: Cannot open user defined database.Log in failed.
Because the database i tried to attach and detach was my default database. Where did i go wrong in attaching db and how to set it right.
THANKS FOR YOUR HELP. THIS WOULD MAKE MY DAY.
Dwight
I don't know where you went wrong with detaching/attaching your database as you don't say how you did it.
Are there any errors? Check the SQL Server Error log and the Windows Event Viewer logs.
(BTS-you really should have started your own thread. The originator of this thread probably doesn't want to keep getting emails that there are responses to their post.)
-SQLBill
Posting advice: FAQ481-4875
Are there any errors? Check the SQL Server Error log and the Windows Event Viewer logs.
(BTS-you really should have started your own thread. The originator of this thread probably doesn't want to keep getting emails that there are responses to their post.)
-SQLBill
Posting advice: FAQ481-4875
THanks for your response SQLBill. I got the solution for the second problem. I had changed my default database to Master and it works.
My first thread about transaction logs still remains a mystery.
Copied form earlier post:
sqlbill, denny and bessebo
It was very interesting to read the conversation. I am facing the same problem. I am a newbie and did not realize to control the transaction log. So our company server went down because the disk got full.
My network adminstrator under time constraints deleted all of the log on the server and restored the server for the last weeks restore point (we back up weekly). Since then i am having a lot of problems with DTS. When ever i run DTS, it says that there is no space for the transaction log backup the transaction log or increase the disk space and the DTS fails.
This are the steps i was considering to correct the problem in future.
1. Use DBCC shrinkfile command.
2. Check the shrink transaction log button.
3. Limit the auto grow size to certain MB's of space.
Can you guys help me on this. I want to know how to delete the existing transaction log to free up the space. The size of the datafiles is 100MB i was planning to allocate 40MB of space for transaction logs. What are the best practice steps for keeping control of transaction logs.
Thanks
Dwight
My first thread about transaction logs still remains a mystery.
Copied form earlier post:
sqlbill, denny and bessebo
It was very interesting to read the conversation. I am facing the same problem. I am a newbie and did not realize to control the transaction log. So our company server went down because the disk got full.
My network adminstrator under time constraints deleted all of the log on the server and restored the server for the last weeks restore point (we back up weekly). Since then i am having a lot of problems with DTS. When ever i run DTS, it says that there is no space for the transaction log backup the transaction log or increase the disk space and the DTS fails.
This are the steps i was considering to correct the problem in future.
1. Use DBCC shrinkfile command.
2. Check the shrink transaction log button.
3. Limit the auto grow size to certain MB's of space.
Can you guys help me on this. I want to know how to delete the existing transaction log to free up the space. The size of the datafiles is 100MB i was planning to allocate 40MB of space for transaction logs. What are the best practice steps for keeping control of transaction logs.
Thanks
Dwight
how to delete the existing transaction log to free up the space
You can use the BACKUP LOG dbname WITH TRUNCATE_ONLY command. However, read the BOL for more information on this command. First do a full backup, then do the truncate, then do a full backup.
The log file can actually grow to a size LARGER than the datafiles. It all depends on what you are doing. If you defrag indexes, it can grow much larger (I believe it is recommended that you have 1.5 times the space available when defragging). While you CAN set the Maximum file size, consider the implications of doing so...if you set it too low your transactions will fail more often.
One cause of transaction failures, when you see the transaction log space error, is due to the tlog not growing fast/large enough.
Let's say your tlog is 1 MB and you set your tlog growth for 1 MB and you are trying to enter 5 MB of data. That means your tlog has to grow 5 times for all the data to be entered. Growing the files can be slightly slower than the actual transaction, so when a transaction is happening, it could be possible there isn't enough space AT THAT MOMENT. But the transaction will fail, even if in another second the growth would finish and the space would be there.
Setting the growth of the log and data files correctly is almost a science. You just have to work with it until you get the correct values.
-SQLBill
Posting advice: FAQ481-4875
You can use the BACKUP LOG dbname WITH TRUNCATE_ONLY command. However, read the BOL for more information on this command. First do a full backup, then do the truncate, then do a full backup.
The log file can actually grow to a size LARGER than the datafiles. It all depends on what you are doing. If you defrag indexes, it can grow much larger (I believe it is recommended that you have 1.5 times the space available when defragging). While you CAN set the Maximum file size, consider the implications of doing so...if you set it too low your transactions will fail more often.
One cause of transaction failures, when you see the transaction log space error, is due to the tlog not growing fast/large enough.
Let's say your tlog is 1 MB and you set your tlog growth for 1 MB and you are trying to enter 5 MB of data. That means your tlog has to grow 5 times for all the data to be entered. Growing the files can be slightly slower than the actual transaction, so when a transaction is happening, it could be possible there isn't enough space AT THAT MOMENT. But the transaction will fail, even if in another second the growth would finish and the space would be there.
Setting the growth of the log and data files correctly is almost a science. You just have to work with it until you get the correct values.
-SQLBill
Posting advice: FAQ481-4875
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question