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?
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