Poison Messages - 5 times's a charm

Hello again,
I have some poison message detection in place, based on the BOL sample. My problem is that after the 5th message retry my queue goes down - that is the fifth retry on any message. In actuallity, the first message is retried 3 times and it is taken off the queue [for real], the second message comes in and on the second retry - pooof - the queue is down.

I though the poison mechanism should work on a per message basis. It there a setting for the queue I missed? Is my only chance for to fix this: re-enable the queue upon BROKER_QUEUE_DISABLED event notification?

Thanks,

Lubomir

The poison message counter is based on RECEIVE verbs being rolled back. So five RECEIVEs rolled back in a row and your queue is disabled. The number of individual messages received/rolled back is irelevant. Any RECEIVE commited will reset the counter to 0.

Unfortunately this is not configurable in any way, you only have the BROKER_QUEUE_DISABLED notification as a way to react.

HTH,
~ Remus

|||Sorry to unmark the answer. I want to have a check after a ROLLBACK on the queue to see if the queue is DISBLED? i just could not figure out the TSQL for that?

if QUEUE is disabled
ALTER [dbo].[ProcessQueue] WITH STATUS = ON

EVENT NOTIFICATION does not allow for a direct TSQL exectuion from what I have seen so far, getting the status of queue and acting on it is not straight forward.

Thanks,

Lubomir|||

Event notifications are deliverd through normal Service Broker messages, so the way to execute TSQL on an event notification is to have an activated procedure on the queue that receives the event notification and execute the TSQL in it. The name of the queue being disabled will be part of the notification message.

To get the queue status use the is_receive_enabled or is_enqueue_enabled in sys.service_queues:

if (1 = (select is_receive_enabled from sys.service_queues where name = '...'))
begin
alter queue [...] with status = on;
end

Trying to enable back the queue from the activated procedure, after the rollback, however, will probably not work, because the queue disabling mechanism is asynchronous so you're verification check query will sometimes get the status disabled, sometimes not.

I recommend you try to avoid the rollbacks in the first place. If you try to incorporate rollback in the logic then you might hit a real poison message situation in production and you system will halt to a grind, spining on this one message.

HTH,
~ Remus

|||Thank you for the reply Remus,
My logic does take out the message after 3 retries, so I figure I should be safe, no?
I perform the rollbacks to freshly process the message in the hope conditions that caused the failure have disappeared - slim chance on that and probably something that will cause more trouble than good.
What do yo think?

Lubomir|||

If what you're looking for is a retry mechanism, then you should probably consider dialog timers, perhaps used in a fashion similar to this:

BEGIN TRANSACTION;
WAITFOR(RECEIVE ... FROM ...), TIMEOUT ...;
IF MESSAGE TYPE IS 'UNRELIABLE REQUEST'
BEGIN
INSERT REQUEST MESSAGE BODY INTO USER TABLE;
BEGIN DIALOG TIMER @.someretrytimeout;
END
ELSE IF MESSAGE TYPE IS 'TIMER'
BEGIN
SELECT REQUEST MESSAGE BODY FROM USER TABLE;
-- Re-arm the timer
BEGIN DIALOG TIMER @.someretrytimeout;
END
COMMIT;

DO UNRELIABLE WORK HERE BASED ON THE RECEIVED/SELECTED REQUEST

IF SUCCEEDED
BEGIN
BEGIN TRANSACTION
SEND BACK RESPONSE;
-- RESET TIMER TO 0 (or END CONVERSATION, as appropiate)
BEGIN DIALOG TIMER 0;
COMMIT
END

This way you separate the error prone processing outside the transaction and you don't need to rollback on failure. Also, you get to controll the retry details: number of retries, time between retries, backout policy etc.

HTH,
~ Remus

Poison Message. The conversation endpoint is not in a valid state for SEND.

Hi all, i searched everywhere but couldn't find any info on the following error that i'm currently receiving:

"The conversation endpoint is not in a valid state for SEND. The current endpoint state is 'DI'."

I understand that this is due to some problem in my send/receive protocol but how do i fix this problem so that i can continue with my testing? Right now i'm forced to drop my entire test database and reinstall everytime this message shows up because i can't send/receive any messages at that point. Is there anyway to get rid of it?

Thanks in advance. This is driving me nuts.

DI stands for DISCONNECTED INBOUND and the conversation goes into this state when the peer has ended the conversation. If you try to send on a conversation in this state you'll get this error. You need to establish a clear protocol on who'w ending the conversation and how, because what s likely happening one side (initiator?) issues the END CONVERSATION verb and ends the conversation while the target is still trying to respond.

|||I think i found a solution to my own problem.

This is from the Microsoft online book:
http://download.microsoft.com/download/f/8/5/f8520d64-f109-4111-b0b0-51f1f6d2d220/Rational_Press_Service_Broker_Sample.pdf

If you look at the example in Chapter 1, you will notice that only one end of the conversation

was ended. In Figure 2.3, you can see one conversation with a state of “DI” (meaning

“disconnected inbound”), indicating that the opposite endpoint was ended with an END

CONVERSATION command but this endpoint of the conversation hasn’t been ended yet.

The other conversation state is “ER” (or “error”) because it has been open long enough

that the timeout has expired and the conversation ended with an error. If you execute an

END CONVERSATION command specifying the conversation_handle for the row in

the sys.conversation_endpoints table, it will go away.

Remembering to end all conversations is a very important programming practice. If you

forget to do this, you will fi nd thousands of rows in the sys.conversation_endpoints table

after your application has run for awhile.

If anyone has other ideas then please let me know.

Cheers!!
|||Thanks Remus for the reply, here is my sending protocol:

declare @.dh uniqueidentifier;
declare @.Message nvarchar(500);

