how to shrink database
I have 100gb SQL 2005 database FULL RECOVER MODE and trying to shrink data size by purging some records.
1. I have to DB live and test. Test database is a copy of live data restored to different file names. however logical name is the same for both.
BACKUP LOG TEST WITH TRUNCATE_ONLY
DBCC SHRINKFILE( Test_log Data, 2)
log files are getting truncated
when I do QUERY ON MY TEST DB
delete from my_table
truncate MY TABLE
DBCC SHRINKFILE(LIVE_DATA, 3)
What is the safe and proper way to shrink DB
1. I have to DB live and test. Test database is a copy of live data restored to different file names. however logical name is the same for both.
BACKUP LOG TEST WITH TRUNCATE_ONLY
DBCC SHRINKFILE( Test_log Data, 2)
log files are getting truncated
when I do QUERY ON MY TEST DB
delete from my_table
truncate MY TABLE
DBCC SHRINKFILE(LIVE_DATA, 3)
What is the safe and proper way to shrink DB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you shrink the database file, everything in it gets very fragmented because of the way it shrinks. If the database will never grow again to the size you are shrinking it from, then you can shrink it and then rebuild the indexes or reorganize them.
If you are trying to get rid of data in a table, I would use TRUNCATE as it only logs the deallocation of the pages, instead of logging the delete of each row. TRUNCATE is faster.