| Author |
Topic |
|
AdrenalineSeed
Starting Member
16 Posts |
 Posted - 2008-06-09 : 14:34:01
|
| Hello,I am having trouble shrinking the log without losing data. If I backup the log, then shrink it, the data being written to the log between the backup and the shrink will be lost and the next backup will be missing a few seconds of data...How can I overcome this? Is there another way to backup a log and remove the entries without losing any data outside of the backup? The databases are in use all the time. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 14:36:58
|
| You will not lose any data when you shrink the database or its files. You should not be shrinking anything after a transaction log backup. You are causing a performance problem. During the transaction log backup, it backs up all of the committed transactions and then removes them from the transaction log.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 14:40:47
|
Hi t,I am finding that after a transaction log backup the size of the log still remains the same. After a shrink it goes back down to 2Meg, I need the log to always stay at 2Meg so that more server HDD space is not taken up beyond the backup of the log.quote:
Originally posted by tkizer
You will not lose any data when you shrink the database or its files. You should not be shrinking anything after a transaction log backup. You are causing a performance problem. During the transaction log backup, it backs up all of the committed transactions and then removes them from the transaction log.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 14:48:31
|
| That is correct that the transaction log file after a log backup will remain the same. But inside the file is free space now.Add more disk space as you are creating a performance problem with your current strategy. 2 MB is tiny for a transaction log!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 15:53:17
|
So the transaction log will not grow if I continue to back it up every day?If it happens to grow by 10 Gigs one day how would I get it back down to a reasonable amount? I do not want it reserving 10Gigs of HDD data for "free" space. No matter how many HDDs I add I will always have limited space.Surley shrinking the log to 2 Megs will lose data. Say I have 4 Megs of data in the log, it must remove at least 2 of those Megs in order to have a 2 Meg log. Does it only remove the oldest entries?quote:
Originally posted by tkizer
That is correct that the transaction log file after a log backup will remain the same. But inside the file is free space now.Add more disk space as you are creating a performance problem with your current strategy. 2 MB is tiny for a transaction log!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 16:04:06
|
| I can't guarantee that it won't grow. You should be backing up your transaction log much more frequently than just once a day. We backup ours every 15 minutes. Not only does this help manage the log file size, it also gives you better recovery options. Shrinking the logs will not lose any data. You can not shrink the log down smaller than the used space.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 17:04:53
|
Every 15 minutes?! Doesn't that exceed the maximum of 64 backup devices appended to a file when you are trying to restore the log?quote:
Originally posted by tkizer
I can't guarantee that it won't grow. You should be backing up your transaction log much more frequently than just once a day. We backup ours every 15 minutes. Not only does this help manage the log file size, it also gives you better recovery options. Shrinking the logs will not lose any data. You can not shrink the log down smaller than the used space.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 17:07:24
|
| Yes every 15 minutes. Most people do every 10 or 15 minutes or hourly transaction log backups. If you don't understand why you would need to backup this often, then you need to take a step back and understand what transaction log backups are used for. Why would you append backups together? We backup our logs and databases to individual files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 17:26:36
|
Seems easier to put them all in one file, so that when I go to restore the database I can select the one .bak file and select to restore all the logs backed up into it. I need to be able to restore quickly and I am going to be restoring logs on a regular basis. I also don't see an option to backup to seperate files, what would they be called? I only see overwright and append.quote:
Originally posted by tkizer
Yes every 15 minutes. Most people do every 10 or 15 minutes or hourly transaction log backups. If you don't understand why you would need to backup this often, then you need to take a step back and understand what transaction log backups are used for. Why would you append backups together? We backup our logs and databases to individual files.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-09 : 17:31:11
|
| Use the overwrite option to write to separate files. Apparently you are using the GUI to do your DBA tasks such as BACKUP and RESTORE. It is very easy to restore thousands of backup files if you don't use the GUI.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 17:43:41
|
Last time we talked about scripting I got overwhelemed. I guess I am just looking to do this the way Microsoft intended through their interface so that there is no confusion or problems.I simply need to get an updated copy of the databases over to our site. However I can't backup the database because that would create a file that contains 10 years of client history. Sending all that information over the SLOW internet is impossible. I just need one day or one week or one month worth of usage.I figured if the server was backing up the log file everyday I could just go grab that one little file to quickly restore the changes on our servers. Then tell it to start a new file or something.  quote:
Originally posted by tkizer
Use the overwrite option to write to separate files. Apparently you are using the GUI to do your DBA tasks such as BACKUP and RESTORE. It is very easy to restore thousands of backup files if you don't use the GUI.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AdrenalineSeed
Starting Member
16 Posts |
Posted - 2008-06-09 : 18:04:24
|
.. Looking into this, never heard ofit.quote:
Originally posted by tkizer
Why don't you just use transactional replication? You can even filter the data with it.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
|
|
|
|
|
Problem with Shrinking log and losing data