Databases Reference
In-Depth Information
Next, you are going to discover some key database properties for all the databases on the instance,
using the query shown in Listing 15-16.
LISTING 15-16:
Database property information
-- Recovery model, log reuse wait description, log file size, log usage size
-- and compatibility level for all databases on instance
SELECT db.[name] AS [Database Name], db.recovery_model_desc AS [Recovery Model],
db.log_reuse_wait_desc AS [Log Reuse Wait Description],
ls.cntr_value AS [Log Size (KB)], lu.cntr_value AS [Log Used (KB)],
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) *
100 AS
[Log Used %], db.[compatibility_level] AS [DB Compatibility Level],
db.page_verify_option_desc AS [Page Verify Option], db.is_auto_create_stats_on,
db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,
db.is_parameterization_forced,
db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on,
is_auto_shrink_on, is_auto_close_on
FROM sys.databases AS db WITH (NOLOCK)
INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK)
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK)
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0 OPTION (RECOMPILE);
-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs ?
-- What compatibility level are they on?
This query returns all the databases on the instance, including the system databases. For each data-
base, a number of important database properties are listed. First is the recovery model for the
database, which can be
SIMPLE
,
FULL
, or
BULK-LOGGED
. Knowing the recovery model for each of your
user databases is critically important! Next, you get the log reuse wait description for each database,
which tells you what is preventing the active portion of the transaction log from being reused.
One of the most common mistakes made by novice DBAs is to have a database running in the
default recovery model of
FULL
without taking regular transaction log backups. When this happens,
the transaction log eventually i lls up completely and attempts to autogrow the transaction log i le
(if autogrow is enabled). If the transaction log is able to autogrow, it continues to do so each time it
i lls up, until at some point it completely i lls up the disk where it is located. When this happens, you
will have a read-only database until you do something to correct the issue. While this is happening,
your log reuse wait description for that database will show up as
LOG
BACKUP
.
Regularly monitoring your log reuse wait description for each database also alerts you about other
problems you need to investigate. For example, if you are using database mirroring and there
are any problems with mirroring (such as not being able to send the log activity to the mirror or
Search WWH ::
Custom Search