Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Question For Nightman Shrinking MSSQL 2000 and 2005  Databases

Posted on 2006-11-28
9
Medium Priority
?
344 Views
Last Modified: 2012-08-13
Issue:
I have several databases that are in the 7 + GB range.  The log files will grow to over 7 + GB.  I'm trying to develop a VB6 application shrink these databases.

These databases are in MSSQL 2000 and MSSQL 2005


The NO_LOG and TRUNCATE_ONLY options of the BACKUP LOG statement will be removed in a future version of SQL Server. These options break the log chain, because they remove the inactive part of the log without making a backup copy of it and truncate the log by discarding all but the active log. Until the next full or differential database backup, the database is not protected from media failure. Therefore, we strongly recommend that you avoid using either of these options in new development work, and that you plan to modify applications that currently use it


If you
Backup the DATA file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB "c:\junk_1" -BkExt "BAK"'

Backup the LOG file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -BkUpMedia DISK -BkUpLog "c:\junk_1" -BkExt "TRN"'

Then do a
USE cax_Development
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
DBCC SHRINKFILE(cax_Development_Log,5)

Does the 5 limit the size of the log file or just shrink it to 5 meg if possible?

Does this break the log chain or could you restore the database and then restore the log file

or how wound you recommend shrinking the log file.
0
Comment
Question by:PhilChapmanJr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 20

Expert Comment

by:Sirees
ID: 18028370
<<Does the 5 limit the size of the log file or just shrink it to 5 meg if possible?>>
<<DBCC SHRINKFILE(cax_Development_Log,5) >> 

It shrinks the size of the file named cax_Development_Log to 5MB.

< could you restore the database and then restore the log file
>>

Yes.
A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18028877
What happens if you have the the DBCC SHRINKFILE(cax_Development_Log,5)  but you have 8 meg of active virtual logs
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 18029313
SQL leaves it at 8M.  SQL will never destroy log data because of a SHRINKFILE.


>> The NO_LOG and TRUNCATE_ONLY options of the BACKUP LOG statement will be removed in a future version of SQL Server. <<

Don't hold your breath waiting for it to happen :-) .
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18029708

If you
Backup the DATA file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB "c:\junk_1" -BkExt "BAK"'

Backup the LOG file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -BkUpMedia DISK -BkUpLog "c:\junk_1" -BkExt "TRN"'

Then do a
USE cax_Development
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
DBCC SHRINKFILE(cax_Development_Log,5)

Is this a good approach to backup data and log files and then shrink the log files.  Is this ok for MSSQL 2000 and MSSQL 2005
0
 
LVL 20

Expert Comment

by:Sirees
ID: 18030406
Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
the data inside the file. I.e., it doesn't break a log backup chain.

I think you shouldn't shrink a logfile on a regular basis - that will only lead to poor performance because it will have to grow again, and the file will most likely be fragmented as well.

Try to have a look at -
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.

From BOL -

If you backup the log files with TRUNCATE_ONLY then ir removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18031348
I beleave i understand it better now.  See if this is correct

You should only shrink the LOG file if for some reason it grows unusually large.  
Example if a similar database log file is 5 MB  and for some reason this one is 11 MB
You would

Backup the DATA file with
EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -VrfyBackup -BkUpMedia DISK -BkUpDB "c:\junk_1" -BkExt "BAK"'

EXEC master.dbo.xp_sqlmaint '-PlanID 4F9F327D-27DD-4505-B016-00E5FF63BC4F -BkUpMedia DISK -BkUpLog "c:\junk_1" -BkExt "TRN"'


USE cax_Development
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
DBCC SHRINKFILE(cax_Development_Log,5)    which is the normal size

If your log file is set to 5 but you only have 3 meg of data the performance will be better than it would if the file had to expand.

We always do complete backups so eventhough TRUNCATE_ONLY invalidates the log file.  If the data and log backup was done at the same time.

And then
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
is run new changes made to the database should be reflected in the Log file.

Is this understand correct?





0
 
LVL 20

Expert Comment

by:Sirees
ID: 18032106
<<And then
BACKUP LOG cax_Development WITH TRUNCATE_ONLY
is run new changes made to the database should be reflected in the Log file.

Is this understand correct?
>>

Yes.

You just take a full backup and then transaction log backups to reflect the changes made after full backup.

Unless, otherwise needed you don't have to Shrink the log file.
0
 
LVL 2

Author Comment

by:PhilChapmanJr
ID: 18032933
I think i understand this fairely well.  Just one more item.

What is the purpose of the Log File and how is it used.
0
 
LVL 20

Accepted Solution

by:
Sirees earned 1500 total points
ID: 18036276
<<What is the purpose of the Log File and how is it used.>>

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

For more info: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question