my mdf file is too big and cant be shrink

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 2005 Forums

SQL Server Administration (2005)

my mdf file is too big and cant be shrink

Author

Topic

ching1874
Starting Member

11 Posts

Posted - 2011-06-26 : 06:40:54

hi there

i have a database which is almost 20G. Originally it is only 3 or 4G. But then after i delete some audit logs and it involves deleting 30 Millions rows. Then both the log and data file size become such big.

i tried to use shrinkfile and it works for the log file only. when i tried to shrink the data file(.mdf), it doesnt reduce any size of the mdf.

appreciate any help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-26 : 06:43:48

Shrink in small increments. Do not try to shrink all in one go especially if your database is live/busy. I typically shrink in 200MB increments.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ching1874
Starting Member

11 Posts

Posted - 2011-06-27 : 04:49:21

Hi tkizer, it doesn't allow me to shrink the data file

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 05:07:37

How are you doing the shrink?
You might need to move the data pages to get a smaller file.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ching1874
Starting Member

11 Posts

Posted - 2011-06-27 : 05:14:45

for the log file, i m using

backup log xxx_db with truncate_only
dbcc shrinkfile('log01',1)

then it free the log file for me at once

however when i try to shrink the data file using the following
dbcc shrinkfile('data_01',1000)
it doesnt allow me to do so and the rror message is File ID 5 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty

any thoughts?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-27 : 05:45:45

Giving the meassage with your first post might have been a good idea.

Is the shrink still going on? It can take a very long time.
Have a look at sysprocesses to see if there is a shrink running.
If there is then I would leave it to complete - if not then maybe bounce the server (and hope).
I take you have a very recent backup?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources