Monday, June 28, 2010

checking sql2005 replication

select la.name,la.publisher_db,
case lh.runstatus
when 1 then 'Start'
when 2 then 'Succeed'
when 3 then 'In progress'
when 4 then 'Idle'
when 5 then 'Retry'
when 6 then 'Fail'
else 'Unknown'
end as runstatus
, lh.time, lh.comments
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
inner join (
select lh.agent_id, max(lh.time) as LastTime
from distribution..MSlogreader_history lh
inner join distribution..MSlogreader_agents la on lh.agent_id = la.id
group by lh.agent_id) r
on r.agent_id = lh.agent_id
and r.LastTime = lh.time
where lh.runstatus not in (3,4) -- 3:In Progress, 4: Idle

select * from distribution..MSlogreader_history

Tuesday, June 8, 2010

SIZE OF THE DATABASE FILES

SELECT name AS [File Name] , file_id, physical_name AS [Physical Name],
size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS [Available Space In MB]
FROM sys.database_files;

OUTPUT
File Name file_id Physical Name Total Size in MB Available Space In MB
-------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- ---------------------------------------
master 1 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 80 71.625000
mastlog 2 D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 1 0.640625

(2 row(s) affected)

How to find the authentication methord used

This script will help you find the authentication method of logins in sql 2005.

SELECT 'Authentication Method'=(
CASE
WHEN nt_user_name IS not null THEN 'Windows Authentication'
ELSE 'SQL Authentication'
END),
login_name AS 'Login Name', ISNULL(nt_user_name,'-') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name