SQL Server - Cannot Shrink LDF Log file and now its huge

Hello,

I’m hoping you can help with this issue I have with SQL Server.

I have a test database where I don’t care about the data. The database is large and I don’t want to delete it but the log file is taking up around 18GB which is leaving my server with virtually no hard disk space left.

The crazy thing is that when I try and shrink the log file not only does it do nothing but it is actually increases the log file size!

I’m attempting to shrink the file using SSMS and the shrink command as well as the following script

ALTER DATABASE [ttdf_Staging] SET RECOVERY SIMPLE
DBCC SHRINKFILE (ttdf_log, 1)

It used to be 12GB and now from me running scripts and invoking the shrink command using the SQL Server Management studio multiple times its increased to 18GB.

If I cannot reduce it soon I’ll have to resort to taking it offline and manually deleting the log file. Ideally, I don’t want to do this as every article I have read advises against doing this but I don’t understand why its increasing plus I have no disk space left.

Can anyone help?


User_59985 Profile Image

User_59985

Rating: 40

Offline


Answers (1)


This is something I’ve had experience with in the past. It’s likely that your database is set to wait for replication. If its in this state you cannot do anything until its been updated to finish its replication task.

Try running the following script against your database.

 

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

alter database [ttdf_Staging] set recovery simple

DBCC SHRINKFILE (acm_log, 1)

DBCC LOGINFO('ttdf_Staging')

alter database [ttdf_Staging] set recovery full

 

once that’s successfully ran have a look in your Data LDF file in /YourSQLServerDirectory/MSSQL/DATA and hopefully it will be a lot smaller

 

JGilmartin

JGilmartin Profile Image

JGilmartin

Technical Architect at Pinewood Technologies

Rating: 2890

C# Expert

Offline



thank you, this worked. the log file is now a fraction of its original size
User_59985
26/11/2017 17:20

Question Statistics
  • Views: 652
  • Answers: 1
  • Comments: 1
  • Asked: User_59985 (40)
  • Date: 26/11/2017 17:10
Tags
SQL Server SQL

© 2016 - 2018 - IntermittentBug