Databases Reference
In-Depth Information
This code merely switches your database context to the named database. Be sure to change the name
to the database that you want to look at. Many people make the mistake of running these queries
while connected to the master system database. If you do that, you will get a lot of mostly useless
information about your master database.
After you are sure you are pointing at the correct database, you can i nd out how large it is with the
query shown in Listing 15-33.
LISTING 15-33:
Database fi le sizes and space available
-- Individual File Sizes and space available for current database
SELECT name AS [File Name], physical_name AS [Physical Name], size/128.0 AS [Total
Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space
In MB], [file_id]
FROM sys.database_files WITH (NOLOCK) OPTION (RECOMPILE);
-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log is not full!!
This query shows you where the data and log i les for your database are located. It also returns how
large and how full they are. It is a good way to help monitor and manage your data and log i le siz-
ing and i le growth. I don't like to see my data i les getting too close to being 100% full, and I don't
like to see my log i les getting more than 50% full. You should manually manage the growth of your
data and log i les, leaving autogrow enabled only for an emergency.
The next query, shown in Listing 15-34, shows a DMV that enables you to focus solely on your log
i le size and space used.
LISTING 15-34:
Transaction log size and space used
-- Get transaction log size and space information for the current database
SELECT DB_NAME(database_id) AS [Database Name], database_id,
CAST((total_log_size_in_bytes/1048576.0) AS DECIMAL(10,1))
AS [Total_log_size(MB)],
CAST((used_log_space_in_bytes/1048576.0) AS DECIMAL(10,1))
AS [Used_log_space(MB)],
CAST(used_log_space_in_percent AS DECIMAL(10,1)) AS [Used_log_space(%)]
FROM sys.dm_db_log_space_usage WITH (NOLOCK) OPTION (RECOMPILE);
-- Another way to look at transaction log file size and space
This query, using a DMV introduced in SQL Server 2008 R2 Service Pack 1, enables you to directly
query your log i le size and the space used, as a percentage. It would be relatively easy to use this
DMV to write a query that could be used to trigger an alert when a percentage of log i le usage that
you specify is exceeded. Of course, if you are properly managing the size of your transaction log,
along with how often you take transaction log backups when you are in the FULL recovery model,
you should not run into problems that often. The obvious exceptions are if something happens with
Search WWH ::
Custom Search