Query about keeping down the size of transaction Logs

Hi I have been struggerling with a problem with the transaction log for a while now and find I'm just getting myself in more of a mess.

My main aim is to get around the problem that the transaction logs just keep getting bigger and bigger.

Now I have look at a few ways around it firstly I tried to use the commands:

BACKUP LOG db_name WITH TRUNCATE_ONLY
DBCC SHRINKFILE (log_name,2)

and this worked really well to reduce the size to a manageable level, however I only just found out the real impact that it has on my recovery ability.

The other solution I have looked at was to backup the database and then to use this command to backup the Transaction Logs and clear them down.

BACKUP LOG db_name
TO Disk = 'C:/example.TRN'
WITH INIT;

However after testing this it doesn't seem to reduce the size of the transaction Logs at all.

It may be a problem with my understand of the way the transaction log works, I persume that when you have a good backup then to get a point in time recovery all you should need is the details of transactions that have happened past the point of the backup and therefore be able to somehow perge the log of all other data. And that is what I persummed the last command I tried would do. However the .LDF file remains the same size.

Am i doing something wrong am I missing something out?

Thanks for any help! EdHowdy

You are on the right track, here are some ideas :

When you take a full backup, it resets all the counters for backups and any tran log backups before the last full backup cant be used after it. OK, so if you want to do a point in time recovery, do a full backup then do tran log backups after that.

What you can do ( during after hours as then you have no activity in the database to cause problems) is this :

(1) start up Query Analyser and connect to the database
(2) execute the command CHECKPOINT
- this will write all inactive entries in the tran log to the database and give you a know starting point for size reduction.

(3) run the command :
DBCC SHRINKFILE('log_file_logical_name',10)

This will attempt to shrink the tran log to 10 MB. This *should* remove all the empty space from the tran log.

****Alternatively ( and this is a big-ish step so make sure no-one is on the database when its happening as while this is going on, you WILL NOT have database recovery capability and will have to go back to your last full backup if problems occur ) :

(1) Take full backup of the database & keep safely to one side ( just in case ) and label it as "BEFORE_<DB_NAME>_SHRINK.BAK" or similar.

(2) Change database into SIMPLE recovery mode. This will allow maximum shrinkage of the tran log. This is the old "truncate log on checkpoint" setting from SQL 6.5/7 .

(3) Open Query Analyser on the database & execute CHECKPOINT .
Then execute :
DBCC SHRINKFILE('<db_data_file_logical_name>',10)
DBCC SHRINKFILE('<db_log_file_logical_name>',10)

(4) Change the database back to FULL recovery mode.

(5) Take a full backup - VERY IMPORTANT. Note that this is the reference point that tran log backups will use if you need to recover the database.

This will be the case untill you do your next normal scheduled full backup, at which point that full backup will be the starting point for any tran logs that follow it, etc.

Post back if anything not clear.

Cheers

SG.|||Thanks SQLguy I think I finally understand now. It all seems to be working okay, that a relieve! Cheers Nixies

Qualification of system tables in queries

I found an "undocumented" stored procedure that returns the size of the
tempdb called sp_tempdbspace - this stored procedure queries two different
tables to compute the allocated size of tempdb as well as figuring out how
much of that size is being used.
My question is about the queries used in this stored procedure - the query
that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but the
query that accesses sysindexes qualifies the table as "tempdb..sysindexes".
I am trying to figure out why these two queries use different qualifications
for these system tables - I suspect that there is a reason for this
difference other than cosmetic or stylistic reasons. I have tried to do
research through web searches, but have not really found anything that looks
to be conclusive.
Any answers or pointers in resolving this would be appreciated.My bet is that it's just a different style (maybe two different people
worked on the query, or maybe the same person at different times). Since
the stored procedure is always called with dbo either implicitly or
explicitly, it doesn't make a whole lot of difference. Especially if it is
just a cosmetic issue, in an undocumented procedure, I wouldn't worry about
it... I would worry more about relying on this stored procedure
(undocumented stored procedures can be changed, or dropped from the product
altogether, without any notice).
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
Perhaps they were written by two different programmers. Best practice is to
fully qualify the name. And **NEVER** rely on any script written by MS or
shipped with MS to be a good example of best practices.|||> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons.
AFAIK, the best practice is to always owner-qualify objects. This can
reduce stored procedure recompilations and avoid ambiguity.
Although there could be a special case for system object access, the more
likely reason for the omission of 'dbo' here is a simple oversight.
Hope this helps.
Dan Guzman
SQL Server MVP
"jdc" <jdc@.discussions.microsoft.com> wrote in message
news:893E0958-783F-431C-A392-8CB71E9191A7@.microsoft.com...
>I found an "undocumented" stored procedure that returns the size of the
> tempdb called sp_tempdbspace - this stored procedure queries two different
> tables to compute the allocated size of tempdb as well as figuring out how
> much of that size is being used.
> My question is about the queries used in this stored procedure - the query
> that accesses sysfiles qualifies the table as "tempdb.dbo.sysfiles", but
> the
> query that accesses sysindexes qualifies the table as
> "tempdb..sysindexes".
> I am trying to figure out why these two queries use different
> qualifications
> for these system tables - I suspect that there is a reason for this
> difference other than cosmetic or stylistic reasons. I have tried to do
> research through web searches, but have not really found anything that
> looks
> to be conclusive.
> Any answers or pointers in resolving this would be appreciated.
>