Databases Reference
In-Depth Information
LISTING 15-14:
SQL Server memory dump information
-- Get information on location, time and size of any memory dumps from SQL Server
SELECT [filename], creation_time, size_in_bytes
FROM sys.dm_server_memory_dumps WITH (NOLOCK) OPTION (RECOMPILE);
-- This will not return any rows if you have
-- not had any memory dumps (which is a good thing)
This query, which was also added in SQL Server 2008 R2 SP1, tells you if and when your SQL
Server instance has generated any memory dumps. Hopefully, you will not see any results for this
query. If you do, start looking in the SQL Server Error Log(s) that correspond to the times for the
SQL Server memory dumps to see if you can i nd any relevant information about what happened to
generate the memory dump. You should also look at the Windows Event logs, and maybe even get
ready to open a support case with Microsoft.
Next, to i nd out how many databases are running on your SQL Server instance, and where they are
located, use the query shown in Listing 15-15.
LISTING 15-15:
Database fi lenames and paths
-- File Names and Paths for Tempdb and all user databases in instance
SELECT DB_NAME([database_id])AS [Database Name],
[file_id], name, physical_name, type_desc, state_desc,
CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Are there multiple data files?
This query returns the i le paths and sizes for the data and log i les for all the user databases and
tempdb. If the
type_desc
column is
ROWS
, that means you have a data i le, whereas
LOG
means a
transaction log i le. This query tells you how many user databases are running on the instance and
how large they are, which gives you some idea of the complexity of the server's workload.
You should be looking to see whether the data and log i les are on different drive letters. Some SAN
administrators like to provision just one large LUN, which makes it harder to track what is going on
from SQL Server and Windows. You also want to ensure that tempdb is not running on the C: drive
of the database server (which is what happens with a default standalone installation of SQL Server).
You can also see whether there are multiple tempdb data i les instead of just one (See Chapter 8),
and whether the larger user databases have multiple data i les instead of just one large data i le.
Search WWH ::
Custom Search