SQL Server DBA Tutorial 32- How to Shrink Database And Database Files in SQL Server

[@m.sprakash3851]

Really Helpful tech Brothers

[@TechBrothersIT]

Thank you dear.

[@ByronForrest2010]

Thank you very much, your instruction was most helpful.

[@TechBrothersIT]

+Byron Forrest you are welcome and thanks for watching. Glad to hear you liked the video!

[@roshanrafeek]

This is really helpful.. Thank you very much...

[@jaswanthreddy3782]

Hi, I've got ssms 2019 server, I'm trying to shrink the database but I didn't find the option Task, so how do we actually shrink without it showing the option?

[@manatelugu3779]

TQ you for good explanation

[@TechBrothersIT]

So nice of you

[@haithamelatrache1231]

Thanks for the video may I ask you what to check and why do I have the following error when I try to shrink the file Msg 3140, Level 16, State 5, Line 1

[@migaelurk]

Should we "detach"/reattached , or "take offline"/make online on the database after we shrink the file? rather just only "refresh" the database? This is because i noticed that, free space available is getting lesser than before, which is weird.

[@shrenikshah7552]

How shrink database affect in production? will it create outage if we shrink database instead of database file?

[@williamcspereira]

Thank you for the help!

[@draxler2941]

thanks A lot.... great and helpful toturial!!!

[@TechBrothersIT]

You are very welcome and thanks for watching.

[@elbadrey]

what about notrancate and trancateonly t-sql, and so what is benefit of using empty when shrinking file!!!

[@prashantmehra7380]

What if we have mirroring enabled for the database what would be the steps

[@andreigiubleanu]

Very good sir.

[@szp917]

Hello, I have a question regarding shrinking the t-log backups on Clustered environment. Scenario is we have 2 DB servers with same instance name say SQL1, SQL2. SQL1 primary and SQL2 is secondary. I have sceduled the T0log backups on primary server using maintenance Wizard, but I cant find any t-log backupsin the destination folder. If we take manual tlog backups we can see them in destination folder but its failing only when scheduled through Maintenance WIzard & clustered environment. Can you please make a video on taking t-log backups on clustered environment. Please let me know if you have any questions.

[@TechBrothersIT]

+Rakesh Joshi when you say it's clustered environment, do you mean sql server is installed in a cluster mode or is it AG (Availability Group) cluster? From your scenario it seems like you have setup AG as you described "primary" and "secondary" - please look at AG properties and see which is preferred replica for backups, when you use normal wizard, take a quick look at TSQL to see whether it is using function to follow AG setup rule? Don't modify existing in prod, just use new wizard until you reach a point where you can view TSQL, you don't have to click ok as it show you the function that it is using or not.

[@drups87]

Hi, I have a question, I have a Win 2008 R2 SQL server that was never given a correct maintenance and now the transaction log file is about 500 GB. I've made a transaction log backup and now I was trying to shrink the log file it but it only free some little space (about 60 MB) each time I run the shrink task. I've tried both shrink actions ("release unused space" and "reogranize pages before releasing unused space") and the behavior is the same with both options. In the shrink file menu for the log is showing me "Currently allocated space: 492690,31 MB" and "Available free space: 472219,08 MB (95%)". Do you have any idea why Iam not being able to shrink my transaction logs? Thanks in advance! regards!

[@TechBrothersIT]

+Diego Draper (Drups) hi, what is the initial size you have for tran log?

[@drups87]

Hi, thanks for replying. The initial size for the transaction logs is set to "492,691" as I can see in the database files properties. In autogrowth is set to "By 100 MB, restricted growth to 2,097,152". Thanks in advance. Regards!

[@bahaaabouzahr2323]

thanks

[@TechBrothersIT]

+bahaa abou zahr you are welcome and thanks for watching!

[@SalmanAli-ud1kc]

if you make this video in hindi to kiya he bat thi

[@gepliprl8558]

hmm... explanation unclear around those allocated mb