Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL server 2005 Backup & restoration size


SQL server 2005 Backup & restoration size

Author
Message
T.LOGANATHAN
T.LOGANATHAN
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 119
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
T.LOGANATHAN
T.LOGANATHAN
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 119
Dear Friend,
Thanks for your suggsstion...
regards

T.Loganathan
pankushmehta
pankushmehta
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 4106
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.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6241 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36180 Visits: 18751
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
pankushmehta
pankushmehta
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 Visits: 4106
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 ?
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6241 Visits: 10403
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17609 Visits: 32267
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search