Shrinking Database Using DBCC SHRINKDATABASE
-
I am trying to shrink a database using DBCC SHRINKDATABASE and am getting the following error
Cannot shrink log file 2 (<dbname>_Log) because all logical log files are in use.
Anybody know what to do to correct this error?
This is on SQL Server 2000 SP 3a
Steve
-
When are you trying to run this process? The process should be run on off hours or in single user mode if possible. Try seeing if there are any processes running, or sleeping, that have un-committed transactions. If possible kill those processes and try to run the shrink again.
-
Yes, it is offhours but not in single-user mode.
Steve
-
The error is indicating you have at least one connection to the database that is still active and doing something. You cannot shrink the log file if it is currently in use. Usually, in off hours, you can manage to find a time when there are no users and no other night-time processes running. This is a good opportunity to shrink a log file.
Since you posted this in the Notification Services area, I cannot tell what version of SQL you are running. If you are running SQL 7, shrinking the file may not get the disk space back right away as giving it back is up to SQL. Another option you could use is detaching your database, deleting or moving the log file, and then attaching the database. If you do this, MS SQL will recognize that the log file is missing. Since the database was properly detached, there are no transactions in the log file that it needs anyway so it will happily create a new log file where the old one was with nothing in it.
The down side is your database will be offline for a short time.
-
Sorry for posting in the wrong area.
I am running SQL Server 2000.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply