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

Transaction Log too big - Need to free some space

Hello,
I need to free up some space at our server, and I noticed that one database's transaction log is getting too big - the database is 1GB and the transaction log 7GB! I have read through the inet for soloutions like shrink, truncate, etc but i got a bit confused. So I need a more structured step approach of what I should do.

Also I want to know if truncating or shrinking the transaction log will cause loss on its data... moreover the server is lacking resources so i need to do whatever overnight (scheduled job)

Thank you
0
takis_1234
Asked:
takis_1234
  • 3
  • 2
1 Solution
 
billy21Commented:
USE MASTER
exec sp_helpdb DBNAME
BACKUP LOG DBNAME TO DISK = 'C:\DBNAME.bak' with init
DBCC SHRINKDATABASE ('DBNAME')
BACKUP LOG DBNAME TO DISK = 'C:\DBNAME_log.bak' with init
DBCC SHRINKDATABASE ('DBNAME')
exec sp_helpdb DBNAME

Repalce DBNAME with the name of your database.  Backing up the log also truncates it.  Backing up twice frees even more space for some reason.
0
 
wael_tahonCommented:
I solved this problem in the past two weeks

check
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21190334.html
0
 
takis_1234Author Commented:
if something goes wrong, will i be able to roll back to the original status of the transaction log from these backups without any loss?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
takis_1234Author Commented:
...also the sql server is preferable not to be restarted
0
 
billy21Commented:
There is no need to restart the SQL Server.  If something goes wrong you can restore the backups.  In the above example the 2 backups overwrite each other.
0
 
billy21Commented:
>In the above example the 2 backups overwrite each other

Ignore that they don't overwrite each other.  Just make sure you're using two different filenames for each of the two backups.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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