Solved

shrink and move transaction log - easy

Posted on 2003-12-11
23
98,831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +7
23 Comments
 

Expert Comment

by:memdy
ID: 9923334
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:Scott Pletcher
ID: 9923385
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
ID: 9923390
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 9923397
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
ID: 9923456
Actually:

exec sp_who

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

Expert Comment

by:TheSpirit
ID: 9923465
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
ID: 9923961
"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
ID: 9924015
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
ID: 9924073
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
ID: 9924181
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
ID: 9924393

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

Author Comment

by:josephfluckiger
ID: 9924418
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
ID: 9924456
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
ID: 9924481
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
ID: 9924518
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
ID: 9924533
Or actually, not "OK" but "Yes" to creating a new log file instead of "No".
0
 

Expert Comment

by:rsrsm
ID: 9925648
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
ID: 9927543

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
ID: 9928028
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:Scott Pletcher
Scott Pletcher earned 50 total points
ID: 9928366
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
ID: 9932634
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
ID: 13385584
The step by step rebuild log from  cryptosid is outstanding.
It saved my day.

0
 

Expert Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

695 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