Home » Archives for August 2012

August 2012

sql server DBA 999

What is SQL Server Health Check? Why is it important?

What is SQL Server Health Check? Why is it important?

A SQL Server health check looks at all aspects of your SQL Server environment in terms of best practices in the areas of Performance, Configuration, Security, Disaster Recovery and the ability of your environment to scale to projected future loads.

With a SQL Server Health Check, one should make a report of our findings and recomendation/solutions.

Fixing the Problems
You may choose to have us fix some or all of the problems found by the SQL Server health check or
you may prefer to have your own team to discuss and work through them.

What are the important aspects of … Read the rest

sql server DBA 999

Database File Free space and checking Database Growth Trends

Database File Free space and checking Database Growth Trends is one of the important tasks for a DBA for Pre-Sizing the Database to avoid the Autogrowths plus to have a idea of Space Requirements on the SQL Server etc.

Below Query can be really helpful for store this information into a Central location and monitor my Database Growth Trends.


SELECT S.FILEID,
CONVERT(decimal(12,2),ROUND(S.size/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty(S.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND((S.size-fileproperty(S.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],
S.name as [DATABASENAME],
S.FILENAME as [FILENAME]
FROM dbo.sysfiles S
… Read the rest

sql server DBA 999

Active Connections to SQL Server Database 2008, 2005 by Application or User

To Check Active Connections to SQL Server Database grouped by User

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections grouped by Login name related which are active in SQL Server Database.

To check Active Connections to SQL Server Database grouped by Login name


SELECT SUM(s.cpu_time) AS CPU,
SUM(s.total_scheduled_time) AS WaitTime,
SUM(s.total_elapsed_time) AS ElapsedTime,
SUM(c.num_reads) AS Reads,
SUM(c.num_writes) AS Writes,
COUNT(1) AS Connections,
s.original_login_name AS login
FROM sys.dm_exec_connections AS c
LEFT OUTER JOIN sys.dm_exec_sessions AS s
ON s.session_id = c.session_id
GROUP BY s.original_login_name

Below query is very helpful in finding the Total CPU usage, Waiting Time, Elapsed Time, Number of Reads, Number of Writes, connections Grouped by

… Read the rest