February 26, 2008 at 9:37 am
i have a very strange situation:
recently, i installed a new server (SQL2k) for an application; for their application database (running in full logging mode), i set up the same maintenance plan and jobs.
this involves backups of the transaction log and afterwards a DBCC Shrinkfile on the transaction log.
on the new server, the transaction log backups are a lot bigger than on the old installation, while application people claim that the application on the new server is not yet doing anything (while its working normally on the old server)
now, i found out, that the log backups have a much more reasonable size, when don't run the DBCC Shrinkfile - job - so does this really generate a lot of data in the logfile to shrink the logfile??
(and, why does this not happen on the old server?)
im quite confused... does anyone know about this?
February 26, 2008 at 5:20 pm
Is it possible for you to follow the below steps:
1. Change the recovery to simple
2. Take a complete backup and then shink db/truncate all the free space from all file (including log)
3. Start back your normail plan (Log backup and followed by shrinkfile)
Hope it will resolve this issue
Regards
Utsab Chattopadhyay
February 26, 2008 at 5:31 pm
why shrink the files at all? Leave the log files to be whatever size they need to be between backups. Continuously truncating them (which then forces them to grow again) doesn't really serve a purpose, other than possibly encourage disk/OS-level fragmentation (never a good thing).
If anything - if the logs get to be too big between backups - increase the frequency of your backups.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 27, 2008 at 8:23 am
i did as proposed: changed the database to simple mode, shrinked the whole database , switched back to full recovery mode.
then, for a while everything was ok... (and nothing did happen on the database)
but the log shrink jobs did not really do anything because the logfile did not grow.
then, i initiated some work on the database, which caused the logfile to grow; after that the transaction log backups are again bigger, but have all the same size as long as nothing more happenes on the database - and the log shrink jobs actually shrink the log everytime.
so, the log shrinking actually generates a lot of transaction log entries?
February 27, 2008 at 8:13 pm
I do not think shrinkfile command generate a large size of transaction. But some commands may generate a large size of log file, such as manipulating indexes.
Are you sure there is no other application running against your database?
Is there any replication or log shipping related to your database?
All of the above may affect your log file.
February 27, 2008 at 9:39 pm
I also aggreee... shrink command does not build log by itself...
At this point... I will suggest to run a trace to identify what exactly happening with the db... This will help us to understand the root cause.
Also, if you have some log reader tool.. it is a good idea to read the log. This will give us an idea who is writing what to the log.
Regards
Utsab Chattopadhyay
February 28, 2008 at 4:48 am
well, i am sure that no one else is doing anything on the db, because to do all these tests, i made myself a copy of the original database (and the jobs working on the db), where no other users have access.
but still, transaction log backups are for now at least 528 kB while nothing (except for the DBCC SHRINKFILE command) is running on the DB.
i did a trace on that DB (the only filters were to limit it to the proper DBID and to exclude stuff concerning SQL Profiler) - and there was nothing in between the two mentioned jobs... (except for some Lock:Acquired / Lock:Released by background sessions).
unfortunately, i don't have any tools here to look into the transaction logs - that would maybe answer the question...
February 28, 2008 at 9:55 am
Your log file size is only half of 1 MB. It is quite small. SQL Server reserve a minimun size for a log file size, set in the database model.
February 28, 2008 at 10:35 am
With log files that small - of course they have to grow every time "someone is doing something on the DB". Log files record (even if temporarily) what is being changed on a DB, so activity = some amount of space for the log to record the change.
Even during SIMPLE recovery mode -the activity is logged to the T-log while the update is occurring. Once the change is committed the transaction is truncated, and the space reused.
It's not ENTIRELY unusual to see transaction logs several times larger than the database itself.
Your log files are starved for space - you need to stop trying to keep them so small. The database needs the log to do work, so give it some room. As a matter of fact - give it LOTS of room. Your log backup sizes don't have much to do with the physical size of the transaction log file on the OS.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 29, 2008 at 1:24 am
well, the numbers i mentioned on the previous post were of course from a test database where really nothing was happening an all the users are locked out;
the reason i started looking into this however is the 'real database', that 'produces' tens of gigabytes of transaction logs a day while application people claim that the application does not do anything (it's not in production yet), so that's maybe more of a problem?! it is also strange, that all the transaction logs have exactly the same size, except if really something happens on the db.
and so i have to explain to the application people, why they need tu buy such a lot more diskspace for transaction log backups than on the 'old' server, where the previous version of the application is running.
also on the 'real' database, i did some tracing, but could not really find such a lot of activity that would create transaction logs that size...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply