LDF file growing and taking up way too much space

[brettz]

The LDF file for my database is LDF file growing and taking up way too much space! I was told by one DBA that the LDF file should automatically be deleted when a full backup occurs? Is there a way to do this? If not, how can I shrink or delete the LDF file without doing any damage to my database?

My current database size is only 2756 MB, however the LDF file for this database is a monsterous 2.06 GB!!! Something has to be wrong or there must be some setting to eliminate all of this excess.

I appreciate any help/suggestions you may offer in advance.

Thanks,

-Brett





The LDF file is your log file. if you do a detach of the DB and then re-attach it, it will use the LDF that is there. If you detach the DB, and delete the associated LDF, re-attaching the DB will then create a new "EMPTY" LDF. if you have backed up your database before the detach, there is no problem on deleting the LDF.

 

[tlbroadbent]

Your DBA is incorrect. THe LDF is not deleted when backup occurs. The DBA needs to SHRINK the LOG FILE. Read faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher."

By the way, 2756 MB = 2.756 GB so the transaction log is actaully less "monstrous" than the database. A transaction log can actually grow larger than the database under some circumstances so the situation is not unusual. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[brettz]

Terry:

I tried your suggestion by executing the DBCC SHRINKFILE
transaction as mentioned on the MS site. However, this did not shrink the size of my .LDF log file at all!

Do you have any other suggestions. I did note your comment on detaching the database. Is this for .TRN and .LDF files:

"Quickly Shrinking the Transaction Log

http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Note: the article above suggests using sp_detach_db and sp_attach_single_file_db to quickly shrink the log file. However, if a database has more than one physical log, this technique should not be used. "

Do you recommend doing this since I only have one .LDF file for the database. Or do you have another recommendation for shrinking the log file.

My goal here is to free up space on the disc where the database is located.

Thanks in advance.

-Brett

 

[brettz]

Terry!

I just looked at the .LDF files and now they seem to be smaller. It must take some time for this to take effect because I had refreshed the directory about 20 minutes after performing the transaction and it was the same size. Now I checked after lunch and it had shrunk! How would you explain this?

-Brett

 

[SQLBill]

Brett,

Have you looked at FAQ183-345? The title is something like "Why isn't my transaction log shrinking?".

-SQLBill

 

[tlbroadbent]

Did you backup or truncate the log before running DBCC SHRINKFILE? You cannot shrink a file if the space is being used.

I seldom have need now to use detach and attach to get rid of a large log file. However, this quick process will work when you have a single LDF. Make sure the database is backed up immediately following the process.

Another quick method would be to BACKUP the Transaction log with TRUNCATE_ONLY and then run DBCC SHRINKFILE. This doesn't make the DB unavailable as detaching it will. The same caution applies - do a full backup immediately after truncating the log.

I recommended the FAQ to provide an overall understanding of the process. This will help avoid the common errors of constantly truncating the log or attempting to shrink files when they can't be shrunk due to being in use. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Guest_imported]

Thanks Terry!

You asked: "Did you backup or truncate the log before running DBCC SHRINKFILE? You cannot shrink a file if the space is being used." Are you asking if I backed up the .TRN or the .LDF log file here? I am aware of the TRUNCATEONLY option for the DBCC SHRINKFILE transaction for the .LDF file I am trying to shring...is this what you mean by truncating the log first?


Let me recap to make sure I do this right:

Step 1) I will perform a backup of my .TRN log using TRUNCATE_ONLY. (I will have to find the syntax b/c I have never done this except trought the UI and the option is not available)

Step 2) I will run the DBCC SHRINKFILE transaction (Should I use the option to TRUNCATEONLY here or should i specify a file size? I couldn't tell by reading the MS documentation)

When you said, "The same caution applies - do a full backup immediately after truncating the log" I don't know whether this means to do the full backup (.BAK) of the database after backing up the the Transaction log (.TRN file) with TRUNCATE_ONLY or after doing the DBCC SHRINKDATABASE with the TRUNCATEONLY option?

I would be greatly indebted if you would clarify.

Thank you,

Brett

 

[tlbroadbent]

The syntax is quite simple. Please refer to SQL BOL for more information. You should be able to accomplish what you need with the following statements. I reiterate - do a full backup when this completes.

BACKUP LOG databasename WITH TRUNCATE_ONLY

DBCC SHRINKFILE(2,0) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[brettz]

Dear Terry:

Well, as I was in the middle trying some of your suggestions on some smaller databases for a test, a company dba came by to tell me he had a stored procedure to shrink databases. The stored procedure was run on a database for test purposes. Here is the command we issued: sp_shrinklog_now 'database_name' For some reason the stored procedure ended up shrinking the log files for all of the databases on that server instead of simply the one designated in the command. Really strange? Here is the stored procedure. Let me know if you have any advice on why this would shrink all the databases on the server. (As you suggested, we did a complete backup of the databases afterward):

If objectproperty (object_id('Sp_Shrinklog_Now'),'isProcedure') = 1 drop procedure Sp_Shrinklog_Now
GO
Create Procedure Sp_Shrinklog_Now @databaseName sysname, @duration Int = 60
As
Begin
Declare @Sql Varchar(8000)
Declare @IsBulkCopy Int, @IsTruncLog Int, @i int, @Sec Varchar(10), @SecAvantFinale Varchar(10)

Set @Sec = Str(@Duration)
Set @SecAvantFinale = Str(Case When @Duration > 10 Then @Duration - 10 Else 0 End)
Set @IsTruncLog = Databaseproperty (@databaseName, 'IsTruncLog')
If @IsTruncLog = 0 Execute sp_dboption @databaseName, 'trunc. log on chkpt.', true

Select @Sql =
'
Use '+@databaseName+'
Set nocount on
Declare @Fileid varchar (10), @db sysname, @i int, @d datetime
Set @db = db_name()
select @FileId = Str(FileId) from sysfiles where fileproperty(name, ''IsLogFile'')=1
checkpoint
Print ''Attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')

if objectproperty (object_id(''PseudoActivity''),''istable'') = 1 drop table dbo.PseudoActivity
Create table dbo.PseudoActivity (T char(1000), C char(1000))
set @i = 0
Set @d = getdate()
while (1=1)
Begin
If datediff (ss, @d, getdate()) < '+@SecAvantFinale+'
insert into dbo.PseudoActivity (t, c)
select top 1000 TABLE_NAME ,COLUMN_NAME
from Information_schema.columns
Else
insert into dbo.PseudoActivity (t, c)
select top 50 TABLE_NAME ,COLUMN_NAME
from Information_schema.columns

delete from PseudoActivity
Waitfor delay ''00:00:01'' -- wait a sec
set @i = @i + 1
If @i % 5 = 0
begin
Print ''Attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')
checkpoint
backup log @db with truncate_only
end
If datediff (ss, @d, getdate()) > '+@Sec+' Break -- If duration exceeded ?, stop
End
Print ''Last attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')
checkpoint
backup log @db with truncate_only
drop table dbo.PseudoActivity
'
Execute (@Sql)
If @@Error > 0 Print @Sql

If @IsTruncLog = 0 Execute sp_dboption @databaseName, 'trunc. log on chkpt.', False
Print 'Do a total backup on '+@databaseName+' !! Partials backups won''t be good until it is done '

End

 

[tlbroadbent]

There is nothing in the SP that would cause it to shrink the logs of all databases. Something else caused that to happen.

Glad you weere able to solve the problem. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.