Query to get database information

Prashanth Jayaram provided a blog post about a script he has to get information about databases on your server.
This is a perfect script for keeping up with what’s going on in your environment or getting an idea about what’s going on a client’s site.

The script Prashanth provided had some errors, so I corrected those you can just copy the code below, paste it in SSMS and run it.

This is the information returned:
Servername
Database Name
[Status]
DataFiles
Data MB
LogFiles
Log MB
TotalSizeMB
Updateability
UserAccess
RecoveryModel
Version
Compatibility Level
Creation date
Last Backup Date

SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,'Updateability'))  Updateability,
convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess ,
convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,'Version')) Version ,
CASE COMPATIBILITY_LEVEL 
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20),  create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'
FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup]
FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME
[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all databases on a server

If need to do something to all your databases there is a stored proc for you.  Its called sp_msforeachdb.
This will loop though all your databases and perform a command for you.  It’s similar to the foreach table sp I wrote about here.
There is one parameter @command.

Let’s get right to an example.

EXECUTE sp_msforeachdb ‘USE ? IF DB_NAME() NOT IN(”master”,”msdb”,”tempdb”,”model”) BACKUP DATABASE ? TO DISK = ”G:?.bak, WITH INIT”’

The ? gets replaced with the database name.
So that will backup any user database to the the G: drive.

This can simplify any looping code you have.

[tweetmeme only_single=”false”]

What’s new in SQL 2008

I talk about SQL server every chance I get because of some the great features available (SSRS, SSIS, SSAS).  So I looked into SQL 2008 the first chance I got.  More than once I have been asked about the new features of SQL server 2008.  So here is a list I complied (not 100% of the features, just the ones I thought they were easy to sell)

Top new features:

  1. T-SQL Intellisense – Intellisense is now available for T-SQL. This allows for easier and quicker development.
  2. Date/Time data types – There is a new data type for just the date and there is a new data type for just the time. There is no longer the need to program around a datetime data type.
  3. Encrypted Backups – Backups can be encrypted to prevent data disclosure or tampering.
  4. Transparent Data Encryption – The entire database can be encrypted by the SQL engine. This encrypts all database data and log files for a database log. All indexes and tables are encrypted. No changes to applications are required.
  5. Memory Management improvements in Reporting Services – Running large reports will no longer consume all available memory.

 

List of some of the new features

Security/Auditing
– Transparent Data Encryption (encryption while data is ‘still’ on disk, transparent to applications)
– External Key Management (Consolidation of key management, integration with external products)
– Data Auditing (1st-class ‘AUDIT’ objects; DDL support; audit objects, principals, data, etc.; support for multiple logging targets)

Availability/Reliability
– Pluggable CPU support
– Enhanced Database Mirroring (compression of mirror streams, enhanced performance, automatic page-level repair for principal/mirror)

Performance
– Data compression (easy to enable/disable online, more efficient data storage (this is NOT traditional data compression))
– Backup stream compression (server level control or backup statement control, all backup types)
– Performance data collection (single, common framework for data collection, reporting, and storage/warehousing)
– Improved Plan Guide support (plan freezing, pull plans directly from plan cache, SSMS integration, etc.)
– Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization amoung groups)

Management
– Policy-based management framework (manage via policies vs. scripts, enterprise-wide support, automated monitoring/enforcement, etc.)
– Integrate with Microsoft System Center
– Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW, unprecidented insight into goings-on)

Development Enhancements
– Improved datetime datatypes (100th nanosecond precision (7 digits past second), time-zone datetime offset, date only, time only)
– HierarchyID datatype (hierarchical-aware data type, ORDPath values, built-in functions, methods, etc.)
– Entity Data Model support (develop ‘business entities’ vs. tables, model complex relationships, retrieve entities vs. rows/columns)
– LINQ
– Sql Server Change Tracking (Change Data Capture, get ‘diff’ data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.))
– Table Valued Parameters
– MERGE statement (‘upsert’ data, also includes deletion functionality)
– Large UDT’s (no more 8000 byte limit on CLR-based UDTs, no more 8000 byte limit for UDA’s)
– Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes)
– XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.)
– Inline initialization and compound assignment

  Service Broker
– New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools, )
– Conversation Priority (set message ordering, send/receive impact, 1-10 levels)

Data Storage
– Data compression (see above)
– FILESTREAM attribute (get the ‘best of both’ functionality from BLOBs in the DB vs. BLOBs on filesystem, no more “to blob or not to blob”)
– Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing)
– Sparse columns (more efficient storage for ‘wide’ tables with many columns that repeat and don’t contain data)
– New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.)

Data Warehousing/ETL
– Partitioned Table Parallelism (no more thread limit per partition)
– Star Join support (no special syntax, optimizer based, full backward syntax support)
– Data compression (see above)
– Resource Governor (see above)
– Persistent Lookups in SSIS (no more re-querying for lookup operators, cache lookups in multiple ways, persist lookups to disk)
– Improved thread scheduling in SSIS (shared thread pool, pipeline parallelism)
– Change Data Capture (see above)
– MERGE statement (see above, great uses with slowly changing dimensions)
– Scale-out analysis services (read-only storage supports multiple AS servers)
– Subspace computations
– New Tools for Cube design
– Best Practice Design Alerting
– Backup cubes with better scalability
– Data-mining add-ins for Excell

Reporting
– IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer)
– Rich-text support
– Enhanced visualiztion (graphing)
– New Word rendering (render reports to Microsoft Word)