Transaction log with huge proportions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

francisco - Portugal - 28 Jun 2005 19:12 GMT

Hi all

My SQL Cluster, has a database with 13Gb, but the transaction log is huge. I
already stopped the file from gowing more then it it's but is there a way of
reduce this file?)already try to shrink the file without sucess). Can i for
instance, detach the database an start it with a new log? Or there's a way of
forcing the compression of the transaction (ldf) log.

Best regards

Signature

Francisco Batista
Portugal
f_batista69@hotmail.com

Reply to this Message

Geoff N. Hiten - 28 Jun 2005 19:34 GMT

Your problem is not cluster-specific. It happens on many SQL servers both
clustered and non-clustered. You need to manage your transaction log.
First, read books-online and learn about SQL Backup and Recovery. You need
to decide if you need point-in-time recovery or will a daily/weekly recovery
work. If you want point in time recovery, you will need to set up a regular
log backup process, otherwise you can set your database to SIMPLE recovery.
Once you have the log settings managed correctly, you can use the DBCC
SHRINKFILE command to reduce the log down to a normal size. You will need
to determine the optimum log size for your specific application. Again,
read the chapter on Backing Up and Restoring Databases under Administering
SQL Server in BOL for a complete discussion of all the concepts involved.

Geoff N.Hiten
Microsoft SQL Server MVP

> Hi all
>
[quoted text clipped - 9 lines]
>
> Best regards

Reply to this Message

francisco - Portugal - 28 Jun 2005 21:42 GMT

I konw that i lack some skills, i'm still taking microsoft courses. But this
is an emergency, the problem is that i don't have available disk to run a
backup, do you have any suggestion?

Please.

Signature

Francisco Batista
Portugal
f_batista69@hotmail.com

> Your problem is not cluster-specific. It happens on many SQL servers both
> clustered and non-clustered. You need to manage your transaction log.
[quoted text clipped - 24 lines]
> >
> > Best regards

Reply to this Message

Geoff N. Hiten - 28 Jun 2005 21:58 GMT

Set your database to SIMPLE Recovery (Enterprise manager, Right click on
database name .. Properties | Options tab.)
From Query Analyzer

Use <myDatabaseName>
DBCC SHRINKFILE (<LogicalLogFileName>,<desiredSize>)

You may have to run the command a couple of times.

See BOL for syntax and warnings. And read that chapter I mentioned. Twice.
Soon.

Geoff N. Hiten
Microsoft SQL Server MVP

>I konw that i lack some skills, i'm still taking microsoft courses. But
>this
[quoted text clipped - 41 lines]
>> >
>> > Best regards

Reply to this Message

francisco - Portugal - 29 Jun 2005 02:10 GMT

Hi

Thank you for pointing me in the right direction i used

DMS DBCC SHRINKFILE (DMS_Log,5000)
GO

I have now a log file of 5GB.

Once again thanks for your guidance, i will keep on studying to became a
better technician.

Regards

Signature

Francisco Batista
Portugal
f_batista69@hotmail.com

> Set your database to SIMPLE Recovery (Enterprise manager, Right click on
> database name .. Properties | Options tab.)
[quoted text clipped - 56 lines]
> >> >
> >> > Best regards

Reply to this Message