SQL Server Performance Forum – Threads Archive

ldf file grows bigger?

Hi guys
My ldf file occupies 125GB and left with only 11GB. What can I do so that my database performance is good?
Do I need to shirnk the file through EM or take a backup of log file and put it in other drive, what are the precautions I need to take to do such job?
Is there any way I can run a job regularly so that in future I do not face such problems. Need a quick reply, its damn urgent plsss.. Thanks!
"He laughs best who laughs last"

Hi, You may take the transaction log backup using truncate_only/ no log option. If you wish to move the ldf to a different path, then you may use sp_detach_db and sp_attach_db. It is also advised to take regular transaction log backups to keep the size of the log under control. For more information on truncating the log file, please refer the following topics in SQL BOL: Shrinking the Transaction Log
Truncating the Transaction Log Regards, Chetan.

Also review other similar posts in this forum to reduce the TLog size. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What is the recovery model for your database in question? I suspect it is "Full".
You can find this out by firing a
SELECT DATABASEPROPERTYEX(‘Pubs’,’Recovery’) in Query Analyzer.
If it is "Simple", then do
BACKUP LOG WITH TRUNCATE_ONLY
DBCC SHRINKFILE If it is "Full" then
BACKUP LOG
DBCC SHRINKFILE Here’s also an interesting link:http://www.karaszi.com/SQLServer/info_dont_shrink.asp
There is also a brute-force method available, which is definitely *NOT* the recommended course of action!!!
– Detach the DB
– Rename or delete the database’s ldf file
– Attach the DB
SQL Server will issue a warning that the log file is missing, but it will create a nice new and small log file. If you intend to use this method, make sure you have a most recent, verified backup! After this urgend isue is fixed, make sure you read through BOL, this website or other online resource about the transaction log. This is a vital part of your database, yet it seems to be some kind of mystery to many SQL Server users. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

I have used below freeware for the above job
http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp

Hi,
are you doing Bulk insertion within this db ?! are you performing regular index defragmentation in backup plan ?
if is that so then also you will find growth of t-log file.
as suggested do regular t-log back and then shrink / truncate.
Regards hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Reddy
Follow as suggested by Frank and never attempt to do such jobs from Enterprise Manager as it tend to block the server resources. It is always suggested to use QA for any admin activities. I believe Transaction log size will not have a massive negative effect on the performance, but you should address the log size by maintaining prompt backups. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks a lot for giving me so many suggestions from all of U, but I have doubt on this log files. As far as my understanding log files are used to roll back the transactions in any disaster, If we had regular backups of the database, what is the use of taking backup of log files for so many years, because we need to roll back the log files right from the time of latest database backup. Is there any use of the log files having it backed up right from the starting OR can we just delete the old log files occured before the database backup. Thanks!
"He laughs best who laughs last"

You wouldn’t never keep log files for a that long time. You need log files (along with the Full Recovery Model) to be able to recover to a most recent stable state of the database. Say for example, you do a full backup each night and transaction log backup each hour. Now, your db crashes at 12:15 PM. In that case you would restore the last full backup and then the log backups after that full backup in sequence. That way you only lose 15 minutes of work. If, for example, no disaster happened between two full backups, the log backups are not really needed and can potentially be deleted. If you are careful, you would keeep the log backup for about a week or so before you delete them. That depends on your whole backup strategy. Also, you will want to keep the backups on a separate physical medium. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

BOL explains
For example, a site executes a full database backup on Sunday night. A set of transaction log backups is made every four hours during the day, with the backups from one day overwriting the backups from the day before. Each night the site makes a differential backup. If one of the data disks for the database fails at 9:12 A.M. on Thursday, the site can: Back up the current transaction log.
Restore the database backup from Sunday night.
Restore the differential backup from Wednesday night to roll the database forward to that point.
Restore the transaction log backups from 4:00 A.M. and 8:00 A.M. to roll the database forward to 8:00 A.M.
Restore the log backup taken after the failure. This will roll the database forward to the time of the failure. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for adding the point-of-failure description. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

yeah I got you. It seems like one of our database has log files right from the begining as there was no DBA to maintain that. so I think I have to delete all the log files and set up a process as you said like every hour log backup and nightly full backup, that may work fine without any issues.
1)Deleting the log file is just delete it as we usually do deletion of files or do we need to follow any methods.
2)Do you want to prescribe me any particular methods I need to follow to perform tasks as I mentioned above. Thank you Frank n Satya ur replies are really very much valuable for me. Thanks!
"He laughs best who laughs last"

The best method is to take full backup and store in good place, then detach the database using SP_DETACH_DB and then delete .LDF file then use SP_ATTACH_SINGLE_FILE_DB in order to create afresh .LDF file for this database, for further information on above SPs refer to BOL. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks satya I’ll follow that and lets c how it runs at my end. Thanks!
"He laughs best who laughs last"

Hi
I am getting the following result when i run the command DBCC SHRINKFILE(DBlog) DbId Field Currentsize minimum size Usedpages EstimatedPages
6 2 145011416 57600 14501416 57600
What do I need to do now.How I can reduce my logsize without losing any transactions?
How can I know to what size the file has to be shrinked?
I have 3 log files for my database and how can I perform SHIRNK?
Thanks!
"He laughs best who laughs last"

"What do I need to do now.How I can reduce my logsize without losing any transactions?" You can see: minimun size, that is the minumun value you can shrink. Anyway you don’t loose transactions. "How can I know to what size the file has to be shrinked?"
EM or sp_heldb youdatabase "I have 3 log files for my database and how can I perform SHIRNK?" DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
) Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis
If I have 3 log files how can I delete ldf file and empty all the space? and also when i use the follwing command y is it taking very long time to restore. RESTORE DATABASE padot
FROM DISK = ‘F:Backuppadotpadot_db_200508152010.BAK’
WITH REPLACE
Thanks!
"He laughs best who laughs last"

The only way to delete ldf file is like Frank said before, dettach database, delete ldf, attach database (review Frank post). About "long time", well restore take all time he needs. No way to accelerate that.
May be you need 3rd party tool like Red Gate Backup.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis
I tried detacching and mess up things friend, i am unable to attach that file again and so dropped tha database and trying to restore whole database and its still runnig for more than 45min to restore.
It seems as Frank said attaching and dettaching doesnt work for 3 log files.
Can u pls pull me out from this. Thanks!
"He laughs best who laughs last"

Yes, I forget that.
IÂ´m afraid you have to wait until restore is finished. I suppose you are alone and no one is working with server.
Also look at Performance Monitor to be shure something is goin on. Check Event Viewer just in case.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi
I have 3 log files out of which 1 of them 120GB and I am trying to SHIRNK it but I was able to get only 5GB of space free by using the command DBCC SHRINKFILE (X_log,30,TRUNCATEONLY). Can I use EMPTYFILE option and trying shrinking again, what actually it does? Thanks!
"He laughs best who laughs last"

If you have setting to single user, that means there is no schedule job or other user using database.
What about to set recovery model to simple, shrink log and back to full again? Well is 23pm in my home, so I hope to go on tomorrow. IÂ´m shure others members will help you early. Good luck. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Read out KBAhttp://support.microsoft.com/?kbid=873235 for more information on this issue and follow as suggested. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

EMPTYFILE moves data (or log entries in your case) from one file to another in the same filegroup. EMPTYFILE is a necessary prerequisite before you can delete the file using ALTER DATABASE. Have you checked the link, I’ve posted above?http://www.karaszi.com/SQLServer/info_dont_shrink.asp It contains futher references back to the MS knowledgebase on log treatment. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Reddy
I guess you’re missing out some information without referring to the relevant links, I bet most of the Frank’s references will take you to the subject. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Luis
As you said I changed it from FULL to SIMPLE then performed DBCC SHRINK and again changed it to FULL. I got totally 116GB of space free. I wonder how it works, did I lose all the transactions from the log file, how does this process effect database? Thanks!
"He laughs best who laughs last"

During SIMPLE recovery model if the CHECKPOINT has been occured then all the transactions in the log will be truncated and in order to sustain any failure with database you must take full database backup that can be used if any database failure occurs. This is always applicable when you use SIMPLE recovery model. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I had a new problem with this again.
Yday I restored the database but now application developers are unable to inerts data intyo the database,is this because of shrinking ldf file. and also i see that database owner is not ‘sa’ for tha databse i restored, how can i bring that owner to ‘sa’ Thanks!
"He laughs best who laughs last"

sp_changedbowner —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hey
It looks somethig weired..I dont understand if its database problem or application side so that I can react according to that.I’ll explain all the story what has happened..pls bare with me. Yesterday I backed up database at 3.30PM and tried to shrink(not worked) then dettach the DB but was unable to attach since I had 3 log files.then I restored the DB from 3.30PM backup.
Today morning I changed the recovery model to SIMPLE–>SHRINK DB–>change model to FULL. This is all I have done with DB. But now application is unable to insert files into the database, but able to pull records upto
day-before-yday. Looks like yday’s transactions were also not there in tha database though i took backup yday evening and started working on that I shud get the files inserted into database from morning but database dont have those.
Guessing that may be due to database owner I changed it to ‘sa’, but still having tha same problem. Feel free to ask ?’s If didnt get this. Thanks for baring with me. Thanks!
"He laughs best who laughs last"

I missing something.
Before you change to simple, you did a full backup and when you try to restore that backup you have the same problem? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

No… I restored DB yday and today I changed model to SIMPLE and SHRINK it and then made it FULL model.
Now I got gud enough space and looks like no problem with DB coz i am able to do SELECT inserted files day-before-yday. Thanks!
"He laughs best who laughs last"

Also: when you dettach database with 3 logs, you try to attach with the same 3 logs?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I tried attaching in that way but its not working since I deleted the log file after dettaching. Thanks!
"He laughs best who laughs last"

Ok. I understand know.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis So…were you able to figure out what actually causing the problem, its database or application side. Thanks!
"He laughs best who laughs last"

No shure.
May be operational problem, with lack of disk space.
Anyway, try to monitor all hardware checking disk space. Review error log from time to time and event viewer. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

If you’ve deleted the log file and would like to attach the database then use SP_ATTACH_SINGLE_FILE_DB. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>