Shrink file !!!

[sendba]

Hai All

My database size is 100 GB and the actual allocated space is 50 GB.Can i run DBCC shrinkfile on the file but this should have not during my Maintenance window.Can i run the command when the users are connecting to the database when the database is in live, if so can you let me know what are the advantage and disadvantage.
I need to know whether it will lock the users or tables and what is the impact on the performance.

Thanks in Advance

Regards
Senthil

 

[bikerboy718]

Senthil,

I am pretty sure that you can run a shrinkfile with out dropping user connections to the databse or locking tables. When you do a shrinkfile you are just shrinking the unused space of the database that is unused. However I would still recommend doing so after hours.

Michael

With Great Power Comes Great Responsibility!!!

Michael

 

[JohnBates]

sendba,

Yes you can shrink the db while it is in use. Should not cause a tremendous load on the server.

example: DBCC SHRINKFILE (gm_data, 25600)

But frequent shrinks will cause disk fragmentation.

If you do not have a good defrag tool, consider investing in Executive Softwares 'Diskeeper' standard edition.

It will defrag SQL Server files, whereas the free Diskeeper Lite that comes with some Microsoft OS does not.

John

 

[craigfw]

Hello Everyone,

I have a developer who (really not me) has filled a log to 75GB and I'm trying to truncate the log and reduce the log file size.

1. Checkpointed the database
2. Backed up the DB
3. Performed a dbcc shrinkfile ('LARS_TEST_40003_Log', truncateonly)
4. Used DBAtrisan to check the space allocated to the log and it's at 69GB!
5. What am I doing wrong?

Thanks,
Craig

 

[yelworcm]

Use 'backup log', in order to clear the transaction log.

 

[SQLBill]

You really should have started your own thread.

That said, you only truncated the data, you didn't return the free space to the operating system. That's what truncate only does. Do a shrinkfile without using Truncateonly.

You might have to do it a couple of times, it doesn't always happen instantaneously.

-SQLBill

Posting advice: FAQ481-4875