Shrinking transaction logs

8 Jul 2009 9:00 PM

HDI

Hello,

I am new to SQL server administration.
I noticed that my backup system didn't backup the log files. Now it
does but the log files have a lot of free space.
I read a lot about this but what's the best thing I can do now to
shrink them?
Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is
their a better way?
Can I only do this when no one is online?

Am I right that this are only log files that you can use to restore.
So whatever I do it doesn't have any consequences to the data?

8 Jul 2009 9:27 PM

Russell Fields

HDI,

Here is the standard reference:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

Since you are adjusting unusually large log files back to a reasonable size
you should simply:
DBCC SHRINKFILE(dbfilename_log, nnn)

The 'nnn' stands for the appropriate size for the particular log file.

You can do it with people online. If this has been this way for a while,
then the log records should be at the beginning of the file and this should
run swiftly in truncating the size of the log file. Doing this will not
affect the integrity of your data.

RLF

Show quoteHide quote

"HDI" <hd***@hotmail.com> wrote in message
news:4e3401de-6e30-4dc0-925a-c2b66f345dd3@y7g2000yqa.googlegroups.com...
> Hello,
>
> I am new to SQL server administration.
> I noticed that my backup system didn't backup the log files. Now it
> does but the log files have a lot of free space.
> I read a lot about this but what's the best thing I can do now to
> shrink them?
> Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is
> their a better way?
> Can I only do this when no one is online?
>
> Am I right that this are only log files that you can use to restore.
> So whatever I do it doesn't have any consequences to the data?

8 Jul 2009 9:36 PM

Erland Sommarskog

HDI (hd***@hotmail.com) writes:

> I am new to SQL server administration.
> I noticed that my backup system didn't backup the log files. Now it
> does but the log files have a lot of free space.
> I read a lot about this but what's the best thing I can do now to
> shrink them?
> Should the command 'DBCC shrinkfile (dbfilename_log) be enough or is
> their a better way?

Normally, you should not shrink files at all, but in a situation like
this where no one has backed up the transaction log, it is likely that
it has grown excessively large.

You have the command, but I think it is a good idea to set a target size.
Try to find out how log you will actually fill up before the next log
backup.

> Can I only do this when no one is online?

Yes, do you can do this online, although I would personally avoid
peak hours. Or even wait until the weekend, or schedule it.

> Am I right that this are only log files that you can use to restore.
> So whatever I do it doesn't have any consequences to the data?

Right.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx