Solved

shrink and move transaction log - easy

Posted on 2003-12-11
23
98,794 Views
Last Modified: 2011-08-18
I am trying to move my overgrown transaction log from one drive to another.

-I can't get the shrink command to work.
DBCC SHRINKFILE(NAG_CDR_Audit_Log, 1)

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.

0
Comment
Question by:josephfluckiger
  • 7
  • 5
  • 3
  • +7
23 Comments
 

Expert Comment

by:memdy
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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:

DECLARE @sql VARCHAR(500)
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(@sql)

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',
    'x:\full\path\to\log\file\Kit_Log.LDF'
0
 
LVL 6

Expert Comment

by:robertjbarker
Comment Utility
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)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
CORRECTIONS: D'OH!

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

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


0
 
LVL 6

Accepted Solution

by:
robertjbarker earned 250 total points
Comment Utility
Actually:

exec sp_who

(Keep trying to put extra junk at the end)
0
 
LVL 4

Expert Comment

by:TheSpirit
Comment Utility
Hi,
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
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
"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.
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q224/0/71.asp&NoWebContent=1


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.
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
Well, using enterprise manager to create a new database using an existing MDF file, doesn't work.
0
 
LVL 3

Expert Comment

by:pra_kumar03
Comment Utility
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
0
 

Assisted Solution

by:memdy
memdy earned 200 total points
Comment Utility
Some info on using "shrink"

http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318

Also check to see if there are any users that might still be logged in.
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility

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?




0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
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."
0
 
LVL 6

Assisted Solution

by:robertjbarker
robertjbarker earned 250 total points
Comment Utility
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...
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
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!

0
 
LVL 6

Expert Comment

by:robertjbarker
Comment Utility
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
0
 
LVL 6

Expert Comment

by:robertjbarker
Comment Utility
Or actually, not "OK" but "Yes" to creating a new log file instead of "No".
0
 

Expert Comment

by:rsrsm
Comment Utility
If the log file is deleted for the database and SQL server is not able to locate it..you 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
   Go
   sp_configure 'allow updates', 1
   reconfigure with override
   Go

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
mode,
   the command does not work. You do not receive an error, but the log is
not
   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
bypass
   recovery mode to rebuild the log. DBCC execution completed. If DBCC
printed
   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
root
   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
   Analyzer:

   sp_detach_db '<db_name>'
   Go

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

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

   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
system
   administrator.

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

   Rebuild the log with this code:

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

   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,
and
   extra log files may need to be deleted.

   After the log is successfully rebuilt, the database is placed in DBO Use
Only
   mode. That is, the status of the database is 2048 irrespective of what
the
   status was previously. You must reset the status using sp_dboption or
through
   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>')
   Go
   begin tran
   update sysdatabases set status = <prior value> where name = '<db_name>'
   -- verify one row is updated before committing
   commit tran
   Go

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

   sp_configure 'allow updates', 0
   reconfigure with override
   Go

WARNING: After verifying the consistency of the database by running DBCC
CHECKDB,
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
the
old log are lost, hence you must also verify the logical consistency of the
data
as well.
0
 
LVL 5

Expert Comment

by:cryptosid
Comment Utility

before using DBCC REBUILD_LOG better check this article and read it through
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9011

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
cryptosid
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
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.

joe
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
Comment Utility
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.
0
 
LVL 1

Author Comment

by:josephfluckiger
Comment Utility
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.

joe
Merry Christmas.
0
 

Expert Comment

by:shweman
Comment Utility
The step by step rebuild log from  cryptosid is outstanding.
It saved my day.

0
 

Expert Comment

by:ljaffe
Comment Utility
Thank you rsrsm!  You saved my you know what with that excellent description of log rebuilding.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now