shrinking log file

  • My database name is test3.2_staging, and log file grown abnormally. When I tried to shrink the database throwing below error.

    USE DatabaseName

    GO

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    GO

    Msg 911, Level 16, State 1, Line 1

    Could not locate entry in sysdatabases for database 'TEST3'. No entry found with that name. Make sure that the name is entered correctly.

    When I renamed the database to test3_2_staging, shrinking log is successfull. Is there restrictions on database names, i mean not to use dot(.)

  • Yes, there are restrictions on database names - and there are quite a few special characters that will cause problems.

    You probably could have gotten around that particular error by quoting the database name (default in SQL Server is brackets). For example:

    BACKUP LOG [dbo].[test3.2_staging] ...

    But, you have an additional problem here. By truncating the log, you have broken the log chain and you need to perform a full backup immediately so your log backups won't fail (you are performing log backups, right?).

    If you are not performing log backups, then you either need to implement frequent log backups (for example - every hour), or you need to change the database to the simple recovery model.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams-493691 (12/24/2009)


    Yes, there are restrictions on database names - and there are quite a few special characters that will cause problems.

    You probably could have gotten around that particular error by quoting the database name (default in SQL Server is brackets). For example:

    BACKUP LOG [dbo].[test3.2_staging] ...

    But, you have an additional problem here. By truncating the log, you have broken the log chain and you need to perform a full backup immediately so your log backups won't fail (you are performing log backups, right?).

    If you are not performing log backups, then you either need to implement frequent log backups (for example - every hour), or you need to change the database to the simple recovery model.

    Thanks for your reply..

    This is dev server, we are not taking any backups. Recovery model is full for test3.2_staging

  • I would recommend changing the recovery model to simple since it is only a dev server. I would also recommend that you start backing up the database daily - just to make sure you can recover your development work if needed.

    Wouldn't want to be in the situation where you lost the dev server and also lost 100's of hours of work because you 'forgot' to backup the database.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I would second Jeffrey's note about changing to simple model if you don't need recovery. Otherwise, you need to run log backups.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply