Martina Ponca's Blog

Working with MDF files from Book CDs:
—————————————————–
Script:
—————————————————–
sqlcmd -E -o “%CD%\attach.log” -Q “exec master..sp_attach_db ‘ExamplesDB’, [%CD%\ExamplesDB.mdf]” -e

—————————————————–
Error:
—————————————————–
exec master..sp_attach_db ‘ExamplesDB’, [C:\Microsoft Press\SQL Server 2008 Examples\Setup\ExamplesDB.mdf]
File activation failure. The physical file name “C:\Microsoft Press\SQL Server 2008 Examples\Setup\ExamplesDB_log.LDF” may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Server SHARP, Line 1
Could not open new database ‘ExamplesDB’. CREATE DATABASE is aborted.

—————————————————–
Solution:
—————————————————–
CHECKDB From Every Angle: EMERGENCY mode repair – the very, very last resort

/*	1. create database with the same name of mdf and ldf
	2. shut down slq server
	3. swap mdf files
*/
ALTER DATABASE ExamplesDB SET Emergency
GO
--DBCC REBUILD_LOG
--GO
ALTER DATABASE ExamplesDB SET SINGLE_USER;
GO
DBCC CHECKDB (ExamplesDB, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE ExamplesDB SET MULTI_USER;
GO

SELECT state_desc FROM sys.databases WHERE name='ExamplesDB';

USE AventureWorks;
GO
DECLARE @LoginName AS VARCHAR (50);
SET @LoginName = '%' + 'MyUser' + '%';

WITH   perms_cte -- all permissions
AS     (SELECT USER_NAME(p.grantee_principal_id) AS principal_name,
               dp.principal_id,
               dp.type_desc AS principal_type_desc,
               p.class_desc,
               OBJECT_NAME(p.major_id) AS object_name,
               p.permission_name,
               p.state_desc AS permission_state_desc
        FROM   sys.database_permissions AS p INNER JOIN
               sys.database_principals AS dp
               ON p.grantee_principal_id = dp.principal_id)
 --users
SELECT p.principal_name,
       p.principal_type_desc,
       p.class_desc,
       p.[object_name],
       p.permission_name,
       p.permission_state_desc,
       CAST (NULL AS sysname) AS role_name
FROM   perms_cte AS p
WHERE  principal_type_desc <> 'DATABASE_ROLE'
		AND (@LoginName IS NULL OR  p.principal_name LIKE @LoginName)
-- database roles
UNION
SELECT p.principal_name,
       p.principal_type_desc,
       p.class_desc,
       p.[object_name],
       p.permission_name,
       p.permission_state_desc,
       CAST (NULL AS sysname) AS role_name
FROM   perms_cte AS p
WHERE  principal_type_desc = 'DATABASE_ROLE'
		AND (@LoginName IS NULL OR  p.principal_name LIKE @LoginName)
--role members
UNION
SELECT rm.member_principal_name,
       rm.principal_type_desc,
       p.class_desc,
       p.object_name,
       p.permission_name,
       p.permission_state_desc,
       rm.role_name
FROM   perms_cte AS p RIGHT OUTER JOIN
       (SELECT role_principal_id,
               dp.type_desc AS principal_type_desc,
               member_principal_id,
               user_name(member_principal_id) AS member_principal_name,
               user_name(role_principal_id) AS role_name
        FROM   sys.database_role_members AS rm INNER JOIN
               sys.database_principals AS dp
               ON rm.member_principal_id = dp.principal_id) AS rm
       ON rm.role_principal_id = p.principal_id
WHERE (@LoginName IS NULL OR rm.member_principal_name LIKE @LoginName)

MSDN XQuery Expressions
MSDN XML Support in Microsoft SQL Server 2005
Indexing XML Data Stored in a Relational Database

Free Books:
The Art of XSD – SQL Server XML Schema Collections
SQL Server Security, Peformance, Administration (Red-Gate.com)

SQL Server 2005 Performance Dashboard Reports package has many more helpful reports.

USE MyDbName;
DECLARE @dbid AS INT;
SELECT @dbid = db_id();
SELECT @dbid, db_name(), db_name(@dbid);

-- Not Used Indexes
SELECT   object_name(i.object_id) AS objectname,
         i.name AS indexname,
         i.index_id
FROM     sys.indexes AS i, sys.objects AS o
WHERE    objectproperty(o.object_id, 'IsUserTable') = 1
         AND o.object_id = i.object_id
         AND i.index_id NOT IN (SELECT s.index_id
                                FROM   sys.dm_db_index_usage_stats AS s
                                WHERE  s.object_id = i.object_id
                                       AND i.index_id = s.index_id
                                       AND database_id = @dbid)
ORDER BY objectname, i.index_id, indexname ASC;

-- Missing indexes
SELECT   'CREATE INDEX missing_index_' + CAST (d.index_handle AS VARCHAR) + ' ON ' + d.statement + ' ('
			+ CASE WHEN equality_columns IS NULL THEN '' ELSE equality_columns END
			+ CASE WHEN (LEN(equality_columns) > 0 AND Len(inequality_columns) > 0) THEN ', ' ELSE ' ' END
			+ CASE WHEN inequality_columns IS NULL THEN '' ELSE inequality_columns END + ')'
			+ CASE WHEN (Len(included_columns) > 0) THEN ' INCLUDE (' + included_columns + ')' ELSE ' ' END
         AS script_to_create_index,
         d.object_id,
         d.equality_columns,
         d.inequality_columns,
         d.included_columns,
         d.statement AS fully_qualified_object,
         d.index_handle,
         gs.*
FROM     sys.dm_db_missing_index_groups AS g INNER JOIN
         sys.dm_db_missing_index_group_stats AS gs
         ON gs.group_handle = g.index_group_handle INNER JOIN
         sys.dm_db_missing_index_details AS d
         ON g.index_handle = d.index_handle
WHERE    d.database_id = isnull(@dbid, d.database_id)
		 --AND d.object_id = isnull(@ObjectID, d.object_id)
ORDER BY avg_user_impact DESC; --avg_total_user_cost desc

-- Index Usage
SELECT   i.index_id,
         object_name(i.object_id) AS objectname,
         i.name AS indexname,
         s.*
FROM     sys.indexes AS i, sys.objects AS o, sys.dm_db_index_usage_stats AS s
WHERE    objectproperty(o.object_id, 'IsUserTable') = 1
         AND o.object_id = i.object_id
         AND s.object_id = i.object_id
         AND i.index_id = s.index_id
         AND database_id = @dbid
ORDER BY objectname, i.index_id, indexname ASC;

I needed to modify column’s XML Schema (drop xml schema, re-attach xml schema) , and got Error Message 511 regarding maximum row size 8060 on SQL 2005.

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8074 which is greater than the allowable maximum of 8060.

I had to run DBCC CleanTable to free up some space. Supposedly, also rebuilding indexes on the table would solve the problem.

Example:

DECLARE
	@StringToSearch varchar(100) ,
	@serverName varchar(50)
SET @StringToSearch = 'sales' -- or column
SET @StringToSearch = '%' +@StringToSearch + '%'
SET @serverName = @@ServerName

USE AdventureWorks
-- Query Table and Column objects
SELECT syscolumns.Name	AS ColumnName,
       sysobjects.name	AS TableName,
       DB_Name()		AS [Database],
       @serverName		AS ServerName
FROM   syscolumns WITH (READUNCOMMITTED) INNER JOIN
       sysobjects WITH (READUNCOMMITTED)
       ON sysobjects.id = syscolumns.id
WHERE  (syscolumns.Name LIKE @StringToSearch OR sysobjects.name like @StringToSearch )
       AND syscolumns.Name NOT LIKE '@%';

-- Query stored procedures, functions, views
SELECT   DISTINCT sysobjects.Name	AS ObjectName,
                  sysobjects.Type	AS ObjectType,
                  DB_Name() AS DBName,
                  @serverName AS ServerName
FROM     sysobjects WITH (READUNCOMMITTED) INNER JOIN
         syscomments WITH (READUNCOMMITTED)
         ON sysobjects.Id = syscomments.ID
            AND syscomments.Text LIKE @stringtosearch
ORDER BY sysobjects.Type, sysobjects.Name;

-- Query XML Schema Collections
SELECT --xsc.xml_collection_id,
		s.name + '.' + xsc.name AS XMLCollection,
       'XML Schema Collection'	AS ObjectType,
       xml_schema_namespace(s.name, xsc.name) AS ObjectXML,
       xsc.modify_date, -- xsc.principal_id, xsc.create_date,
       DB_Name()	AS DBname,
       @serverName	AS ServerName
FROM   sys.xml_schema_collections AS xsc WITH (READUNCOMMITTED) INNER JOIN
       sys.schemas AS s WITH (READUNCOMMITTED)
       ON xsc.schema_id = s.schema_id
WHERE  xsc.xml_collection_id <> 1
       AND (CONVERT (VARCHAR (MAX), xml_schema_namespace(s.name, xsc.name))
						COLLATE DATABASE_DEFAULT LIKE @StringToSearch
            OR s.name LIKE @StringToSearch
            OR xsc.name LIKE @StringToSearch);

-- Query SQL Server Agent Jobs - you will need admin rights
SELECT sysjobs.name + ' (JobName)' AS SQLJobName,
       sysjobsteps.step_name + ' (StepName)' AS SQLJobStepName,
       sysjobsteps.command,
       sysjobsteps.Database_Name,
       sysjobsteps.Server
FROM   msdb.dbo.sysjobs WITH (READUNCOMMITTED) INNER JOIN
       msdb.dbo.sysjobsteps WITH (READUNCOMMITTED)
       ON sysjobs.Job_ID = sysjobsteps.Job_ID
WHERE  sysjobsteps.command LIKE @StringToSearch;

List of XML Schemas:

List of Columns with XML Schemas