Update Database Compatibility Level


-- This statement updates all databases that have a compatibility level
-- of 90 (SQL Server 2005) to 100 (SQL Server 2008)

DECLARE @ID INT
DECLARE @COUNT INT
DECLARE @DB_NAME NVARCHAR(200)
DECLARE @SQL NVARCHAR(200)

SET @ID = 1
SET @COUNT = (SELECT MAX(DBID) FROM sys.sysdatabases WHERE cmptlevel <> 100)

WHILE @ID <= @COUNT 
 BEGIN 
   SET @DB_NAME = (SELECT name FROM sys.sysdatabases 
                   WHERE cmptlevel = 90 AND dbid = @ID) 
                     IF @DB_NAME IS NOT NULL 
                       BEGIN 
                         SET @SQL = 'ALTER DATABASE 
                                     [' + @DB_NAME + '] 
                                     SET COMPATIBILITY_LEVEL 
                                       = 100' 
                         EXEC (@SQL) 
                     END
                     SET @ID = @ID + 1 
 END

List All Jobs on a SQL Server

SELECT name, description, enabled, date_created, date_modified
FROM msdb.dbo.sysjobs;

"Property Owner is not available for Database" In SQL Server 2008

Right click on a database and select properties - The following error appears:

"Property Owner is not available for Database '[TEST]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)"

I was logged in as SA so I was not sure why this was happening. So I ran this query t see exactly who owned the DB:

SELECT databases.NAME, server_Principals.NAME
FROM sys.[databases]
FULL JOIN sys.[server_principals]
ON [databases].owner_sid = [server_principals].sid

Turns out that the owner name spot was NULL, so nobody owned it - not even SA. This fixed it:

USE [ReportServerTempDB]
GO
EXEC sp_changedbowner 'sa'
GO

SP to Automatically Shrink all Files on all DBs on a Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: K Griffith
-- Create date: 28 Dec 2010
-- Description: Dynamically loops through all the files of a given DB and shrinks them.
-- Caveat: This procedure had to be done with Dynamic SQL because functions
-- (such as USE) cannot have variables passed to them. Additionally, EXEC()
-- starts a new thread and is unaware of anything going on around it.
-- Therefore, the ENTIRE procedure had to be coded using Dynamic SQL and
-- EXEC().
-- =============================================
ALTER PROCEDURE [dbo].[sp_ShrinkFiles]
-- Add the parameters for the stored procedure here
@DB_NAME NVARCHAR(300)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL AS NVARCHAR(500)
DECLARE @SQL1 AS NVARCHAR(500)
DECLARE @SQL2 AS NVARCHAR(500)
DECLARE @SQL3 AS NVARCHAR(500)
DECLARE @SQL4 AS NVARCHAR(500)

-- Select the DB for use
SET @SQL = 'USE [' + @DB_NAME + ']'

-- Declare and set variables dynamically to run in the EXEC() function
SET @SQL1 = ' DECLARE @FILES AS INT
DECLARE @FILENAME AS VARCHAR(500)
DECLARE @SPACEUSED AS INT
SET @FILES = (SELECT COUNT(NAME) FROM sys.database_files)'

-- Shrink the files by setting the recovery model to SIMPLE
SET @SQL2 = ' ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE '

-- Loop through all the files in the given DB. Get file names and Space Used by each
-- Shrink each file
SET @SQL3 = ' WHILE @FILES > 0
BEGIN
SET @FILENAME = (SELECT name FROM sys.database_files
WHERE FILE_ID = @FILES)

SET @SPACEUSED = (SELECT CAST(FILEPROPERTY(name, ''SpaceUsed'')
AS int)/128.0
FROM sys.database_files
WHERE FILE_ID = @FILES)

DBCC SHRINKFILE (@FILENAME, @SPACEUSED)

SET @FILES = @FILES - 1
END '

-- Reset the database recovery model.
SET @SQL4 = ' ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL'

-- Execute all of the Dynamic SQL
EXEC (@SQL + @SQL1 + @SQL2 + @SQL3 + @SQL4)

END

Getting the Physical Location of the MDF and LDF Files in SQL Server

SELECT name, physical_name
FROM sys.master_files;

Some Notes about Mirroring in SQL Server 2008

Some important items to note about database mirroring:

• The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database
• The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups
• The mirror database must have the same name as the principal database
• Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' )

- It's much easier to set up a mirrored configuration than using other high-availability technologies, such as failover clustering
- The disadvantage of mirroring working at the DB level is that system DBs (master, model, tempdb, and msdb) are not mirrored and, in fact, can't be mirrored. Therefore logins, SQL Server Agent Jobs, and other security configurations aren't identical between the two servers. This means there is a bit more administrative overhead to ensure that things function on both servers the same. However, there is more flexibility in the use of both systems
- DB mirroring involves a principal server and a mirror server. An optional witness server can also be involved. The witness server helps decide when an automatic failover can occur, under very specific circumstances
- The principal and mirror roles for each DB swap when a failover occurs
- There are 2 types of mirroring: synchronous and asynchronous
- In synchronous mirroring (AKA full transactional safety), a transaction on the principal database isn't considered to be committed until a copy of the transaction log record for that transaction is physically copied over the network to the mirror server. Therefore, no data loss can be guaranteed in a synchronous configuration
- Asynchronous configuration is AKA 'having transactional safety turned off' -> the log records are sent to the mirror server but aren't guaranteed to be in sync. So, some data loss may occur if there is a failure on the principal system before the committed transaction log records are copied to the mirror server