Database Need free space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Need free space

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-01 : 20:26:41

File size is
45254 primary data
23318 USED
21936 FREE

im running
USE DATABASENAME
dbcc shrinkfile (primary_data, 23319)

But it taking forever is there another way to get back the 21 gig

Thanks

Westley
Posting Yak Master

229 Posts

Posted - 2007-11-01 : 22:43:27

Not really, you will need to do shrinkfile if you really need the space. But why do you need to do that in the first place? If you do not know why the data file become 45G, there must be a reason for it, and it might grow back to 45G in a week or 2 (maybe rebuild index job or some batch import etc).
Unless you are sure that you can free up that space, don't shrink it.

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 05:09:33

MVJ [I think it was him!] posted something on SQLTEAM about shrinking the file in "modest" steps, rather than all in one go.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-02 : 05:13:48

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-11-02 : 05:22:36

That's the one! Thanks Madhi.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-02 : 11:33:54

Thanks i take a look at it.
Basically someone ran a script and it kept updating the actual tables like Holding tables and it kept going and going.

So i removed the data from thes Holding tables and wanted to get the 25 gig backs.

Cheers

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-02 : 11:35:27

Thanks for link for file i try it out.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-02 : 11:47:50

Was going to give it a try on test but got confused
database as follow

DBA Primary 26391 23360 3030
DBA Dynamic 10067 7252 13476
DBA Report 13720 13476 3461
Transaction Log spacd 7104 3461 3643

This is not bad figures normally but last night the
DBA Primary 45254 23360 21936
DBA Dynamic 10067 7252 13476
DBA Report 13720 13476 3461
Transaction Log spacd 7104 3461 3643

So using the script i could not see where i put just the
DBA Primary

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-02 : 23:17:49

Don't understand your issue. If db needs certain size, why do you try to keep shrinking it?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-03 : 08:19:54

The other day it grew out of control by a runaway process and eat up 23 gig which is not normal i managed to shrink it down but it took for ages - so someone said use script below to do in small amounts so i was going to try it out

This is now after i did a massive shrink
DBA Primary 26391 23360 3030
DBA Dynamic 10067 7252 13476
DBA Report 13720 13476 3461
Transaction Log spacd 7104 3461 3643

But it was

DBA Primary 45254 23360 21936
DBA Dynamic 10067 7252 13476
DBA Report 13720 13476 3461
Transaction Log spacd 7104 3461 3643

So in test i wanted to try out the script to do in small amounts but did not know how to plug in the DBA Primary part to the script

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-03 : 20:41:07

What's DBA Primary? File group in the db? You can't shrink file group. Don't think shrinking in smaller chunk will help since you have to shrink multiple times.

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 02:35:59

"Don't think shrinking in smaller chunk will help since you have to shrink multiple times."

I think the idea was that a smaller shrink would take a "reasonable" amount of time, so could be scheduled each night, say, until the file was back to a reasonable size.

Kristen

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-04 : 16:11:18

But you'll find it's not the case since most of the time are spent on moving data around.

Kristen
Test

22859 Posts

Posted - 2007-11-04 : 17:09:00

Well ... in the link above to MVJ's script he reckons that there are benefits shrinking in small steps. Obviously if it can be done in one go that's going to be best, but if its taking too long, or might need interrupting, the small steps method may help.

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-04 : 17:32:33

I give the script a try out but did not see what filegroup to enter
Maybe i only need to shrink
Database called DBA for example

Three primary filegroups
DBA Primary 26391 23360 3030 this one
DBA Dynamic 10067 7252 13476
DBA Report 13720 13476 3461
Transaction Log spacd 7104 3461 3643

But did not see where to add dba primary

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-04 : 18:10:44

You can shrink db or file but not file group.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-04 : 21:08:49

The script on the link lets you shrink a database file. You cannot shrink a filegroup, only a file.

The directions in the link are fairly clear:
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script

If you can't figure out the files, try running the script on this link:
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

CODO ERGO SUM

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-11-05 : 06:23:29

Thanks i look at that today and the shrink script and see how i get on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources