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 |
|
Paul279
Starting Member
3 Posts |
 Posted - 2014-11-20 : 05:20:31
|
| Hello community,I have googled many hours to find solutions to my questions but I am not really satisfied.So starting with my questions:1. I have a database with the recovery type "Full". Now of course my database_log file gets bigger an bigger. Therefore I planed a Backup. Every night a full backup. Up to 15 o`clock transaction backups. 15 o`clock a differential backup and than after 15 o`clock again transaction backups until midnight, than I have again the full backup.So my understand until yet was that the database_log file gets only bigger when you do not make updates. But I do regularly and the logfile of the database doesn`t get smaller. For example my database is 3GB in size. But my Logfile of this database is already 32GB big.What i am doing wrong?2. When I have it understood right, than I need always the tail log to restore any differential or transaction logs after a crash. Okay the first thing here is: Where can I backup this tail log. I have searched around in Microsoft Studio 2008R2, but I cannot find it.3. I copy all my backups to a different machine. Now my machine is completely broken. Imagine a fire in the serverroom. How can I then restore my data on a different SQLServer when I don`t have the tail log of the old machine?Thanks for explanationGreetingsPaul |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-11-20 : 11:31:50
|
| 1. Sounds like your transaction log file grew to that size before you started backing it up. Backing up the tlog does not shrink the file down. It only frees up space inside the file. Do a one-time shrink of the ldf file to reclaim the space. You can use DBCC SHRINKFILE. I would suggest a 1GB starting point, but the file size it'll end up being is dependent upon how often your tlog is backed up and how big your largest transaction is. Often times, it'll be dependent on an index rebuild job so you'll need to plan for the large index size.2. It only matters when it comes time to do a restore. Backing up the tail just means doing a final tlog backup prior to starting your restore, that way you get the tail of the file too.3. You can still restore without the tail of the log. You'll just have data loss. How much data loss is dependent upon how often you backup the tlog. It used to be said to backup the tlog every 15 minutes. Nowadays, it's becoming more common to back it up every 1-5 minutes so that you have as little data loss as possible in the case of a recovery.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Paul279
Starting Member
3 Posts |
Posted - 2014-11-20 : 21:01:28
|
| Thank you Tara for your answer1. My db is now really only about 1GB in file size. :-)But my question is did I make it right?First I tried: only a shrink on the tlog. Did not do anything.After that I tried it with setting the database in simple mode shrink the database and set the database again in full mode. Is this a good behaviour? Because when I looked by google around, I saw somebody speaking about to get the db_log smaller. He said that shrinking a database is very unsecure and should not be done. So, did I do it now right?3. Okay I will reconfigure my transaction logs, because I have transaction logs every 2 hours.Thank you |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-11-21 : 12:51:50
|
| 1. I don't recommend changing the recovery model as that breaks the transaction log chain and limits your point-in-time recovery points. If you ever have to do it, immediately run a full or differential backup to start the chain again. Whether or not you did it right, I don't know. I would need to see your code.Shrinking a database has NOTHING to do with security.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Paul279
Starting Member
3 Posts |
Posted - 2014-11-22 : 05:13:42
|
| Thank you Tara for your help. I got my informations that I wanted |
|
|
|
|
|
|
|
|
3 questions regarding Logfiles and Tail Log