Shrinking Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Shrinking Database

Author

Topic

loknathmahato
Starting Member

27 Posts

Posted - 2011-12-12 : 14:06:08

Hi Friends,

I have implemented Log shipping on my MS SQL server 2008 database but at the weekend i am facing a major issue regarding log file. At the end of the week when we do maintenance of our database we have to perform major deletion on our database due to that log file size grow up to 20 GB thereafter we perform re-indexing then the size grow up to 25 GB or more. That is not affordable because if we go with this size then next week we perform same thing then the log file size would be double.

So can somebody suggest me that what i have to do?
Can i shrink the database?
Will shrink affect the Log shipping?

My average MDF file size is within 15 GB .

Thanks

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-12-12 : 14:18:10

You do not want to shrink the log file, or the database. The log file will grow to the size it needs to be to support your transactions. By shrinking it on a regular basis, all you are doing is causing performance issues when it has to grow again.

If your database is in simple recovery, then the log file space will be marked as reusable by the system. If your database is in full or bulk_logged recovery model, the log file space will be marked as reusable after a transaction log backup. You should have regular scheduled transaction log backups (recommendation is every 15 minutes - at least) for any databases in full or bulk_logged recovery models.

If the above is set correctly, the log file will stabilize at the size it needs to be and won't grow anymore.

Jeff

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-12 : 16:03:27

The log file won't be double if you do the same next week. The log space is reusable, so the file will hit a steady size. If it needs to be 25GB for the index rebuilds, then leave it at 25GB.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2011-12-13 : 06:29:49

Our daily-delete-maintenance takes about 45 minutes.

We schedule an additional Log Backup to run every 2 minutes for an hour starting from the same time as the daily maintenance routine. That way the LDF file does not grow excessively just to accommodate the maintenance task. We have more Log Backup Files, but in total they are about the same overall size.

The database files on the database you are log-shipping TO will be the exact same size as the ones the log files are coming FROM.

The size of the 25GB log shipping files might cause some issues though - disk full, network traffic, and so on ...

pnash
Starting Member

26 Posts

Posted - 2011-12-13 : 07:03:32

Here is a very good article

http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-13 : 09:54:34

The Sql.log file may consume all disk space and cause SQL Server to run slowly when you turn on ODBC Tracing

http://support.microsoft.com/kb/268591

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-13 : 13:55:17

quote:
Originally posted by johntech

The Sql.log file may consume all disk space and cause SQL Server to run slowly when you turn on ODBC Tracing

http://support.microsoft.com/kb/268591

That's talking about a .log file, not the transaction log (which the OP is referring to)

--
Gail Shaw
SQL Server MVP

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-12-14 : 08:22:05

Hi Gila Monster
I copy that from the Locknth

we have to perform major deletion on our database due to that log file size grow up to 20 GB

So I didn't know the difference

------------------------------------------------------But look to below

Introduced in SQL Server 7.0 was the ability automatically grow and to
shrink the physical size of database data and transaction log files.
Auto grow of files doesn't happen before the file is full, it happens
when new space is needed (like when an insert is performed), so the
user will wait the time it takes to grow until the modification is
completed.

Auto grow and shrink can be very useful under special circumstances,
for example after archiving data to some other location. However, we
often see DBA's doing shrink on a regular basis and the purpose of this
article is to explain some of the downsides of shrink and what actually
happens when you shrink a database file. Also, it is worth noticing
that the auto grow functionality was mainly added so the dba wouldn't
be paged in the middle of the night because the database is full. It
was never intended to be a high performance feature or to replace the
need to manage space usage, especially at the high end

Acknowledgements
I like to thank SQL Server MVP Mark Allison,
http://www.markallison.co.uk, who has provided valuable suggestions and
input for this article.

Versions of SQL Server
This article applies to SQL Server 7.0, 2000 and 2005, where not noted
otherwise.

More information
You can shrink a database file using either DBCC SHRINKDATABASE (which
targets all files for the database) or DBCC SHRINKFILE (which targets a
particular database file). I prefer SHRINKFILE. I will not go through
the details of the commands here; they are documented in SQL Server
Books Online. Let us first determine what actually happens when you
shrink a database file:

Shrinking of data file
When you shrink a data file, SQL Server will first move pages towards
the beginning of the file. This frees up space at the end of the file
and the file can then be shrunk (or as I like to view it: "cut off at
the end").

Shrinking of transaction log file
SQL Server cannot move log records from the end of the log file toward
the beginning of the log file. This means that SQL Server can only cut
down the file size if the file is empty at the end of the file. The
end-most log record sets the limit of how much the transaction log can
be shrunk. A transaction log file is shrunk in units of Virtual Log
Files (VLF). You can see the VLF layout using the undocumented DBCC
LOGINFO command, which returns one row per virtual log file for the
database:

DBCC LOGINFO('myDatabase')

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 253952 8192 11 0 128 0
2 253952 262144 13 0 128 0
2 270336 516096 12 0 128 7000000025000288
2 262144 786432 14 2 128 9000000008400246

The interesting column is "Status". 0 means that the VLF is not in use
and 2 means that it is in use. In my example, I have 2 at the end of
the file (read result from top to bottom) and this means that the file
cannot currently be shrunk.
In 7.0, you have to generate dummy transactions so that the usage of
the log file wraps toward the beginning of the file. You can then empty
the log file using BACKUP LOG and then shrink the file.
In SQL Server 2000, the generation of dummy log records is done for you
when you execute the DBCC SHRINKFILE command.
What you end up doing is BACKUP LOG, DBCC SHRINKFILE several times.
Investigate the layout of the log file using DBCC LOGINFO in between.

If you have loads of VLF (many rows returned from DBCC LOGINFO), you
probably had a small file size for the log initially and then had lots
of small autogrow. Having lots of VLF is a bit of a performance hit. If
this is your case, consider shrinking the log file to a very small size
and then expand the file size to something comfortable (a bigger size).
Here are some articles specifically about management of log file size:
How to Shrink the SQL Server 7.0 Transaction Log
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
Log File Grows too big
Log file filling up
Considerations for Autogrow and AutoShrink
From
http://bytes.com/topic/sql-server/answers/527336-transaction-log-keeps-growing

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-14 : 11:01:20

Yes, and???

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources