• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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

0
techmiss
Asked:
techmiss
  • 7
  • 2
  • 2
  • +2
1 Solution
 
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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now