Shrinking MDF and LDF Files

[Guest]

I have a SQL Server 2005 database I created several months back. Over time it
has grown rather large in size, for example my LDF File is now 400MB. I've
written a stored procedure that will delete all the data in all tables and
reseed my identity columns to zero.

With the database empty I'd like to SHRINK the database so the files are
their original sizes.

How can a achieve this. I've tried the Shrink Database command and nothing
happens.

Thanks.

 

[Robert Morley]

I have no experience with SQL 2005, so this may not be accurate, but I find
in SQL 2000, shrinking each file individually sometimes helps. In 2000,
you'd start the "Shrink Database" as normal, but in the dialog box, there's
a button near the bottom that allows you to specify what to do to each file
individually. If it's the same in 2005, try it and see if it helps.



Rob

 

[Sylvain Lafontaine]

The problem with the LDF file is that you are using the Full (or Complet)
recovery model and that you have never made a backup of the log file.
Before shrinking the file, you must either backup the log file or swith to
the Simple recovery model. See the newsgroup m.p.sqlserver.server for more
info on that.

For the MDF file, if you are making some big delete, you can shring it;
however, the problem is not as important as with Access because SQL-Server
will finish to reuse the freed space when it will need it. (Access seems to
complet forget about deallocated space, hence the necessity of compacting
mdb files on a regular basis). If you take into account the possibility of
fragmenting the database file, shrinking the SQL-Server database files is
not so a good idea (but it's not so bad, either).

 

[Robert Morley]

The problem with the LDF file is that you are using the Full (or Complet)

recovery model and that you have never made a backup of the log file.

D'oh! I forgot about that part. <blush>


Rob

 

[David Portas]

I have a SQL Server 2005 database I created several months back. Over time it
has grown rather large in size, for example my LDF File is now 400MB. I've
written a stored procedure that will delete all the data in all tables and
reseed my identity columns to zero.

With the database empty I'd like to SHRINK the database so the files are
their original sizes.

How can a achieve this. I've tried the Shrink Database command and nothing
happens.

Thanks.

Shrinking is almost never a good thing to do. There's a useful summary
about it here:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

If your log file is growing out of control then you aren't managing
your backups properly.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 

[Peter Yang [MSFT]]

Hello Greg,

Agree with Sylvain that you shall back up log file first. David's link has
very detailed inforamtion about that.

When DBCC SHRINKFILE is run, SQL Server 2000/2005 shrinks the log file by
removing as many virtual log files as it can to attempt to reach the target
size. If the target file size is not reached, SQL Server places dummy log
entries in the last virtual log file until the virtual log is filled and
moves the head of the log to the beginning of the file. The following
actions are then required to complete the shrinking of the transaction log:

1. You must run a BACKUP LOG statement to free up space by removing the
inactive portion of the log.
2. You must run DBCC SHRINKFILE again with the desired target size until
the log file shrinks to the target size.

Please see the following article for details also included in David's link

272318 INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
http://support.microsoft.com/?id=272318

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Yang [MSFT]]

Hello Greg,

If you are not concerned about losing data, you could detach the database,
and use sp_attach_singlefile_db to attach the mdf file only, this will
create a new a new ldf file for the database. Please note
sp_attach_single_file_db works only on databases that have a single log
file. For example:

USE master;
GO

EXEC sp_detach_db @dbname = 'AdventureWorks';
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',
@physname = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf'

Please let's know if you have further comments or questions. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Sylvain Lafontaine]

In term of performance, shrinking the log file back to its *original* file
size is not necessarily a good thing. See the following article for more
info:

http://www.sqlskills.com/resources/SQLServerHAChapter9.pdf

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)