October 1, 2013 at 6:54 am
I have a production databases running on SQL Server 2008 R2 Ent editionand.The size of this DB is 170 GB. Unallocated space available in this database of around 130 GB. Please see below stats of this database:
database_name database_size unallocated space
--------------- --------------- ------------------
Manager 174929.31 MB 131038.92 MB
reserved data index_size unused
------------- ------------ ------------- ----------
44329360 KB 38557904 KB 5342496 KB 428960 KB
I have tried to shrink this database from GUI & through command (DBCC SHRINKFILE), but unable to do so. Unallocated space is remain the same.
I have tried following ways to get this DB shrink:
1) Defrag all the indexes then ran DBCC SHRINKFILE
2) Trunate T-Log file then ran DBCC SHRINKFILE
3) Manually ran Ghost cleanup stored procedure then ran DBCC SHRINKFILE
but finally the Output of DBCC CHECKALLOC is still same:
The total number of extents = 740590, used pages = 5808614, and reserved pages = 5923760 in this database.
I have tried all possible way but DB is not getting shrink. Can any one help me on this issue?
October 1, 2013 at 7:22 am
What's the recovery mode for the database?
1) Defrag all the indexes then ran DBCC SHRINKFILE
Don't do this... defrag indexes and the shrink database will ruin the indexes again.
Pedro
October 1, 2013 at 7:37 am
It could well be the tlog that is taking up all the space you see in that particular output. try dbcc sqlperf(logspace) and see if you have a huge tlog. If so, try a tlog shrink. Note that you may not be able to reduce the size depending on where the currently active Virtual Log File endpoint is. I believe you can add junk transactions to get it to wrap around to the beginning again, at which point a truncate/shrink of the log file should work fine.
Another possible solution for the tlog issue (use with care!!) is to detach the database and reattach while having sql server build a fresh (tiny - be sure to grow it appropriately afterwards) tlog for you.
If it truly is the data file that is at issue here, will need a deeper check to find out why it won't shrink.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 1, 2013 at 8:06 am
Hi,
Thanks for your reply. Please find stats for dbcc sqlperf(logspace)
Database Name Log Size (MB) Log Space Used (%) Status
--------------------- ------------- ------------------ -----------
Manager 599.9922 0.668954 0
Log file size is very less.
Please find DB files stats:
type_desc data_space_id name size
---------- ------------- ------- -----------
ROWS 1 M_DATA 22314152
LOG 0 M_LOG 76800
October 1, 2013 at 8:18 am
Moreover, is there any way to recover Reserved space from SQL Server (except DBCC SHRINKFILE). Please see below tables which are top contributers of un-used/reserved space.
Table Name# Records Reserved (KB)Data (KB)Indexes (KB)Unused (KB)
dbo.Table1267,576 21,851,53621,838,5923,408 9,536
dbo.Table2817,218 3,791,3123,752,29638,512 504
dbo.Table3209,958 3,687,5443,347,6648,232 331,648
October 1, 2013 at 10:17 am
You either shrink or you export all data, build a new database, and import all data.
I am afraid that getting deeper into why you cannot shrink the database file (it isn't the log file) will be beyond the ability of a forum to assist. You may wish to try Microsoft support or some other form of direct help.
Good luck! Maybe someone else has some ideas here.
P.S. I suppose someone having an open transaction could prevent the full shrink? dbcc opentran to find that.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 1, 2013 at 10:24 am
Hi,
You could try to close all transaction by setting the database in single user mode... if possible...
USE master
GO
ALTER DATABASE [DATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [DATABASE]
GO
DBCC SHRINKFILE (N'[DATABASE_LOG]' , 0, TRUNCATEONLY)
DBCC SHRINKFILE (N'[DATABASE_DATA]' , 0, TRUNCATEONLY)
GO
USE master
GO
ALTER DATABASE [DATABASE] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Pedro
October 2, 2013 at 3:52 am
did you a full backup of the transactionlog, or of the database?
i can only shrink my transactionlog if i made backup before.
October 4, 2013 at 8:06 am
You mentioned that you truncated the trans log, but that's no longer possible with SQL 2008 R2. Are you certain the log was truncated? Regardless, the right way to do this is to first run a backup of the trans log and then immediately perform the shrink before another lock is granted at the tail of the log. Anything else will affect your backup chain and prevent you from being able to restore to a point in time, which is why it's no longer possible to truncate a log in SQL 2008 R2. Try something like this and see if it resolves your issue.
BACKUP LOG [databasename]
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' WITH COMPRESSION;
USE [databasename];
GO
DBCC SHRINKFILE (logfilename_log, 0);
GO
Joshua Perry
http://www.greenarrow.net
October 4, 2013 at 9:14 am
if you have tried all and still unable to release the space and you absultuly need to ... go this route
1*- On the shrink file chose your File Tyep - Log and select the file name (if multiple)
2*- on Shrink Action "select reorganize pages before releasing unused space" and shrink it to desire size.
Drawback on this is it will cause massive fragmentation.
refer:
http://technet.microsoft.com/en-us/library/ms190757.aspx
http://www.sqlservercentral.com/Forums/Topic863273-146-1.aspx#bm863293
That will take you where you need to be.
Do you know the reason why this was bumped up so high? And is there a reason why you would need to be released back?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply