February 3, 2011 at 11:10 pm
Hi,
For shrinking the database you can use the below command
BACKUP LOG "database name" WITH TRUNCATE_only
DBCC SHRINKFILE(logical name of the log file ,1,TRUNCATEONLY)
February 4, 2011 at 6:24 am
Hello!
I am interested in shrinking the size of the backup file ( not the log file ).
Your command looks like it is going to shrink the transaction log.
February 4, 2011 at 6:29 am
mw112009 (2/4/2011)
Hello!I am interested in shrinking the size of the backup file ( not the log file ).
Your command looks like it is going to shrink the transaction log.
The only way to shrink the backup size is to delete data and take a backup of the tlog. The only option left is to use some sort of compression (zipping, 3rd party backup tools, or compression available in 2008 enterprise and 2008 R2 Standard).
As far as "compressing" the restore, the only way to do that is shrink the db before the backup is taken. The restore will use the info for the data file sizes wether the space is truly required or not.
February 4, 2011 at 6:42 am
Ninja's_RGR'us (2/4/2011)
mw112009 (2/4/2011)
Hello!I am interested in shrinking the size of the backup file ( not the log file ).
Your command looks like it is going to shrink the transaction log.
The only way to shrink the backup size is to delete data and take a backup of the tlog. The only option left is to use some sort of compression (zipping, 3rd party backup tools, or compression available in 2008 enterprise and 2008 R2 Standard).
As far as "compressing" the restore, the only way to do that is shrink the db before the backup is taken. The restore will use the info for the data file sizes wether the space is truly required or not.
Why take a backup of the transaction log ? I do not need the deleted data any more.
May be I did not explain clearly. I have a big database ( a copy of a prod database with 5215 tables ).
The plan is to only keep 10 customers and delete all other data ( which I can do ).
Then someone suggested that I rebuild the indexes ( This will reduce the size of indexes ).
Then I plan to make a backup file ?
I want the size of the backup file to be very small.
Next steps:
1. Move the backup file to a DEVLOPMENT Server.
2. Do a restore
The issue here is our development server does not have enough hard drive space. So that it why i had
to ask everyone how to reduce the size of the database ?
Hope I explained clearly
February 4, 2011 at 6:49 am
If you have active transactions in the system you won't be able to shrink the log, hence you need to back the tlog to allow the shrink (especially after a big purge like you are doing).
Here's the steps you need.
Backup prod
Restore copy on server with enough hd space.
move to simple recovery
dump the data
checkpoint
shrink the whole db (make sure the files have as little free space as possible, you shoul dbe able to dump 50-99% of the filesize)
then either detach, move, attach
or backup, move, restore, delete useless copy.
February 4, 2011 at 7:08 am
Ninja's_RGR'us (2/4/2011)
If you have active transactions in the system you won't be able to shrink the log, hence you need to back the tlog to allow the shrink (especially after a big purge like you are doing).Here's the steps you need.
1.)Backup prod
2.)Restore copy on server with enough hd space.
3.)move to simple recovery
4.)dump the data
5.)checkpoint
6.)shrink the whole db (make sure the files have as little free space as possible, you shoul dbe able to dump 50-99% of the filesize)
then either detach, move, attach
or backup, move, restore, delete useless copy.
Thanks. I got a few more questions
Steps 1,2,3 are simple
Step 3
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE
Step 4 - dump data
You mean delete the data then rebuild each index ( I can certainly do that )
Step 5
what does checkpoint mean ? Not sure about this ?
Step 6
How do you do that ( What would be the commad ? )
Step 7 - Make a new backup usng the "BACKUP DATABASE... " ( I can do that )
Before I do this don't I have to set the recovery mode back to full ?
Hopefully the size of this backup file should be a few gigabytes.
Step 8
Restore the backup file in step 7 to the development server
DO I Have to set the recovery mode to full before I do this ? Please explain.
Forgot to mention...
Do not worry about active transactions at this time. I am not interested in any active
trasnactions. The cope of this project is COPYING the prod database, reduce size and then restore in a DEVELOPMENT server
Thanks a lot.
February 4, 2011 at 8:12 am
checkpoint is a command, look it up in books online.
to shrink the whole db :
USE [PRODDB_restored]
GO
DBCC SHRINKDATABASE(N'PRODDB_restored')
GO
February 4, 2011 at 8:14 am
rebuilding the indexes is a maybe here... if you only keep 10 clients worth of data, you might not need to with so little selectivity.
February 4, 2011 at 8:19 am
"...Before I do this don't I have to set the recovery mode back to full ?..."
You may not need FULL recovery model for your DEV database. That's up to you.
Here's some good info in Gail's article: http://www.sqlservercentral.com/articles/64582/
February 4, 2011 at 8:25 am
1.) Ok, I know what a checkpoint is (thanks )
2.) Also know how to shrink the entire database using the command you mentioned ( Will this take care of shrinking the transaction log ? I hope so )
3.) Can you please explain why you need to switch recovery modes ( What is the advantage )
Thanks
February 4, 2011 at 8:27 am
mw112009 (2/4/2011)
3.) Can you please explain why you need to switch recovery modes ( What is the advantage )
The transactions don't need to be log in most dev environements... save on maintenance and hardware needs.
February 4, 2011 at 8:36 am
Ninja's_RGR'us (2/4/2011)
mw112009 (2/4/2011)
3.) Can you please explain why you need to switch recovery modes ( What is the advantage )The transactions don't need to be log in most dev environements... save on maintenance and hardware needs.
I wont be having any trasnactions ( While I am using the restored copy in the intermediate server which i am using just to make the small backup file. The only thing i am doing is deleting records and rebuilding indexes. I don't need the deleted data.)
Hmm.. I think I know what you mean. When you are in "SIMPLE" recovery and if I do the mass deletions, those trasnactions will not go to the transaction log and therefore the size will not grow.
Please confirm..
Thanks
February 4, 2011 at 8:41 am
Well yes and know. The log can grow during the delete. Let's say you delete a table with 10 GB of data pages, then the log will grow as such to accomodate the transaction.
Once the delete is done, you should be able to shrink it right back to a couple mbs (as you see fit).
February 4, 2011 at 8:49 am
You should probably take a step back and look at how much space the database files are using, and find the size of the individual tables in the database. You can find that information using the script on the link below.
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
If you need to shrink the database data files, you can use this script:
Shrink DB File by Increment to Target Free Space
February 4, 2011 at 9:25 am
Michael Valentine Jones (2/4/2011)
You should probably take a step back and look at how much space the database files are using, and find the size of the individual tables in the database. You can find that information using the script on the link below.Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
If you need to shrink the database data files, you can use this script:
Shrink DB File by Increment to Target Free Space
Hello
I have 5212 tables. I don't think I can analyze table by table ?
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply