Re: commiting the transaction log?

Good point, Linchi.

I was thinking "closed/saved" as opposed to some data being modified inside the file while it is
opened. I didn't consider grow/shrink, but it is reasonable that those would also be seen as file
modifications from the file system's perspective.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Linchi Shea" <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:783B8B8F-4863-42C8-B7B8-25C4A7EEEDCE@xxxxxxxxxxxxxxxx
AFAIK, the file system will only register date change when a file is closed
(saved).

I guess that depends on what is meant by 'closed/saved'. My exprience has
been that the file growth (such as that by alter database or autogrowth) is
reflected in the OS immediately. I just tried ALTER DATABASE MODIFY FILE, and
confirmed that's the case. But of course I don't know if this is true in all
cases.

Linchi

"Tibor Karaszi" wrote:

AFAIK, the file system will only register date change when a file is closed (saved). As long as
SQL
Server has a file open for a long time, you will not see the date change.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Zoran" <ms.news@xxxxxxxxx> wrote in message news:e9VMUIgGKHA.3948@xxxxxxxxxxxxxxxxxxxxxxx
Hi Linchi,

thanks for the explanation!

Is that the reason for the log file (in the file system) having time stamp that is pretty old
(several months)? I mean, if the log file is making space "inside" itself, then to the file
system
it appears pretty static, even if the things are happening (posts are being written to the
log)?

I ask since I experienced that a log file could appear several month old for a database in our
SQL
server, despite the fact that I know that there are a lot of things happening in the database
every day?

But even if that is true, how come that the file system is not registering (timestamp, if not
the
size?) when things are being updated / writen to a T-log file in the file system?

Thanx in advance!

Regards,

Zoran


"Linchi Shea" <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i en meddelelse
news:36766C44-06CA-487D-8F32-7727C0B3BFAE@xxxxxxxxxxxxxxxx
But even if I run the FULL backup, and then the T-log backup, I can see that
the log is not being smaller?

It appears that you are talking about the log file size. Note that backing
up the transaction log will truncate the inactive part of the log so that
there is more free log space inside the transaction log for future
transaction logging, but it does NOT shrink the log file in the file system.

Linchi

"Zoran" wrote:

Hello All,

I have an mirrored database on SQL 2008 whose tranastion log is beginning to
fill up the disk, since Maintenance Plans didn't run during the weekend.

But even if I run the FULL backup, and then the T-log backup, I can see that
the log is not being smaller?

Shouldn't this FULL backup job commit the things from the T-log, so it can
be normal size again?

What do I do to bring it back to normal size again? (I cannot switch to
simple since it is mirrored)

Thanks in advance!

Zoran










.

Relevant Pages

  • Re: detaching db and stopping services
    ... > Tibor Karaszi, SQL Server MVP ... >>> backup the filecreated by that process and you should have your database ... >> blown the standby database once. ...
    (microsoft.public.sqlserver.server)
  • Re: Using maint plan to backup db to network drive
    ... Tibor Karaszi, SQL Server MVP ... In setting up our system imaging software I wanted to move our database ... I see that when I click browse to select the backup location it allows you ... UNC path it says its invalid but lets me enter it anyway, ...
    (microsoft.public.sqlserver.tools)
  • Re: Full Backup and Log backup timing
    ... The database I tested on is only 50 MB in size, I didn't see that wait in there... ... > backup while a Full backup is in progress but it can not finish until the ... >> Tibor Karaszi, SQL Server MVP ... >>> running when the Tlog backup job kicks in. ...
    (microsoft.public.sqlserver.server)
  • Re: sql server 7.0 backup script.
    ... Thanks hari and nice to see this...Hari, SQL Server MVP ... > in which backup needs to be taken. ... > all the databases with a unique name place it in the folder your are ... >> AUTO BACKUP THE ALL DATABASE BACKUP ...
    (microsoft.public.sqlserver.clients)
  • Re: Recovering SQL Server 2000 database without a backup
    ... >>Andrew J. Kelly ... >>SQL Server MVP ... >>> database and I don't have a recent backup. ...
    (microsoft.public.sqlserver.tools)
Quantcast