SQL SERVER – Maintenance Activity – Attach mdf file without ldf file

Following command will take care of creating the new db using existing single data file. This command will also creates a new log file and you can ignore previous log files if any in case if it has more initial allotted size which you don’t want to give for new db.
CREATE DATABASE <DB Name> ON
(FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SS.mdf’)
FOR ATTACH_REBUILD_LOG
GO

You can also use …

EXEC sp_attach_single_file_db @dbname = ‘SAMS_QA’,

@physname = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SS.mdf’;

Reference – http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

SQL Server – Maintenance Commands – DBs Info, Processes, Longest Running Process & Underlying Query

— List all processes running on SQL Server
sp_who2

— List all processes running on SQL Server for a specific DB
SELECT [Database]=DB_NAME(dbid), spid, ‘Kill ‘ + cast(spid as varchar(10)), last_batch, status, hostname, loginame
FROM sys.sysprocesses
WHERE dbid = DB_ID(‘SS_QA’)

— Get all the databases info
EXEC sp_databases
EXEC sp_helpdb
EXEC sp_msForEachDB ‘PRINT ”?”’

— Find longest running SPIDs

select
p.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), ‘1900-01-01’),
121), 12) as ‘batch_duration’
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in (‘background’, ‘sleeping’)
and P.cmd not in (‘AWAITING COMMAND’
,’MIRROR HANDLER’
,’LAZY WRITER’
,’CHECKPOINT SLEEP’
,’RA MANAGER’)
order by batch_duration desc

— SQL running for a given spid

declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)

set @spid = 70 –XXX — Fill this in

select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid

SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end – @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)

SQL Server – DB Maintenance Commands

Change DB to single/multi user mode

use master;
go
alter database <DB NAME> set single_user with rollback immediate;
alter database <DB NAME> set multi_user;
go

Take the Database Offline/Online
ALTER DATABASE <DB Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [myDB] SET ONLINE
GO

In case if above commands are not working bcos the DB is in use then you can find which process is using the DB and kill those processes using …

EXEC sp_who2 — To see which processes are using the DB
KILL (<SPID>) — Give the process id (SPID) to Kill it

One other way to kill all the active processes is – Detach by selecting Drop Connections checkbox and Attach it again.