Automating "picture" of server performance

Hi all

I need to do the following using the performance monitor tool.

Im monitoring the %cpu used, transaction/sec and page/sec of a server using the perfomance monitor.

What i would need to do is to monitor all these information in a log that will reset himself once it has reached its maximum size limit. I also need to be able to take a "screenshot" of what was in the log when it became full but i cannot simply save the file because it would use too much space in the long run.

Is there a way for me to automating this information and make it so that each time the log become full i can take a screenshot (By that i mean getting a image of the information under the form of a graphic) of what was inside the log before it restart?

Ask if you need more precision

Dale

If you can't save the file, you probably can't save a graphic that would include everything and all the details in the file.

You can automate perf mon and dump it to a CSV. But if you tried to create a screenshot that shows all the details of all the info in the CSV and save all of those, it's likely not going to save space. Maybe you should look at the interval you are using in perf mon. Or save the file somewhere off the server if it's just space on the server itself. Or load the csv files into a table in a database. I just am not following the whole screenshot concept. It's not going to be that useful in analysis to have a ton of screenshots to look through.

-Sue

|||

I need those to help determine the best moment of the week to monitor the system through a server sided trace.

I think the best will be to just keep all the log and extract the information i want from it at the end of the week.

automatic transaction - Stored procedure - @@identity

Hi, i am having a hard time understanding the concept maybe .

i am using automatic transaction as such:

[AutoComplete]

public void SaveInvoiceAndCharges()

{

//try

//{

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

// DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

//}

//catch (Exception e)

//{

// throw e;

//}

}

and my sp :

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Invoice_New]

(

@.DateIssued datetime,

@.Reference nvarchar(50),

@.Note nvarchar(50),

@.DocumentType int,

@.CompanyCode int,

@.Valid nchar(1),

@.Fees int,

@.isDistributed nchar(1)

)

AS

SET NOCOUNT OFF;

Begin try

INSERT INTO [Invoice].[Invoice] ([DateIssued], [Reference], [Note],

[DocumentType], [CompanyCode], [Valid], [Fees], [isDistributed])

VALUES (@.DateIssued, @.Reference, @.Note, @.DocumentType, @.CompanyCode,

@.Valid, @.Fees, @.isDistributed);

return @.@.identity

End try

begin catch

raiserror('Test - Error', 16, 1 )

End catch

.. the code being run is not actually throwing any error.

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

updates but actually just does nothing and i lose the invalid row.

what is happening here please?

thank you ,

hrub

hrubesh:

I am not sure what else is going on but most likely you should be returning SCOPE_IDENTITY() instead of @.@.identity. Also, what are you getting for a return code?

|||

Hi, thanks for your reply,

i did not understand what u mean by what am i getting for a return code.

|||

Hrubesh:

You have this line in your code:

Code Snippet

return @.@.identity

How are you invoking the stored procedure? And after the execution of the stored procedure completes, what is the value returned by the stored procedure? Or are you just ignoring the return value? Finally, what do you mean by "it just does nothing." And if you don't know what the return value is, how do you know that "it is just doing nothing."

|||

hi,

i have been searching on the issue, and this is what i believe now it is not an issue with the sql part of it.

exception unhandled by user code in serviced components is what i am looking into now.

thanks for your help,

btw why the difference between @.@.identity and SCOPE_IDENTITY() ..

i have been using @.@.identity so that it automatically updates my parent row and child row with the new id , i am using

DSPinvoiceandcharges.Tainvoice.Update(DSinvoiceAndcharges.Invoice);

DSPinvoiceandcharges.Tainvoicecharge.Update(DSinvoiceAndcharges.InvoiceCharge);

invoice is the parent table and invoicecharge the child, and it works.

i will try the scope_identity asa i get the exception unhandled workaround. ..

yep so how i am invoking the sp is that Tainvoice is the sql data adapter that i generated using the wizard in my dataset, that created stored procedures on an sql command, and it automatically calls the corresponding insert/upd/del sp.

thanks a lot.

|||

@.@.identity will frequently work but it is not technically the correct choice. I gave an example of how @.@.identity can go wrong in this thread last year:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=880725&SiteID=1

Another thread in which I discussed with my friend Craig was here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=793185&SiteID=1

|||

thanks kent.

Automatic rollback inside using statement?

If I start a transaction using the following approach ...

using (SqlTransaction trans = destConn.BeginTransaction())
{

...do some transfers using SqlBulkCopy
}

...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?

No.If an error occurs,Rollback the transaction in acatch statement to void out any changes and thenrethrow the error so that the application can deal with it accordingly.

http://davidhayden.com/blog/dave/archive/2005/10/14/2515.aspx

|||Is it really necessary to close the connection inside ausing (SqlConnection connection1 =new SqlConnection(connectString1)) block like David Hayden does?|||

They don't do it here:

http://msdn2.microsoft.com/en-us/library/tchktcdk.aspx

Also look at TransactionScopes they're pretty interesting:

http://msdn2.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

|||

Let me tell you the internals of transaction it is mathmatically defined to be one operation at a time by the people who created it but SQL Server langauge T-SQL let you create more than one at a time through what we call transaction savepoints you may have seen them in David's blog entry. That is called nested transaction but you must use savepoint or the whole thing will rollback to the first if there is an error. Those savepoints can be rolled back as of service pack 3 of SQL Server 2000 if needed. And you must use the second Using statement before you use them so the system can call dispose for you automatically, that is good practice so resources like transaction is not running when you are through with them. And if you are not hosting the application in your local intranet you cannot use transaction scope because transaction scope performs illegal none atomic transactions and SQL Server does the right thing and promotes the operation to distributed transaction, if you use transaction scope you must call a T-SQL stored procedure doing the correct atomic transaction to finish your transaction block. There is an auto commit mode that is the default unless you change the definition in SQL Server and automatic roll back is if you don't use the savepoint and there is an error in your code. Post again if you still have questions. Try the thread below and read the code and the info in the links in it and you will understand what I have explained here. Hope this helps.

http://forums.asp.net/thread/1284424.aspx

|||

Thanx!

I'll read the thread

Automatic Purge of Transaction Log Data?

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.

With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!

For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).

Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.

|||

Here are some good resources that will help you better understand and control the Transaction Log:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

And especially, this article about why cautions about reducing the Log file size is worth reading:

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thank you very much Marcelo and Arnie. I appreciate all of the information you have provided. I will spend this afternoon and tomorrow morning looking into it! :0)

automatic or manual ROLLBACK

Hi,
I have a job that executes the TSQL below. Do I need to do the
errorchecking myself (as below) or does the transaction automaticaly
rollback if a statement fails?
Feel free to comment on the script. (Its never too late to learn).
Yours sincerely,
Jo
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @.MYERROR
BEGIN TRANSACTION
INSERT INTO sequencetoolingbak
(
orderid,
partno,
jobno,
deliverydate,
time_of_creation
)
SELECT orderid, partno, jobno, deliverydate, time_of_creation
FROM dbo.sequencetooling AS seq
WHERE deliverydate < DATEADD(d, -14, GETDATE())
AND NOT EXISTS
(
SELECT orderid
FROM dbo.sequencetoolingbak AS bak
WHERE bak.orderid = seq.orderid
)
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
DELETE FROM dbo.sequencetooling
WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
SET @.MYERROR = @.@.ERROR
IF @.MYERROR <> 0
BEGIN
PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Jo SegersHi
Relying on default error handling to be the same in future versions of SQL
Server is not good at all as it could change.
You should always handle the error yourself (like in your code) as the
execution will be predictable.
Regards
Mike
"Jo Segers" wrote:

> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers
>|||The transaction will not be automatically rolled back, unless you SET
XACT_ABORT ON first. Some errors will terminate the batch, which means that
your error handler won't be executed though.
For a complete explanation of errorhandling in SQL Server, see the articles
by SQL Server MVP Erland Sommarskog on www.sommarskog.se .
Jacco Schalkwijk
SQL Server MVP
"Jo Segers" <jo.segers@.alro.be> wrote in message
news:eSgVzgsAFHA.4008@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a job that executes the TSQL below. Do I need to do the
> errorchecking myself (as below) or does the transaction automaticaly
> rollback if a statement fails?
> Feel free to comment on the script. (Its never too late to learn).
> Yours sincerely,
> Jo
> SET NOCOUNT ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> DECLARE @.MYERROR
> BEGIN TRANSACTION
> INSERT INTO sequencetoolingbak
> (
> orderid,
> partno,
> jobno,
> deliverydate,
> time_of_creation
> )
> SELECT orderid, partno, jobno, deliverydate, time_of_creation
> FROM dbo.sequencetooling AS seq
> WHERE deliverydate < DATEADD(d, -14, GETDATE())
> AND NOT EXISTS
> (
> SELECT orderid
> FROM dbo.sequencetoolingbak AS bak
> WHERE bak.orderid = seq.orderid
> )
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'INSERT ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> DELETE FROM dbo.sequencetooling
> WHERE DATEDIFF(d, deliverydate, GETDATE()) >= 14
>
> SET @.MYERROR = @.@.ERROR
> IF @.MYERROR <> 0
> BEGIN
> PRINT 'DELETE ERROR ' + CONVERT(VARCHAR, @.@.ERROR)
> ROLLBACK TRANSACTION
> RETURN
> END
> COMMIT TRANSACTION
> --
> Jo Segers|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Relying on default error handling to be the same in future versions of SQL
> Server is not good at all as it could change.
> You should always handle the error yourself (like in your code) as the
> execution will be predictable.
> Regards
> Mike
OK,
Thanks for your reply.
Jo Segers

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option fro
m
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:

> Hi:
> Somebody knows how I can program a stored procedure for automatic backup t
o
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale |||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
[vbcol=seagreen]
> In SQL Server Management Studio, Go to Management, Maintenance Plans, righ
t
> click the node and select New. Choose the "Back Up Database Task" option f
rom
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
>|||You can create an Agent Performance Condition Alert that triggers on log ful
l percent and trigger
your job that does a backup of the log. If prefer to just schedule my backup
s regularly, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...[vbcol=seagreen]
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic fo
rm
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale
"Tibor Karaszi" wrote:

> You can create an Agent Performance Condition Alert that triggers on log f
ull percent and trigger
> your job that does a backup of the log. If prefer to just schedule my back
ups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Perc
ent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert i
s fired.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...[vbcol=seagreen]
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale
> "Tibor Karaszi" wrote:
>|||It′s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:

> When the condition is true, yes. For instance, you can for the counter "Pe
rcent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert
is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> .
>

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale
In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:

> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale
|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
[vbcol=seagreen]
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...[vbcol=seagreen]
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale
"Tibor Karaszi" wrote:

> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>
>
|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...[vbcol=seagreen]
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale
> "Tibor Karaszi" wrote:
|||It′s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:

> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> .
>

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale :)In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:
> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale :)|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
> > Hi:
> > Somebody knows how I can program a stored procedure for automatic backup to
> > disk in SQL Server 2005 checking the transaction log?
> > Thanks in advance
> > Ale :)|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> click the node and select New. Choose the "Back Up Database Task" option from
>> the toolbox. Configure the backup wizard as needed.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Ale" wrote:
>> > Hi:
>> > Somebody knows how I can program a stored procedure for automatic backup to
>> > disk in SQL Server 2005 checking the transaction log?
>> > Thanks in advance
>> > Ale :)|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale :)
"Tibor Karaszi" wrote:
> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> > ok, I checked this but I have one doubt: how can I check my log? if I want
> > that my backup init when my log is at 80 %, how can I send an automatic form
> > the backup process to work? it exists a manner for programming this in SQL
> > Server 2005? (like a cron in UNIX)
> > Thanks again
> > Ale
> >
> > "AndyP" wrote:
> >
> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> the toolbox. Configure the backup wizard as needed.
> >>
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Ale" wrote:
> >>
> >> > Hi:
> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> > disk in SQL Server 2005 checking the transaction log?
> >> > Thanks in advance
> >> > Ale :)
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale :)
> "Tibor Karaszi" wrote:
>> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
>> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ale" <Ale@.discussions.microsoft.com> wrote in message
>> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>> > ok, I checked this but I have one doubt: how can I check my log? if I want
>> > that my backup init when my log is at 80 %, how can I send an automatic form
>> > the backup process to work? it exists a manner for programming this in SQL
>> > Server 2005? (like a cron in UNIX)
>> > Thanks again
>> > Ale
>> >
>> > "AndyP" wrote:
>> >
>> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> >> click the node and select New. Choose the "Back Up Database Task" option from
>> >> the toolbox. Configure the backup wizard as needed.
>> >>
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Ale" wrote:
>> >>
>> >> > Hi:
>> >> > Somebody knows how I can program a stored procedure for automatic backup to
>> >> > disk in SQL Server 2005 checking the transaction log?
>> >> > Thanks in advance
>> >> > Ale :)
>>|||It´s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:
> > This Agent triggers when the condition is completed?
> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> > Ok, I'll try it!
> > This Agent triggers when the condition is completed?
> > Thanks!
> > Ale :)
> >
> > "Tibor Karaszi" wrote:
> >
> >> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> >> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> >> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> >> > ok, I checked this but I have one doubt: how can I check my log? if I want
> >> > that my backup init when my log is at 80 %, how can I send an automatic form
> >> > the backup process to work? it exists a manner for programming this in SQL
> >> > Server 2005? (like a cron in UNIX)
> >> > Thanks again
> >> > Ale
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> >> the toolbox. Configure the backup wizard as needed.
> >> >>
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Ale" wrote:
> >> >>
> >> >> > Hi:
> >> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> >> > disk in SQL Server 2005 checking the transaction log?
> >> >> > Thanks in advance
> >> >> > Ale :)
> >>
> >>
> >>
> .
>

Automated backup failing

I keepp getting the following error in my application log for SQL Server 2000:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan master, msdb, model''
(0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
Does anyone know what this means?
--
John SchusterSpecify a report file for the plan and check that file for detailed information about errors,. My
guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
can't do log backup for master even if you set it to full recovery (master is special).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>I keepp getting the following error in my application log for SQL Server 2000:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan master, msdb, model''
> (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> Does anyone know what this means?
> --
> John Schuster|||Tibor,
Thank you very much. I will try doing what you suggest.
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||I do have reporting set up. This is the message I see for the maintenance
plan in question:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database model: Transaction Log Backup...
Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>I do have reporting set up. This is the message I see for the maintenance
> plan in question:
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> Backup can not be performed on database 'master'. This sub task is ignored.
> [1] Database model: Transaction Log Backup...
> Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Specify a report file for the plan and check that file for detailed information about errors,. My
>> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
>> can't do log backup for master even if you set it to full recovery (master is special).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >I keepp getting the following error in my application log for SQL Server 2000:
>> >
>> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
>> > 'DB Maintenance Plan master, msdb, model''
>> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
>> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >
>> > Does anyone know what this means?
>> > --
>> >
>> > John Schuster|||Tibor,
How, then, do I back up the master and msdb databases? Is it possible?
--
John Schuster
"Tibor Karaszi" wrote:
> Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >I do have reporting set up. This is the message I see for the maintenance
> > plan in question:
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > Backup can not be performed on database 'master'. This sub task is ignored.
> >
> > [1] Database model: Transaction Log Backup...
> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [2] Database model: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'msdb'. This sub task is ignored.
> >
> > Deleting old text reports... 1 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Specify a report file for the plan and check that file for detailed information about errors,. My
> >> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> >> can't do log backup for master even if you set it to full recovery (master is special).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >I keepp getting the following error in my application log for SQL Server 2000:
> >> >
> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> >> > 'DB Maintenance Plan master, msdb, model''
> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >
> >> > Does anyone know what this means?
> >> > --
> >> >
> >> > John Schuster
> >>
>|||John,
You can only perform the Full backup, don't check for the Transaction log
backups.
Chris
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> Tibor,
> How, then, do I back up the master and msdb databases? Is it possible?
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Yep, as I suspected. You can't do log backups on databases that are in
>> simple recovery model.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >I do have reporting set up. This is the message I see for the
>> >maintenance
>> > plan in question:
>> >
>> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> > Server
>> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > Backup can not be performed on database 'master'. This sub task is
>> > ignored.
>> >
>> > [1] Database model: Transaction Log Backup...
>> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > [2] Database model: Verifying Backup...
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Backup can not be performed on database 'msdb'. This sub task is
>> > ignored.
>> >
>> > Deleting old text reports... 1 file(s) deleted.
>> >
>> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Specify a report file for the plan and check that file for detailed
>> >> information about errors,. My
>> >> guess is that you try to do log backups for databases that are in
>> >> simple recovery mode. Also, you
>> >> can't do log backup for master even if you set it to full recovery
>> >> (master is special).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >I keepp getting the following error in my application log for SQL
>> >> >Server 2000:
>> >> >
>> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> > Maintenance Plan
>> >> > 'DB Maintenance Plan master, msdb, model''
>> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
>> >> > by
>> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >> >
>> >> > Does anyone know what this means?
>> >> > --
>> >> >
>> >> > John Schuster
>> >>|||Thank you.
--
John Schuster
"Chris Wood" wrote:
> John,
> You can only perform the Full backup, don't check for the Transaction log
> backups.
> Chris
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> > Tibor,
> >
> > How, then, do I back up the master and msdb databases? Is it possible?
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> simple recovery model.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >I do have reporting set up. This is the message I see for the
> >> >maintenance
> >> > plan in question:
> >> >
> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> > Server
> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > Backup can not be performed on database 'master'. This sub task is
> >> > ignored.
> >> >
> >> > [1] Database model: Transaction Log Backup...
> >> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > [2] Database model: Verifying Backup...
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> > ignored.
> >> >
> >> > Deleting old text reports... 1 file(s) deleted.
> >> >
> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Specify a report file for the plan and check that file for detailed
> >> >> information about errors,. My
> >> >> guess is that you try to do log backups for databases that are in
> >> >> simple recovery mode. Also, you
> >> >> can't do log backup for master even if you set it to full recovery
> >> >> (master is special).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >Server 2000:
> >> >> >
> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> > Maintenance Plan
> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
> >> >> > by
> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >> >
> >> >> > Does anyone know what this means?
> >> >> > --
> >> >> >
> >> >> > John Schuster
> >> >>
> >>
>
>|||Recommended backup strategy for system databases (such as master, msdb,
model) is taking full backups of them when you make a change in your SQL
Server system like changing SQL Server configuration, adding new logins,
adding new jobs, adding linked servers etc.
Transaction Log backup is unnecessary for system databases.
--
Ekrem Ã?nsoy
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> Thank you.
> --
> John Schuster
>
> "Chris Wood" wrote:
>> John,
>> You can only perform the Full backup, don't check for the Transaction log
>> backups.
>> Chris
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
>> > Tibor,
>> >
>> > How, then, do I back up the master and msdb databases? Is it possible?
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep, as I suspected. You can't do log backups on databases that are in
>> >> simple recovery model.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >> >I do have reporting set up. This is the message I see for the
>> >> >maintenance
>> >> > plan in question:
>> >> >
>> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> >> > Server
>> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
>> >> > on
>> >> > 9/27/2007 12:00:06 AM
>> >> > Backup can not be performed on database 'master'. This sub task is
>> >> > ignored.
>> >> >
>> >> > [1] Database model: Transaction Log Backup...
>> >> > Destination:
>> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > [2] Database model: Verifying Backup...
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > Backup can not be performed on database 'msdb'. This sub task is
>> >> > ignored.
>> >> >
>> >> > Deleting old text reports... 1 file(s) deleted.
>> >> >
>> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> >> > 9/27/2007 12:00:06 AM
>> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> > --
>> >> > John Schuster
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Specify a report file for the plan and check that file for detailed
>> >> >> information about errors,. My
>> >> >> guess is that you try to do log backups for databases that are in
>> >> >> simple recovery mode. Also, you
>> >> >> can't do log backup for master even if you set it to full recovery
>> >> >> (master is special).
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >> >I keepp getting the following error in my application log for SQL
>> >> >> >Server 2000:
>> >> >> >
>> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> >> > Maintenance Plan
>> >> >> > 'DB Maintenance Plan master, msdb, model''
>> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
>> >> >> > on:
>> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
>> >> >> > invoked
>> >> >> > by
>> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
>> >> >> > 1).
>> >> >> >
>> >> >> > Does anyone know what this means?
>> >> >> > --
>> >> >> >
>> >> >> > John Schuster
>> >> >>
>> >>
>>|||Thank you.
--
John Schuster
"Ekrem Ã?nsoy" wrote:
> Recommended backup strategy for system databases (such as master, msdb,
> model) is taking full backups of them when you make a change in your SQL
> Server system like changing SQL Server configuration, adding new logins,
> adding new jobs, adding linked servers etc.
> Transaction Log backup is unnecessary for system databases.
> --
> Ekrem Ã?nsoy
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> > Thank you.
> > --
> > John Schuster
> >
> >
> > "Chris Wood" wrote:
> >
> >> John,
> >>
> >> You can only perform the Full backup, don't check for the Transaction log
> >> backups.
> >>
> >> Chris
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> >> > Tibor,
> >> >
> >> > How, then, do I back up the master and msdb databases? Is it possible?
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> >> simple recovery model.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >> >I do have reporting set up. This is the message I see for the
> >> >> >maintenance
> >> >> > plan in question:
> >> >> >
> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> >> > Server
> >> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
> >> >> > on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > Backup can not be performed on database 'master'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > [1] Database model: Transaction Log Backup...
> >> >> > Destination:
> >> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > [2] Database model: Verifying Backup...
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > Deleting old text reports... 1 file(s) deleted.
> >> >> >
> >> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> >> > --
> >> >> > John Schuster
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Specify a report file for the plan and check that file for detailed
> >> >> >> information about errors,. My
> >> >> >> guess is that you try to do log backups for databases that are in
> >> >> >> simple recovery mode. Also, you
> >> >> >> can't do log backup for master even if you set it to full recovery
> >> >> >> (master is special).
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >> >Server 2000:
> >> >> >> >
> >> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> >> > Maintenance Plan
> >> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
> >> >> >> > on:
> >> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
> >> >> >> > invoked
> >> >> >> > by
> >> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
> >> >> >> > 1).
> >> >> >> >
> >> >> >> > Does anyone know what this means?
> >> >> >> > --
> >> >> >> >
> >> >> >> > John Schuster
> >> >> >>
> >> >>
> >>
> >>
> >>
>

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
Check out SCOPE_IDENTITY() in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***Check out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Auto-Grow and Shrink Via Code

Hey guys,
Does anybody know of a way to turn on the Auto-Grow feature for a
transaction log via T-SQL code? What we want to do is during a
scheduled job, turn the auto grow feature on then after the job is
finished, shrink the log, then cut auto grow back off?
Any thoughts? Thanks in advance!
Hunter
**************Please Post to Group so that all can benefit!Have you looked at the ALTER DATABASE topic in Books Online? Of particular
interest:
AUTO_SHRINK ON | OFF
If ON is specified, the database files are candidates for automatic periodic
shrinking.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Hunter" <bamared36054@.yahoo.com> wrote in message
news:2a0a8bc1.0312020836.3923452e@.posting.google.com...
> Hey guys,
> Does anybody know of a way to turn on the Auto-Grow feature for a
> transaction log via T-SQL code? What we want to do is during a
> scheduled job, turn the auto grow feature on then after the job is
> finished, shrink the log, then cut auto grow back off?
> Any thoughts? Thanks in advance!
> Hunter
> **************Please Post to Group so that all can benefit!|||Sorry, wrong section... 'grow' != 'shrink' Aaron! Look under filespec:
< filespec > ::=( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Autogrow

Is there a way for me to determine how often the database or transaction
logs have triggered/executed autogrow?
--
Any and all contributions are greatly appreciated ...
Regards TJYou can monitor this in a trace or using profiler using the
Database event classes for DataFileAutoGrow and
LogFileAutoGrow.
-Sue
On Fri, 19 Sep 2003 16:33:55 -0400, "TJ"
<nospam@.nowhere.com> wrote:
>Is there a way for me to determine how often the database or transaction
>logs have triggered/executed autogrow?

auto-commit vs begin transaction overhead

I've googled for the answer and searched sql server 2005 books online and I haven't been able to find the answer to my question. My main question is what is the difference in overhead between using auto-commit on every statement or explicitly calling begin transaction on every statement. I'm basically looking for documentation that points to the difference. Read on for the scenario...

We have an application that has problems with deadlocks. Part of our strategy in handling these deadlocks is resubmitting the transaction. My solution to this part of the strategy is to wrap every storedprocedure in a transaction so this would include reads and writes. My architect has raised some concerns with regard to performance if it's implemented this way. I've come across documentation (http://msdn2.microsoft.com/en-us/library/ms187878.aspx) in the sql server 2005 books online that auto-commit happens on every statement anyway but haven't come across anything that explicitly states the performance penalty if there is one. Is there difference in overhead between calling 'BeginTransaction' for every statement or just letting auto-commit do it's magic. Is this implemented in SqlClient object or at a database level?Some folks from the database engine team would be best suited to answer your question re: auto-commit vs. explicit transaction performance. Depending on their answer, you might also want to follow up with folks in the .Net Data Access and Storage forum to ensure there are no performance implications in the particular client that you are using.|||

AFAIK, definetely there is an overhead. And how much overhead depends on the traffic on your application, and the size of the DB.

There will be lot of contention since transactions will keep the locks until the transaction commits. You might see slow down in your transaction times. If you have a web form and a submit button, you keep the user waiting longer for his transaction to commit if there are more transactions waiting. Also, there could be timeout issues. Your CPU could shoot up. If you use #temp tables your tempDB could blow up.

If you have dead lock scenarios I would look into which procs cause it and fix them rather than wrap every T-SQL with a transaction.

And if you really want to measure the overhead, you can use some 3rd party tools like NetIQ and do a load test and quantify the results.

Auto Rollback of transaction

Hello,
I had a discuss of SQL Server's auto rollback behaviour with my colleague.
He hasn't convinced me.
We use SQL Server 2000.
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
The point of this discussion is that, for a while, we've been checking for
errors (IF @.@.ERROR <> 0....do something) right after table minipulation
statements. Now I see that this way of checking for error is actually
pointless, since SQL jumps right out of the SP when it encounters table
minipulation errors anyway.
But I can't be 100% sure about my argument. So is there anyone out there
that can confirm what I am thinking is right or wrong?
Thank you for your time.
ConaxHI
Read this
http://www.sommarskog.se/error-handling-II.html
Regards
R.D
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Hi Conax,
As you can see from the following sample code in both cases 1) table
manipulation
and 2) non table manipluation, the stored proc does continue and @.@.error
should be checked right after the statement in question.
Please check Books online (BOL) for more information on @.@.error checking.
When in doubt, its a good idea to type a sample test code and see how things
work.
HTH...
set nocount on
go
create table t
(
c1 int not null,
c2 int
)
go
insert t values(1,1)
insert t values(1,2)
insert t values(2,1)
insert t values(2,1)
go
CREATE PROCEDURE dbo.proctblerror
AS
/* you mentioned...
What I've noticed is that, inside a stored procedure, after BEGIN TRANS
txTransName
If an error is generated while manipulating table data, then the whole
transaction will roll back by SQL server, and SQL does not process any other
code further down the SP.
*/
begin tran txTransName
update t set
c1 = null
where c2=1
if (@.@.error = 0)
begin commit tran txTransName print 'commited tbl manip' end
else
begin rollback tran txTransName print 'rolled back tbl manip' end
print 'running rest of tbl manip proc'
select * from t
GO
EXECUTE dbo.proctblerror
GO
CREATE PROCEDURE dbo.procnontblerror
AS
/*
You also mentioned...
However if an error is generated by 'non-table minipulation statement', for
example, do a SELECT 100/0 (division by zero), then SQL does not roll back
the transaction.
*/
begin tran txTransName
select 222
if (@.@.error = 0)
begin commit tran txTransName print 'commited non tbl manip' end
else
begin rollback tran txTransName print 'rolled back non tbl manip' end
print 'running rest of non tbl manip proc'
select * from t
GO
EXECUTE dbo.procnontblerror
GO
drop table t
DROP PROCEDURE dbo.proctblerror
drop proc dbo.procnontblerror
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Conax" wrote:

> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any oth
er
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement', fo
r
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>
>|||Thanks R.D and Z.S, you've been a great help.
Regards
Conax
"Conax" <ConaxLiu@.hotmail.com> wrote in message
news:e%23hlvbCtFHA.1472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I had a discuss of SQL Server's auto rollback behaviour with my colleague.
> He hasn't convinced me.
> We use SQL Server 2000.
> What I've noticed is that, inside a stored procedure, after BEGIN TRANS
> txTransName
> If an error is generated while manipulating table data, then the whole
> transaction will roll back by SQL server, and SQL does not process any
other
> code further down the SP.
> However if an error is generated by 'non-table minipulation statement',
for
> example, do a SELECT 100/0 (division by zero), then SQL does not roll back
> the transaction.
> The point of this discussion is that, for a while, we've been checking for
> errors (IF @.@.ERROR <> 0....do something) right after table minipulation
> statements. Now I see that this way of checking for error is actually
> pointless, since SQL jumps right out of the SP when it encounters table
> minipulation errors anyway.
> But I can't be 100% sure about my argument. So is there anyone out there
> that can confirm what I am thinking is right or wrong?
> Thank you for your time.
> Conax
>