Help needed with Transaction Logs

i keep getting a message stating that the disk is fulkl so i have been tryin
g
to shrink the database to start with using the following code
USE [BossData]
GO
DBCC SHRINKDATABASE(N'BossData', 50 )
GO
All i get is the following error
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
Has any one any suggestions please ?Check whether executing BACKUP LOG... can resolve the problem.
** Solution 1
- change the location of the backup device to a HDD with enough HDD space
- do a full backup on the database
- shrink the database using "dbcc shrinkdatabase (dbname)"
- change the location of the backup device to a HDD to the original
directory (with enough HDD space)
** Solution 2 (when there is not enough HDD space, and you cannot add a new
HDD)
** warning ** (from BOL) TRUNCATE_ONLY removes the inactive part of the log
without making a backup copy of it and truncates the lob. This option frees
space. Specifying a backup device is unnecessary because the log backup is
not saved. The changes recorded in the log are not recoverable. For recovery
purpose, immediately execute BACKUP DATABASE.
*** It would be better if you have a valid backup anyways.
- backup log dbname with truncate_only
- dbcc shrinkdatabase (dbname)
Both backup (full/log) and "dbcc shrinkdatabase" can be done with the
database online (and without detaching the database).
There is also a database option 'autoshrink' that could be used for
shrinking a database periodically and automcatically by SQL Server. By
default, the 'autoshrink' option is set to OFF in SS2000 (except SS2000
Personal Edition). You will need to implement an appropriate backup
strategy, anyways.
-- To set the autoshrink database option. (When true, the database files are
candidates for automatic periodic shrinking.)
sp_dboption 'dbname', 'autoshrink', 'TRUE/FALSE'
References
- Shrinking the transaction log
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
- Truncating the transaction log
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Peter Newman" <PeterNewman@.discussions.microsoft.com> bl
news:1B25C6DC-84D9-4E8B-B62B-DC4A4689CD00@.microsoft.com g...
> i keep getting a message stating that the disk is fulkl so i have been
trying
> to shrink the database to start with using the following code
> USE [BossData]
> GO
> DBCC SHRINKDATABASE(N'BossData', 50 )
> GO
> All i get is the following error
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
should
> be discarded.
> Has any one any suggestions please ?
>
>
>

Help needed with Backup and Restore

If I decide to backup my transaction logs on one server and move them
to another server with the same "everything"

What do I need to do in order to automate this if it is possible

Vincento"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0410121038.777ed048@.posting.google.c om...
> If I decide to backup my transaction logs on one server and move them
> to another server with the same "everything"
> What do I need to do in order to automate this if it is possible
>
> Vincento

It sounds like you're looking for log shipping - if you have SQL 2000
Enterprise Edition, then check out the information in Books Online. If you
don't, then it's still relatively easy to implement something yourself:

http://www.winnetmag.com/Article/Ar...3231/23231.html
http://sqlguy.home.comcast.net/logship.htm
http://www.sql-server-performance.c...og_shipping.asp

Also search Google for "log shipping" and you should get plenty of hits.

Simon

Help needed to restore SQL Database

Hi Gurus,

Can anyone assist?

How to recover the database if I have only the current transaction log and a older version of .mdf & .ldf files. No backup had being performed via Enterprise Manager.

I have installed MSSQL2000 on Windows 2003 Server. Created a new database 'sample' and inserted some data into the 'sample' db. Afterwhich, I offline the 'sample' db and copied the .mdf & .ldf to another backup directory.

Restart the database by bring it online again and continue to insert more data ... (approx 100,000 rows of data).

Afterwhich, I tried to damage the running database .mdf file and the database went into offline / suspect mode.

How can I recovered to the point in time of failure based on just current transactional log & older version of .mdf & .ldf files?

Can any gurus out there advises?If you have not been making backups, you will not be able to recover to a point in time. Recovering to a point in time requires that the database be in the 'Full' recovery model and that you make periodic full backups combined with transaction log backups. The best that you *might* be able to do in this scenario is to perform a single file attach (see sp_attachdb).

Regards,

hmscott

help needed in error handling and undo transaction

I am reading a temptable, and doing 2 inserts. In case of error, i want the 2 inserts to be undone, and move to the next line. The complete opposite is happening and the process is being stopped while i wanr it to move on!Help appreciated!

This is my code:

BEGIN TRANSACTION

if exists(select [id] from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#textfile'))

drop table #textfile

CREATE TABLE #textfile (line varchar(8000))

BULK INSERT #textfile FROM 'c:\init_newsl.txt'

DECLARE table_cursor CURSOR FOR SELECT line FROM #textfile

OPEN table_cursor FETCH NEXT FROM table_cursor INTO @.oneline

SET XACT_ABORT ON

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')

BEGIN

INSERT INTO mytable1 values(@.f1, @.f2)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table1. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

INSERT INTO mytable2 values(@.f3, @.f4)

IF @.@.ERROR <> 0

BEGIN

PRINT 'Error in insertion of table2. Error is ' + LTRIM(STR(@.@.ERROR))

RAISERROR('',15,1)

goto next_line

END

goto next_line

next_line:

FETCH NEXT FROM table_cursor INTO @.oneline

END /* while fetch status = 0 */

Hi Terry,

You need to begin a transaction for each unit of work that you with to either commit or rollback. In your case, you are encapsulating the entire process in the transaction by placing your begin outside of the individual fetch statements. Also, I can't see a commit/rollback anywhere.

I would question your need to use a cursor here - can you post what you're trying to do and maybe we can help?

Anyway, if you did want to go down the cursor route, you would need to:

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')
BEGIN
BEGIN TRANSACTION t1

INSERT INTO MyTable1...

IF (@.@.ERROR <> 0)
BEGIN
ROLLBACK t1
GOTO NextLine
END

...etc

NextLine:
IF (@.@.TRANCOUNT >= 1) -- or >= 2 if you've a parent tran...
COMMIT t1

FETCH...
END

Cheers,
Rob

Help needed for Transaction Support in SQL server 2005

Hi,

I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.

my data layer methods somewhat looks like

public void Save(order value)
{
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
int orderId = SaveOrderMaster(value);
value.OrderId = orderid;
int childId = SaveOrderDetails(value);
//complete the transaction
transactionScope.Complete();
}
}

here
1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.

2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".

My Problem:
Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.

The problem may occur if your code calls the second procedure before the first procedure is done (or before the row has been added to the parent table). This can happen, because in your code you have not ensured the second procedure to call after the first procedure is completed. So either you can implement this logic in your code, or you can make use of trigger functionality in SQL. For example you can use an INSERT trigger on the parent table to automatically do the same thing as your second procedure did:

CREATE TRIGGER trg_InsOrdDetails ON Orders FOR INSERT
AS
--do your insert here, you can also call the second procedure

go

For more information about SQL trigger, you can refer to this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp

|||Jay

But those calls are from Businss Layer Dll which written in .NET. So when SaveOrderMaster(value) call returns it should update the record in database. because SaveOrderMaster(value) calls one stored procedure that returns the id.

so call is flowing in the following order:

UI calls BLL.Save(value) -->
BLL calls DAL.SaveOrderMaster(value) -->
DAL calls Stored Procedure and set the newly created id in value object.
BLL Calls DAL.SaveOrderDetails(values) -->
DAL calls stored procedure with the details and Id (created in SaveOrderMaster call).
and I am getting the foreignKey error while making call to SaveOrderDetails().

But I am not able to find the source of error. as this is not repeatative behaviour, some time it ouccurs after 5000 BLL.Save() calls.

I also cant use triggers here, as order detail data is huge. and i dont want to call my pass so many data to stored procedure in one go. (aka. business and design requirement -:) )

any help in this regard is highly appriciated?|||

When you have the error, what's the orderId in the parent table, and does it look like what you'd expect? Also, are you using identity(), or Scope_identity to get the key? If you're not using scope_identity(), then that could be your problem.

|||I am using the Scope_identity to retrive the value for the Order Table identity column.
and if i remove the Transaction, My SaveOrder method save the Master table entry while some time I am getting error while saving order details.|||

The TransactionScope class performs none atomic transactions which is not legal so pass your code to T-SQL transaction block and your problem will go away. Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

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

Help needed for Sql2005 Transaction Logs

Hi,

I have run a lot of insert/ delete, update queries on a database in sql 2005 for a couple of months

Is there a way to track when and what are sql transactions that are have been executed?

Thanks in advance for your time and help.

whitze

You can monitor the activity in your database by running sql profiler........but this can be performed when you want to identify the bottlenecks in your db or in your server.........but it is not advisable to run it always....if you have the trace which is captured when those DML's were performed you can track it .......else i dont think its possible..........im not too sure about it......|||

If you have a complete trail of the Transaction Logs, you could use one of the several third party log tools to accomplish your task.

If you do not have a complete trail of Transaction Logs, then most likely, you will not be able get that information.

help needed

How to view the updated table in a day to day transaction in sql server2000?
Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?

help needed

How to view the updated table in a day to day transaction in sql server200
0?Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?

help needed

How to view the updated table in a day to day transaction in sql server2000?Hi
Please explain what you actually want... If this is auditing then check out
the CREATE TRIGGER example in Books online as this has an example of simple
auditing. Another way of auditing is to use a third party too such as those
from Lumigent (Lumigent Log Explorer)
http://www.lumigent.com/ or PI, http://www.logpi.com
John
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?|||Yes it is possible!!
Jay Freeman
The answer is only as descriptive as the question...
"Rizwan" <Rizwan@.discussions.microsoft.com> wrote in message
news:4B4CEF94-09B4-4DCA-883D-D76BD147B019@.microsoft.com...
> How to view the updated table in a day to day transaction in sql
server2000?