SQL2K @#@$%@ Logfiles 1

[hughed00]

How do I get the logfile to truncate? I have tried everything (except obviously the right thing).

Actually, everything short of writing a query to do it, which I have not attempted since every gui way seems to fail so my faith in writing the query is low.

I do daily FULL backups, I only have one database, one instance and the stupid logfile is 14gig and has run out of space causing all jobs to fail.

What stupid thing am I not doing? I seem to remember that SQL6 full backups also truncated the log??

I even did a backup of the transaction log selecting the options to have the transactions that have already been written be removed.

PLEASE set me straight. I have month end reports due and can't load my data.

Thanks!

 

[tlbroadbent]

In SQL 7 and 2000, the log will be truncated when backups are done. However, the file will not necessarily shrink. You can run DBCC SHRINKDATABASE and DBCC SHRINKFILE. I suggest reading these articles. The firs one explains how to immediately shrink the log. The others will help you maintain a small log.

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

faq183-345

Microsoft SQL 7 article

http://support.microsoft.com/support/kb/articles/Q256/6/50.ASP

Microsoft SQL 2000 articles

http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1uzr.asp

http://support.microsoft.com/support/kb/articles/Q272/3/18.ASP

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[hughed00]

Thanks, I have "shrunk" file, but it seems to get no smaller. I will give these a read. Thanks again.

 

[hughed00]

Well, it worked fine. Still thought this was part of the backup procedure. I guess it got a bit more complicated as more management features were added.

Will add this so I can keep log in check.

Thanks VERY much,

-- Dave