- Find out who's using what databases:
- Open SQL Query Analyzer
- type "exec sp_who" into the Query window.
- Click "Execute Query" or press F5.
- From cryptosid at experts-exchange:
- before using DBCC REBUILD_LOG better check this article and read it through
/ / 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...
- Get information on a DB's logfile:
- Open Query Analyzer
- Type this in the query window, then click Execute Query:
- use db_name
- dbcc LOGINFO
- Detach and re-attach a database:
- confirm that nothing has the db open. Open QA and run "exec sp_who"
- If nobody is using the db, detach the database with the QA command "EXEC sp_detach_db 'db_name' ", or right-click the DB in the enterprise mgr and select "detach database".
- Re-attach the DB by using the QA command "EXEC sp_attach_db 'db_name', 'x:\full\path\to\db\file\db_filename.MDF' " or by right-clicking the databases in the enterprise mgr, and select "attach database".
- How to set up SQL mail
- Safely delete a transaction log:
- make sure nobody's using the database, as above under "Detach..."
- Backup the database.
- make sure nobody's using the database still.
- detach the database, as above.
- rename the log file (.LDF file)
- re-attach the database, as above.
- logfile should be automatically re-created.
- From rsrm at experts-exchange, http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20823435.html
Comment from rsrsm
Date: 12/11/2003 07:29PM PST
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:
sp_configure 'allow updates', 1
reconfigure with override
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '
-- note the value of the status column for later use
update sysdatabases set status = 32768 where name = '
-- Verify one row is updated before committing
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
4. Stop and restart SQL server.
If you run DBCC REBUILD_LOG without recycling the server, the following
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:
',' ' )
is the name of the database and is
the physical path to the new log file, not a logical file name. If you
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
Move the log file over from the Winnt\System32 folder to the preferred
', ' ', ' '
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\
' 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)
',' ' )
If the command is successful, the following message appears:
Warning: The log for database '
' 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
6. Set the database in single-user mode and run DBCC CHECKDB to validate
', 'single user', 'true'
update sysdatabases set status =
where name = ' '
-- verify one row is updated before committing
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