Get a List of CLR’s Registered to a Database

Get a list of CLR’s registered to a database.  This is helpful when migrating or troubleshooting.  For more information on CLR’s check out Books Online or http://msdn.microsoft.com/en-us/library/ms131089(SQL.100).aspx

 

SELECT o.name AS ObjectName, a.name AS Assembly,

      o.type_desc AS ObjectType

FROM   sys.assembly_modules am

JOIN   sys.objects o ON am.object_id = o.object_id

JOIN   sys.assemblies a ON a.assembly_id = am.assembly_id

WHERE  a.is_user_defined = 1

UNION

SELECT at.name, a.name, ‘Type’

FROM   sys.assembly_types at

JOIN   sys.assemblies a ON at.assembly_id = a.assembly_id

WHERE  a.is_user_defined = 1

Remove maintenance plans left after a server rename.

Many times when upgrading SQL Server or migrating to new hardware, it is necessary to rename the server when it’s ready to be used as production.  However, if any maintenance plans created before the server was renamed are still present after the rename, you may find that trying to delete the old jobs results in error 547 (below).

Drop failed for Job ‘User Databases – Full Daily Backup.Subplan_1′. (Microsoft.SqlServer.Smo)

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_schedule_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘schedule_id’.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)_________________________________________________________________________ 

 Here are the steps to correct the problem:

 – Find the maintenance plan name and id that you want to delete.

– Write down the id of the one you want to delete.

SELECT name, id FROM msdb.dbo.sysmaintplan_plans

 

 – Place the id of the maintenance plan you want to delete

– into the below query to delete the entry from the log table

DELETE FROM msdb.dbo.sysmaintplan_log WHERE plan_id = ‘<<id from 1st query>>’

 – Place the id of the maintenance plan you want to delete

– into the below query and delete the entry from subplans table

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE plan_id = ‘<<id from 1st query>>’

 – Place the id of the maintenance plan you want to delete

– into the below query to delete the entry from the plans table

DELETE FROM msdb.dbo.sysmaintplan_plans WHERE id = ‘<<id from 1st query>>’

 Now you can delete the jobs from Management Studio. 

Find out when statistics were last updated.

Your SQL database uses statistics to choose the most efficient plan for retrieving or updating data, so you want them accurate and up-to-date. Otherwise, it can cause the SQL Server engine to take the wrong path, and taking the wrong path means slower performance. Check out the code below to see when your stats were last updated.   If you need help with SQL server, please contact me. It’s also good practice to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks.

SQL Server Administration:  Find out when statistics were last updated.   Out of date statistics are a common cause of poor performance.  Tables that are active should have statistics updated regularly.  Use the query below to find out if your databases have old statistics.   USE <<database>> GO – find last time when stats had been updated. SELECT object_id AS [TableId], index_id AS [IndexId], OBJECT_NAME(object_id) AS [TableName], name AS [IndexName], stats_date(object_id,index_id) stat_update_date, INDEXPROPERTY(object_id, name,’IsAutoStatistics’) is_system_generated_stats FROM sys.indexes WITH (NOLOCK) OPTION(MAXDOP 1) GO

What’s Causing Files to Automatically Grow

Every DBA has encountered a database that has a mystery statement that will unexpectedly cause either the transaction log or a table to grow out of control and fill the drive.  Here is a way to find out who is running what statement that is causing the problem.  Find out what statements are causing database files to automatically grow.

 /*  Step 1 – Capture a Profiler or server side trace with these events:

      Event 41 = SQL:StmtCompleted

      Event 45 = SP:StmtCompleted

      Event 92 = Data file auto growth event

      Event 93 = Log file auto growth event

* Make sure to include these columns:

      ApplicationName,EventClass,DatabaseID,HostName,LoginName,

      ObjectID,SPID,StartTime,EndTime,TransactionID,TextData,

      Reads,Writes,CPU,Duration

*/   

–    Step 2 – Load the trace into a table:

SELECT*INTO TraceTable FROM::fn_trace_gettable(<<path to trace file>>,DEFAULT)

GO

–    Step 3 – Make a column to hold part of the textdata for search purposes

ALTERTABLE TraceTable ADD QueryText VARCHAR(MAX)

GO

