Backup log and Shrik dstabase sql 2008 R2

SQL Server Administration
1

Hi,

I am using sql server 2008 r2 and having database size more than 600 GB.

How can use Backup and shrink file command: Below is the code and it is giving error. Please help.

BACKUP LOG DB
DBCC SHRINKFILE ('DB',2)

2

What is the size of the mdf file? And what is the size of the ldf file?

What recovery model is the database using? If it's NOT simple recovery model, are you performing regular log backups? If not, what is your RPO goal for this database, meaning how much data can you lose in the case where you need to restore from a backup?

3

Shrinking the database will cause massive fragmentation. Shrinking on a regular basis is pretty useless and a waste of CPU time because you haven't solved the problem of what may be causing it to grow.

You need to find out what's causing the undesirable growth and fix that or you'll be caught in the vicious cycle of shrinks that cause fragmentation and index jobs that cause growth... sometimes substantial growth.

4

What's the error message, otherwise we will just be guessing ...

At the least you need to provide a Path and Filename of where you want the backup stored.

The DOCs for Backup command are here:

https://msdn.microsoft.com/en-us/library/ms186865.aspx

5

ALTER DATABASE [mydatabase] SET RECOVERY FULL
DBCC SHRINKFILE (< log_file_name_Log >)

6

You should have a regular backup plan for your database and log. The manner and style in which you decide to do that will affect both RPO (Recovery Point Objective) for how much data you can stand to lose - and RTO (Recovery Time Objective) how long you can be down while recovering. Both of those considerations should drive any backup plan that you have and the associated cost to achieve those SLAs.

If you're shrinking because you have limited disk space then get more disks. If you're shrinking because the database has allocated space and you're seeing a large % free... stop shrinking! The database will have to allocate that space again.

Shrinking a database is not a good thing. Every database needs a certain amount of "working space". This includes both the data and log files. It's expensive on resources to grow a file. One company for which I worked as always shrinking their log file (twice a day). I set a generous starting size for the DB @ 4GB each for data and log - and left it alone... performance improved. Now the database starts off with enough space to accommodate a weeks worth of increased data. That info is collected and trended to determine if the data & log file should have more space allocated. Also (and this may just be me...) set the growth to increase in multiples of 8 (MB/GB). This makes the math simple for page allocation for SQL Servers 8K page size. Never grow by a percentage (my opinion).

7
  1. Shrink isn't the best option
  2. Are your log backups working correctly
  3. check your Log files and data file sizes and growth rates, NO percentages. Try to keep number of times it expands down.
  4. Here is what I use, please note to do this Before a Full backup because it can\will break the Backup Chain

SELECT * FROM sys.database_files --Give me the info I need

DBCC UPDATEUSAGE ( [db name) WITH NO_INFOMSGS;

USE [dbname]
GO
ALTER DATABASE [db name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE (dbname_log, 1) --Set the value you are trying to shrink too (SQL makes the actual determination)

ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT

SELECT * FROM sys.database_files --check if it change

8

Thanks to all,
Actually I want convert that below sql server 2000 code into sql server 2014 code.

Here is sql server 2000 code :

Backup log dbname with TRUNCATE_ONLY
DBCC SHRINKFILE (dbname_LOG,2)

Thank You

9

This is a very bad idea - and has been disabled because it is a tremendously bad idea. If you do not want or need transaction log backups - switch the database recovery model to simple, perform a one-time only shrink of the transaction log to an appropriate size for the database and leave it alone.

If you need the ability to restore to a point in time (which you should for any production system) - then implement regular transaction log backups at least every 15 minutes if not more frequently. Once those have been setup - then perform a one-time only shrink of the transaction log to an appropriate size for the database and leave it alone.

10

"Hi,
Shrink isn't the best option since it would increase Fragmentation. If you still want to shrink your database:

  1. Check whether SQL is generating your transaction logs correctly.
  2. Check the database size.
  3. Have a complete backup before shrinking the database.
  4. Execute the code: DBCC SHRINKFILE(dbname)
    "