shrink DB

Dear expertise,

Right now it seems that no matter how many time I backup the log with truncate only and shrink DB, the DB size can't be smaller anymore.

is that true that the shrink DB command can't shrink the DB size smaller than the initial size of the DB data file or log files?

Any way for us to make it even smaller? by creating the DB files again with smaller size and move the data/table to the new file group?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardSmeeCommented:
Hi u could try backing up the db after shrinking it and the logs and restoring to a new db you have just created with small initial data and transaction log sizes
0
blandyukCommented:
How are you doing the DBShrink? Running it from a query of going through the options in the content-menus? There is an option which is "Reorganize files before releasing unsed space". If this is not checked, a DBShrink might not affect your database. Check this and try it again.
0
chapmandewCommented:
WHY are  you shrinking the db?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ProjectChampionCommented:
Please note that Truncation does not reduce the size of a physical log file. Reducing the physical size of a log file requires shrinking the file.

As for DBShirnk, Chapmandew is right, so assuming that you have justifiable reasons for shrinking your DB, rather than shrinking the whole DB it'd be more efficient to find out which data/log file has excessive free space and shrink only the file(s) which need to be shrunk.
The following query is one way to find this out:
use MyDB;
Go
SELECT name, FILEPROPERTY(name, 'SpaceUsed') usedPages, size AS totalPages, groupid, filename  FROM  sysfiles WITH(NOLOCK);

The you can decide which file (if any) needs shrinking and shrink it using the following command:
USE [MYDB]
GO
DBCC SHRINKFILE (N'MyDB_Data' , xxx)
GO

Where xxx is the target size. This would reorganise the pages and then release the free space after keeping xxx for the file. You could use it like this:
DBCC SHRINKFILE (N'MyDB_Data' , xxx, TRUNCATEONLY)
and it'd only truncate unused space at the end of the file.
Or you could use it like this:
DBCC SHRINKFILE (N'MyDB_Data' , EMPTYFILE)
in which case it'd empty the file bby moving all the data to another file in the same filegroup.
0
chapmandewCommented:
You really shouldn't be shrinking a data file at ALL unless you're running out of space.  
0
marrowyungSenior Technical architecture (Data)Author Commented:
chapmandew,

I do this all the time and in the new company, all dB size is very large and I don't think we need that size, I just try to do this for better capacity control and the disk space alert always send email to us.

I try this method many time and it works all the time, I truncate logs and the shrink , this make the log files shrink to 1-2 MB, very good.

DBA100.
0
chapmandewCommented:
you shrink the log files or the data files?

If you shrink the data files, you're hosing yourself.
0
marrowyungSenior Technical architecture (Data)Author Commented:
chapmandew,

I use ShrihkDB, the whole DB.

What is hosing?

DBA100.
0
chapmandewCommented:
http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

Note:  this article is from the guy who wrote DBCC SHRINKDB (and most other DBCC commands)
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

Some DMV can show how many % used and how many % reserved, right?

Marrow.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

I don't understand this:

DBCC SHRINKFILE (N'MyDB_Data' , EMPTYFILE)
in which case it'd empty the file bby moving all the data to another file in the same filegroup.

in this case, it don't say which "another files" it will move data to, seems a bit dangerous, right?

Marrow.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

By using this,

use MyDB;
Go
SELECT name, FILEPROPERTY(name, 'SpaceUsed') usedPages, size AS totalPages, groupid, filename  FROM  sysfiles WITH(NOLOCK);.

used pages is all in KB, right? and so as totalpage is the used + reserved, right? still in KB,right?

if the return result is:

Primary_data       137792      331928  1 S:\SQL2005\MQASQL\Data\primary_data.mdf

then I can do
DBCC SHRINKFILE (N'Primary_data' , 137792)
GO

if I want to shrink it to a size that equal to usedPages?

DbA100


0
ProjectChampionCommented:
Dear DbA100,
usedPages and totalPages in the result of the query from sysfiles is the number of "Pages". Each page is 8KB so 137792 and 331928 means that your "primary_data.mdf" file is currently taking 2593MB of which 1076MB is used space, so 1517MB is available space for shrinking.
I cannot  advise whether you should or shouldn't shrink your data file - that's your decision - but if you want to go ahead and shrink it, then the minimum size you can set in the shrink command is the currently used space, i.e. 1076MB (or 1077MB).

Good Luck! : )
0
ProjectChampionCommented:
Regarding your queston about which file (in the same filegroup) it'll move the data to, you can well control it by temporarily changing the sttings of the autogrowth on the other files in that FG. Please note that the finest-grain logical unit of storage as far as user is concerened is the FileGroup, you can tell SQL Server to store objects in a particular FG, and you can also find out which FG contains a specific object such as a table or an index. But if you have multiple files per FG, there's no sure way to tell which one contains a specific object. So as far as storage management is concenrned, assuming that you have multiple files in the FG in question, what you should care about is:
1. Which file is on which drive?
2. Which drive has enough free space for all the data you want to be moving?
3. Select a file (in that FG) on the above drive and either allocate enough size manually or set it to autogrowth.
4. Temproarily set all other files in that FG to restricted growth.
5. start the shrinking, and it'll move all or at least most of the data to the file in step 3.

That has worked for me every time; for instance in one occasion I had inherited a DB on an old server with several small partitions/drives (added to the server during the time) and DB files exploded into dozens of micro-files accross all these mini-partitions. This way I've manged to reorganise DB into fewer files/FGs designed logically to allow for more efficient maintenance (for example piecemeal restore). I've also used this technique (together with alter index rebuild on ...) for separating non-clustered indexes from clustered indexes and the rest of the data in order to improve the performance.
0
marrowyungSenior Technical architecture (Data)Author Commented:
let me check check.

Update you very soon.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

As you explained the size of the data we are using, what I want to know is the CORRECT syntax of DBCC SHRINKFILE,

Is it DBCC SHRINKFILE (N'Primary_data' , 137792)
GO
in my case?

or
DBCC SHRINKFILE (N'Primary_data' , 1077)
GO?

This is what is shown from books online:

"USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

ALSO
I tried to restore one DB for testing and what I found is:

before shrink both mdf and log files:

Client_INGFUND      160      256      1      S:\DatabaseFiles\Client_INGFUND_SHRINKTEST\Client_INGFUND_SHRINKTEST.mdf
Client_INGFUND_log      15985      15985      0      S:\DatabaseFiles\Client_INGFUND_SHRINKTEST\Client_INGFUND_SHRINKTEST_log.ldf

After shrink:
Client_INGFUND      160      256      1      S:\DatabaseFiles\Client_INGFUND_SHRINKTEST\Client_INGFUND_SHRINKTEST.mdf
Client_INGFUND_log      16385      16385      0      S:\DatabaseFiles\Client_INGFUND_SHRINKTEST\Client_INGFUND_SHRINKTEST_log.ldf
So why the log is large after shrink ? data already coming in ?


DBA100.


0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

Why using alter index rebuild on  for separating non-clustered indexes from clustered indexes ? I think drop and create can be use, right?

can you show me an example on using the filegroup shrink together with alter index.

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

DBCC SHRINKFILE (N'MyDB_Data' , xxx, TRUNCATEONLY) can only apply to data files but not log file., that's why it has no effect to my log files,

What is the best way to truncate inactive log files?

DBA100;
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

I tried to shink the log file in this way:

backup log <database> with truncate_only;

it makes the usedPage much smaller, like 26 pages, but the totalPages doesn't become smaller, any reason why ?

the totalPage is now 15985 pages, which is aroud 124MB.

Anyway to make the usedPage = totalPages? This is the case of my mdf files, but not for Log files.

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

I found something interesting:

if I run this:
DBCC SHRINKFILE (Client_INGFUND_log, 3);
GO.

The result is
DbId             Field               CurrnetSize   MinimumSize  UsedPage     Estimated Page.
 33      2      15985      15985      15984      15984

But when I run this,
use MyDB;
Go
SELECT name, FILEPROPERTY(name, 'SpaceUsed') usedPages, size AS totalPages, groupid, filename  FROM  sysfiles WITH(NOLOCK);.

The result for the log is
UsedPage, TotalPages, Groupid                filesname
26      15985      0      S:\DatabaseFiles\Client_INGFUND_..

The UsedPage shows is different, but the result from DBCC Shrinkfiles (, 3); is correct but I tried to shrink the target size to 3MB, why the usedPage still 124MB?

DBA100.


0
ProjectChampionCommented:
Dear DBA100,
Sorry for delay in getting back to you...
Regarding Transaction log, the best way to keep the size under control is the back up. If your DB is using full recovery model, the log usually get's automatically truncated after transaction log backup and if it's using the simp[le recovery, after database backup. In either case the log is truncated after all the records within one or more virtual log files become inactive. However sometimes the truncation might be delayed for a number of reasons (please look up "Managing the Transaction Log" and "Factors That Can Delay Log Truncation" in BOL.

As for managing the phgysical size of the log and your comment:
>>DBCC SHRINKFILE (N'MyDB_Data' , xxx, TRUNCATEONLY) can only apply to data files but not log file
As I pointed out earlier, truncation doesn't reduce the size and if you need to retrieve the space you should shrink the LOG file using "DBCC SHRINKFILE" (Please look up "Shrinking the Transaction Log" in BOL).
If you can't see any any effect immediately, it's becuase as mentioned above, something is delaying the truncation of your log.
I'm too young to remember the this ; ) but there was a script around (I think originally published by SQL Server Professional Magazine in an article by Andrew Zanevsky) that would force shrink the log by producing lots of treansaction which would fill up the log and repeatedly rtuncate it until it was shrunk to/near the target size, but only worked in versions 7 and 2000, but nevertheless if you can find it on the net, it'll give you some idea about
how does shrinking the Log file work.

I would suggest you look up "Virtual Logs" in BOL; and please also consider the following info and best practices recommended by Microsoft:
"1. A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
2. Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
3. A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database."

In conclusion, if you have backed up your DB/T-Log (depending on the type of recovery model of your DB) and have tried the DBCC ShrinkFile, you have done all you should. As for the remaning unused space in your log file, don't worry, it isn't lost for ever. It's very likely that the next time you backup the log/db and shrink the log after an operation which produces a large number of transaction, it'll all be returned to OS.
0
Anthony PerkinsCommented:
>>backup log <database> with truncate_only;<<
FYI.  MS has finally dropped this command in SQL Server 2008 and is no longer allowed.
0
marrowyungSenior Technical architecture (Data)Author Commented:
acperkins,

Thanks for this update. then what command we should use to truncate log ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

As I metioned before, I have use "DBCC SHRINKFILE" but it doesn't works. Let me re-quote:
"
if I run this:
DBCC SHRINKFILE (Client_INGFUND_log, 3);
GO.

The result is
DbId             Field               CurrnetSize   MinimumSize  UsedPage     Estimated Page.
 33      2      15985      15985      15984      15984

But when I run this,
use MyDB;
Go
SELECT name, FILEPROPERTY(name, 'SpaceUsed') usedPages, size AS totalPages, groupid, filename  FROM  sysfiles WITH(NOLOCK);.

The result for the log is
UsedPage, TotalPages, Groupid                filesname
26      15985      0      S:\DatabaseFiles\Client_INGFUND_"

You can see that I have use the Shrinkfile command .

We all using Full recovery model and I don't know why the 2 x command above shows resulit differently.

Also we are not going to use Redgate as the backup the remove log/ truncate log is availlable when backing up the tranaction log, however, it gives the same result. the physical size of the log can reduce further but the mdf can, this is what totally different from what I saw before.

When I am doing the e commcerce, the log can be as small as 1-2 MB, but this time, the DB I shrink has no one use it, it give result like this.

I have read the BOL but it doesn't fix the issue.


DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

I also tried to run

DBCC SQLPERF(LOGSPACE);
GO

it shows that the DB I would like to shrink the log has log size of 124.875MB but Log space used (%) is 0.163, so there should be huge amount of space to free up, right?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

the shrink files is not going to cause the lost of data, right? as from the BOL:

DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB.

so is the total used page of the data is 100MB, I can't shrink file to 99MB, right?

DBA100.
0
ProjectChampionCommented:
Hi DB100,
You can be sure that DBCC Shrink File will not cause any data loss. You can even issue a target size of 0 without any risk of data loss in which case, if the file is not your primary data/log and there's another file in the same FG, all data will be moved to the other file; and if there's nowhere to move the data, it'll only shrink as far as it can, i.e. the actual space used and not beyound that.      
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

Thanks for this, please update me other the rest of my question.

how about if I don't set the Target_size? It will shrink as much it can?

DBA100.
0
ProjectChampionCommented:
Dear DBA100
If you don't specify the target size, DBCC ShrinkFile will reduce the file size to the default size for the file, which is the size of the file when it was created.

All you need to do is to backup your DB and transaction log and then execute the shrinkfile with the required target size. If after this it still doesn't get rid of all the unused space, it's because there is an active part of the log which is not at the beginning of your log file, therefore any unused space between beginning of the file and beginning of the active part of the log remains untouched - please have a look at the internal structure of the log and virtual logs in BOL.

You should be able to use DBCC LOGINFO(Your_DB_Name) command to find out where this active part is in your log file. Value 2 for the "Status" marks the active part and I bet the start offset is not somewhere very close to the the total size of your log file - hence why shrinkfile doesn't seem to be working.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

I think the size of the physical log needs to run the "backup log with truncate_only" first and then shrink using shrinkfile.

So this means:

1) shrinkfile can be done one log files
2) backup log with truncate only is good use with this command.

What do you think?

If you means" backup your DB and transaction log" by using SQL management studio, then this one doesn't works.

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
if the  backup your DB and transaction log"  phrase in SQL 2008, I am woundering how can we control the log size good.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I really are thinking BOL is wrong. I can't truncate log first then shrinkfiles for log. This means shrinkfile can work for log.
0
Anthony PerkinsCommented:
Please don't take this the wrong way, but if the data is important to you than you really should contract a reputable DBA to assist you.  They will be able to give you the hands on training you need in order to maintain SQL Server.

Good luck.
0
marrowyungSenior Technical architecture (Data)Author Commented:
acperkins,

I don't understnad what you mean? you are answering my question ?

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
acperkins,

What is your solutoin on this ? You seems has a lot of idea on this.

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

You are good helper, please help me next time and I respect your real help.

Give you point now.

DBA100.
0
ProjectChampionCommented:
DBA100, thanks for your kind comment, I'd be glad if I could be of any help to you or to anyone in the EE community. This is the whole point of EE; we're here to help each other by sharing what we know and save time and effort by learning from fellow EE users' experiences. I have personally learned ten times more than what I've contributed. : )
0
marrowyungSenior Technical architecture (Data)Author Commented:
yes, thanks, very helpful, you are helping me step by step. I am surpriseing that the BOL is wrong that LOG shrink is possible, but we have to truncate log first. amazing.... HAHHA
0
marrowyungSenior Technical architecture (Data)Author Commented:
ProjectChampion,

Any email address you use mostly? or MSN?

DBA100.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.