shrink and move transaction log - easy

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.

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'

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)

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.

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

"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.

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

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?

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."

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

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

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

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.

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.