UPDATE TraceTable SET QueryText =CONVERT(VARCHAR(MAX), TextData)

GO

–    Step 4 – Get all spids and trasactionids that are associated with auto growth

SELECTDISTINCT SPID, TransactionID

INTO #GrowTrans

FROM TraceTable

WHERE EventClass IN(92, 93)

GO

–    Step 5 – Get the info on statements called by the spid and transactionid associated with auto growth

SELECT t.*

FROM TraceTable t

JOIN #GrowTrans g ON (t.SPID = g.SPID AND t.TransactionID = g.TransactionID)

ORDERBY StartTime

GO

Attempt to Recover a Suspect Database

Databases will go into suspect state for a multitude of reasons. If you find your database in SUSPECT state, check all error logs and the Windows Event Log for any related errors, and try to resolve them. This is a disaster situation!  Refer to the script below for help.

SQL Server Administration:  You can try to save data by creating an empty database and copying tables to it after placing the database into EMERGENCY and then SINGLE_USER state. Most of the time, a suspect database has to be restored to be able to be used again. However, sometimes it is possible to repair a suspect database. I recently did just that for a client, and recovered 4 suspect databases.

 

Use the script below to check the integrity of the database or to repair it. Running an integrity check will tell you where the errors are and what the minimum repair you can do is. Always make sure you have database and transaction log backups so you can recover a failed database to point in time, if necessary. 

– Attempt repair suspect database

USE master;

GO

– Set database in EMERGENCY read only state

ALTER DATABASE MyDB SET EMERGENCY

GO

– Set database in SINGLE_USER state

ALTER DATABASE MyDB SET SINGLE_USER

GO

USE MyDB

DBCC CHECKDB(MyDB) WITH NO_INFOMSGS  — Run integrity Check

– DBCC CHECKDB (MyDB, REPAIR_REBUILD) WITH NO_INFOMSGS  — Repair database with no dataloss

– DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS  — Repair database with possible dataloss

GO

– Set database in MULTI_USER state

ALTER DATABASE MyDB SET MULTI_USER

GO

Find out How Much Data Files are Waiting with Filestats

Disk performance is a common issue on SQL Server systems.  In these situations, it is very useful to know how much SQL Server is waiting for I/O and what files are handling the most work or experiencing the poorest performance.  The virtual file stats DMF provides this information and is ideal for periodic captures, e.g., at the beginning, middle, and end of a day.  This function returns one row per file and can return this information either for specific files or for every file attached to a SQL Server instance.

To retrieve information for all files attached to a 2005 or 2008 instance, use the following syntax:

select * from sys.dm_io_virtual_file_stats (NULL, NULL)

The equivalent on SQL 2000 is as follows:

select * from ::fn_virtualfilestats(-1,-1)

Some of the most useful information is shown below:

• Number of reads and writes issued against this file
• Total number of bytes read from and written to this file
• Total time, in milliseconds, users waited for I/O completions overall (also reads and writes specifically on 2005 and 2008)
• Number of disk bytes used by this file

Since these values are cumulative from the last time the SQL Server instance was restarted, differences between samples must be computed.  Either SQL Server T-SQL or Excel can be used to perform these calculations.  In addition, the database and file IDs must be translated into meaningful names using the sys.databases  and sys.database_files DMVs on 2005 and 2008.  For those still using 2000, system tables must be used to decode these values.

Using this information, a DBA can prove whether the disk subsystem is performing adequately and possibly find alternative disk locations to which database files may be moved.

Master Database Compatibility Level

If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases.  This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade.  This has the negative side effect of not being able to use operators, keywords and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level.

For instance, if you want to examine currently executing queries using SQL 2005 with your master database in 80 compatibility …

USE master

GO

SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,

CASE WHEN s1.statement_end_offset = -1 THEN 8192

ELSE s1.statement_end_offset/2 – s1.statement_start_offset/2 END + 1) as QueryText

FROM sys.dm_exec_requests s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

GO

… you will receive the following error…

Msg 321, Level 15, State 1, Line 5

“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

As a workaround, you can execute the same query against tempdb, model or msdb and it will work with no errors.  It is permissible to change the compatibility level of the master database should you find it in a low compatibility level.

–SQL 2005

EXEC sp_dbcmptlevel ‘master’ , 90

GO

–SQL 2008

EXEC sp_dbcmptlevel ‘master’ , 100

GO

http://msdn.microsoft.com/en-us/library/ms178653.aspx

http://technet.microsoft.com/en-us/library/bb933942.aspx

Oct 2010 TOM: Are your backups ready for a disaster?

Database backups are a critical part of SQL Server disaster planning. It’s also critical to know that your backups are viable. Read the following tip and be assured you’re ready should a disaster hit. And don’t forget to regularly monitor the 11 VitalSigns  by SQLRx to debug performance bottlenecks.

SQL Server Administration: The best way to verify backups is to actually restore backup files, but many times this task falls by the wayside due to lack of space or time. In lieu of restoring backup files, you can use RESTORE VERIFYONLY to confirm that a backup set is complete and readable. Here is a routine that can be used to verify the most recent backup file for each database in a SQL instance. This can be used in a job to routinely verify that backup files are intact and should be able to be restored in the event of a disaster.
___________________________________

SET NOCOUNT ON
DECLARE @dbname VARCHAR(128)
DECLARE @finishdate DATETIME
DECLARE @diskbackup NVARCHAR(260)
DECLARE @verifystatement NVARCHAR(360)
DECLARE @timebegin DATETIME
DECLARE @timeend DATETIME
DECLARE @timeelapsed INT
DECLARE @FileFound INT

DECLARE csrDatabases CURSOR FOR
SELECT database_name, MAX(backup_finish_date)
FROM msdb.dbo.backupset
WHERE type = ‘D’
GROUP BY database_name

OPEN csrDatabases

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @diskbackup = bmf.physical_device_name
FROM msdb.dbo.backupmediafamily bmf JOIN msdb.dbo.backupset bs
ON (bmf.media_set_id = bs.media_set_id)
WHERE bs.type = ‘D’
AND bs.database_name = @dbname
AND bs.backup_finish_date = @finishdate

EXEC master.dbo.xp_fileexist @diskbackup, @FileFound OUTPUT
IF @FileFound=1
BEGIN
SET @verifystatement = ‘RESTORE VERIFYONLY FROM DISK = ”’+@diskbackup+”’ WITH LOADHISTORY’
SELECT @timebegin = GETDATE()
EXEC sp_executesql @verifystatement
SELECT @timeend = GETDATE()
SELECT @timeelapsed = DATEDIFF(s,@timebegin,@timeend);

PRINT ‘Backup file ‘+@diskbackup+’ verified in ‘+CAST(@timeelapsed AS VARCHAR(20))+’ seconds.’

END

FETCH NEXT FROM csrDatabases INTO @dbname, @finishdate
END
 
CLOSE csrDatabases
DEALLOCATE csrDatabases
SET NOCOUNT OFF

September 2010 Tip of the Month

Performance Monitor: Monitor the performance counters SQLServer:Buffer Manager (Page reads/sec) and SQLServer:Buffer Manager (Page writes/sec) to detect poor performance. These counters measure physical IOs and not logical IOs. Heavy activity can indicate lack of database memory, a poor table implementation or can show that applications are not accessing a database correctly. Monitoring these counters will give insight into changes in workload as well. Many times, alot of writes will occur at the same time that poor performance is noticed.

For both Page reads/sec and Page writes/sec performance thresholds are: 
> 1000 – Very High Activity 
> 500 – High Activity 
> 90 – Elevated Activity

August 2010 Tip of the Month

DBA’s need to know when it is safe to stop services or to do other work. It is preferable to do such things when no jobs are running to prevent job failures and data integrity issues.  Check out the query below to help.

SQL Server Administration:  Find the next time that any enabled job will run. Run the query below to return a list of jobs on any SQL Server along with the last run duration and next run time.  

USE msdb

GO

SET NOCOUNT ON;

SELECT CONVERT(VARCHAR(20),SERVERPROPERTY(‘ServerName’)) AS ServerName,

j.name AS job_name,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS last_run_duration,

ja.next_scheduled_run_date

FROM msdb.dbo.sysjobactivity ja

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id

join msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id

WHERE ja.session_id=(SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)

AND j.enabled = 1

ORDER BY job_name;

SET NOCOUNT OFF;

GO