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

Query a Sql Server LOG (LDF) File

Hello guys,
Actually i was thinking is there any way to query the LDF ( sqlserver transaction log file) to get the operations performed during that time span?
Hope we can do it!
Regards
Salim
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Get LogExplorer from www.lumigent.com , it's the only way AFAIK.
--
Jacco Schalkwijk
SQL Server MVP
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi,
You can use this undocumented command:
DBCC log ( {dbid|dbname } [, { -1 | 0 | 1 | 2 | 3 | 4}] )
For example:
DBCC log ('pubs', 2)
I'm sure that you can find more information about this if you search on
google, i don't have the information about what each number means right now.
--
Regards,
Kristofer Gafvert - IIS MVP
http://www.ilopia.com - FAQ & Tutorials for Windows Server 2003, and SQL
Server 2000
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
Problems with spam and viruses? See
http://www.ilopia.com/security/newsposting.aspx
"Salim" <salim_belim@.hargreaveslansdown.co.uk> wrote in message
news:%23repm8$kDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hello guys,
> Actually i was thinking is there any way to query the LDF ( sqlserver
transaction log file) to get the operations performed during that time span?
> Hope we can do it!
> Regards
> Salim
>
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Query

Hi
I need this statement to print to the same log.txt when running xp_cmdshell
PRINT 'Upgrading.... ' + @.DataBaseName
How do I fit it into this command?
set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Thanks
JacoHi
Use
PRINT 'Upgrading.... ' + DB_NAME()
instead.
John
"Jaco" wrote:

> Hi
> I need this statement to print to the same log.txt when running xp_cmdshel
l
> PRINT 'Upgrading.... ' + @.DataBaseName
> How do I fit it into this command?
> set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
> c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
> exec master..xp_cmdshell @.command
> Thanks
> Jaco
>|||On Tue, 20 Sep 2005 03:16:05 -0700, Jaco wrote:

>Hi
>I need this statement to print to the same log.txt when running xp_cmdshell
>PRINT 'Upgrading.... ' + @.DataBaseName
>How do I fit it into this command?
>set @.command = 'osql -Usa -Padmin -S jaco_ -d ' + @.DataBaseName + ' -i
>c:\temp\Complete(7-9).sql' + ' -n ' + ' >> c:\temp\log.txt'
>exec master..xp_cmdshell @.command
>Thanks
>Jaco
Hi Jaco,
If you can change the contents of c:\temp\Complete(7-9).sql, then add
this command at the beginning:
PRINT 'Upgrading.... ' + DB_NAME()
If you can't change c:\temp\Complete(7-9).sql, then use this:
set @.command = 'echo Upgrading.... ' + @.DataBaseName
+ ' >> c:\temp\log.txt'
exec master..xp_cmdshell @.command
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

queries filling up logfile

There is a file in my c:\winnt\system32 by the name sysfile.log, and it
contains what seem to be the queries a reporting tool uses, and its filling
up on disk space real fast,
is anyone aware of this problem
please help..asapHi
You don't say what the report tool is! You may want to contact the
manufacturer. Also check that you don't have ODBC tracing turned on if you
are using a DNS, usually the default file is different to your filename but
it can be changed.
John
"Santu" <Santu@.discussions.microsoft.com> wrote in message
news:8D4006B0-3CAB-44DE-9014-C9D22C4BF7B9@.microsoft.com...
> There is a file in my c:\winnt\system32 by the name sysfile.log, and it
> contains what seem to be the queries a reporting tool uses, and its
> filling
> up on disk space real fast,
> is anyone aware of this problem
> please help..asap
>|||That was exactly the problem,
thank you so much,
you're our hero|||Hi
I am glad it is fixed despite my dyslexic DSN!!
You should see an improvement when you use other tools as well.
John
"Santu" wrote:

> That was exactly the problem,
> thank you so much,
> you're our hero

Q:Data file/log file growth

Sorry for the cross post but I really would need an answer to this as soon
as possible.
Two questions:
1. How can I know how many times and what dates etc a database file with
automatic growth either in percent or in megabyte has grown?
2. If I specify to automaticly grow by 10 percent. Will the file then each
time grow with 10 percent of the original file size (when created) or with
10 percent of the current size?
/Peter> Sorry for the cross post but I really would need an answer to this as soon
> as possible.
So because you are urgent, that somehow makes your question relevant for
programming, setup, msde and tools? Did you also cross-post to the BMW,
Oracle, and LOTR newsgroups? ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||I´m sorry but I see no need for arrogans.
I had been better of with an answer to my questions but maybe these ones
were kind of tricky... ;-)
Regards /Peter
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:OcoU9BBxDHA.3816@.tk2msftngp13.phx.gbl...
> > Sorry for the cross post but I really would need an answer to this as
soon
> > as possible.
> So because you are urgent, that somehow makes your question relevant for
> programming, setup, msde and tools? Did you also cross-post to the BMW,
> Oracle, and LOTR newsgroups? ;-)
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>|||For the second (from BOL)
When % is specified, the growth increment size is the specified percentage
of the size of the file at the time the increment occurs.
For the first.
Not sure of any stored data for this (doesn't say that it doesn't exist) but
at least for the number of growths you could calculate from start DB size +
growth % (assumes no shrinks)
Dave
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter" <peter@.dsds.com> wrote in message
news:OH0%231rAxDHA.2356@.TK2MSFTNGP12.phx.gbl...
> Sorry for the cross post but I really would need an answer to this as soon
> as possible.
>
> Two questions:
> 1. How can I know how many times and what dates etc a database file with
> automatic growth either in percent or in megabyte has grown?
> 2. If I specify to automaticly grow by 10 percent. Will the file then each
> time grow with 10 percent of the original file size (when created) or with
> 10 percent of the current size?
> /Peter
>|||You could use the DataFileAutoGrow/LogFileAutoGrow event
class under the Database event category to trace the
automatic growth of your data/log files. Personally, I
don't think this is a good solution. It's a bit like
shooting a fly with cannon.
I think a better solution would be for SQL Server to log
an entry in the errorlog when an auto growth takes place.
It would be even better if SQL Server could provide a
switch to control whether such an entry is written to the
errorlog.
Linchi
>--Original Message--
>Sorry for the cross post but I really would need an
answer to this as soon
>as possible.
>
>Two questions:
>1. How can I know how many times and what dates etc a
database file with
>automatic growth either in percent or in megabyte has
grown?
>2. If I specify to automaticly grow by 10 percent. Will
the file then each
>time grow with 10 percent of the original file size (when
created) or with
>10 percent of the current size?
>/Peter
>
>.
>|||> I´m sorry but I see no need for arrogans.
I see this far too often.
"Well, my question is really important, or my problem is really urgent. I
couldn't be bothering figuring out which group it belonged in, or I wanted
to get the widest even remotely relevant audience possible. So I just
blasted it to every group with SQL Server in its name."
Which translates, roughly, to "... because my question is more important
than everyone else's."
So, IMHO, the arrogance lies elsewhere...
--
Aaron Bertrand
SQL Server MVP|||> 1. How can I know how many times and what dates etc a database file with
> automatic growth either in percent or in megabyte has grown?
There's a profiler event methinks for this. Or download the util proc from www.dbmaint.com which you
can run as a job at desired interval and will email you each time the db file is bigger than on last
execution.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <peter@.dsds.com> wrote in message news:OH0%231rAxDHA.2356@.TK2MSFTNGP12.phx.gbl...
> Sorry for the cross post but I really would need an answer to this as soon
> as possible.
>
> Two questions:
> 1. How can I know how many times and what dates etc a database file with
> automatic growth either in percent or in megabyte has grown?
> 2. If I specify to automaticly grow by 10 percent. Will the file then each
> time grow with 10 percent of the original file size (when created) or with
> 10 percent of the current size?
> /Peter
>

Q:Data file/log file growth

Two questions:
1. How can I know how many times and what dates etc a database file with
automatic growth either in percent or in megabyte has grown?
2. If I specify to automaticly grow by 10 percent. Will the file then each
time grow with 10 percent of the original file size (when created) or with
10 percent of the current size?
/Peter| Two questions:
|
| 1. How can I know how many times and what dates etc a database file with
| automatic growth either in percent or in megabyte has grown?
--
You can capture a profiler trace which includes the Data file autogrow
event.
|
| 2. If I specify to automaticly grow by 10 percent. Will the file then each
| time grow with 10 percent of the original file size (when created) or with
| 10 percent of the current size?
--
From SQL Server Books Online:
"When % is specified, the growth increment size is the specified percentage
of the size of the file at the time the increment occurs. If FILEGROWTH is
not specified, the default value is 10 percent and the minimum value is 64
KB. The size specified is rounded to the nearest 64 KB."
Hope this helps,
--
Eric Cárdenas
SQL Server support|||Ok, but I cant "go back" to look for a specific database for how many times
etc the database file has expanded?
E.g. if i don´t would like to run profiler all the time?
/Peter
"Eric Cardenas" <ecard@.anonymous.com> wrote in message
news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> | Two questions:
> |
> | 1. How can I know how many times and what dates etc a database file with
> | automatic growth either in percent or in megabyte has grown?
> --
> You can capture a profiler trace which includes the Data file autogrow
> event.
> |
> | 2. If I specify to automaticly grow by 10 percent. Will the file then
each
> | time grow with 10 percent of the original file size (when created) or
with
> | 10 percent of the current size?
> --
> From SQL Server Books Online:
> "When % is specified, the growth increment size is the specified
percentage
> of the size of the file at the time the increment occurs. If FILEGROWTH is
> not specified, the default value is 10 percent and the minimum value is 64
> KB. The size specified is rounded to the nearest 64 KB."
> Hope this helps,
> --
> Eric Cárdenas
> SQL Server support
>|||> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
No, that information is not recorded and stored automatically. If you want to do this at a periodic
bases, I recommend that you run a job with a certain interval which check if size has increased and
run that job periodically.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter" <Peter.nospam@.news.com> wrote in message news:OQidQnWxDHA.2000@.TK2MSFTNGP11.phx.gbl...
> Ok, but I cant "go back" to look for a specific database for how many times
> etc the database file has expanded?
> E.g. if i don´t would like to run profiler all the time?
> /Peter
>
> "Eric Cardenas" <ecard@.anonymous.com> wrote in message
> news:9nx2KpTxDHA.424@.cpmsftngxa07.phx.gbl...
> > | Two questions:
> > |
> > | 1. How can I know how many times and what dates etc a database file with
> > | automatic growth either in percent or in megabyte has grown?
> > --
> > You can capture a profiler trace which includes the Data file autogrow
> > event.
> >
> > |
> > | 2. If I specify to automaticly grow by 10 percent. Will the file then
> each
> > | time grow with 10 percent of the original file size (when created) or
> with
> > | 10 percent of the current size?
> > --
> > From SQL Server Books Online:
> >
> > "When % is specified, the growth increment size is the specified
> percentage
> > of the size of the file at the time the increment occurs. If FILEGROWTH is
> > not specified, the default value is 10 percent and the minimum value is 64
> > KB. The size specified is rounded to the nearest 64 KB."
> >
> > Hope this helps,
> >
> > --
> > Eric Cárdenas
> > SQL Server support
> >
>|||> Ok, but I cant "go back" to look for a specific database for how many
times
> etc the database file has expanded?
No, as others have mentioned, SQL Server doesn't track this information.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/