0

I test bulk insert daily on my table for an ongoing project, the bulk files are around 1200000 records per day. The transaction log keeps on increasing. I shirked my transaction log once which was of 9 gb, no backup has yet been taken. Is it wise to shrink the transaction log like this. Can I shrink the log after a full backup or need to back up the log separately? Thanks

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
Kai
  • 352
  • 1
  • 16
  • This question appears to be off-topic because it is about SQL Server administration. – Andrew Barber Apr 08 '14 at 13:21
  • Your use case suggests you should *not* shrink your transaction log at all. You are consistently needing it to grow to that size. – Andrew Barber Apr 08 '14 at 13:22
  • Pretty new to stackoverflow so didn't know about admin tag, have added it. So, i do not shrink the log even after taking a full backup? – Kai Apr 08 '14 at 13:31
  • 1
    No no; don't just add a tag. This question isn't on topic for the whole site. We're for programming, not administration. And the tag you added isn't what you think it is; It's about programming in a tool called MySQL Administrator; not even related to SQL Server. This kind of question can be asked on [dba.se], **but**, you'll want to read their Help Center before posting anything there. Finally, yes; you shouldn't shrink the log file in your case. You are using that space every day, and it's expensive for SQL to have to increase the size back each day. – Andrew Barber Apr 08 '14 at 13:35
  • Ok i got your point I will repost this in Database Administrators. Thanks for your help! – Kai Apr 08 '14 at 13:37
  • **NO!** Do not repost this there. Please read my comment much more carefully. – Andrew Barber Apr 08 '14 at 13:38
  • "you'll want to read their Help Center before posting anything there" Yes sir, already looking for similar questions there and wont post until necessary and before reading. :) – Kai Apr 08 '14 at 13:40
  • Excellent; *Whew*! :) – Andrew Barber Apr 08 '14 at 13:41
  • One silly question, how do i close this post or someone else will? – Kai Apr 08 '14 at 13:42
  • It's closed already, and will be deleted automatically soon enough. – Andrew Barber Apr 08 '14 at 13:42

2 Answers2

2

Here is great link, this is a summary of the info:

How do you clear the SQL Server transaction log?

Some things you don't want to do

  • Back up the log with TRUNCATE_ONLY option and then SHRINKFILE. For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new, full backup.

  • Detach the database, delete the log file, and re-attach. I can't emphasize how dangerous this can be. Your database may not come back up, it may come up as suspect, you may have to revert to a backup (if you have one), etc. etc.

  • Use the "shrink database" option. DBCC SHRINKDATABASE and the maintenance plan option to do the same are bad ideas, especially if you really only need to resolve a log problem issue. Target the file you want to adjust and adjust it independently, using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE (examples above).

  • Shrink the log file to 1 MB. This looks tempting because, hey, SQL Server will let me do it in certain scenarios, and look at all the space it frees! Unless your database is read only (and it is, you should mark it as such using ALTER DATABASE), this will absolutely just lead to many unnecessary growth events, as the log has to accommodate current transactions regardless of the recovery model. What is the point of freeing up that space temporarily, just so SQL Server can take it back slowly and painfully?

  • Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

How do you clear the SQL Server transaction log?

Community
  • 1
  • 1
T McKeown
  • 12,971
  • 1
  • 25
  • 32
0

Back up the transaction log (I assume the database is in full recovery) and then shrink file

DBCC SHRINKFILE (2, x)

(x is target size in MB)

Make sure to leave some free space in the file to avoid autogrowths.

You can also switch to simple recovery before shrinking if this didn't work as expected, and then switch back to full.

If the log continues to grow, check 

select log_reuse_wait_desc from sys.databases

for the database in question.

dean
  • 9,960
  • 2
  • 25
  • 26