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

SQL Transaction logs

Have 2008 R2 SQL server.  Had an issue over the weekend that caused the transaction log of one of the DB to fill up (DB is 6gb, Transaction log was 911gb!)  I configured maintenance plan thinking after the full backup, those logs would be shrunk but they haven't, my recovery mode is set to full,   I was thinking of setting to simple, shrink DB, and set back to full if that would clear the logs out.  But shouldn't maint. plan be doing that?
0
leadtheway
Asked:
leadtheway
  • 4
  • 4
1 Solution
 
Lee SavidgeCommented:
Is the maintenance plan backing the logs up as well? In full recovery you HAVE to back up the logs as well as the database.
0
 
Lee SavidgeCommented:
Oh, and don't shrink the database. Back the logs up and shrink the logs
0
 
leadthewayAuthor Commented:
yes theres a separate job for backing up the logs, but its not cleaning them,  how would I shrink the transaction log DB, ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lee SavidgeCommented:
Something like this:

BACKUP LOG MyDB TO DISK = N'X:\Backup\MyDB_log.bak'
GO
DBCC SHRINKFILE('MyDB_Log', 1)
GO

Open in new window

0
 
leadthewayAuthor Commented:
ok when i look at the datapath for the DB in question i see 2 files

DBName= primary datafile
DBName_1=Transaction Log file, so in your scenario by doing below


BACKUP LOG DBNAME TO DISK = N'X:\Backup\DBName_log.bak'
GO
DBCC SHRINKFILE('DBName_Log', 1)
GO


That would just backup the transaction log file and then shrink it right, i don't have to specify the DBNAME_1 listed above?
0
 
Lee SavidgeCommented:
My code example is telling SQL to back up the log for the database named MyDB so you give it the name of the database. SQL will get the log file name from the database properties. No you don't specify the name of the log file until you tell ShrinkFile which file you want shrinking.
0
 
leadthewayAuthor Commented:
so in this case for the dbcc Shrinkfile i would specify the name of the log file in the datapath (dbname_1)
0
 
leadthewayAuthor Commented:
i ended up doing a combination of putting in simple recovery mode and then doing shrinkfile, cleared the neccessary space
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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