shrink and move transaction log - easy

I am trying to move my overgrown transaction log from one drive to another.

-I can't get the shrink command to work.

I also tried to run the detach command
sp_detach_db Kit
but I get the error:  "Cannot detach the database 'Kit' because it is currently in use." even though I don't have any apps using it.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Probably has a read lock on the log file becuase the DB is writing to the file all the time. Stop the SQL server service copy/delete the log off and then restart the service.
Scott PletcherSenior DBACommented:
Make sure you're not in the Kit db when trying to run the detach in QA :-)

Also, just to make sure, KILL any tasks using the db first:

SET @sql = ''
SELECT @sql = @sql + ' KILL ' + CAST(spid AS VARCHAR(4)) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = 'Kit'
AND spid > 50 AND spid <> @@SPID

EXEC sp_detach_db 'Kit'

Then, rename the existing log (or delete, if feeling confident), make a backup copy of the db (if you want to be very safe), then do the attach:

EXEC sp_attach_db 'Kit', 'x:\full\path\to\db\file\Kit_Data.MDF'

Verify that the db is working correctly, then you can delete the old.

Finally, do one final detach/attach to move the log.  First detach; move the log; then attach, specifying both the data file and the new log file location in the attach command:

EXEC sp_attach_db 'Kit', 'x:\full\path\to\db\file\Kit_Data.MDF',
You can find out who or what is using the db at the time using:

exec sp_who()

(I think.  Don't have my server up at the moment. I'll check)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:

SELECT @sql = @sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' '

Verify that the db is working correctly, then you can delete the old LOG.


exec sp_who

(Keep trying to put extra junk at the end)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Before running the sp_detach_db close enterprise Manager and ensure that you execute a
Use Master
to ensure that Query Analizer and Enterprise Manager are not connecting to the database
josephfluckigerAuthor Commented:
"sp_who" was helpful to find out who was in the DB. (my home computer I left on this morning :)

"use master"  was also the solution.  The microsoft article has this command, but I was ignoring it.

ok, I got it detached, if I just want to discard the old file and create a new file, how do I do that? Can I just use the enterprise_manager create database wizard and choose, the existing MDF file, but create a new LDF file?

thx all, you guys are good.
josephfluckigerAuthor Commented:
Well, using enterprise manager to create a new database using an existing MDF file, doesn't work.
See Scott's a answer once u detach and rename the ldf files and then attach again it will create the ldf file on its own.
And after that once u confirm that ur DB is fine then u can go and delete the old ldf file
Some info on using "shrink";EN-US;272318

Also check to see if there are any users that might still be logged in.
josephfluckigerAuthor Commented:

I detached and reatached successfully, but then after I deleted the log file and tried to reattach the data file,
exec sp_attach_db NAG_CDR_Audit,'D:\NAG\D\sql2003\backup\NAG_CDR_Audit_Data.MDF'

I get the error:

Could not open new database 'NAG_CDR_Audit_New'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\NAG_CDR_Audit_Log.LDF' may be incorrect.

Which is still looking in the old file location. Can I make a copy of the trasaction log of another database and attach that?

josephfluckigerAuthor Commented:
well that didn't work, when I tried to use the trasaction log of a different DB, I get the error: "Cannot associate files with different databases."
You can try using sp_attach_single_file_db without specifying a log file.

Or, you can try using the Enterprise Manager to reattach. Right click on the "Databases", go to

"All Tasks->Attach Database"

Follow the suggestions...
josephfluckigerAuthor Commented:
Both gave me the same error.

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'NAG_CDR_Audit'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\NAG_CDR_Audit_Log.LDF' may be incorrect.

It seems to really want that log file!

When you try to reattach using Enterprise Manager, and the log file is not there, it does not allow you to create a new log file?  In the same message box where it says the physical file name... may be incorrect, it should also say " a new log file may be created" - just say OK
Or actually, not "OK" but "Yes" to creating a new log file instead of "No".
If the log file is deleted for the database and SQL server is not able to locate can rebuild the log. try the following steps (But if the log file is not deleted then you can ignore this comment):

REBUILD LOG and recover a suspect database:

1. Back up the .mdf/.ndf and .ldf files.

2. Change the database context to Master and allow updates to system tables:

   Use Master
   sp_configure 'allow updates', 1
   reconfigure with override

3. Set the database in Emergency (bypass recovery) mode:

   select * from sysdatabases where name = '<db_name>'
   -- note the value of the status column for later use
   begin tran
   update sysdatabases set status = 32768 where name = '<db_name>'
   -- Verify one row is updated before committing
   commit tran

   If you run DBCC REBUILD_LOG without setting the database in Emergency
   the command does not work. You do not receive an error, but the log is
   rebuilt either.

4. Stop and restart SQL server.

   If you run DBCC REBUILD_LOG without recycling the server, the following
   message displays:

   Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in
   recovery mode to rebuild the log. DBCC execution completed. If DBCC
   error messages, contact your system administrator.

5. The syntax for DBCC REBUILD_LOG is as follows:

   DBCC REBUILD_LOG('<db_name>','<log_filename>')

   where <db_name> is the name of the database and <log_filename> is
   the physical path to the new log file, not a logical file name. If you
do not
   specify the full path, the new log is created in the Windows NT system
   directory (by default, this is the Winnt\System32 directory).

   If the file is placed in the Winnt\System32 folder, you can move it to a
   different folder by running the following commands from the SQL Query

   sp_detach_db '<db_name>'

   Move the log file over from the Winnt\System32 folder to the preferred

   sp_attach_db '<db_name>', '<data_file_name>', '<log_filename>'

   If a log file with the same name as specified in DBCC REBUILD_LOG already
   exists in that directory, then the following message occurs:

   Server: Msg 5025, Level 16, State 1, Line 1
   The file 'C:\MSSQL7\Data\<log_filename>' already exists. It should be
   renamed or deleted so that a new log file can be created.
   DBCC execution completed. If DBCC printed error messages, contact your

   You will need to specify a different filename or rename or delete the

   Rebuild the log with this code:

   DBCC TRACEON (3604)
   DBCC REBUILD_LOG('<db_name>','<log_filename>')

   If the command is successful, the following message appears:

   Warning: The log for database '<db_name>' has been rebuilt.
   Transactional consistency has been lost. DBCC CHECKDB should be run to
   validate physical consistency. Database options will have to be reset,
   extra log files may need to be deleted.

   After the log is successfully rebuilt, the database is placed in DBO Use
   mode. That is, the status of the database is 2048 irrespective of what
   status was previously. You must reset the status using sp_dboption or
   the SEM.

6. Set the database in single-user mode and run DBCC CHECKDB to validate
   physical consistency:

   sp_dboption '<db_name>', 'single user', 'true'
   DBCC CHECKDB('<db_name>')
   begin tran
   update sysdatabases set status = <prior value> where name = '<db_name>'
   -- verify one row is updated before committing
   commit tran

7. Turn off the updates to system tables by using:

   sp_configure 'allow updates', 0
   reconfigure with override

WARNING: After verifying the consistency of the database by running DBCC
and fixing any errors, please make sure to check the database for logical
consistency as well. Because a new log has been built, the transactions in
old log are lost, hence you must also verify the logical consistency of the
as well.

before using DBCC REBUILD_LOG better check this article and read it through

a safe alternative would be
Backup the consistent Database.
and RESTORE the database with the MOVE option using which u can move the database files to whichever drive u want...

this method is entirely safe.
hope that helps
josephfluckigerAuthor Commented:
yea, the backup and restore option would have been the easiest, except the other SQL server that I backed it up to before I deleted the trasaction log crashed.(bad SQL day yesterday).

But I think I can still recover the files from the crashed box and try to attach those. Hopefully I won't get the same "can't find log" problems.

Scott PletcherSenior DBACommented:
It's normal to get an error like this one:

Device activation error. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\data\DbName_Log.LDF' may be incorrect.

But it's usually followed by this msg:

New log file 'd:\Program Files\Microsoft SQL Server\MSSQL\Data\DbName_log.LDF' was created.

In your case there is something wrong with the db itself.  Did it detach cleanly?  

If you have the original log, and a backup of the original db (before the attempted attach), you can put those files back in place and attach them and you will be back to where you started and can try again.
josephfluckigerAuthor Commented:
thx all, each comment was helpful. I wasn't quite brave enough to try the kill or the rebuild log,  but after two SQL servers blew up on my yesterday, I was able to piece everything back together thx to your help.

Merry Christmas.
The step by step rebuild log from  cryptosid is outstanding.
It saved my day.

Thank you rsrsm!  You saved my you know what with that excellent description of log rebuilding.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.