question

Mohamed4053 avatar image
Mohamed4053 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.
sql-server-2008
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
mayankbhargava avatar image
mayankbhargava answered
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
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
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
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Guru, Thanks for your answer. Do we have any other way to shrink the log files apart form above two methods???
0 Likes 0 ·
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.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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).
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice point. I didn't notice that target.
0 Likes 0 ·
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)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.