August 12, 2009 at 4:34 am
Hi,
I have a production Database which is in Full Recovery Model, we have a daily backup [full database option], through the maintainance plan. Which is happening.
The LDF size of the Prodution is getting increased daily 2-3 GB, even though we have enough disk space the ldf size is getting increased. How to reduce the size of the LDF File of the production database, even after the full backup or transactional backup it has not reduced. Is there any way where in i can reduce the LDF Size. Please Let me know.
I found that shrinking the database file frequently is not recomended, so pls provide me a technique so that growth of the LDF is truncated and the backup both full and transaction is there.
With Regards
Dakshina Murthy
August 12, 2009 at 6:13 am
Please read through this - Managing Transaction Logs[/url]
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
August 12, 2009 at 6:54 am
Hi,
Was there any thing wrong in my question, if so please let me knew. I am new to this. So i am not getting it.
I have obsorved few things, i tried with a full backup and then i executed the commond as below to check the databasename, logsize in MB, LogSpace Used In %, status
DBCC SQLPERF (logspace)
MyDB82.42969 96.14847 0
After DB Full Backup it changed like this
MyDB82.42969 96.17157 0
Then i took a transaction backup, and now the i executed the above command and the out put is as follows
MyDB82.42969 10.49545 0
This means the logsize in MB or the log file size will not change even after the transaction log backup it will reduce the LogSpace Used In %. So to reduce the ldf file size, is it that we have to shrink the database and the databasefiles.
I heard that regularly shrinking the database and the files is not adviceable from the net through some blogs. So what shall i do to reduce the file size geting increased day by day in terms of GB even after the DB Full Backup.
Please advice
With Regards
Dakshina Murthy
August 12, 2009 at 8:04 am
dakshinamurthy (8/12/2009)
Was there any thing wrong in my question, if so please let me knew. I am new to this. So i am not getting it.
Nothing wrong with the question, it's a very common question which is why I wrote the article that I referred you to. Did you read it?
I have obsorved few things, i tried with a full backup and then i executed the commond as below to check the databasename, logsize in MB, LogSpace Used In %, status
Full backups do not affect the transaction log.
This means the logsize in MB or the log file size will not change even after the transaction log backup it will reduce the LogSpace Used In %. So to reduce the ldf file size, is it that we have to shrink the database and the databasefiles.
Log backups make space within the log available for reuse, they do not affect the size of the log file. Since the log is excessively big, you can do a once-off shrinkfile on the log file (not the data files) to get it to a reasonable size.
I heard that regularly shrinking the database and the files is not adviceable from the net through some blogs. So what shall i do to reduce the file size geting increased day by day in terms of GB even after the DB Full Backup.
Please read the article I referenced. It explains how to manage transaction logs so that they don't grow big.
If you still have questions after reading it, feel free to ask them.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply