March 20, 2008 at 6:30 am
Hi there,
When running sp_spaceused on a database I get the following:
Database Size: 108 493.94 MB
Unallocated Space: 98 738.68 MB
Reserved: 9 988 616 KB
Data: 5 957 424 KB
index_size: 3 200 200 KB
unused: 830 992 KB
This is a copy of our production database that we need to restore in our Dev/Training environments. We’ve deleted/truncated 'old' transactional & only kept recent data.
The Primary file is still over 110GB, but data & indexes only add up to about 10GB. Can anyone tell me what I’m doing wrong with my shrinking operations? Or am I missing anything?
I’ve followed both these KB’s:
http://msdn2.microsoft.com/en-us/library/aa258824(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa258287.aspx
To release the unallocated space mentioned above, I have tried the following:
USE 'dbname'
GO
DBCC SHRINKDATABASE 'dbname', TRUNCATEONLY
GO
I’ve also tried DBCC SHRINKFILE, using Management Studio, but to no avail.
I’ll appreciate any recommendations.
Thanks,
Lian
March 20, 2008 at 10:42 am
use
DBCC shrinkfile (filename, expected size in MB , notruncate)
once this is completed then
DBCC shrinkfile(filename, expected size in MB , truncateonly)
This will do trick.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
March 21, 2008 at 5:52 am
If the database was using replication in production you may have to also mark the transactions as complete in order to be able to shrink the logfile.
March 21, 2008 at 7:23 am
Mohan Kumar (3/20/2008)
useDBCC shrinkfile (filename, expected size in MB , notruncate)
once this is completed then
DBCC shrinkfile(filename, expected size in MB , truncateonly)
This will do trick.
Thanks Mohan, will give it a try.
Mark, thanks, the log is under control - Currently at 100 MB:)
March 21, 2008 at 7:35 am
The above is correct, but something that makes life pretty easy when constantly restoring databases from a production environment into DEV/QA is to write a little post restore procedure that...
- Changes recovery models to simple
- Set autoshrink ok
- Shrink log files
March 21, 2008 at 7:59 am
the truncateonly option only deallocates space up to the last used extent, so you were probably not getting much space back as you had an allocated extent near the end of the database.
the notruncate followed by truncateonly option will work but you can just go for not specifying these options and acheive the same result in one hit. As data will be moved to the front of the file it can be time consuming and intrusive, it will also cause fragmentation in the database, so don't run it after rebuilding indexes!
---------------------------------------------------------------------
March 31, 2008 at 3:47 am
Thanks for the pointers. Unfortunately it's producing the same results...
Is there anything else I can/should look at?
March 31, 2008 at 11:12 pm
i had issues like this with sql2000/sp4/cluster, and i think some of this problem may happen with sql 2005 before sp2, so i am not sure u have 2005 with or without sp2?
but anyway let me give u an idea about what may be the problem, actually your problem that sql for certain reason (bug) sometimes miscalculate the free spaces over the pages and due to this it consider that tha page is full before actually reaching its full cabacity (it is not the fill factore issue), that's why there will be lots of free spaces unused by your databases, use teh following script to get a detail list of tables in your database and the unused space over each of them
CREATE TABLE #temp (TableName NVARCHAR (128), RowsCnt VARCHAR (11), ReservedSpace VARCHAR(18), DataSpace VARCHAR(18), CombinedIndexSpace VARCHAR(18), UnusedSpace VARCHAR(18))
EXEC sp_MSforeachtable 'INSERT INTO #temp (TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace) EXEC sp_spaceused ''?'', FALSE'
SELECT TableName, RowsCnt, ReservedSpace, DataSpace, CombinedIndexSpace, UnusedSpace
FROM #temp
ORDER BY TableName
DROP TABLE #temp
u will discover the tables that is eating up your spaces, and what i do in my cases is creating dump clustered indexes over these table , this recover the lost space and then u can shrink and u will get your lost space,,, sorry for my english
regards,
April 1, 2008 at 5:45 am
Thanks Bessem,
There's also a report you can run from Management Studio for this info.
Unused space in my tables only add up to about 750MB (Againt a total of nearly 95GB total unused space)
April 1, 2008 at 6:00 am
What sizes are the actual op sys files (mdf/ldf/ndf) ?
Do these correlate with what sp_spaceused is tellling you?
If the log file is huge run DBCC OPENTRAN to determine the oldest transaction, as indicated in previous posts an old open transaction could be preventing the log file from shrinking significantly.
April 1, 2008 at 7:11 am
sp_spaceused & OS correlate.
The log file is under control & is currently sitting on 100MB.
The problem is that the MDF file is sitting on 105GB, but I'd like it to be more or less the size of the data + index pages (About 20GB)
April 1, 2008 at 11:03 pm
Preet (4/1/2008)
What sizes are the actual op sys files (mdf/ldf/ndf) ?Do these correlate with what sp_spaceused is tellling you?
If the log file is huge run DBCC OPENTRAN to determine the oldest transaction, as indicated in previous posts an old open transaction could be preventing the log file from shrinking significantly.
i am still runnign sql 2000/sp4 in my case, and yes actually teh spaces lost correlate to the unsused space over the tables, when the problem happened we opened a case with Microsoft, and after investigation they repprted to me that this is a bug in sql 2000/sp4/cluster in my case, and unfortunatly they will not be able to fix it because of cost blah blah issue, and my only workaround is the clustered indexex to shrink the DB size and recover the unsused space, and i should go for sql 2005 sp2.
April 1, 2008 at 11:06 pm
Lian Pretorius (4/1/2008)
Thanks Bessem,There's also a report you can run from Management Studio for this info.
Unused space in my tables only add up to about 750MB (Againt a total of nearly 95GB total unused space)
This is interesting!, what the reasons for this unused space then? it should be related to something indexes , tables..
April 2, 2008 at 6:34 am
1. back up the T-Log first using the command
BACKUP LOG MyDb TO MyDb_log
GO
2.Truncate the Transaction Log using the command
BACKUP LOG MyDb WITH TRUNCATE_ONLY
GO
Truncation does not reduce the size of a physical log file,it reduces the size of the logical log file.To reduce the Physical log file, we need to shrink the file.
3. Shrink the Log file using the command
USE MyDb
DBCC SHRINKFILE (MyDb_Log)
GO
I think this might help you.
April 2, 2008 at 6:39 am
Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file and marks as inactive the virtual logs that do not hold any part of the logical log.
A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
To know more about this please visit the following link
http://www.akadia.com/services/sqlsrv_programming.html#Example%20Shrinking%20the%20Transaction%20Log
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply