Recipe for a good, solid maintenance plan

Hello

I'm in the proces of a major revision of the maintenance plans for the SQL servers in our company, and in connection with that I would like to hear how other people are doing this.

Here's a quick rundown of the plan:

Critical DBs will be backed up on tape (daily incremental, weekly full, w. Veritas Backup Exec 9.0). Also, there will be full daily disk backups for easy quick recovery. These will be done locally, as I have bad experiences trying to backup across a network share.

DBs of medium importance will be backed up fully every day on disk. These BAK-files will then be backed up on tape, if I find it necessary.

Although I rarely restore from the tapes, I think they're nice to have in case the office burns down or who knows what.

The maintenance plans will be split into 3:
1. System DBs maint. plan
2. Critical importance DBs maint. plan
3. Medium importance DBs maint. plan

The more I think about it, the more I think I might just classify all production DBs as critical and all test DBs as medium. Maybe that would make more sense.

For all disk backups, optimization and integrity checks (and backup) will be done daily. For DBs of critical importance (eg. production DBs) Transaction log back will be done as well. What's a good schedule for this? Once every 3 hours or so? Every hour? How much burden does this operation put on the server?

I guess that's about it so far. If anyone has any suggestions or comments, I would be very pleased to hear them.

MNJFrequency of trx. log backups depends on the level of activity of action queries and recoverability requirements. In one of our databases here we're doing 15-minute trx. log dumps and the resulting file varies from 800MB to 2.5GB in size. Another database barely creates a 100K logs but we're doing dumps every 30 minutes for its point-in-time recoverability requirements. It all depends.

As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)|||[i]As per your breakdown, it looks good. But as our disaster recovery excersises shown, - it's beneficial to have your system databases backed up last. Here we're using SQLMAINT utility to run our maintenance plans (SQLMAINT -PlanName <app_db_maint_plan>). This way it's easier to sequence the steps to your likes. Also, do make sure you log all outputs, in case something goes south :)

Why is it beneficial to backup the system DBs last? Since I keep system and user DBs separated into different maint. plans, I guess I can just schedule the system maint. plan to occur 15 min. after the user main. plan?

MNJ|||For one, if MSDB is backed up last, - it will contain the latest backup information of all other databases, as well as itself. This information is available when looking at the General tab of database Properties window.

As per scheduling, - as I said earlier, I have execution of all maintenance plans in one batch with SQLMAINT. If you're using Scheduled Tasks, then you can add a step with SQLMAINT -PlanName <sys_db_maint_plan> after your application databases.|||Good point with system DBs, I will take that into consideration. I suppose if I just make sure to schedule them a bit apart, it should work out ok.

MNJ

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVECAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengren
the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVECAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process th
is message so you
rollback. Since messages are received in order, you will just read the same
message next time.
Essentially, you end up in an endless loop. This is called a "poison message
" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter
's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process
this message so you
> rollback. Since messages are received in order, you will just read the sam
e message next time.
> Essentially, you end up in an endless loop. This is called a "poison messa
ge" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolt
er's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

Receive Error: 17805 Invalid buffer received from client

Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
BrettSee response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:

>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour
I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i nee
d
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>

Receive Error: 17805 Invalid buffer received from client

Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error and what kind of filter if any do i need to apply?
Please advise..
Cheers!
Brett
You should be able to track it down quickly if it's
happening every hour. You get capture exceptions using the
Errors and Warnings class. You can then see the spid that
generated this error. If you also capture the T-SQL
statements and stored procedures at the same time, you will
see what the spid that generated the exception was
executing.
What you filter on depends more on your system. You can at
least filter out the applications SQL Profiler and SQLAgent%
-Sue
On Fri, 21 Oct 2005 15:33:20 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:

>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
> State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error and what kind of filter if any do i need to apply?
>Please advise..
>Cheers!
>Brett
>

Receive Error: 17805 Invalid buffer received from client

Hello
I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
receive several notifications with the following:
Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
State: 3 Invalid buffer received from client.
I want to create a SQL Profiler trace that will help me to identify which
sql statements \ stored procedures are causing this error. What will i need
to specify in the trace to identify the transaction that is causing this
error?
Please advise..
Cheers!
Brett
See response in microsoft.public.sqlserver.clients
-Sue
On Fri, 21 Oct 2005 13:50:53 -0700, "Brett Davis"
<bdavis123@.cox.net> wrote:

>Hello
>I am running SQL Server 2000 with SP 4 on Windows Server 2003. Every hour I
>receive several notifications with the following:
> Reply: Message not found. Insertion strings:Error: 17805, Severity: 20,
>State: 3 Invalid buffer received from client.
>I want to create a SQL Profiler trace that will help me to identify which
>sql statements \ stored procedures are causing this error. What will i need
>to specify in the trace to identify the transaction that is causing this
>error?
>Please advise..
>Cheers!
>Brett
>
sql

Rebuildix clutered index on different field

Hello!
I have got a table with clustered key defined on primary key. This tables is
referenced using FK by hundreds of other tables using its primary key. I am
trying to build clustered index on different column in the most efficient
way. It looks like I have to drop PK constraint first before changing
clustered index. This also means I have to drop/recreate all FK constraints
which could be time consuming. I suppose disabling constraints wouldn't
help. Is there quickest way of changing clustered index?
Thanks,
IgorHi
As there can only be one clustered index on a table I think you will need to
drop it which would require all FKs referencing it to be dropped first,
although alternatively you may want to create an indexed view that can have
it's own clustered index.
John
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eftV0UGPFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I have got a table with clustered key defined on primary key. This tables
> is referenced using FK by hundreds of other tables using its primary key.
> I am trying to build clustered index on different column in the most
> efficient way. It looks like I have to drop PK constraint first before
> changing clustered index. This also means I have to drop/recreate all FK
> constraints which could be time consuming. I suppose disabling constraints
> wouldn't help. Is there quickest way of changing clustered index?
> Thanks,
> Igor
>|||Thanks, John. I have come up with script that drops recreates FK
automatically.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23qxCINOPFHA.1932@.tk2msftngp13.phx.gbl...
> Hi
> As there can only be one clustered index on a table I think you will need
> to drop it which would require all FKs referencing it to be dropped first,
> although alternatively you may want to create an indexed view that can
> have it's own clustered index.
> John
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eftV0UGPFHA.3356@.TK2MSFTNGP12.phx.gbl...
>

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system databases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folder on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lot
Look at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system da
tabases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folde
r on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lotLook at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.