Scritps to shrink all database instance in the server

Question

Scritps to shrink all database instance in the server

monkeyDBA

Default port

Points: 1457
More actions
October 8, 2010 at 1:06 am

#229458

Hi all,
Can anyone please share how to shrink all the transaction log and data file in the specific databases using T-SQL?
Kindly advice. Thanks. 😀
Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Why would you want to shrink all files on the server? Shrinking files is not a good habit:-). Shrinking the data files will cause fragmentation problems. Shrinking the log file without any special reason, will cause it to expend again during runtime and it will get to its size again (unless you've modified the recovery model to simple or you made a onetime operation that caused it to grow).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

monkeyDBA Default port Points: 1457 More actions · Answer 2

Thanks for your comment.

I was requested by user to shrink the database transaction log files in order to free up more space in the drives. There are quite a number of database instances hosted in the same server. That is why I need a script which can shrink the database transaction log files or data files of all database instances.

Regards,monkeyDBAWe dont grow when things are easy; We grow when we face challenges.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

jimmy.liew (10/8/2010)
I was requested by user to shrink the database transaction log files in order to free up more space in the drives.

That's like building a wall made of sand to hold back the ocean.

If the logs got that big, they will get that big again. All you're doing is gaining a little bit of time. They will grow again and you'll be right back where you are.

Consider getting more drive space.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Are you performing t-log backups on these databases?