SQL Log Files

Hi All,

I have a small database with a 90GB log file, it had obviously not had any maintenance on it for some time.

My question is how to reduce the size of this logfile, I have tried backing up database and transaction logs with truncate and running DBCC shrinkfile but this has not had any effect.

I have changed the recovery model to simple (as I don't see the point of it being on full if the logs are not backed up)  does this negate the need for the log-file.

I want to avoid detaching the database and creating a new logfile if possible.

Thanks

techmissAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to shrink the file. with sql 2005 you use the DBCC SHRINKFILE; with with 2008, the DBCC SHRINKDATABASE command

http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

aka:
http://technet.microsoft.com/en-us/library/ms190757.aspx
0
BrandonGalderisiCommented:
It does not negate the need for the log file.  the log file must be adequately sized to handle what occurs between checkpoints.  Generally speaking this is the size of the largest transaction that will run.  Now that you have set the database to simple recovery mode, you should be able to shrink it to 1GB which should be sufficient.
0
Scott PletcherSenior DBACommented:
You don't want to shrink the whole database, just the log.  Shrinking a db can cause fragmentation, hurting db performance, so you should really (almost) *never* use SHRINKDATABASE.

Therefore, I think you should use DBCC SHRINKFILE in SQL 2008 also.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

techmissAuthor Commented:
Hi guys, thanks for your replies.

I have performed the DBCC shrinkfile operation on my database, however the log file is still huge as you can see from the attached picture it has 99% free space but won't let it go.

In addition on the properties of the database log file it's initial size is 86234MB (I know!) can it be shrunk below this size?

I have the database in simple recovery mode at the moment to stop it increasing.

Thanks

   Log File Free Space
0
BrandonGalderisiCommented:
What happens if you select "reorganize pages before releasing unused space" and set the T log to 1024 or something reasonable?
0
techmissAuthor Commented:
It flickers but stays the same size, I've managed to trim about a GB off it but it won't go any smaller.

K
0
Anthony PerkinsCommented:
Make sure you have a good backup and then execute the following:
DBCC SHRINKFILE ('YourTransactionLogFileNameGoesHere', TRUNCATEONLY)

Note that I stated TRUNCATEONLY and not TRUNCATE_ONLY (this last is no longer supported in SQL Server 2008)
0
Anthony PerkinsCommented:
Once you have done that you can modify the size to a more appropriate size using something like this:
ALTER DATABASE YourDatabaseName MODIFY FILE (NAME 'YourTransactionLogFileNameGoesHere', SIZE 1000MB)   -- Change as appropriate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
techmissAuthor Commented:
I'll give it a try, just restoring the DB to a test box to have a play...
0
techmissAuthor Commented:
When I run the DBCC SHRINKFILE command I get the following:

Does this mean that it cannot go lower than it's minimum size?

Also when I run the ALTER DATABASE command as:
------
ALTER DATABASE CRM-Database MODIFY FILE (NAME 'CRM-Database_log.ldf', SIZE 1000MB)  
-----

I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.

Is this because I have a hyphen in my DB name?

Cheers



DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
5      2           70          70          64          64

Open in new window

0
techmissAuthor Commented:
Ignore that, had a play around with thr syntax and got it as
ALTER DATABASE "CRM-Database" MODIFY FILE (NAME = 'CRM-Database_log', SIZE = 1000MB)  

but it still won't reduce smaller than 70 (I assume thats pages?)

K
0
techmissAuthor Commented:
Yes It can and it has..sorry bad day!

Cheers

K
0
Scott PletcherSenior DBACommented:
Sorry, I'm very busy, get back when I can for very brief "break" moments.

The SQL Server "standard" is to use square brackets around the names, like so:

ALTER DATABASE [CRM-Database] MODIFY FILE (NAME = [CRM-Database_log], SIZE = 1000MB)
0
techmissAuthor Commented:
Thanks, appreciate your help.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.