SET @.Message = N'<MessageTypes xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.org/"><TransactionID>12345</TransactionID><MessageType>Swap</MessageType><Source>OMS</Source><IsCancel>false</IsCancel></MessageTypes>';

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

And here is my receiving protocol (I removed all error handling for simplicity):

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

SET @.ErrNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
SET @.EndDlgNS = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue) timeout 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

So are my "END CONVERSATION" statements in the correct place?

Thanks a lot.
|||Hello again, I just wanted to clairfy that the receiving procedure is called automatically by the receiving queue to process the messages. So i guess i'm not understanding exactly when to end the conversation.

The above receiving procedure is incorrect, here is the correct version:

CREATE PROCEDURE ServiceProc
AS
DECLARE @.MsgXml xml, @.MsgTypeName nvarchar(128),
@.dh uniqueidentifier, @.Message nvarchar(500);

BEGIN TRAN;

WAITFOR (RECEIVE TOP(1)
@.MsgXml = CAST(message_body as xml),
@.MsgTypeName = message_type_name,
@.dh = conversation_handle
FROM ServiceQue), TIMEOUT 1000;

-- Send reply message
SET @.Message = N'<ResponseMessage><Message>Received</Message></ResponseMessage>';
SEND ON CONVERSATION @.dh
MESSAGE TYPE ResponseMessage(@.Message);

END CONVERSATION @.dh;
PRINT '(ServiceProc) Reply Message Sent Successfully.'

COMMIT TRAN

I really appreciate all the help.

Thanks
|||

Scorpio8 wrote:

BEGIN DIALOG CONVERSATION @.dh
FROM SERVICE ClientServiceOMS
TO SERVICE 'TranProcessorService'
ON CONTRACT TradeCon
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.dh
MESSAGE TYPE MessageTypes(@.Message)

END CONVERSATION @.dh;

For one, you are doing fire-and-forget and that has some problems, see here: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx. Second, the target is trying to respond on a conversation that was already ended by the initiator (hence the error you're getting).

Remove the END CONVERSATION on the sending side, have the initiator wait for the response from the target and end it's side of the conversation after it received a response.

|||Thank you very much Remus, that article and your other articles were very helpful. I got everything working now. But even though all the conversations end up gracefully i still end up with an entry in the sys.conversation_endpoints queue on both database instances:

select state, state_desc from sys.conversation_endpoints

state state_desc
CD CLOSED

Do i need to clear this queue every now and then or should it automatically clear up when the conversations end?

|||

Hi, i found an answer to my question in the book "Pro SQL Server 2005 Service Broker" which i got over the weekend, great book. The answer is on page 59:

"As soon as you process the EndDialog message on the initiator side, the conversation will end on both sides and then discarded from memory. However, for security reasons, it takes about 30 minutes until the initiator's conversation endpoint is deleted from sys.conversation_enpoints catalog view."

Just how in the heck were we mortals supposed to figure this out by ourselves!! lol

Poison Message Sample in BOL

Hello Remus,

I have implemented Event Notification sample you provided in my logic, but I am perplexed that even though my watched queue does go down, I do not get message in the Event_Collector queue all the time. The Event_Collector queue does stay enabled though. Any thoughts? I guess any suggestions on debugging the event notification portion would be helpful.

Lubomir
Upon further look at the sys.event_notifications view, I discovered that my event notification is not there any more - it disappears. So the first time the queue goes down, the notification is successful and the Event Notification is still in sys.event_notifications. Second attemp removes the Event Notification from sys.event_notifications.

Lubomir|||

Can you check if the event notification infrastructure isn't receving back an error or an end dialog message on the system dialog that delivers the notifications? Look into errorlog or in Event Viewer (Application), see if you find a record similar to one of these:

Event notification conversation on dialog handle '{4B086B77-C691-DA11-AD12-00030D303B72}' closed without an error.

or:

Closed event notification conversation endpoint with handle '{B1663957-C791-DA11-AD12-00030D303B72}', due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service &apos;s&apos;.</Description></Error>'.

HTH,
~ Remus

|||Thank you Remus,

Yup it was the End Dialog message I had; Things work great now.

Lubomir

Poison Message Sample in BOL

Hi,

We've been trying to diagnose a problem with service broker's poison message detection and tried to run the sample script in BOL to see how it solved the problem. It appears the example doesn't seem to work correctly,

IF (@.messageTypeName =
'//Adventure-Works.com/AccountsPayable/ExpenseReport')
BEGIN
DECLARE @.expenseReport NVARCHAR(MAX) ;
SET @.expenseReport = CAST(@.messageBody AS NVARCHAR(MAX)) ;
EXEC AdventureWorks.dbo.AddExpenseReport
@.report = @.expenseReport ;
IF @.@.ERROR <> 0
BEGIN
/* THIS ROLLBACK INCREMENTS THE FAILURE COUNT */
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
END ;
ELSE
BEGIN
EXEC AdventureWorks.dbo.ClearMessageTracking
@.conversationHandle ;
END ;
END ;
ELSE

In the scenario where AddExpenseReport fails it will retry the message the next time round the loop - TrackMessage looks to see how many times the message has failed:

IF @.count > 20
/* @.count WILL NEVER GET ABOVE 5 */
BEGIN
EXEC dbo.ClearMessageTracking @.conversationHandle ;
END CONVERSATION @.conversationHandle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;
END ;
ELSE

But the counter will not get to 20 as after 5 the queue will be disabled which causes ProcessExpenseReport to fail. Subsequent message cannot then be processed until the queue is re-enabled.

We have changed the sample so that once the message has failed four times it is then written to a dead message table and we can then call commit so that the queue can carry on without being disabled:

[in ProcessExpenseReport]
...
EXEC AdventureWorks.dbo.AddExpenseReport @.report = @.expenseReport ;
IF @.@.error <> 0
BEGIN
-- The message has failed, we need to work out if we are safe to
-- retry or not. If this message has failed four times already
-- we need to recieve it, save it into another table and commit to
-- avoid service broker from disabling the queue. Otherwise we can
-- rollback and try again to see if it works next time.

-- Find out how many times this message has failed
declare @.failCount int
exec GetFailCount @.conversationHandle, @.failCount output
-- If we are about to reach the threshold
if @.failCount >= 4
begin
-- Insert the message into the dead message table. A sql agent
-- job will at some point in the future put the contents of this
-- table back onto the queue for reprocessing
EXEC DeadMessage @.conversationHandle, @.expenseReport
end
else
begin
-- We are safe to rollback and take another run at it
ROLLBACK TRANSACTION UndoReceive ;
EXEC TrackMessage @.conversationHandle ;
end
END ;
ELSE
BEGIN
-- Call to SP worked we can clear down our message tracking
EXEC ClearMessageTracking @.conversationHandle ;
END ;
END ;
...
/* Procedure to find out how many times this message
has failed to be processed */
CREATE PROCEDURE GetFailCount
@.conversationHandle uniqueidentifier,
@.failCount int output as
begin
SELECT @.failCount = [count] FROM ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle
end
go

/* Procedure to insert the message into the Dead message
table and end the conversation */
create procedure DeadMessage
@.conversation_handle uniqueidentifier,
@.message_body varchar(max)
as
begin
insert into ExpenseServiceDeadMessages ( message_body ) values ( @.message_body )

EXEC dbo.ClearMessageTracking @.conversation_Handle ;
END CONVERSATION @.conversation_Handle
WITH ERROR = 500
DESCRIPTION = 'Unable to process message.' ;

end
go

/* Updated version of TrackMessage which no longer checks the
fail count - it just increments each time */
CREATE PROCEDURE TrackMessage
@.conversationHandle uniqueidentifier
AS
BEGIN
IF @.conversationHandle IS NULL
RETURN ;

DECLARE @.count INT ;
SET @.count = NULL ;
SET @.count = (SELECT count FROM dbo.ExpenseServiceFailedMessages
WHERE conversation_handle = @.conversationHandle) ;

/* TrackMessage no longer checks the failure count */
IF @.count IS NULL
BEGIN
INSERT INTO dbo.ExpenseServiceFailedMessages
(count, conversation_handle)
VALUES (1, @.conversationHandle) ;
END ;
ELSE
BEGIN
UPDATE dbo.ExpenseServiceFailedMessages
SET count=count+1
WHERE conversation_handle = @.conversationHandle ;
END ;
END ;
GO

/* New table to hold the Dead messages */
CREATE TABLE ExpenseServiceDeadMessages (
message_body varchar(max)
) ;

Now this solves the problem. If a message arrives that can't be processed (either becuase the message itself is bad, or because of an system fault with another system that we interact with) the message is moved to the DeadMessage table and the queue carries on and does not get disabled.

In our application however none of this code should be required, as long as we are confident that the data in the messages are "valid". In our case the only reason the processing would fail is if an external system we are dependant fails for some reason (e.g. Maybe writing the message to disk and the disk is full ). In this scenario we want to retry the message indefinately until the error clears. If we could disable the poision message detection this would solve the problem and remove the need for this solution.

Any thoughts on this? Have we missed the point somewhere....

Cheers,

Neil.

The main problem with just disabling the poison message check is that when you roll back a message, it's still at the top of the queue so nothing else gets processed until the message succeeds. You're probably better off commiting the receive and re-sending the meaasge - maybe to some kind of retry queue - so that you don't block other messages on the queue. Keep in mind that if you have multiple messages on the same dialog you'll have to be careful how you handel this so you don't end up changing the message order. I've seen a couple examples of people who use conversation timers to implement a timed retry for cases like this when there is a possibility of the remote resource being unavailable for an extended time.|||

I'd like to add that when a queue is being disabled (e.g. because of a poison message), a BROKER_QUEUE_DISABLED event notification is fired. Here is a small demo to ilustrate this:

use tempdb;
go

create procedure sp_rollback_on_pm
as
begin
set nocount on;
begin transaction;
receive * from q_pm;
rollback;
end;
go

create queue q_pm
with activation (
status = on,
max_queue_readers = 1,
procedure_name = [sp_rollback_on_pm],
execute as owner);
go

create service s_demo_pm on queue q_pm ([DEFAULT]);
go

create queue q_en;
create service s_en on queue q_en ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification ev_qm on queue q_pm for broker_queue_disabled to service 's_en', 'current database';
go

declare @.dh uniqueidentifier;
begin dialog conversation @.dh
from service s_demo_pm
to service 's_demo_pm'
with encryption = off;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
send on conversation @.dh;
go

select cast(message_body as xml),* from q_en
go

An administrator can receive notifications when queues are being disabled as a result of a poison message and it can inspect the queue and fix the issue or remove the problem dialog. Or an activated procedure on the q_en queue can automatically fix the issue and re-enable the q_pm queue (of course, assuming that the issue can be fixed automatically).

HTH,
~ Remus

|||Thanks for the replies - this has allowed us to progress to a past this problem.

I do entirely understand why service broker is doing what it's doing, it will quickly enable you to locate any messages that break the system doe to an application bug. However in the scenario where a message cannot be processed due to a situation beyond your control this features requires you to implement a lot more code then perhaps you should.

In our scenario, if we cannot process message A which is at the front of the queue, we are not going to be able to process message B either, so there's no point trying. The ability to turn of poision message detection would be very useful.

I imagine this has been discussed elsewhere but it also strikes me as odd that I can now write code:

-- About to start a transaction
BEGIN TRANSACTION
RECEIVE * FROM myQueue
ROLLBACK TRANSACTION
-- The database is now in a different state from when begin tran was called!!

I personally have always relied on the premise that EVERYTHING I DO (with SQL) inside a transaction is undone when I call rollback. This no longer seems to be true?

Cheers,

Neil|||

Everything you did in the transaction is undone when the transaction is roled back. The message is back in the queueand any other data updates are reversed. The queue is disabled as a result of the rollback, after the rollback occured.

In the case you describe, since the application is able to detect the problem message, I wouldn't actually call it a poison message. Is more like a message your app understands but is not willing to process. It seems to me that a more appropiate action would be to move the message into a different queue (i.e. forward the message to a secondary service) or end the dialog with an error.
The poison message was indeed thought more for cases when the application crashes when processing the message and the system spins on this message, w/o making any progress.

|||

I can see Neil's issue with the poison message handling, and the inability to turn it off. I guess he's got a queue whose processing ultimately depends on some external system (say a web service call or something). In the scenario where the web service is unavailable, you'd pull a message from the queue and try to call the web service. If the web service fails (for example, due to network issues), then you want to rollback the receive to enable that message to be retried in the future. It's not really possible for an application to know whether a network-based call is going to work in advance.

Moving the message of to another queue merely delays the inevitable - the other queue needs processing, and of course it can fail in the same way. I guess you could just take messages from the second queue and add them back to the first, but this seems like a poor solution (it also causes the messages to be reordered, which could have its own issues).

Much as I understand (and applaud) the reasoning behind poison message detection, it does seem that there are scenarios where it would be useful to just turn it off.

|||For the scenario you describe I'd recommend using conversation timers, not rollback the message. Rolling back would make the message visible again immeadetly, triggering another attempt. Instead, using a timer would allow for a delayed retry, like this:

begin transaction
Receive
if web request message
store the request data in table
else if timer message
read request data from table
begin conversation timer (e.g. 1 minute)
commit
do the web request
if succeeded
begin transaction
reset the conversation timer
send back response
commit

Ultimately, everything in the server is optimized for the commit case. Rollbacks should really be extraordinarily and this is how we approached the problem.

As a last resort, the BROKER_QUEUE_DISABLED event notification can be used to enable back the queue automatically.

HTH,
~ Remussql

Poison Message Question

I know I have to build in some ability to deal with a posion message. I was thinking that (sort of mentioned in the article http://msdn2.microsoft.com/en-us/library/ms166137.aspx) I would write a special stored procedure that would handle this situation. I don't know how I could activate this. I don't want to poll of course in a waitfor.

Does anyone have a good idea on this?

When a poison message is detected and a queue is disabled, an event notification is also fired. To subscribe to this event, create an event notification:

CREATE EVENT NOTIFICATION [QueueDisabled]

ON QUEUE [<queue name>]

FOR BROKER_QUEUE_DISABLED

TO SERVICE 'QueueDisabledServiceHandler', 'current database';

You can nou implment the procedure as an activated procedure on the queue servicing the 'QueueDisabledServiceHandler' service. The event message body contains the name and schema of the queue being disabled because of a poison message.

HTH,
~ Remus

Point In Time Restore Question

This is a multi-part message in MIME format.
--=_NextPart_000_0038_01C393F7.79D233D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I am restoring a production database onto a test server to a point in = time.
I restore the database from script with no issue, but when I try to = restore the first of 6 logs, I receive the following message;
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 237426000000004300001, which is = too late to apply to the database. An earlier log backup that includes = LSN 237424000000007700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
What am I doing wrong? What does this mean and how do I get my log = restore script to work to get my database restored to a point in time?
Thanx!
--=_NextPart_000_0038_01C393F7.79D233D0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
I am restoring a production database onto a test server to a point = in time.

I restore the database from script with no issue, but when I try to = restore the first of 6 logs, I receive the following message;

Server: Msg 4305, Level 16, State 1, Line 1The log in this = backup set begins at LSN 237426000000004300001, which is too late to apply to the = database. An earlier log backup that includes LSN 237424000000007700001 can be restored.Server: Msg 3013, Level 16, State 1, Line 1RESTORE LOG = is terminating abnormally.

What am I doing wrong? What does this mean and how do I get = my log restore script to work to get my database restored to a point in = time?

Thanx!
--=_NextPart_000_0038_01C393F7.79D233D0--JLS
This is telling you that you have a missing log between the database you
restored and the log that you are trying to restore. The message is telling
you that you need the log backup containing:
LSN 237424000000007700001
The log you are attempting to restore starts with:
LSN 237426000000004300001
As you can see, your log start point is greater. If you have no log backups
between the database backup and the 7700001 log backup, then that may mean
that the log was truncated during that period. When the chain of LSNs is
incomplete, you cannot restore past the break.
(In the MSDN, search for "SQL LSN" and you will get some background on
this.)
Russell Fields
"JLS" <judy.shoop@.sartomer.com> wrote in message
news:%23RvzWiBlDHA.2364@.TK2MSFTNGP11.phx.gbl...
I am restoring a production database onto a test server to a point in time.
I restore the database from script with no issue, but when I try to restore
the first of 6 logs, I receive the following message;
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 237426000000004300001, which is too
late to apply to the database. An earlier log backup that includes LSN
237424000000007700001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
What am I doing wrong? What does this mean and how do I get my log restore
script to work to get my database restored to a point in time?
Thanx!|||I do a full database backup, then an hour later I start my hourly log
backups.
I think I may need to do a log backup immediately after the database backup
if the log is somehow being truncated after the full backup, and before my
hourly log backup job starts. I need to have a full backup and an hourly
log backup.
The full backup is scheduled for 7:45, then the hourly log backups kick off
at 8:45. The first log backup I am trying to restore is the one right after
the full database backup, therefore, I guess the log is being truncated
somehow in that hour window.
Thanx for the tip on looking this up in MSDN.
--
JUDE
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23S2DROClDHA.2772@.TK2MSFTNGP12.phx.gbl...
> JLS
> This is telling you that you have a missing log between the database you
> restored and the log that you are trying to restore. The message is
telling
> you that you need the log backup containing:
> LSN 237424000000007700001
> The log you are attempting to restore starts with:
> LSN 237426000000004300001
> As you can see, your log start point is greater. If you have no log
backups
> between the database backup and the 7700001 log backup, then that may mean
> that the log was truncated during that period. When the chain of LSNs is
> incomplete, you cannot restore past the break.
> (In the MSDN, search for "SQL LSN" and you will get some background on
> this.)
> Russell Fields
>
> "JLS" <judy.shoop@.sartomer.com> wrote in message
> news:%23RvzWiBlDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I am restoring a production database onto a test server to a point in
time.
> I restore the database from script with no issue, but when I try to
restore
> the first of 6 logs, I receive the following message;
> Server: Msg 4305, Level 16, State 1, Line 1
> The log in this backup set begins at LSN 237426000000004300001, which is
too
> late to apply to the database. An earlier log backup that includes LSN
> 237424000000007700001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
> What am I doing wrong? What does this mean and how do I get my log
restore
> script to work to get my database restored to a point in time?
> Thanx!
>|||Definitely, you don't have to do another log backup
straight after full backup.
When you backup transaction log to the same log backup
file, make sure you use WITH NOINIT option which do not
overwrite the previous backup. Or backup to the different
file name.
When you restore transaction log, you specify WITH FILE=[file no] to specify file number on the file.|||In addition, look in msdb, the backup history tables. These might assist in finding out where the
missing log backups went.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> Definitely, you don't have to do another log backup
> straight after full backup.
> When you backup transaction log to the same log backup
> file, make sure you use WITH NOINIT option which do not
> overwrite the previous backup. Or backup to the different
> file name.
> When you restore transaction log, you specify WITH FILE=> [file no] to specify file number on the file.|||Thanx Johnny, but I am doing everything that you have suggested. I backup
the log to the same backup file as the database, it is the first log file
backup after the full database backup, and when I restore I use WITH FILE
[file no]. The backup most certainly contains WITH NOINIT, until the full
database backup occurs the next night, this is when I INIT.
"Johnny" <anonymous@.discussions.microsoft.com> wrote in message
news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> Definitely, you don't have to do another log backup
> straight after full backup.
> When you backup transaction log to the same log backup
> file, make sure you use WITH NOINIT option which do not
> overwrite the previous backup. Or backup to the different
> file name.
> When you restore transaction log, you specify WITH FILE=> [file no] to specify file number on the file.|||Ok, great! I will, because I would actually like to know what has happened.
Thanx!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OFpE3dIlDHA.1764@.tk2msftngp13.phx.gbl...
> In addition, look in msdb, the backup history tables. These might assist
in finding out where the
> missing log backups went.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Johnny" <anonymous@.discussions.microsoft.com> wrote in message
> news:2d30601c39474$bfb89c90$a601280a@.phx.gbl...
> > Definitely, you don't have to do another log backup
> > straight after full backup.
> >
> > When you backup transaction log to the same log backup
> > file, make sure you use WITH NOINIT option which do not
> > overwrite the previous backup. Or backup to the different
> > file name.
> >
> > When you restore transaction log, you specify WITH FILE=> > [file no] to specify file number on the file.
>

Point in Time Restore Part II

In the hereunder written message I talk about point in time restore.
It is now based upon the fact that there are no hardware problems or what so
ever.
I just would like to roll back to a situation of some time (minutes, hours
or what ever) ago.

Used to the ingres database a point in time restore can take place UP to
any, any, any time since the last FULL backup. (any time up to now !!!)

I can't understand why a point in time restore can only be done based upon
transaction log backups. The current transaction log is also available in my
opinion. (Turn off the power, turn on the power and you will notice that the
automatic recovery is based upon this transaction log file; so in that case
this file is used)

That's what my question is about. Is it correct that a point in time restore
in a SQL server environment can only be done up to the last transaction log
backup.

Bye

Arno de Jong,
The Netherlands.A.M. de Jong (arnojo@.wxs.nl) writes:
> Used to the ingres database a point in time restore can take place UP to
> any, any, any time since the last FULL backup. (any time up to now !!!)
> I can't understand why a point in time restore can only be done based
> upon transaction log backups. The current transaction log is also
> available in my opinion. (Turn off the power, turn on the power and you
> will notice that the automatic recovery is based upon this transaction
> log file; so in that case this file is used)
> That's what my question is about. Is it correct that a point in time
> restore in a SQL server environment can only be done up to the last
> transaction log backup.

Just because Ingress works in one way, there is no requirement for MS
SQL Server to work in that way too.

In SQL Server you need a transaction-log backup to do a point-in-time
restore, but since you can backup the transaction log at any point,
this is not any serious restriction.

I should add here that you must be running full or bulk_logged recovery
mode to have availability to point-in-time restore. With simple recovery
mode, this feature is not available.

In the case you want to undo a fatal SQL statement like an UPDATE without
a WHERE clause, you may be interested in exploring the third-party tool
Log Explorer from Lumigent, check out www.lumigent.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

POINT IN TIME RESTORE

When I try to restore a database with query analyser and point in time resto
re, I always obtain this error message : The log in this backup set terminat
es at LSN 69508000000440000001, which is too early to apply to the database.
A more recent log backup t
hat includes LSN 69508000000440300001 can be restored.
My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
RESTORE LOG TEST1 FROM BBA
WITH recovery, STOPAT = '2004-03-07 8:30:00'
I have the same problem with all my databases and if I look in my device BBA
, I can see that I start with a full backup on 2004-03-07 and I continue wit
h logs backup until 2004-03-13.
What's wrong ?I forgot to mention that my full backup were at 5:00 AM on 2004-03-07|||Is there only 1 tlog created upto "8:30" and after?
If there are multiple tlogs you'll have to issue something like...
restore database dbname.bak from bba with norecovery
restore log logname1.tlog from bba with norecovery
restore log logname2.tlog from bba with recovery, stopat = '2004-03-07 8:30:
00'
Regards,
Craig.
Cris wrote:

> When I try to restore a database with query analyser and point in time restore, I
always obtain this error message : The log in this backup set terminates at LSN 6950
8000000440000001, which is too early to apply to the database. A more recent log bac
kup
that includes LSN 69508000000440300001 can be restored.
> My request is : RESTORE DATABASE TEST1 FROM BBA with norecovery
> RESTORE LOG TEST1 FROM BBA
> WITH recovery, STOPAT = '2004-03-07 8:30:00'
> I have the same problem with all my databases and if I look in my device B
BA, I can see that I start with a full backup on 2004-03-07 and I continue w
ith logs backup until 2004-03-13.
> What's wrong ?|||Hi,
To add on to craigs posting,
The POINT-IN-TIME recovery will work only if the database's Recovery moel is
set to "FULL" and the model is not
changed after theFULL database backup.
Steps:
1. Perform a transaction log backup of the original database (If you have
not performed one)
2. RESTORE DATABASE TEST1 FROM BBA (Give the correct backup file / device
name) WITH NORECOVERY
3. Restore the subsequent transaction log files in order of backup WITH
NORECOVERY option till the final transaction log file
4. In the the final transaction log restore mention WITH RECOVERY and
STOPAT='date and time'
Thanks
Hari
MCDBA
"Craig H." <spam@.[at]thehurley.[dot]com> wrote in message
news:#T$bgUICEHA.2556@.TK2MSFTNGP12.phx.gbl...
> Is there only 1 tlog created upto "8:30" and after?
> If there are multiple tlogs you'll have to issue something like...
> restore database dbname.bak from bba with norecovery
> restore log logname1.tlog from bba with norecovery
> restore log logname2.tlog from bba with recovery, stopat = '2004-03-07
8:30:00'
> Regards,
> Craig.
> Cris wrote:
>
restore, I always obtain this error message : The log in this backup set
terminates at LSN 69508000000440000001, which is too early to apply to the
database. A more recent log backup that includes LSN 69508000000440300001
can be restored.
BBA, I can see that I start with a full backup on 2004-03-07 and I continue
with logs backup until 2004-03-13.|||Sorry to jump in, I have read the thread and it is close to what I
would like to accomplish but not quite.
I have a perfefctly good database with a .mdf and a .ldf file. The
recovery model is FULL. It was a new database a few weeks ago for
developing a new product.
We entered all kinds of info into it before we realized we were not
backing it up. Today we erased all the data...oops..
So I have a good .mdf and a full .ldf, I do a backup of both (wrong
thing)? And I try to restore using the cdoe I have seen here before..
RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
NORECOVERY;
RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
STOPAT = 'Mar 18, 2004 10:00 AM';
This runs without error but restores the database to its CURRENT state
which is empty of data!
If I run the same from Enterprise Manager and I check the "Point in
time" it will not allow me to select anything but today.
It seems that I should be able to do this since I have a full .ldf
file but the method is beyond my technical skills.
Any ideas?
Thanks
Tim|||You haven't provided us with exact information of what you have, and at
which point in time those backups (loosely speaking) were taken.

> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
OK, so what we need to know is, compared to this point in time (when you
erased all data), what file backups (ldf, mdf) and database backups (BACKUP
DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
on a time line compared to the erase of the data.

> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)?
You generally don't do backup of the underlying database files in SQL
Server. Use BACKUP DATABASE and BACKUP LOG instead.
Then you continue to say that you use the RESTORE command, but you haven't
given us the information about when you did the BACKUP DATABASE and BACKUP
LOG.
You are probably toast, though. My guess is that you have some old file
level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
LOG. You then put back those old database files, but want to come closer to
the point of disaster. So you do the RESTORE DATABASE. And we don't need to
go no further, because that put the database in the same state as it was
when the backup command was taken (which was after the point of disaster).
You really need to read the sections about backup and restore in Books
Online to understand how these things work in SQL Server. If you want more
help with this particular situation, please provide more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403221607.6e054dc9@.posting.google.com...
> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Tim|||Tibor,
Thanks for trying to help a dummy
You are right on many fronts
a) I need more knowledge in BOL
b) I didn't decribe clearly, due to "a"
Here is the timeline
1- create database
2- enter data
3- erase data
4 - enterprise manager backup database, backup transaction log
5- restore database with norecovery
6- restore log with recovery, stopat (date time before data erased)
I use osql to run the job, the job runs but all the data is still gone
If I run the same restore from enterprise manager I am unable to
select a date prior to the time of the 1st transaction log backup.
I guess that is the point, you have to have a database backup from
BEFORE the point in time you want to restore to...
I am toast eh?
Tim
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<#
6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
> You haven't provided us with exact information of what you have, and at
> which point in time those backups (loosely speaking) were taken.
>
> OK, so what we need to know is, compared to this point in time (when you
> erased all data), what file backups (ldf, mdf) and database backups (BACKU
P
> DATABASE) and transaction log backups (BACKUP LOG) you have. Again, put it
> on a time line compared to the erase of the data.
>
> You generally don't do backup of the underlying database files in SQL
> Server. Use BACKUP DATABASE and BACKUP LOG instead.
> Then you continue to say that you use the RESTORE command, but you haven't
> given us the information about when you did the BACKUP DATABASE and BACKUP
> LOG.
> You are probably toast, though. My guess is that you have some old file
> level backup. And after the disaster, you did BACKUP DATABASE and BACKUP
> LOG. You then put back those old database files, but want to come closer t
o
> the point of disaster. So you do the RESTORE DATABASE. And we don't need t
o
> go no further, because that put the database in the same state as it was
> when the backup command was taken (which was after the point of disaster).
> You really need to read the sections about backup and restore in Books
> Online to understand how these things work in SQL Server. If you want more
> help with this particular situation, please provide more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tim" <tsullivan@.websul.net> wrote in message
> news:a1920b75.0403221607.6e054dc9@.posting.google.com...|||This is what you should have done in order to do the restore as you wish:

> 1- create database
> 2- enter data
BACKUP DATABASE
> 3- erase data
BACKUP LOG
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
The BACKUP DATABAE can of course be at an earlier point in time, but not
later, I'm afraid.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tim" <tsullivan@.websul.net> wrote in message
news:a1920b75.0403230651.23d72ffe@.posting.google.com...
> Tibor,
> Thanks for trying to help a dummy
> You are right on many fronts
> a) I need more knowledge in BOL
> b) I didn't decribe clearly, due to "a"
> Here is the timeline
> 1- create database
> 2- enter data
> 3- erase data
> 4 - enterprise manager backup database, backup transaction log
> 5- restore database with norecovery
> 6- restore log with recovery, stopat (date time before data erased)
> I use osql to run the job, the job runs but all the data is still gone
> If I run the same restore from enterprise manager I am unable to
> select a date prior to the time of the 1st transaction log backup.
> I guess that is the point, you have to have a database backup from
> BEFORE the point in time you want to restore to...
> I am toast eh?
> Tim
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message news:<#6Mb5LLEEHA.3412@.TK2MSFTNGP10.phx.gbl>...
(BACKUP
it
haven't
BACKUP
to
to
disaster).
more|||Try like this
[url]http://www.sql-server-performance.com/lockwoodtech_log_navigator_spotlight.asp[/ur
l]

> Sorry to jump in, I have read the thread and it is close to what I
> would like to accomplish but not quite.
> I have a perfefctly good database with a .mdf and a .ldf file. The
> recovery model is FULL. It was a new database a few weeks ago for
> developing a new product.
> We entered all kinds of info into it before we realized we were not
> backing it up. Today we erased all the data...oops..
> So I have a good .mdf and a full .ldf, I do a backup of both (wrong
> thing)? And I try to restore using the cdoe I have seen here before..
> RESTORE DATABASE mydatabase FROM DISK='c:\mybackup\pnedata' WITH
> NORECOVERY;
> RESTORE LOG PNEBilling FROM DISK='c:\mybackup\pnedata' WITH RECOVERY,
> STOPAT = 'Mar 18, 2004 10:00 AM';
> This runs without error but restores the database to its CURRENT state
> which is empty of data!
> If I run the same from Enterprise Manager and I check the "Point in
> time" it will not allow me to select anything but today.
> It seems that I should be able to do this since I have a full .ldf
> file but the method is beyond my technical skills.
>
> Any ideas?
> Thanks
> Timsql

Pls help me on the following sql select - I get an error message

Hi
I get an error : ODBC ERROR - Incorrect syntax near , from Crystal SQL
reports.
Your help is really appreciated
SELECT
National_Oxygen_Pte_Ltd_Customer_Budget."Customer No_",
National_Oxygen_Pte_Ltd_Customer_Budget."Item Category Code"
FROM
"nox"."dbo"."National Oxygen Pte Ltd$Customer Budget"
National_Oxygen_Pte_Ltd_Customer_Budget
WHERE
(National_Oxygen_Pte_Ltd_Customer_Budget
."Customer No_" ,
National_Oxygen_Pte_Ltd_Customer_Budget."Item Category Code") NOT IN (SELECT
National_Oxygen_Pte_Ltd_Sales Invoice_Line."Customer No_",
National_Oxygen_Pte_Ltd_Sales_Invoice_Li
ne."Item Category Code" FROM
"nox"."dbo"."National Oxygen Pte Ltd$Sales Invoice Line"
National_Oxygen_Pte_Ltd_Sales_invoice_li
ne WHERE
National_Oxygen_Pte_Ltd_Sales_invoice_li
ne."Shipment Date" >= {ts
'2005-03-01 00:00:00.00'} AND
National_Oxygen_Pte_Ltd_Sales_invoice_li
ne."Shipment Date" < {ts '2005-03-3
1
00:00:01.00'}Radha,
Why did you use , at the where condition?
National_Oxygen_Pte_Ltd_Custo=ADmer_Budg
et."Customer No_" ,
National_Oxygen_Pte_Ltd_Custom=ADer_Budg
et."Item Category Code")
Is this a column?

Pls Help - can't install Sql Server Reporting Services

I have Sql Server Personal Edition SP3 installed on my PC. I get an error
message when trying to install the reporting services trial package. Does
the reporting services not work with a personal edition of Sql Server?
The error message is "The edition of the report server database is not
supported on the edition of Sql Server 2000 you have chosen".
Thanks,Paul, I will be interested to see any responses you receive I have been
experiencing the same problems for the past 2 days. With what appear to be
the exact same setup you are running XP SP2, SQL 2000 SP3a and trial edition
of reporting services.
"Paul Tilson" wrote:
> I have Sql Server Personal Edition SP3 installed on my PC. I get an error
> message when trying to install the reporting services trial package. Does
> the reporting services not work with a personal edition of Sql Server?
> The error message is "The edition of the report server database is not
> supported on the edition of Sql Server 2000 you have chosen".
> Thanks,
>
>|||The System Requirements page at
http://www.microsoft.com/sql/reporting/productinfo/sysreqs.asp says that
only Standard, Enterprise, and Developer Editions are supported. If you're
just doing this for trial purposes, how about installing the Evaluation
Edition of SQL Server while you're testing?
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Tilson" <tilsonpa@.yahoo.com> wrote in message
news:10o3egfkodqaic4@.corp.supernews.com...
>I have Sql Server Personal Edition SP3 installed on my PC. I get an error
>message when trying to install the reporting services trial package. Does
>the reporting services not work with a personal edition of Sql Server?
> The error message is "The edition of the report server database is not
> supported on the edition of Sql Server 2000 you have chosen".
> Thanks,
>

Please tell me why @@Error is not picking up the error

in this procedure the Gender column is a bit datatype. as you can see i'am adding a string.

when i exec proc my error message "print 'error'" is not showing. please help.

you can email at cbmorton!at!gmail.com

CREATE PROCEDURE [dbo].[sp_newuserregistration] AS
BEGIN TRAN
INSERT INTO UserLoginInfo (UserName,[Password]) values ('?','?')
SELECT @.@.IDENTITY AS 'Identity'
DECLARE @.UserID AS INT
SET @.UserID = @.@.Identity
INSERT INTO UserAccountInfo (UserID, Email) VALUES (@.UserID, '?')
INSERT INTO UserProfileInfo (UserID, FirstName, LastName, Gender, DateOfBirth) VALUES (@.UserID, '?', '?', 'x', null)
IF @.@.ERROR <> 0
BEGIN
PRINT 'error'

ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END

GO

--EXEC sp_newuserregistration

The batch stops executing on the error and you can not catch it. You will need to use try/catch but it is only available in SQL Server 2005.|||? As Andreas mentioned, this is a batch-aborting exception -- for this reason, I usually like to use SET XACT_ABORT in my stored procedures that use transactions. Setting that option to ON will cause the transaction to automatically roll back in case of an exception. You might want to read the following articles for more background on all of this: http://www.sommarskog.se/error-handling-I.html http://www.sommarskog.se/error-handling-II.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <codrakon@.discussions.microsoft.com> wrote in message news:2db52d22-6890-435c-92c1-e794a3077677@.discussions.microsoft.com... in this procedure the Gender column is a bit datatype. as you can see i'am adding a string. when i exec proc my error message "print 'error'" is not showing. please help. you can email at cbmorton!at!gmail.com CREATE PROCEDURE [dbo].[sp_newuserregistration] ASBEGIN TRANINSERT INTO UserLoginInfo (UserName,[Password]) values ('?','?')SELECT @.@.IDENTITY AS 'Identity'DECLARE @.UserID AS INTSET @.UserID = @.@.Identity INSERT INTO UserAccountInfo (UserID, Email) VALUES (@.UserID, '?')INSERT INTO UserProfileInfo (UserID, FirstName, LastName, Gender, DateOfBirth) VALUES (@.UserID, '?', '?', 'x', null)IF @.@.ERROR <> 0BEGINPRINT 'error' ROLLBACK TRANENDELSEBEGINCOMMIT TRANEND GO --EXEC sp_newuserregistration

please help: 'PRIMARY' filegroup is full

Hi, all,
I got the error message 'Could not allocate space for
object 'B_Table' in database 'UDB' because the 'PRIMARY'
filegroup is full..' and I check the error log file there
is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
Operating system error 112(There is not enough space on
the disk.) But the truth is there still is 44G free space.
Both the data and log files are setting auto grow 10%.
why I get this kind of error? how can i fix it?
Thanks!
HawkHello Hawk!
1.Could it be that there are quotas on the disk ? Therefore, remove qutoas
for the SystemAccount you are starting SQL Server with. (What does the event
log say ?)
(2.If not, try to resize the data file via QA and scrpt code using ALTER
Database.
3.If This doesn´t do the right thing, add another data file to the primary
file group and try to get around. (but remeber, this is only a woraround,
not the best idea, it is only to test the case that the file could not be
resized))
Jens Süßmeyer.
"hawk" <abchawk@.hotmail.com> schrieb im Newsbeitrag
news:01c401c36671$a339fd10$a301280a@.phx.gbl...
> Hi, all,
> I got the error message 'Could not allocate space for
> object 'B_Table' in database 'UDB' because the 'PRIMARY'
> filegroup is full..' and I check the error log file there
> is error message like this 'C:\MSSQL7\data\UDB_Data.MDF:
> Operating system error 112(There is not enough space on
> the disk.) But the truth is there still is 44G free space.
> Both the data and log files are setting auto grow 10%.
> why I get this kind of error? how can i fix it?
> Thanks!
> Hawk|||To make it bigger, use
ALTER DATABASE xxx
MODIFY FILE...
Info in Books Online.
Or use EM, Properties for the DB and specify a bigger size for the file.
To shrink, yes, use DBCC SHRINKFILE. I do not recommend regular shrinking. That just mean that the
db jumps up and down in size which is a costly operation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hawk" <abchawk@.hotmail.com> wrote in message news:0e9501c36722$2c3362e0$a301280a@.phx.gbl...
> Hi, Dan, Thank you very much.
> If my Data File size is 4.2GB right now and I get the
> primary filegroup is full. then I should resize the data
> file bigger, for example resize to 6.0GB or 7.0GB,right?
> Could I use DBCC DHRINKFILE to shrink the data file?
> I don't know what should I do to down size the data file
> or resize it bigger.
> Thank you very much.
> Have a good one
>
>

Please help: object reference not set to an instance of an object

I have created a new local website from the "Personal Web Site Starter Kit" template. I got an error message when i double click on the database file:

"Object reference not set to an instance of an object."

I cannot open Personal.mdf in VS2005.

I have not tuch anything. SQL Server 2005 Express is running in bacground. What is wrong?

This sounds like something is not registered correctly in Visual Studio.

The ASP.net forums is a better place to ask questions about ASP.net and Visual Web Developer. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

Mike