Ramblings of a DBA

# re: SQL Server jobs on production instances

Tara -- This is a great post; it is so nice to see everything all in one grid, with examples, notes, and links. Not being a DBA myself, but occasionally called upon to administer a database server, it is very handy to see what the experts do to keep things running smooth. Thank you!

# re: SQL Server jobs on production instances

Thanks Jeff. I'm not sure what the experts do on their systems, but I've shown what we do here. ;)

# re: SQL Server jobs on production instances

Hi Tara,
a wonderful handout - "maintenance-plan"!

# re: SQL Server jobs on production instances

We also run UPDATE USAGE on a weekly basis.

# re: SQL Server jobs on production instances

Tim, hopefully you aren't running update usage on SQL Server 2005 as it is no longer necessary. The only time that you need to run it on 2005 is when the databases have been upgraded from an earlier version to 2005. And you only need to run it once in this situation. After that and also on newly created databases, the data is always kept up to date. No more dbcc updateusage!

I found this out about two weeks ago when I was doing some research on a problem. I stumbled across it in SQL Server Books Online.

# re: SQL Server jobs on production instances

Great effort Tara.

# re: SQL Server jobs on production instances

Fantastic, Tara! It is really great that you would share this with the db community. I definitely will keep my eyes open for other posts from you. Thanks again!

# re: SQL Server jobs on production instances

Hi Tara,

Very handy set of scripts! Thank you.

Just a small comment: I ran your code below on a system with 1 user-database, which happened to be in an offline state.
I then recieved the message:
Msg 942, Level 14, State 4, Procedure isp_ALTER_INDEX, Line 67
Database 'PerfTest' cannot be opened because it is offline.

To solve this, I've added "and @dbname is not null" to the WHERE clause.

SET NOCOUNT ON

DECLARE @dbName sysname, @rc int

SELECT name
INTO #db
FROM sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'

SELECT @rc = 1, @dbName = MIN(name)
FROM #db

WHILE @rc <> 0 and @dbname is not null
BEGIN
EXEC dbo.isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000

SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name

SET @rc = @@ROWCOUNT
END

DROP TABLE #db

# re: SQL Server jobs on production instances

should this also work for every month Feb, or months with 31 days for deleting database backup history

DECLARE @d datetime

SET @d = DATEADD(day, -30, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory

# re: SQL Server jobs on production instances

Kamran,

That code just deletes the last 30 days of data. It doesn't touch month information. So it could be any day of the year and it will still delete the past 30 days.

# re: SQL Server jobs on production instances

I also run transaction log backup on model db since it is shipped in full recovery by default.

# re: SQL Server jobs on production instances

It is unnecessary to run transaction log backups on model as you shouldn't be running any DML (or similar) operations on it that would require point in time recovery. A full backup of model should be enough for recoverability. The log should never fill up for model.

# re: SQL Server jobs on production instances

In your isp_UPDATE_STATISTICS you hard code the table owner as dbo. To prevent errors for the odd tables that sneak in that are owned by another schema I have changed the dynamic SQL that populates the temp table with the list of tables to:

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects '
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'' and uid = 1'

I am guessing that dbo will always be uid 1 and so far it works for me.

# re: SQL Server jobs on production instances

I was running a monitoring application as evaluation called Longitude and it gave me a warning that model db log was almost full. Do you think setting model db as simple recovery a good idea?

# re: SQL Server jobs on production instances

Artem, you should look into why this is occurring rather than making a config change. But yes you can make the change, it affects all newly created databases though, so keep that in mind.

# re: SQL Server jobs on production instances

I understand that, but it doesn't hurt to backup the tran log does it?

# re: SQL Server jobs on production instances

It wouldn't hurt anything.

# re: SQL Server jobs on production instances

In response to Scott's earlier comment:

>> To prevent errors for the odd tables that sneak in that are owned by another schema I have changed the dynamic SQL ...

Two handy new functions in SQL 2005 are SCHEMA_NAME ( schema_id ) and OBJECT_SCHEMA_NAME ( object_id [, database_id ] ).

The original dynamic SQL could have been changed to
SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT ( OBJECT_SCHEMA_NAME (id) + ''.''+ [name]) AS [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysobjects '
SET @SQL = @SQL + 'WHERE xtype = ''U'' AND [name] <> ''dtproperties'' '

Its a pain to have different versions of code for SQL 2000 and 2005, but for this type of code its often better to do that because of the differences introduced by SQL 2005 "schema separate from ownership". We should probably change this to use sys.objects (and "xtype" to "type") and we get:

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Tables (TableName) '
SET @SQL = @SQL + 'SELECT ( SCHEMA_NAME(obj.schema_id) + ''.''+ [name]) AS [name] '
SET @SQL = @SQL + 'FROM ' + @dbName + '.sys.objects AS obj '
SET @SQL = @SQL + 'WHERE type = ''U'' AND [name] <> ''dtproperties'' '
SET @SQL = @SQL + ' AND is_ms_shipped = 0'
SET @SQL = @SQL + ' AND obj.schema_id IS NOT IN ( SCHEMA_ID( ''sys'' ), SCHEMA_ID( ''INFORMATION_SCHEMA'' ) )' -- always 4 and 3

Note that column sys.objects.principal_id is normally NULL in SQL 2005, since most objects are schema owned. So don't try to check for "principal_id = 1" or you probably won't find much.

If you lookup the "Compatibility View" sys.sysobjects in SQL 2005 BOL, you will find the following warning:
-------------------------------------------
For databases upgraded from an earlier version of SQL Server, the schema ID is equal to the user ID of the owner.

Important:
If you use any of the following SQL Server 2005 DDL statements, you must use the sys.objects catalog view instead of sys.sysobjects.

CREATE | ALTER | DROP USER

CREATE | ALTER | DROP ROLE

CREATE | ALTER | DROP APPLICATION ROLE

CREATE SCHEMA

ALTER AUTHORIZATION ON OBJECT
-------------------------------------------

There are equivalent warnings on the old pre-2005 stored procedures for managing object, user, roles, logins, etc,. These stored procedures try to mimic the pre-2005 behavior by manipulating schemas to match the owner.