September 7, 2010 at 12:45 am
Bhuvnesh (9/6/2010)
take a full backup and then try shrinkdatabase
Why is a full DB backup going to help?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2010 at 12:48 am
Actually its a dev db that needs to be refreshed from prod but space issue is there in dev. I wnat to shrink some other db's datafile in dev which has 80% free sapce available.Size is abt 122 GB, but i am shrinking in smaller chunks like 500 mb. Its not completing and has taken 3+ hrs. So i cancelled it now.
September 7, 2010 at 12:54 am
Vivek29 (9/7/2010)
Actually its a dev db that needs to be refreshed from prod but space issue is there in dev. I wnat to shrink some other db's datafile in dev which has 80% free sapce available.Size is abt 122 GB, but i am shrinking in smaller chunks like 500 mb. Its not completing and has taken 3+ hrs. So i cancelled it now.
Perhaps you are impatient ?
Is there a benefit to shrinking in small chunks ? If you have 80% free space of 122 gig, wouldn't it be better to shrink to 50 G at one time, leave 25 G free for growth, then rebuild indexes ?
September 7, 2010 at 12:56 am
What's the output of sp_spaceused?
What's the shrink operation waiting for? (check sys.dm_exec_requests)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2010 at 12:58 am
May be:-) I tried later,it completed but no space gained now. Size is as it was.
September 7, 2010 at 1:04 am
If its not shrunk by 500 mb, whats the surity it would do for 50 gb. Also, i tried doing rebuilding indexes, but not fruitful.
September 7, 2010 at 1:10 am
There have been several questions asked, that you have not answered, that might provide useful information.
September 8, 2010 at 2:51 am
Have you tried to see what books online says about DBCC SHRINKFILE?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 8, 2010 at 3:55 am
OK, maybe someone said this already. I also had the issue of the data file that won't shrink. Upon seeing this post I decided to run it again on a test database. First of all I did some reading on SQL BoL and saw the following:
If the shrink operation runs without error, but the file size have not changed try the following:
use AdventureWorks -- your database
SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
This will show you the available size of the database files.
Now, the question that I cannot answer is: How much free space should I have in relation to the size of the database file?
Maybe someone can answer that for us 'cause I have been searching and maybe I'm just blind.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 8, 2010 at 4:01 am
Manie Verster (9/8/2010)
How much free space should I have in relation to the size of the database file?
it might be anything above initial size + auto growth size(percentage). please verify it this is guess of mine
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 8, 2010 at 5:58 am
Did some more reading and testing and here is a link you can check out. This Microsoft link actually does it on the gui and not by script and I tried it just to see if anything happens that did not normally happen. I did as they said and on the reorganize files before releasing unused space I said 5 % in the maximum free space in the files after shrinking (might be a bit low - was 18% before I started) and then I saw a difference. I would not recommend you to do the same but look at what you can make it and be safe and then try it. By the way, this DBCC SHRINKDATABASE and when I clicked on script this action I got the following:
USE [MyDatabase]
GO
DBCC SHRINKDATABASE(N'MyDatabase', 5)
GO
Bet you haven't tried that yet! Yet there is one more question. What is the ideal free space size in relation to the database size.
I have an answer here but not confirmed. Can someone confirm please?
it might be anything above initial size + auto growth size(percentage). please verify it this is guess of mine
-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
September 8, 2010 at 6:23 am
Manie Verster (9/8/2010)
What is the ideal free space size in relation to the database size.
There's no one good answer to that. Depends on how large the tables are, how fast the DB grows, etc. I like to have at least a couple weeks (preferably a month or more) estimated growth as free space, and no less than the size of the largest index in the DB (for rebuilds)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 10:00 am
What is the size of the datafile you are trying to Shrink
September 8, 2010 at 10:53 am
I see that you are trying to shrink a 122 Gig Data file. I think you might have already tried DBCC SHRINKFILE instead of shrinkDatabase. Secondly, Have you checked the values in transaction_sequence_num or first_snapshot_sequence_num columns of sys.dm_tran_active_snapshot_database_transactions view.
September 8, 2010 at 10:59 am
What is the size of the datafile and what is the DBCC option you are using to shrink the data file?
Best Regards,
SQLBuddy
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply