November 1, 2010 at 2:53 am
Hi,
I have got a production database about 50GB and a transaction log 21GB
I did DBCC shrinkdatabase and DBCC shrinkfile on both database and the transaction log- files
This gave me a reduction of 40GB on the mdf file and 6.5GB on the ldf file
After 24 hours, everything were back to as it was before.
I am not a DBA, but I dont think the database has so much information that it can fill 50GB space.
What can I do to reduce the size of the database? As you know, we have a maintenance job and that creates another 70GB backup files not to mention the hourly transcation backup jobs.
Thanks
Bash
November 1, 2010 at 4:21 am
Sais (11/1/2010)
Hi,I have got a production database about 50GB and a transaction log 21GB
I did DBCC shrinkdatabase and DBCC shrinkfile on both database and the transaction log- files
This gave me a reduction of 40GB on the mdf file and 6.5GB on the ldf file
After 24 hours, everything were back to as it was before.
I am not a DBA, but I dont think the database has so much information that it can fill 50GB space.
What can I do to reduce the size of the database? As you know, we have a maintenance job and that creates another 70GB backup files not to mention the hourly transcation backup jobs.
Thanks
Bash
1.What is the database recovery model?
2.Why you didn't take t-log backups?
3.Is there any active transation running?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 1, 2010 at 9:05 am
@ sais
Which is hte recovery model for your database and also
did you perform any maintenance jobs before your file sizes again increase?
When was the last time you perfomed a backup log transacton file?
Regards,
Sushant
Regards
Sushant Kumar
November 1, 2010 at 1:03 pm
Did you release the unused space after you shrunk the database? Also, I hope you rebuilt all of your statistics and indexes as they are now completely out of whack and adding a great amount of usage to the CPU while any query is being ran.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 2, 2010 at 1:56 am
Database Recovery Model = Full
Yes, Transaction log backup are being taken via Maintenance job and its now down to less than 36MB
But the Data file is still between 40 and 50 GB
How can I release the unused space after shrinking the database file (MDF) ?
Thanks
Bash
November 2, 2010 at 2:27 am
Sais (11/2/2010)
Database Recovery Model = FullYes, Transaction log backup are being taken via Maintenance job and its now down to less than 36MB
But the Data file is still between 40 and 50 GB
How can I release the unused space after shrinking the database file (MDF) ?
Thanks
Bash
You can use the shikfile(TRUNCATEONLY) option to release the unused space.
First why do you want release the unused space ?
What is the auto growth setting?
Is you db is readonly?
Did you face any disk space problem ?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 2, 2010 at 3:45 am
Hi,
I dont think my MDF file (46GB) is actually holding that much information.
Why? Cause my Recovery Model is FULL and my LDF file is now under 5MB
First why do you want release the unused space ?
What is the auto growth setting?
Automatically grow file (10%)
Is you db is readonly?
No
Did you face any disk space problem ?
Yes, at the moment. In the feature, no since it's gonna be moved to a NAS storage
But, the problem will still remain then if its growing abnormally all the time.
Thanks
Bashir
November 2, 2010 at 4:12 am
Sais (11/2/2010)
Hi,I dont think my MDF file (46GB) is actually holding that much information.
Why? Cause my Recovery Model is FULL and my LDF file is now under 5MB
First why do you want release the unused space ?
What is the auto growth setting?
Automatically grow file (10%)
Is you db is readonly?
No
Did you face any disk space problem ?
Yes, at the moment. In the feature, no since it's gonna be moved to a NAS storage
But, the problem will still remain then if its growing abnormally all the time.
Thanks
Bashir
Bashir
What is the auto growth setting?
Automatically grow file (10%)
Change the autogrowth to MB .
Is you db is readonly?
No
Did you face any disk space problem ?
Yes, at the moment. In the feature, no since it's gonna be moved to a NAS storage
But, the problem will still remain then if its growing abnormally all the time.
IMO:
Your database is R/W then definitely it ll grow in feature,so
Try to move the files to another drive if possiple.
--As i already mention Look the BOL and use the shrink option if you want remove the unused space.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 2, 2010 at 7:03 am
@ sais
How much free space does your mdf file contain?
Shrinking is not a good method as the database is not read only and so again it will increase,
If you have real space constaint, then you can go ahead and shrink the mdf files.
Are you running any maintenace plans like rebuilding indexes for the database you mentioned?
Regards,
Sushant
Regards
Sushant Kumar
November 2, 2010 at 7:51 am
No maintenance plan for rebuilding the indexes.
I have tried to shrink the MDF files without any progress.
I used both DBCC shrinkdatabase(MyDB, TRUNCATEONLY) and also DBCC Shrinkfile(MyDB,20480)
However, shrinking the Transaction Log files went well, and it is decreased from 21GB to 7MB
// Bash
November 2, 2010 at 7:53 am
If you have never run a backup of the Database (I bet you have not) you will need to do this first.
After that you can either do a real Tran Log Backup, or one that just truncates the Tran Log. Both methods are in the books online. The key here is to make sure the Tran Log is truncated so that it can be shrunk. You may need to do this twice.
Now DBCC will shrink the Tran Log files for you and remove the unsed space. After this is done, do a defrag of your hard disk.
To keep it from happening in the future, Set-up some hourly or at least Daily tran log backups. If you Tran Log hourly, create nightly Full backups, if you Tran Log daily, weekly should be fine.
Plenty of information in the SQL books online about shrinking a Transaction Log or DB that is out of control.
If you have access to the internet, go to the TechNet web site, search on DB Maintenance Plans, make one.
November 2, 2010 at 7:56 am
sais
please post the results of the following query executed against your database
select[FileSizeMB] = convert(numeric(10,2)
, round(a.size/128.,2))
, [UsedSpaceMB] = convert(numeric(10,2)
, round(fileproperty( a.name,'SpaceUsed')/128.,2))
, [UnusedSpaceMB] = convert(numeric(10,2)
, round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))
, [DBFileName] = a.name
from sysfiles a
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 2, 2010 at 7:59 am
Like the other posts, I would not be so quick to reduce the .mdf size, but if you are certain of any pending growth and are limited on space, to free up the unused space within the database do the following:
In SSMS | expand Server | Expand Databases | right click on the database | select Task | Shrink | Files | confirm File type is Data and select Release unused space from the Shrink action section
**** - Also, please make sure you rebuild your indexes and statistics when this is completed!!!!
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
November 2, 2010 at 8:11 am
chris.s.powell (11/2/2010)
please make sure you rebuild your indexes and statistics when this is completed!!!!
Problem is, this just causes the database to grow again!! All that I\O to shrink is now compounded by an index rebuild and the file growth.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 2, 2010 at 8:16 am
I believe I mentioned the processor hit in my first post and in both of my posts I strongly suggested that the individual think twice before performing any type of shrink. As for the rebuild index, it will be recorded in the log file, with a proper backup plan in place, that space will be freed after it is completed.
By my posting, I was only trying to give the user what they were wanting, but I made certain to point out drawbacks to this plan. We as posters, can only make suggestions and remind individuals of their decisions.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply