July 5, 2011 at 12:51 am
HI,
I have a databse having the ".mdf" file too large.
How can i reduce the size of the mdf file ?
July 5, 2011 at 12:55 am
JDBA-eqms (7/5/2011)
HI,I have a databse having the ".mdf" file too large.
How can i reduce the size of the mdf file ?
First why do want to reduce the MDF?
have you facing any space issue.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 5, 2011 at 12:57 am
Have you run out of disk space ?
You can run a shrink file using the DBCC shrinkfile command,
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
You could enable compression
Creating Compressed Tables and Indexes
You could put in place an archival plan
July 5, 2011 at 1:00 am
Hi,
I need to create a copy of that database on other Server.
I tried DBCC SHRINKFILE, but it doesn't shrink the file as there is no free space available in the mdf file.
July 5, 2011 at 1:04 am
JDBA-eqms (7/5/2011)
Hi,I need to create a copy of that database on other Server.
I tried DBCC SHRINKFILE, but it doesn't shrink the file as there is no free space available in the mdf file.
take backup and restore it with move where you have space(drive). thats it.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 5, 2011 at 5:18 am
July 5, 2011 at 5:31 am
Jayanth_Kurup (7/5/2011)
drop the indexes on the tables and run shrink database..........................then go ahead and perform your backup and restore followed by re creating all the indexes.
Please tell me you are joking here!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 5, 2011 at 5:34 am
This allows you to restore by mounting a db straight from the backup. Takes very little extra room on top of the backup size.
July 5, 2011 at 5:35 am
dont waste time dropping indexes to save space on the copy, as you will need the space again when you recreate them.
The simple way to move a database is to back it up and then restore it to the other server. If you need the file smaller for the transfer then compress it.
July 5, 2011 at 5:40 am
steveb. (7/5/2011)
dont waste time dropping indexes to save space on the copy, as you will need the space again when you recreate them.The simple way to move a database is to back it up and then restore it to the other server. If you need the file smaller for the transfer then compress it.
... And Virtual-Restore can do a restore even from the zipped version of the backup. So at this point you really don't have any excuses for being out of room!
July 5, 2011 at 11:50 pm
Ninja's_RGR'us (7/5/2011)
steveb. (7/5/2011)
dont waste time dropping indexes to save space on the copy, as you will need the space again when you recreate them.The simple way to move a database is to back it up and then restore it to the other server. If you need the file smaller for the transfer then compress it.
... And Virtual-Restore can do a restore even from the zipped version of the backup. So at this point you really don't have any excuses for being out of room!
These 2 responses are good advice, even if they will not solve your problem because the usual scenario is that the employer does not spend enough on resources that are not apparently essential, such as the excellent software from RedGate (my employer would not fund it either!) or extra disk for testing/troubleshooting (my problem as well).
Lessons?
1. be careful of following advice ... as in other life issues.
2. convince someone in charge how important it is to provide both tools and spare capacity
3. Look at a few other issues that may assist in the interim:
- What is this database Recovery Model? Is it (or can you change it to) SIMPLE? (ask first).
If it isn't, you have a few other issues to sort out first and be careful before doing a full backup as this will break the transaction log backup chain.
If it is Simple, taking a full backup will not only clean up transaction log space (checkpoint would do that ), but will create a .BAK file that only has data and no unnecessary transactions.
- compare the size of this BAK file with the size of the MDF. In my experience, this is a quick way to tell if your DB has grown too much.
If there's a huge discrepancy, you could truncate or reset the initial size and reset your autogrowth parameters anyway.
- If you now take a fresh backup, you may have gained enough to be able to do a restore on the other server. But it all depends on how well the recovery model + backup policy are set.
- don't do anything rash!
- as with all advice, take mine with a grain of salt as well !
July 6, 2011 at 1:57 am
Ol'SureHand (7/5/2011)
be careful before doing a full backup as this will break the transaction log backup chain.
No it doesn't, changing the recovery model from Full or Bulk Logged to Simple will!
Ol'SureHand (7/5/2011)
If it is Simple, taking a full backup will not only clean up transaction log space (checkpoint would do that ),
Checkpoint has various controls and one of them is to attempt to remove inactive transactions once the log is 70% full, so it is possible for checkpoint to occur and not find anything to do
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 6, 2011 at 2:11 am
Ol'SureHand (7/5/2011)
but will create a .BAK file that only has data and no unnecessary transactions.
what do you mean by this ?
July 6, 2011 at 3:31 am
Ol'SureHand (7/5/2011)
- What is this database Recovery Model? Is it (or can you change it to) SIMPLE? (ask first).If it isn't, you have a few other issues to sort out first and be careful before doing a full backup as this will break the transaction log backup chain.
Backups do not ever break the log chain, and I wouldn't consider being in a recovery model other than simple to be an 'issue'
If it is Simple, taking a full backup will not only clean up transaction log space (checkpoint would do that ), but will create a .BAK file that only has data and no unnecessary transactions.
The backup doesn't do that. The backup runs a checkpoint before it starts and that's what truncates the log, not the backup.
All backups contain some amount of log records, enough to recover the DB to a consistent point. That is no different in simple or full recovery.
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
July 6, 2011 at 3:36 am
Worst case scenairo, drop the indexes on the tables and run shrink database you should have quite some freespace after this. then go ahead and perform your backup and restore followed by re creating all the indexes.
I wasnt kidding , i did ask if hes run out of disk space. Infact I know this worked for another person where they had ) disk space free and were unable to do anything about it.
Anyway , its a worst case scenario , it didnt suggest it off the bat 😛 read my suggestions prior to this post they should be more to your liking.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply