transaction log management

[NickyJay]

Hi,

New to SQL Server and just found out that one of our databases has a 20gb transaction log!!!! This was the initial size created with the database (obviously someone doesn't know their ,,,).

Anyway, I've tried TASKS - SHRINK LOG FILE and setting this to reorganise and go to 10gb (thought I'd try it incrementally to be safe) and it doesn't do anything. Can anybody suggest how to get the log file down and KEEP IT DOWN??

Thanks!

 

[mrdenny]

How much data is in the transcation log file?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[NickyJay]

suspect not a lot - the initial size of the log was set to 19936 mb and it's now at 20410.

 

[TheSQL]

In SQL Enterprise Manager, right click on the database > All Tasks > Shrink Database. Click on Files..., and select the log file. It'll tell you it's current size and how much space is used. You can use dbcc shrinkfile to get the size of the log file down.

For SQL 2000:

http://support.microsoft.com/kb/272318

 

[NickyJay]

I tried the tasks - shrink database route, and I put the size that I was going to "step down" to (10gb just to try and see if it worked) but it did nothing! I'm not too sure of the DBCC SHRINKFILE syntax as looked that up on the web (that's why I went for the easy "tasks" route) ;0)

Just tried dbcc shrinkfile and with database online it says
"Cannot shrink log file 2 (NALDClipsDB_Log) because all logical log files are in use"

and when it's offline it can't use the database?

Am I missing something?

Thanks!

 

[TheSQL]

You need to stop whatever application is accessing the database from connecting to it. Then issue the "BACKUP LOG MyDB WITH TRUNCATE_ONLY" command...that will set a checkpoint in the log, after which DBCC shrinkfile should shrink the log for you as expected.

I find that the GUI works less than half the time - I would imagine that it's issuing DBCC SHRINKFILE without setting a checkpoint first.