Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Backups » SQL server 2005 Backup & restoration size
Add to briefcase

SQL server 2005 Backup & restoration size Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
T.LOGANATHAN
Posted Monday, August 30, 2010 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 25, 2010 2:53 AM
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
Post #977267
Grant Fritchey
Posted Monday, August 30, 2010 7:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #977327
T.LOGANATHAN
Posted Tuesday, August 31, 2010 3:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 25, 2010 2:53 AM
Points: 18, Visits: 119
Dear Friend,
Thanks for your suggsstion...
regards

T.Loganathan
Post #977783
pankushmehta
Posted Tuesday, August 31, 2010 5:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 29, 2011 9:01 AM
Points: 211, Visits: 4,106
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.
Post #978399
WayneS
Posted Tuesday, August 31, 2010 6:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 6,386, Visits: 8,288
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
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
Post #978421
Steve Jones - SSC Editor
Posted Tuesday, August 31, 2010 6:32 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864
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
Post #978422
pankushmehta
Posted Tuesday, August 31, 2010 6:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 29, 2011 9:01 AM
Points: 211, Visits: 4,106
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 ?
Post #978424
WayneS
Posted Tuesday, August 31, 2010 6:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 6,386, Visits: 8,288
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
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
Post #978425
Grant Fritchey
Posted Wednesday, September 01, 2010 5:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #978673
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.