What is in _log.ldf files?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Friar Broccoli - 09 Jul 2008 18:18 GMT

I have been mucking about (reading) recently with some
MicroSoft SQL Server database files. One of the databases
I have been given contains an enormous 20Gig+
*_log.ldf file (about 10 times as large as the *.mdf)

Can anyone tell me what is in an *.ldf and is there someway
of reading the contents. The only thing I have is MSSQLEXPRESS and
third party software (SAS) which
permits me to access the databases via the ODBC.

I am particularly interested in knowing if *.ldf files contain
ROLLBACK information, that would allow me to return the
database to a state prior to recent additions and deletions.

Thanks;

Friar Broccoli
Robert Keith Elias, Quebec, Canada Email: EliasRK (of) gmail * com
Best programmer's & all purpose text editor: http://www.semware.com

--------- I consider ALL arguments in support of my views ---------

Reply to this Message

Rick Sawtell - 09 Jul 2008 18:25 GMT

>I have been mucking about (reading) recently with some
> MicroSoft SQL Server database files. One of the databases
[quoted text clipped - 17 lines]
>
> --------- I consider ALL arguments in support of my views ---------

Read up in the BOL about Log files.

This is the transaction log for the database. It stores all of the
transactions that have been applied to the database.

To reset the log (note the file size will not change, but the inactive
transactions will be removed) is to back it up.

To change the size of this file, you will first need to back it up, then
perform a SHRINKFILE on it.

HTH

Rick Sawtell

Reply to this Message

Roy Harvey (SQL Server MVP) - 09 Jul 2008 18:54 GMT

That is the database log file. A large log file with a small database
is what happens when a database is set up in FULL recovery mode but
the logs are never backed up. (Another way of saying it was installed
but never managed.) You need to read up on recover models and backup
in the documentation, but to put things simple the configuration that
provides the best protection of your data is FULL recovery model with
regular log and database backups. The schedule depends on the amount
of activity, but some common configurations are ever few hours for the
log and once per day for the data. If the FULL model is combined with
proper backups you can recover the database to a point in time.
However, there is no support for manual ROLLBACK of any type.

Note that once you have the log being managed instead of ignored you
will not need 20GB+ if the data is one tenth of that size. Once
things have stabilized a one time use of DBCC SHRINKFILE would be
appropriate.

Roy Harvey
Beacon Falls, CT

>I have been mucking about (reading) recently with some
>MicroSoft SQL Server database files. One of the databases
[quoted text clipped - 17 lines]
>
> --------- I consider ALL arguments in support of my views ---------

Reply to this Message

Friar Broccoli - 09 Jul 2008 19:53 GMT

On Jul 9, 1:54 pm, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.net>
wrote:

> That is the database log file. A large log file with a small database
> is what happens when a database is set up in FULL recovery mode but
[quoted text clipped - 7 lines]
> proper backups you can recover the database to a point in time.
> However, there is no support for manual ROLLBACK of any type.

My situation is that: people come into my office, drop
database files on my desk, and ask me to tell them what
is in them, so I have no interest in using or modifying this
database.

The particular database I am interested in contains about 3 years
worth of data. From reading the code, I know the initial data
is created by adding data from other data bases at the end of each
day, then the daily database is deleted. I am also pretty sure
the *.ldf files go back for the full 3 years because I can read the
dates from the beginning of the *.ldf file in a binary view.

At roughly 3 month intervals roughly 30% of the records are deleted
and the data is reindexed, then new data is added daily as normal.

What I would like to do is (re)create an empty data base and then
run the restore procedure forward to some point in time prior to
one of the delete/reindex events.

Obviously, I have no idea how to do that now, but if I work at
it, do I have any hope of succeeding at that or anything similar?

Thank you very very much for your previous unusually clear reply.

> Note that once you have the log being managed instead of ignored you
> will not need 20GB+ if the data is one tenth of that size. Once
[quoted text clipped - 28 lines]
>
> > --------- I consider ALL arguments in support of my views ----------

Reply to this Message

Roy Harvey (SQL Server MVP) - 09 Jul 2008 20:26 GMT

Unless you also have a backup from BEFORE the point in time you want
to go to you are out of luck. You can only go forward from a known
database restore.

Roy Harvey
Beacon Falls, CT

>On Jul 9, 1:54 pm, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.net>
>wrote:
[quoted text clipped - 64 lines]
>>
>> > --------- I consider ALL arguments in support of my views ----------

Reply to this Message

Friar Broccoli - 10 Jul 2008 12:10 GMT

On Jul 9, 3:26 pm, "Roy Harvey (SQL Server MVP)" <roy_har...@snet.net>
wrote:

> Unless you also have a backup from BEFORE the point in time you want
> to go to you are out of luck. You can only go forward from a known
> database restore.
>
> Roy Harvey
> Beacon Falls, CT

This is to thank you for your extremely informative
and to_the_point posts.

> On Wed, 9 Jul 2008 11:53:02 -0700 (PDT), Friar Broccoli
>
[quoted text clipped - 69 lines]
>
> >> > --------- I consider ALL arguments in support of my views -----------

Reply to this Message