How to move transaction log file (.ldf)

Is your drive out of space because your database is growing? Then you might think to move the transaction log by using SQL Server stored procedures for detaching and attaching the database.

For example, to move the pubs database from drive C to drive D:

  1. First use the following sp_detach_db command to detach the database
    EXEC sp_detach_db 'pubs'
  2. Next, copy the pubs_log.ldf file to the destination drive.
  3. Finally, after you copy the file, you can use the sp_attach_db stored procedure to reattach the data to SQL Server.
    EXEC sp_attach_db 'pubs', 'c:\mssql\data\pubs.mdf', 'd:\mssql\data\pubs_log.ldf'

How to show all connected users in SQL Server

The simplest way to list all users and processes is to use the system stored procedure sp_who. To list all active users:

sp_who 'active'

To list details about a particular user:

sp_who 'login-name'

How to check if SQL Server Agent is running

Here it is the T-SQL code snippet:

SELECT program_name, login_time, loginame, cmd, hostname
FROM master.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher'


Subscribe to SQLhint RSS