• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

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.
0
Cannon1707
Asked:
Cannon1707
  • 3
  • 3
  • 2
  • +2
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now