August 30, 2010 at 6:44 am
Hi friends,
Can any one give the suggestion regarding SQL server 2005 Backup & restoration size.
My DB size is -> 288347 (MDF) bytes (288 MB)
-> 13752896 (LDF) '' (13.7 GB)
While taking the full backup through SQL Agent jobs, its taking around 1.5 GB only.
My question is , There is no more space in the server and If I create same db with another name its occupying same size what i have mentioned above. How to reduce tran. log file to solve this issue ?
regards,
T.Loganathan
my email id : logu.t@nic.in
August 30, 2010 at 7:57 am
Is your database set to Full Recovery mode? If so, you need to be backing up the log files too.
When you back up a database, it backups up the data and the definitions of the database. Part of the definition is how much space the database has been allocated. If your log files are huge AND empty, then you may need to shrink the file, prior to the backup. Then, the backed up database will have a smaller file definition. However, shrinking files can be extremely problematic and should only be approached when you know it's safe. You can make a mess of your system by shrinking files too often or inappropriately. Usually, assuming you are in Simple recovery or Full recovery with log backups in place, the log file is the size it needs to be (assuming auto file growth) to support the transactions on your system. If you are in Full recovery without log backups, the log file will just keep growing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2010 at 3:24 am
Dear Friend,
Thanks for your suggsstion...
regards
T.Loganathan
August 31, 2010 at 5:13 pm
If your database is in full recovery mode, you can backup the Transaction log and then shrink the file.
If your database is in simple recovery mode, you can just shrink the log file.
However, also keep track of the size of the transaction log file initial size value. if this is set to too high, you will not be able to shrink your file below this size. you should reduce this limit first.
August 31, 2010 at 6:25 pm
pankushmehta (8/31/2010)
If your database is in full recovery mode, you can backup the Transaction log and then shrink the file.If your database is in simple recovery mode, you can just shrink the log file.
However, also keep track of the size of the transaction log file initial size value. if this is set to too high, you will not be able to shrink your file below this size. you should reduce this limit first.
Please see what Grant said above:
However, shrinking files can be extremely problematic and should only be approached when you know it's safe.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 31, 2010 at 6:32 pm
I like Grant's suggestion. Don't shrink unless you have to, and then only shrink to the size it should be based on transactions. Make sure you have log backups going.
If development or test systems are an issue, Idera makes Virtual Database and Red Gate makes Virtual Restore, which can let you mount a backup file as a database.
August 31, 2010 at 6:33 pm
pankushmehta (8/31/2010)
If your database is in full recovery mode, you can backup the Transaction log and then shrink the file.If your database is in simple recovery mode, you can just shrink the log file.
However, also keep track of the size of the transaction log file initial size value. if this is set to too high, you will not be able to shrink your file below this size. you should reduce this limit first.
I agree, however the question posted was
My question is , There is no more space in the server and If I create same db with another name its occupying same size what i have mentioned above. How to reduce tran. log file to solve this issue ?
August 31, 2010 at 6:34 pm
T.LOGANATHAN (8/30/2010)
Hi friends,Can any one give the suggestion regarding SQL server 2005 Backup & restoration size.
My DB size is -> 288347 (MDF) bytes (288 MB)
-> 13752896 (LDF) '' (13.7 GB)
While taking the full backup through SQL Agent jobs, its taking around 1.5 GB only.
My question is , There is no more space in the server and If I create same db with another name its occupying same size what i have mentioned above. How to reduce tran. log file to solve this issue ?
regards,
T.Loganathan
my email id : logu.t@nic.in
Since you have such a (relatively) small database, and such a HUGE log file (I run a 7gb data file with a 1.5gb log file), I strongly suspect that your database is in the full recovery mode, and that you're not taking transaction log files. This will cause your log file to swell to enormous levels as compared to your data, as you are exhibiting.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 1, 2010 at 5:55 am
pankushmehta (8/31/2010)
pankushmehta (8/31/2010)
If your database is in full recovery mode, you can backup the Transaction log and then shrink the file.If your database is in simple recovery mode, you can just shrink the log file.
However, also keep track of the size of the transaction log file initial size value. if this is set to too high, you will not be able to shrink your file below this size. you should reduce this limit first.
I agree, however the question posted was
My question is , There is no more space in the server and If I create same db with another name its occupying same size what i have mentioned above. How to reduce tran. log file to solve this issue ?
I think Wayne has hit the answer. You're in full recovery and you're not running log backups. I'd fix that first. Then, you can shrink the log file to a more appropriate size, take a backup and then restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply