SQL Script

Question

SQL Script

mrfixit

SSC Journeyman

Points: 84
More actions
June 18, 2008 at 8:12 am

#192303

Hi
I'm very new to SQL Scripting and would appreciate any help
I'd like to script the shrinking of all the DB's on a server.
I know a list of all the db's is held in master..sysdatabases.
I don't know how to use this in a loop.
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

This can be done with a cursor, but I would recommend NOT doing it unless you absolutely have to. Most of the time, shrinking databases is a bad idea.

They usually just have to grow again, and every time they have to do that, you end up with a delay in transactions while it grows, and you usually also end up with a fragmented hard drive (with all the performance and stability problems that causes).

Are you absolutely shure you need to shrink all the databases on a regular basis? Have you examined the reasons given/assumed for this and made sure they are worth the almost certain degradation of performance and stability?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 740332 More actions · Answer 2

First of all you shouldn't shrink databases. It moves things around and causes fragmentation and can slow down index response and increase query times. Shrinking should only be done if one particular database had some event that added a lot of data, grew the file, and it needs to be recovered.

The data files in SQL Server should be set to a size larger than the data so there is room for growth. You want growth at discrete intervals, not every day. These files are not like text files or some other file you create on your workstation.

That being said, ms_foreachdb is a place to look for looping help. You might also read up on cursors in Books Online a bit since you can drive a short process loop with a query and a cursor. Cursors aren't efficient in SQL Server, so you want to avoid them if possible. Something with a few rows in it, however, that's rarely run isn't a problem.

We like to see you put some effort into what you're doing or research rather than just giving code. This almost sounds like some sort of test or interview questions.

mrfixit SSC Journeyman Points: 84 More actions · Answer 3

Hi

Thanks for you comments.

It is a test box that regularly has large data sets added then removed.

Thanks for the pointer to cursors, it was exactly what I needed.

Script may be a bit clunky but it a start.

-------------------------------------------------------------------------

use [master]

go

Declare @Name nvarchar(MAX)

Declare @Name1 nvarchar(MAX)

Declare C1 CURSOR READ_ONLY for Select name from master..sysdatabases order by Name

Open C1

Fetch next from C1 into @name

While @@Fetch_Status = 0

Begin

Print ':'+@name +':'

set @name1 = 'DBCC SHRINKDATABASE(' +'N'+char(39)+@name+char(39)+')'

Print ':'+@name1+':'

exec dbo.sp_MSforeachdb @name1

Fetch next from C1 into @name

End

Close C1

Deallocate C1

--------------------------------------------------------------------------------------