SQL Server Shrink Multiple Files

Hi,

We have a standard SQL maintenance plan that runs on our SQL servers - part of which is to shrink the log files of the databases using the T-SQL DBCC SHRINKFILE command.

We have a new SQL 2014 server with around 30 databases, so it would be very time consuming to create the T-SQL statement naming all of the databases and log files.

Is there a way to included all database and log files?

Thanks,
David.
Cannon1707Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Shrinking databases is not a best practice at all. Even it's a practice to be avoid.
Why do you want to shrink databases?
0
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The question I would ask is why the log files are filling up and why you need to shrink them.

The fact that the log files grew once means that they would occupy require amount of space when the system performs the same operation again. In my opinion, if the growth is not acceptable, revisit the application to identify what is causing the log growth (or preventing the freeing up of log space) and fix that instead.

In case of full and bulk-logged recovery models, performing a transaction log backup would free up the log space and the log should be able to cycle back and reclaim the space.
0
Cannon1707Author Commented:
Hi all,

We do hourly transaction logs backups and a nightly full backup of user databases, with maintenance tasks to remove old transaction log backup files. There's also optimisation tasks to rebuild and reorganise indexes.

My SQL knowledge is poor at best, but I always though you had to shrink the transaction log files after this?

Thanks,
David.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Don't take it only for us. Please check Microsoft recommendation in this MSDN article:
Consider the following information when you plan to shrink a database:

•A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

•Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

•A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

•Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.
0
nishant joshiTechnology Development ConsultantCommented:
Hi Cannon1707,

You can create SSIS package for your requirement.Just prerequisites is you must have servername and db name in csv file or in DB table.

Below are steps to create package:

Create source from CSV file to retrieve server name and database name
Add for each loop to package assign retrieved server name and database to variables
create dynamic oledb connection using these variables
add execute sql task to for each loop and add below code into execute sql task

DECLARE @LogFileName VARCHAR(255)

SELECT @LogFileName = name
FROM sys.master_files
WHERE database_id = db_id()
  AND type = 1

DBCC SHRINKFILE(@LogFileName,1)

Open in new window


Hope this will helps.
0
nishant joshiTechnology Development ConsultantCommented:
And one more script to shrink all db file in server.

DECLARE @SQL VARCHAR(MAX) = ''

SELECT @SQL = @SQL+ 'USE '+d.name+CHAR(13)+'DBCC SHRINKFILE( '''+ m.name + ''',1);'+CHAR(13)
FROM sys.databases d
INNER JOIN sys.master_files m
	ON d.database_id = m.database_id
  AND type = 1

PRINT @SQL
EXEC (@SQL)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cannon1707Author Commented:
Hi,

Sorry if this has caused any confusion. I'm not shrinking the databases, just the transaction log files.

@Nishant - Have you got something like that but for the transaction log files?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
As Nakul commented above, you should check why your transaction logs are growing that much.
A transaction log operation can originate locks depending on how much the file is growing. You can kill a database performance with that.
0
Anthony PerkinsCommented:
Sorry if this has caused any confusion. I'm not shrinking the databases, just the transaction log files.
If you value your databases, then don't.  I will even add this: Please.
0
nishant joshiTechnology Development ConsultantCommented:
Cannon,Provided script is to shrink log file not for database.

for database we are using command "SHRINKDATABASE".
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.