|
|
Posted by anonieko on 10/02/35 11:56
Thats an excellent reply. Here is the link that was mentioned above
about SHRINKING DATABASE IN SQL SERVER
Thanks to Tibor Karaszi's article
Overview
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
So what is the problem? Why shouldn't I shrink database files on a
regular basis?
Have a look at below list and then you can determine for yourself
whether or not you want to shrink database files regularly:
Each page moved will be logged to the transaction log. Say you have a
database using 3GB of data and indexes, the log file will need 3GB
space for the shrink. This 3GB will also be included in the following
transaction log backup. This doesn't seem to happen if the database is
in simple recovery mode. (Applies to shrinking of data files.)
After the shrink, as users add rows etc in the database, the file has
to grow again. Growing a database file is an expensive operation, it
hurts performance. During the grow operation, some modifications will
be blocked until the grow operation has finished. (Applies to shrinking
of both data and log files.)
SQL Server 2005:
SQL Server 2005 has "instant file initialization" which means that
database files can be created and also grow very fast; Windows doesn't
"zero out" the data in the database file. Instant file initialization
is only available for data files, not log files. Also, instance file
initialization requires that the service account for the SQL Server
service has the SE_MANAGE_VOLUME_NAME windows privilige.
There are situations where autogrow doesn't "catch up" with the space
usage requirements. This will result in an error message from SQL
Server when the modification is performed, returned to the client
application: error 1105 if data is full and 9002 if log is full.
(Applies to shrinking of both data and log files.)
Moving datapages around will fragment your database. (Applies to
shrinking of data files.)
Heavy shrinking and growing of database files will fragment your file
system, which will hurt performance even more. (Applies to shrinking of
both data and log files.)
Conclusion
Only you can determine in the end whether you want to shrink or not.
With above information, you hopefully have enough to go on when making
that decision.
Transact-SQL code
Below you find the T-SQL code I ran to prove that shrinking of a
database will generate the same amount of transaction log records as
the amount of data which was moved:
--Script to show that shrink produces a lot of log record, about same
amount as the data which is moved.
USE master
GO
DROP DATABASE shrink_test
GO
CREATE DATABASE shrink_test
ON PRIMARY
( NAME = shrink_test_data,
FILENAME = N'c:\shrink_test_data.mdf',
SIZE = 2MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = shrink_test_log,
FILENAME = N'c:\shrink_test_log.ldf',
SIZE = 3MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
GO
SET NOCOUNT ON
USE shrink_test
CREATE TABLE t(c1 int identity CONSTRAINT PK_shrink_test PRIMARY KEY,
c2 char(3000) default 'hello')
DECLARE @i int
SET @i = 1
WHILE @i <= 40000
BEGIN
INSERT t DEFAULT VALUES
IF @i%100 = 0 --Truncate log on every 100'th row
BACKUP LOG shrink_test WITH NO_LOG
SET @i = @i + 1
END
SELECT COUNT(c1) AS "Number of rows before delete, should be 40000"
FROM t
--Delete every other row in the table, in a loop so log doesn't grow!
--DECLARE @i int --Remove comment if not run all in one batch
SET @i = 1
WHILE @i <= 40000
BEGIN
-- IF @i%2 = 0
IF @i<20000
DELETE FROM t WHERE c1 = @i
SET @i = @i + 1
END
SELECT COUNT(c1) AS "Number of rows after delete, shuld be 20000" FROM
t
--Make sure the tlog file doesn't auto-truncate
BACKUP DATABASE shrink_test TO DISK = 'NUL'
--Should be large
EXEC master..xp_getfiledetails 'c:\shrink_test_data.mdf'
--Should be small
EXEC master..xp_getfiledetails 'c:\shrink_test_log.ldf'
GO
--Seems we need a waitfor in order for the shrink to do its job
WAITFOR DELAY '00:02:00'
GO
--This shrink might now produce a lot of log record as 20000 rows will
be moved!
DBCC SHRINKFILE (shrink_test_data, 40)
--So, did the log grow?
EXEC master..xp_getfiledetails 'c:\shrink_test_log.ldf'
--My result, the data file shrunk to 80MB and the log file grew from
3MB to 76MB!!!
GO
USE master
GO
DROP DATABASE shrink_test
Navigation:
[Reply to this message]
|