How do I shrink log files in SQL SERVER 2012
I have a SQL Server 2012 express addition with many databases that the log files have not been backed up in a long time. One log file is 1.1 TB. Anyway, after I backup the databases and log files. How do I skrink the log files? When I try I will get this message:
"Cannot shrink log file 2 (SS_NCShared_Vehicle_Log) because the logical log file located at the end of the file is in use."
Any advice would be welcome. I've been away from being a DBA for a long time. I've had these problems before but it was a long time ago and on a different version of SQL Server.
"Cannot shrink log file 2 (SS_NCShared_Vehicle_Log) because the logical log file located at the end of the file is in use."
Any advice would be welcome. I've been away from being a DBA for a long time. I've had these problems before but it was a long time ago and on a different version of SQL Server.
ASKER
How can I shrink a log file without backing it up first? The one that is 1.1TB will take too long.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I run this:
Dbcc opentran (SS_NCRecords_Property)
I get this:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, I think I can shrink the log file. However when I run this:
USE [SS_NCRecords_Property]
GO
DBCC SHRINKFILE (N'SS_NCRecords_Property_L
GO
I will get this:
Cannot shrink log file 2 (SS_NCRecords_Property_Log
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any ideas?
Dbcc opentran (SS_NCRecords_Property)
I get this:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So, I think I can shrink the log file. However when I run this:
USE [SS_NCRecords_Property]
GO
DBCC SHRINKFILE (N'SS_NCRecords_Property_L
GO
I will get this:
Cannot shrink log file 2 (SS_NCRecords_Property_Log
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any ideas?
why do you see the need to run use, ?
you need to allow trancate.
DBCC SHRINKFILE (N'SS_NCRecords_Property_L
Change the recovery mode to simple since you are not regularly backing up the transaction log.
The purpose of the full recovery mode deals with backing up the DB and the transaction log on a regular basis to make a recovery to a point in time possible.
make sure you backup the DB right before you run the truncate.
then decide whether you still need the full recovery method and if so, make sure to use windows scheduler with powershell cmdlets to backup the DB/transaction log on a regular basis while at the same time managing the backup files (deleting old ones to avoid running out of space where those backup files are stored)
you need to allow trancate.
DBCC SHRINKFILE (N'SS_NCRecords_Property_L
Change the recovery mode to simple since you are not regularly backing up the transaction log.
The purpose of the full recovery mode deals with backing up the DB and the transaction log on a regular basis to make a recovery to a point in time possible.
make sure you backup the DB right before you run the truncate.
then decide whether you still need the full recovery method and if so, make sure to use windows scheduler with powershell cmdlets to backup the DB/transaction log on a regular basis while at the same time managing the backup files (deleting old ones to avoid running out of space where those backup files are stored)
ASKER
I was able to shrink the log files. Changed the recovery method to simple. One daily full backup is fine for these databases.
Simple means no point in time restore/recovery. The max potential loss is one entire day when ......
Glad I could help.
To manage transaction log growth, one runs transaction log backups once, twice if every couple of hours.........
This will help manage the size of the transaction log or its growth.
Glad I could help.
To manage transaction log growth, one runs transaction log backups once, twice if every couple of hours.........
This will help manage the size of the transaction log or its growth.
Dbcc opentran(database)
In an express version and without a regular backup, why is the db settings are for full?
Using a simple might limit the growth, but since you seem to have an active transaction holding it up,
What is accessing the db. See if you can reset/restart those services, in order to let that transaction event to close at which point the log can be shrunk.