How do I decrease the size of my sql server log file

Question

So I have been neglecting to do any backups of my fogbugz database  and now the fogbugz ldf file is over 2 and half gigs  Thats been built up over the six months we ve been using fogbugz   I backed up the database  then I backed up  and truncated the transaction log  yet the transaction log is still 2 and a half gigs  I did a shrink on the log file and its still 2 and a half gigs  Nothing I do seems to shrink the file in size   Is there anyway to fix the problem  Or is the only way back at this point to detach the database  delete the log file and then reattach with a new one

User · Answer

You have to shrink  amp  backup the log a several times to get the log file to reduce in size  this is because the the log file pages cannot be re-organized as data files pages can be  only truncated   For a more detailed explanation check this out  WARNING   Detaching the db  amp  deleting the log file is dangerous  don t do this unless you d like data loss

User · Answer

I had the same problem  my database log file size was about 39 gigabyte  and after shrinking  both database and files  it reduced to 37 gigabyte that was not enough  so I did this solution    I did not need the ldf file  log file  anymore      Important    Get a full backup of your database before the process     Run  checkpoint  on that database  Detach that database  right click on the database and chose tasks     Detach      if you see an error  do the steps in the end of this text  Move MyDatabase ldf to another folder  you can find it in your hard disk in the same folder as your database  Just in case you need it in the future for some  reason such as what user did some task   Attach the database  right click on Databases and chose Attach      On attach dialog remove the  ldf file  which shows  file not found  comment  and click Ok   don t worry the ldf file will be created after the attachment process   After that  a new log file create with a size of 504 KB        In step 2  if you faced an error that database is used by another user  you can   1 run this command on master database  sp who2  and see what process using      your database   2 read the process number  for example it is 52 and type  kill 52   now your      database is free and ready to detach   If the number of processes using your database is too much   1 Open services  type services in windows start  find SQL Server     process     and reset it  right click and chose reset     Immediately click Ok button in the detach dialog box  that showed the   detach error previously

User · Answer

Ensure the database s backup mode is set to Simple  see here for an overview of the different modes   This will avoid SQL Server waiting for a transaction log backup before reusing space  Use dbcc shrinkfile or Management Studio to shrink the log files    Step  2 will do nothing until the backup mode is set

User · Answer

Welcome to the fickle world of SQL Server log management   SOMETHING is wrong  though I don t think anyone will be able to tell you more than that without some additional information  For example  has this database ever been used for Transactional SQL Server replication  This can cause issues like this if a transaction hasn t been replicated to a subscriber   In the interim  this should at least allow you to kill the log file    Perform a full backup of your database  Don t skip this  Really  Change the backup method of your database to  Simple  Open a query window and enter  checkpoint  and execute Perform another backup of the database Change the backup method of your database back to  Full   or whatever it was  if it wasn t already Simple  Perform a final full backup of the database    You should now be able to shrink the files  if performing the backup didn t do that for you    Good luck

User · Answer

This is one of the best suggestion in which is done using query  Good for those who has a lot of databases just like me  Can run it using a script  https   medium com  bharatdwarkani shrinking-sql-server-db-log-file-size-sql-server-db-maintenance-7ddb0c331668 USE DatabaseName  GO -- Truncate the log by changing the database recovery model to SIMPLE  ALTER DATABASE DatabaseName SET RECOVERY SIMPLE  GO -- Shrink the truncated log file to 1 MB  DBCC SHRINKFILE  DatabaseName Log  1   GO -- Reset the database recovery model  ALTER DATABASE DatabaseName SET RECOVERY FULL  GO

User · Answer

Perform a full backup of your database  Don t skip this  Really  Change the backup method of your database to  Simple  Open a query window and enter  checkpoint  and execute Perform another backup of the database Change the backup method of your database back to  Full   or whatever it was  if it wasn t already Simple  Perform a final full backup of the database  Run below queries one by one   USE Database Name select name recovery model desc from sys databases ALTER DATABASE Database Name SET RECOVERY simple DBCC SHRINKFILE  Database Name log   1

[sql] How do I decrease the size of my sql server log file?

Examples related to sql

Examples related to sql-server