Identify the last full, log and differential backup date and times for each database.

The following script is very useful if you just want to find out when each database was last backed up. It returns the database name, it’s recovery type, (important so that missing log backups for SIMPLE recovery can be ignored), and the date and time of each full, log and differential backup. This query could be used in an Agent job which could be scheduled to run each day and look for any databases that (a), haven’t had a full backup for more than a day and (b), any databases which are using the full recovery model, which haven’t had a transaction log backup for more than a day.


SELECT DISTINCT D.database_id AS Database_Id,
                D.NAME        AS Database_Name,
D.recovery_model_desc,
Isnull(CONVERT(VARCHAR(25), Max(F.backup_start_date)
OVER (
partition BY F.database_name ),
113), ‘NEVER’)
AS Last_Full_Backup_Date,
CASE D.recovery_model_desc
WHEN ‘SIMPLE’ THEN ‘N/A’
ELSE Isnull(CONVERT(VARCHAR(25), Max(L.backup_start_date)
OVER (
partition BY L.database_name ), 113),
‘NEVER’)
END           AS Last_Log_Backup_Date,
                Isnull(CONVERT(VARCHAR(25), Max(I.backup_start_date)
OVER (
partition BY I.database_name ),
113), ‘NEVER’)
AS Last_Diff_Backup_Date
FROM   sys.databases D
LEFT OUTER JOIN msdb..backupset F
ON F.database_name = D.NAME
AND F.type = ‘D’
LEFT OUTER JOIN msdb..backupset L
ON L.database_name = D.NAME
AND L.type = ‘L’
LEFT OUTER JOIN msdb..backupset I
ON I.database_name = D.NAME
AND I.type = ‘I’
ORDER  BY D.database_id; 

Advertisements

Never forget what that database is used for, with SQL Server Extended Properties!

When I create a new database, I always set the Extended Properties of the database so that I can record a few key pieces of information. This is really useful to be able to quickly identify what the database is used for, who the business owner is, the date it was created and any other information that may be valuable, but easily forgotten.  There are numerous times people have asked me what the database is for such and such an application, especially when there are a few versions of the same software being used across the organisation and each one has its own database named slightly differently.

You can see the extended properties of a database by right clicking on the database object in SSMS and selecting ‘Properties’. Then select ‘Extended Properties’.

ExtendedPropertiesBlank

I usually keep to the same format when recording property values, namely:

  • A description of the database, including the application that uses it.
  • The date it was created.
  • The business owner, or team which supports the application.
  • The server name of each server that connects to it.  This is especially useful when the database has to be moved to a new or upgraded SQL Server and you need to know which servers have to have their connection strings changed.

To fill in the extended property I run the following script on the database in question, adding in the values for each property type:

USE []
GO
EXEC sp_addextendedproperty @name=N’Contact’, @value=N” — Example: Accounts Team, John Smith
EXEC sp_addextendedproperty @name=N’Created’, @value=N” — Example: 01/01/2015
EXEC sp_addextendedproperty @name=N’Description’, @value=N” — Example: Used for Payroll data, must be setup with Full Recovery.
EXEC sp_addextendedproperty @name=N’ApplicationServer’, @value=N” — Example: AppServer1, AppServer15
GO

Once the databases have had their properties set, you can then periodically run reports on them. This can be useful if you want to find out if a database is still used, but don’t know who the contact is or what the database is actually used for. To do this, the following script can be run to generate a report:

— Create a global tempory table to hold the results.
CREATE TABLE ##results
(id INT IDENTITY(1, 1) ,
DatabaseName VARCHAR(50) ,
Description VARCHAR(250) ,
Contact VARCHAR(250));

— This first command will simply add all databases to the table
DECLARE @command VARCHAR(1000);
INSERT INTO ##results
(DatabaseName)
SELECT name
FROM sys.databases
WHERE name NOT IN ( ‘master’, ‘tempdb’, ‘model’, ‘msdn’ );

— Build the update command to get the description and Contact information for each database.
SELECT @command = ‘USE [?] Update ##results
SET Description = CONVERT(VARCHAR(250), Description.value),
Contact = CONVERT(VARCHAR(250), Contact.value)
FROM sys.extended_properties Description,
sys.extended_properties Contact
WHERE Description.class_desc =”DATABASE” AND Description.name = ”MS_Description”
AND Contact.class_desc =”DATABASE” AND Contact.name = ”MS_Contact”
AND ##results.DatabaseName = DB_NAME(DB_ID());

— Execute the command on each database
EXEC sp_MSforeachdb @command;

— Select the results and drop the table
SELECT *
FROM ##results
ORDER BY DatabaseName;

DROP TABLE ##results;

This results in the following output:

ExtendedPropertiesResults

What is also useful, is this report shows any databases that don’t have the properties set. This means you can run this periodically to make sure you keep the records updated.  You can change the query used in the report to generate any information held in the extended properties.

For more information on Extended Properties check out BOL, or this blog post by Glen Berry which details the use of Extended Properties in great detail.

Identify the size and document count of a single SharePoint Site

The following query is useful, if you have a SharePoint site which uses one content database and all websites hang off the one top url.  The script can be used to identify the total size of each site and the number of documents contained in the site.  

USE [<YourContentDatabaseName>]; — don’t forget the semi-colon!

with
folderhierachy as(
select f.fullurl, (((sum(cast(d.size as bigint))) / 1024) /1024) as sizeinMB, count(d.Id) as DocCount
from webs f, docs d, lists l
where
d.webid = f.id
and
l.tp_id = d.listid
and
d.size > 0
and
(d.LeafName NOT LIKE ‘%.stp’)
AND (d.leafname NOT LIKE ‘%.aspx’)
AND (d.leafname NOT LIKE ‘%.xfp’)
AND (d.leafname NOT LIKE ‘%.dwp’)
AND (d.leafname NOT LIKE ‘%template%’)
AND (d.leafname NOT LIKE ‘%.inf’)
AND (d.leafname NOT LIKE ‘%.css’)
group by f.FullUrl
)
select substring(fullurl,1,charindex(‘/’,fullurl)) as Url, sum(sizeinMB) as “Size In MB”, sum(DocCount) as “Document Count”
from folderhierachy
group by substring(fullurl,1,charindex(‘/’,fullurl))
order by Url, [Size In MB] desc;