June 8, 2009 at 2:24 am
Hi,
The transaction log backup of the user databse has taken the almost disk space(size 122GB). The datbase recovery model is set to full. What is the best and safe way to get solve this problem?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
June 8, 2009 at 2:30 am
Shrink the file.
--
--Backup log
BACKUP LOG [DB_TEST] TO DISK = N'C:\BackupLS\DB_TEST_log_022709.trn' WITH NOFORMAT, NOINIT, NAME = N'DB_TEST-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
--If needed issue checkpoint (read BOL)
checkpoint
--Shrink file
dbcc shrinkfile (DB_TEST_log,target_size)
--
Tanx 😀
June 8, 2009 at 2:46 am
Sumanta Roy (6/8/2009)
Hi,The transaction log backup of the user databse has taken the almost disk space(size 122GB). The datbase recovery model is set to full. What is the best and safe way to get solve this problem?
Dont Shrink the file first, this is bad advice.
Are you perfoming t-log backups?
if not you should be as this will mark space in the log for re-use.
June 8, 2009 at 2:50 am
Just to add on Erwin's response. In order to avoid this problem in the future you should consider doing one of those options:
1) Modify the database's recovery model to simple
2) Leave the database's recovery model in full recover, but create a job that backups the database's log.
I recommend that you'll read in BOL about recovery models, so you'll know which option to choose.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 8, 2009 at 2:53 am
steveb (6/8/2009)
Sumanta Roy (6/8/2009)
Hi,The transaction log backup of the user databse has taken the almost disk space(size 122GB). The datbase recovery model is set to full. What is the best and safe way to get solve this problem?
Dont Shrink the file first, this is bad advice.
Are you perfoming t-log backups?
if not you should be as this will mark space in the log for re-use.
Actually at this point he should shrink the log file. The log file is currently too big and takes a lot of space because it was not backed up and it was in full recovery model. There is no reason to let the log stay at that size.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 8, 2009 at 5:16 am
1) Modify the database's recovery model to simple
2) Leave the database's recovery model in full recover, but create a job that backups the database's log.
I will suggest the second option as none wants to afford data loss during a disaster.
June 8, 2009 at 6:38 am
thanks for the reply.
So I can take the transaction log backup with the option truncate and shrink the log file after that. Can I shrink the file to the minimum like 1024KB?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
June 8, 2009 at 6:51 am
start by setting the recovery model to simple for this database
and then take a full database backup. This will truncate the log file
Shrink the file to the desired size, if it doesnt shrink execute
DBCC LOGINFO
against that database and check the status column
Once the file is shrunk, if you're not concerned with point in time restores then leave the database in simple recovery mode. If you need PIT restores set the recovery back to full and take another full backup immediately then implement log backups going forward
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 8, 2009 at 6:53 am
Once you take a log backup, Its safe to shrink the log file
Yes you can shrink the file to a fixed size
DBCC SHRINKFILE('filename',SizeInMb)
eg DBCC SHRINKFILE('prodLog',1024)
For future, monitor the log file growth and plan for frequent log backups accordingly. You can configure alerts also for taking log backups automatically when log file fills say 90 %
June 8, 2009 at 6:58 am
Sumanta Roy (6/8/2009)
thanks for the reply.So I can take the transaction log backup with the option truncate and shrink the log file after that. Can I shrink the file to the minimum like 1024KB?
I would not shrink it right down to 1MB, it will likely just have to grow again. You need an estimate of how much log space is required to support normal database activity. If you have no other reference shrink the log to 20% of data file size as a starting point and then monitor its use to see if that is a good size. Also set a sensible growth factor for the file (NOT a percentage of 1mb)
---------------------------------------------------------------------
June 8, 2009 at 7:47 am
I'm just a bit confused by the situation. Did you have the database in full recovery but you were NOT running log backups? That could explain the excessive size of the log and most of the advice given here, especially Barry's, is the way to go.
However, did you already have log backups running and the log file grew to a large size despite the backups? In that case, you have a different problem. Simply backing up & shrinking the log will not solve the problem. In fact, it will make the problem worse. If this is the case, you'll need to identify what caused the log to grow, because it will cause it to grow again. Just shrinking the log and turning away is setting yourself up for a possibly bigger problem next time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply