asked
Need some suggestion to shrink database log files
I am new bee for sql server dba. I need some suggestion to shrink the database log file. I have tried and found some solution to shrink the file. But i think this is not opted solution for this. **Solution 1:-** ALTER DATABASE nologtest2 SET RECOVERY SIMPLE; GO -- Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (nologtest2_log, 1); GO -- Reset the database recovery model. ALTER DATABASE nologtest2 SET RECOVERY FULL; I am not sure i can do this in production. **Solution 2:-** Backup log nologtest2 to disk='C:\MAS\nologtest32_log.trn' DBCC SHRINKFILE (nologtest2_log, 1); We can took some transaction log backup and then we can shrink the database. But i believe it will break the lsn chain. Then we may face some issue in logshipping environment. I need some very opted solution for reduce the database log file issues. **Note:-** I am not much familiar with sql dba. If i am ask anything wrong or understandingly please correct me.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Hello Mohammad, Shrinking the database may or maynot affect the size of data. I would suggest to shrink the database, and rebuild the indexes for better output. Use the below query as a sample. -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO -- Shrink the Database DBCC SHRINKDATABASE (ShrinkIsBed); GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO -- Rebuild Index on FirstTable ALTER INDEX IX_SecondTable_ID ON SecondTable REORGANIZE GO -- Name of the Database and Size SELECT name, (size*8) Size_KB FROM sys.database_files GO`enter code here` -- Check Fragmentations in the database SELECT avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('SecondTable'), NULL, NULL, 'LIMITED') GO
Shrinking a database doesn't affect the data directly. It just removes unallocated space from the database. And, shrinking the database, whether or not you defragment your indexes, results in file fragmentation in the data files. It's not a good idea to shrink databases regularly.
Bad advice: Shrinking a database has a huge impact on your I/O, results in fragmented data/indexes, which has to fix, which results in more I/O and logging
Solution 1 will break the log sequence chain. Better to use Solution 2. Further, you can run the log backup, then run a CHECKPOINT command, then run the log backup again. Run another CHECKPOINT. Then try to shrink the log file. That's going to be the safest way to go about it and will likely result in success. If you were to go with Solution 1, I'd recommend immediately taking a Full backup after you're done with the shrink and the database is back in Full recovery. Then start taking logs from there so you avoid issues with the LSN.
Hi Guru, Thanks for your answer. Do we have any other way to shrink the log files apart form above two methods???
None that are safe. The best method is what you have outlined as Solution 2, but include the thoughts that @Magnus Ahlkvist added in his answer.
I agree with Grants answer. But I'd like to point out one thing with your scenario. You are shrinking the database log file to 1Mb. That's probably not what you want to do. If you want the database to be in full recovery, your log file will become full and autogrow pretty soon with a logfile size of 1Mb. A grow operation takes time, and it will cause file fragmentation. I would recommend you shrink the log file to a more reasonable size (preferably the maximum size your log file will reach between each log backup under normal operation).
Ever heard about virtual log files? In addition to Magnus answer, I recommend to shrink the logfile and then do a resize to a reasonable size (instead of 1mb growth steps)
17 People are following this question.
