0

I am confused about SQL Server t-Log file size growth (.ldf file). I analyzed various blogs/topics by DBA's some recommending Log file Shrink while some advised not to do so. Here is what I used to follow:

  1. Take Database in Full mode if its not already in it.

  2. Shrink Log files if not enough memory freed then move to next step.

  3. De-Attach Database. (sometimes it went in Single Mode and no matter what deadlock process I kill , it will never come in multi-user mode again. BIG PROBLEM!!! )
  4. move .ldf file to other location and Restore mdf file only.

This was not a recommended technique so I surfed to following script i.e. reducing Virtual Log File (VLF) to free unused space:

USE dbname
BACKUP LOG dbname TO DISK = 'C:\x\dbname.trn'
--First param below is fileno for log file, often 2. Check with sys.database_files
--Second is desired size, in MB.
  DBCC SHRINKFILE(2, 500)
  DBCC SQLPERF(LOGSPACE) --Optional
  DBCC LOGINFO --Optional 
  Now repeate above commands as many times as needed!

I am not sure if is the VLF script professionally recommended to try at Production Environment where Down time cannot be afforded. Please advise how should I move forward.

TT.
  • 15,428
  • 6
  • 44
  • 84
Baseer Ul Hassan
  • 27
  • 1
  • 13
  • Why do you think the log file needs to be shrunk? Is this a one time thing or something you are having to do regularly? – SQLChao Sep 15 '17 at 19:37
  • Actually I come to situation on and off at various SQL Servers where LOG file size increases too much and The Disk Space is almost 99% consumed this leads to malfunctioning / Choking of Database Server. – Baseer Ul Hassan Sep 15 '17 at 19:53
  • The first thing to do would be to figure out why the logs are getting so large. Maybe backups aren't occurring, maybe they just see so many transactions they actually need to be larger and disk space needs to be increased. There are a number of things to consider such as the resources it takes to grow a log file. Read the top rated answer here https://dba.stackexchange.com/questions/53857/i-need-to-shrink-my-database-i-just-freed-a-lot-of-space – SQLChao Sep 15 '17 at 20:03
  • Ok i will go though the link you shared and respond. thanks – Baseer Ul Hassan Sep 15 '17 at 20:15
  • _"This was not a recommended technique"_ Said who? Why? – underscore_d Apr 18 '18 at 13:34
  • Possible duplicate of [How do I decrease the size of my sql server log file?](https://stackoverflow.com/questions/829542/how-do-i-decrease-the-size-of-my-sql-server-log-file) – underscore_d Apr 18 '18 at 13:35

1 Answers1

0

1) What recovery model is your database? 1.1) If its FULL recovery model, then are doing Transaction Log backups? If not then thats the reason why your logs are growing. You can either make the transactional log backups, or change the recovery mode to Simple and release the empty space.

Do not shrink your files.

mvisser
  • 667
  • 5
  • 11
  • The mode is Simple and unused log statements should be truncated automatically. but the t-Logs size still grew after two - three weeks. To overcome it I turned the mode to Full and then took a log backup , de-attach and attach operation as discussed in question. – Baseer Ul Hassan Sep 16 '17 at 10:54