Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-09-20 : 11:21:35
|
I have a data file size 5GB and Log file size 6.2GBthe size of back up file fot the database is 5GB and log file is 6GB..are the size of back up files going to be same as the actual file size? Also, I am doing a log back up every hourand the first log back up size is the same as the actual log file ( 6Gb), after that it's only 10~ 100kb.Can you explain to me why?Here is the log file info Size space usedApp 6251.242 1.017712 As you can see, the size of log file is 6.2 GB but only 1% of space being used... Why it tryin gto back up the whole log file even though there is only 1% of space has been used?? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-20 : 11:59:44
|
| BBecause backups weren't taken previously and the size of the log was not managed and it continued to grow. SQL Server does not reduce the size of the log automatically, and for good reason. It adds to the overhead and would impact performance.You need to look at DBCC SHRINKLOG and DBCC SHRINKFILE to reduce itBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-09-20 : 12:17:39
|
| Thanks, I guess my questio is that why the first log back up is so huge and after that it's fairly smaill.I am doing a transaction log back up every hour. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 12:21:03
|
| Everything that you have described is normal. The full backup will be about the size of the used space in the MDF file. The transaction log backup will be about the size of the used space in the LDF file. When you performed the first one, the used space was around 6GB. All of your future tlog backups will be smaller. Shrinking the files will not change these backup sizes as the size only has to be with space used and not space total.Tara Kizer |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-09-20 : 12:49:39
|
| thanks tara,When you said,"When you performed the first one, the used space was around 6GB". does this mean there was 6GB of log file during the first transaction log back up?why it says only 1% of spaced being used? is it because I am doing the hourly transaction log back up? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 12:55:52
|
| sp_spaceused can be inaccurate. Did you run the @updateusage option for sp_spaceused or DBCC UPDATEUSAGE?Tara Kizer |
|
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-09-20 : 13:07:08
|
| When I run sp_spaceusedon APP Database [coce] dataabse size unallocated spaceApp 11757.44 MB -271.38 MB[/code]why is the unallocaed space negative number? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-20 : 13:09:36
|
| Did you read my last post?Tara Kizer |
|
|
|
|
|
|
the size of backup file