The best practices for log file management dictate that you find the optimal size for your logfile. Meaning is your logfile grows from MB to 1GB in 1 days worth of work and you backup your log every hour and you have a nightly full backups, you would leave your log file at 1GB and backup it up every hour leaving it the same size and at night after your full backup, capture the tail of the log.
That will provide you a the best log file performance and b transaction logfiles for point in time recovery. My databases are set with recovery at FULL but the transaction log was never being reduced in size even though the DB's are backed up everynight.
I tried many things I had found on various search links that were almost invariably not successful. Here is what I finally found that works for me. For me the nice thing about this is that it returns the DB to the same Recovery that it is set to without me needing to remember to restore the option. Of course, when I run this I insure that no one is using the database and do a backup immediately before running it.
Thanks guys this thread has been very helpful. I ended up following Dennis' advice of performing a database log file backup, and running the code he provided to alter DB to simple, shrink, then alter it back to full.
I was able to do this while the DB was live. To continue this discussion, please ask a new question. Get answers from your peers along with millions of IT pros who visit Spiceworks.
I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. I found that the log size is MB and Log space used is There were some replication-related objects remaining in the database, even after removing the replication.
But it didn't work for us as replication was not active at the time and actually replication had been removed long before.
The solution was to import the database contents to another database using the import option of SQL Server. If there is a transaction or job blocking the action, use Activity monitor to identify the process and kill it, or use the SQL Agent job activity monitor to end the job.
Read How to Shrink SQL Server log for an explanation how the circular nature of the log may prevent shrink after truncation. Counter intuitively you must advance the log, by generating log writes, to allow it to shrink. I have found that I have to perform 2 or 3 backups of both the database and the transaction log to get the transaction log to actually reduce in size. I have a database that was created with Full recovery model.
Every night it performs backups of the database and the transaction log but inevitably the transaction log seems to continually grow over weeks. I followed the steps recommended by Microsoft and after the 4th or 5th iteration of backing up both the database and the transaction log the transaction log will finally release its extra space and shrink.
Then I go back and delete the multiple backups I have created. You need to create a backup first, dependent on the backup model that is set up for the database before you can shrink the database. Once you've backed up your database you can compress it. However, shrinking the database is not a good idea since heavy index fragmentation will occour and searching for data will become slow.
In my case it worked. After bringing DB online log was created automatically and it's size was kb instead of 70GB. But this is only a workaround.
The root problem is not resolved. In my case we are using replication. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
I backed up all databases and then shrunk them. How can I reduce the sized by a lot? Can I delete transaction logs, now that backups run? What would be the SQL command to use?
Perform a backup. By default, backup might not be set to simple. Put your DB in simple recovery mode, run dbcc shrinkfile, set it back to full or whatever and it should be much better. You will reclaim all that white space. By default, backup might not be set to full. Even if you truncate log files you won't get back the white space without shrinking the log. Dennis is correct if you never gave it a chance for log bloat that will definitely work.
Think of it similar to exchange and doing an offline defrag. You can shrink your DB size but you won't reclaim the space until you offline defrag exchange. Also Dennis is correct that setting your DB recovery model to simple can get you out of log bloat but some DBs need to be set to full and for recovery purposes you would want them to be.
While simple recovery will reduce any problems with the logs, it also eliminates any capability to restore the database to a point in time. You can only then restore the database to the last full backup. I'd be sure the business is on board with losing a day or more worth of data before you switch to simple recovery.
The recovery mode can be changed to simple by just right-clicking the DB and getting its properties, and making the change to the recovery mode and clicking OK. Their statements listed above will also effect this change. Is there a reason you don't just make a full backup and choose to truncate the logs as part of the backup's option?
If you backup up the log, run a checkpoint, and then backup the log again, you should end up with lots of free space in the files and then you can shrink them. I agree with this, if you only need the most recent data then it would be fine, if not the larger log files are needed. By doing this you set your recovery model to simple and never changed it back. If you are doing daily backups with log truncatiing, you should keep the log size down automatically, i would strongly suggest you use the full recovery model if this is a important DB.
Seriously, ask the business if it's ok that you lose ALL the data between one nightly backup and the next.
0コメント