Transaction Log to big! 1

[jasonsas]

Hi,

We currently have a SQL database that is only used for reporting. Our actual data is stored in another format and then sent to the SQL database each night for reporting purposes only.

The transaction log for this database is now up to 33Gb, leaving only a few Mb left on the drive it's on. As such, this is not a "critical" database and we don't need the transaction log very big. Under the properties of the database I tried restricting the size of the log to say 10Gb, but because it's already up to 33Gb, it won't shrink it. Is there anyway to make this transaction log smaller, or delete it altogether and create another so I can free up some space?

Thanks for any help!

 

[SQLBill]

Have you ever done a backup of the transaction log? (Have you ever done a full backup?)

First thing to do. Decide how much data you can afford to lose if your db crashes. If it's not important to you and you can afford to just have the last full backup, then do this:

1. Run BACKUP LOG dbname WITH TRUNCATE_ONLY
2. Run a full backup of the database.
3. In Enterprise Manager, right click on the database, select Properties, go to the Options tab and change the Recovery Mode to SIMPLE.
4. Run the DBCC SHRINKFILE command for the transaction log file.

If you can't afford to lose any data, do this:

1. Backup the log (if you have never done a full backup, do that first)
2. Run the DBCC SHRINKFILE command for the transaction log file.
3. Schedule regular backups of the transaction log

Refer to the BOL for more information on backing up the transaction logs and shrinking the log file. Also, search this site and check the FAQs also there's lots of information about shrinking the logs. (Also check the MS SQL Server Programming forum).

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[timscronin]

DBCC shrinkfile works OK, for a fast solution (if you can do this), backup db and log, detach db, move ldf to different location, attach db and let it create new ldf file. After this make sure you regularly backup and dump trans file