Backup log and Shrink Sql 2014

High Availability
1

Hi

I am doing migration of from 200 to 2014 and I want to convert that below sql server 2000 code into sql server 2014 code because below code is giving error in sql 2014. Please help.

Here is sql server 2000 code :

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

Thank You

2

Why have you got a problem with the size of your TLog?

That's the first question to answer. It should not be necessary to shrink it, except in very exceptional circumstances (something ran amok!). Shrinking the log fragments the file and causes performance problems.

What is the Recovery Model of the Database set to?

3

You should never perform shrinks as a scheduled job or automatically via auto-shrink. Instead, you should be backing up your logs more frequently, such as every minute.

2 Likes
4

The "WITH TRUNCATE_ONLY" option is no longer allowed in SQL Server. The new equivalent code would be:

DECLARE @recovery_model_desc varchar(60)

SELECT @recovery_model_desc = recovery_model_desc
FROM sys.databases
WHERE name = 'dbname'
IF @recovery_model_desc <> 'SIMPLE'
    ALTER DATABASE dbname SET RECOVERY SIMPLE;
DBCC SHRINKFILE( dbname_LOG, 2);
IF @recovery_model_desc <> 'SIMPLE'
BEGIN
    IF @recovery_model_desc = 'FULL'
        ALTER DATABASE dbname SET RECOVERY FULL
    ELSE
        ALTER DATABASE dbname SET RECOVERY BULK_LOGGED
END /*IF*/
1 Like
5

Thank you all,

Recovery model of all database is Simple, Can I use this code

use dbname

DBCC SHRINKFile(dbname,truncateonly)

6

No, but you don't need to. If the db is in Simple recovery model, you can just do this:

CHECKPOINT;
DBCC SHRINKFILE( dbname_LOG, 2);

7

Alright, Please let me know Why "Backup log dbname with TRUNCATE_ONLY" option is no longer allowed in SQL Server.

Is there any another way to doing this or how it is performed in 2014.

8

I just gave you a script that does it. You put the db into Simple recovery model. Then, to be sure, take a CHECKPOINT (I left that part out originally). Then, if you put the db back into full or bulk_logged mode, take a full backup right away.

DECLARE @recovery_model_desc varchar(60)

SELECT @recovery_model_desc = recovery_model_desc
FROM sys.databases
WHERE name = 'dbname'
IF @recovery_model_desc <> 'SIMPLE'
ALTER DATABASE dbname SET RECOVERY SIMPLE;
CHECKPOINT
DBCC SHRINKFILE( dbname_LOG, 2);
IF @recovery_model_desc <> 'SIMPLE'
BEGIN
IF @recovery_model_desc = 'FULL'
ALTER DATABASE dbname SET RECOVERY FULL
ELSE
ALTER DATABASE dbname SET RECOVERY BULK_LOGGED
END /IF/

9

Microsoft has deprecated from BOL:
The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model.

10

That's good - Backup of the Log is not the problem :slight_smile:

Why are you needing to shrink the log? Something caused the log file to grow to the size it is. If you shrink it that process will just expand it back to the same size again - you need to find & fix whatever is causing the log file to grow too large. Regular Shrink of the log file is not a good thing to do.

11

"Hi,

  1. Truncate_Only is no longer available in SQL 2014
  2. Instead of using Truncate_Only, we can use “ DBCC SHRINKFile(dbname,truncateonly)”
  3. Shrink isn't the best option since it would increase fragmentation
  4. Check whether SQL is generating your transaction logs correctly
  5. Check the database size
    "
12

You can try the method in the MSDN article
http://msdn.microsoft.com/en-us/library/ms189493.aspx

13

Here is the quick method to migrate SQL Server from one database to another database:
https://www.cybrary.it/0p3n/best-way-copy-sql-server-database-one-server-another/