Hi,
I need to set up a dump of my main database's transaction log to dump every hour but not overwrite until the 8th hour (keeping 7 log dumps). Can anyone tell me how to set up the scheduled tasks for the transaction log dump? Is there anything special I need to do for the main database dump?
Thank you, Laurie
I am testing a procedure to automate the transaction log dump. I am following the steps located in Chapter 22 of the Microsoft SQL Server Administrator's Companion ("Automatic Transaction Log Dumps Using Performance Monitor"). The alert in Performance Monitor appears to be starting when the log is 75% full, but the alert is not firing off the file that contains the dump transaction sql command. For the 'Run Program on Alert' box this is what I have: isql -Ssvrname -Usa -P -id:appsmssqlinndump.sql
The dump.sql file contains: 'dump transaction pubs with no_log'
I have also tried the following 4 steps: 1) Created a SQL Alert Messsage, 2) Created an NT Performance Monitor Threshold Alert to run sqlalrtr to issue a certain error when the pubs log is 75% full, 3) Created a TSQL Task, and 4) Created a SQL Server Alert to run the Task created in step 3. This appears to do the same thing. The Alert is fired off, but the Task is never executed. Note: I am able to execute the task from within the Schedule Tasks Window.
I am using Standard Security with SQL Server 6.5 (sp5a) running on NT4.
Thanks for you help in advance.
Hi,
does anybody know how to automate the loading of incremental transaction dumps. The manual way is to use the "load tran DB with FILE = x" statement.
Since this has to be done in the right sequence and i need to automate this task to keep a second server up to date i like to know if there is a stored procedure
or any other tool which could do the task.
Thanks for your help
Best Regards
Yorn Ziesche
our users believe that we lost some valid data, but no one knows who did it,
I thought I can find it from the transaction dumps I take every hour
so ,Can I read Transaction Dumps (*.TRN) file in SQL server 7.0 or Can I get this information through other means.
Thanks for your help
DP
Can anyone tell me where I can find information about why sql server might be executing a stack and/or symptom dump?
That is, what are some of the conditions that might cause this. Every time I try to DBCC DBREINDEX on a certain table, stack
and symptom dumps occur and SQL Server remains incommunicado (can't connect, even locally) until I restart it. I'm going to try to fix the table by using
rename table, create new table (and dependent objects) , transfer data, and toast the original table. No problem, I've done that
before with smashing success. But, if this solves my problem, I'll still be no wiser about what it was that caused the problem in
the first place. Any suggestions, questions, comments, or even jeers would be appreciated.
Thanks,
Chris
HiIam new to this groupSO can any one provide me with dumps of 1z0-007regdsAJax
View 4 Replies View RelatedHi !
I have one crashed database !
I have no dumps at all !
I have cold backup of the data files !!
Is it possible to just restore the datafiles and everything`s nice again ??
Hi:
Actually we have problems doing database dumps, so we're copying directly the .DAT files
to a backup machine, I mean the .DAT files are the data devices that uses SQL no dumps files.
We can do or eecute the dump process.
What problems can we expect doing this?
Which can be the problems 'cause we cannot dump our DB's?
Thanks
I have noticed a question on the Admin exam which involves declaring and usting a time variable for scheduling a backup or dump of a database. Is this possible and has anyone else seen this on the exam or used it in reality?
View 2 Replies View RelatedHello gurus,
Our SQL Servers is giving us a headache, after a certain period in time, either SQL Service automatically shuts down by itself or hangs. I've opened the logs and found hex dumps. Can you help me out with these?
2007-07-08 04:04:35.20 spid53 SqlDumpExceptionHandler: Process 1760 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* ************************************************** *****************************
*
* BEGIN STACK DUMP:
* 07/08/07 04:04:35 spid 53
*
* Exception Address = 0042D46D
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 6AF1EDF0
* Input Buffer 4088 bytes -
* USE Document DBCC DBREINDEX (dtproperties) DBCC DBREINDEX (REfID_DocID
* ) DBCC DBREINDEX (RF_UNISYS_ErrorCodes) DBCC DBREINDEX (SYS_Document_F
* lat_Meta_Data) DBCC DBREINDEX (SYS_Document_Meta_Data) DBCC DBREINDEX
* (SYS_Document_Meta_Detail) DBCC DBREINDEX (SYS_Documents) DBCC DBREIND
* EX (SYS_ETFuelType) DBCC DBREINDEX (SYS_ETStatus) DBCC DBREINDEX (SYS_
* HF_Document_Meta_Data) DBCC DBREINDEX (SYS_HF_MI_Emission_Results) DBC
* C DBREINDEX (SYS_ITP_Failed) DBCC DBREINDEX (SYS_MI_Emission_Results)
* DBCC DBREINDEX (SYS_RF_Document_Meta_Data) DBCC DBREINDEX (SYS_RF_Docum
* ent_Status) DBCC DBREINDEX (SYS_TR_Document) DBCC DBREINDEX (SYS_TR_SM
* S_Document) USE Industry DBCC DBREINDEX (dtproperties) DBCC DBREIND
* EX (IND_MF_Industry) DBCC DBREINDEX (RF_ErrorCodes) DBCC DBREINDEX (RF
* _OperationCodes) DBCC DBREINDEX (RF_Unisys_ErrCodes) DBCC DBREINDEX (S
* YS_Admin_Has_Agents) DBCC DBREINDEX (SYS_Admin_Sharing) DBCC DBREINDEX
* (SYS_Companies) DBCC DBREINDEX (SYS_Company_Has_Admins) DBCC DBREINDE
* X (SYS_Company_Meta_Data) DBCC DBREINDEX (SYS_HF_Admin_Has_Agents) DBC
* C DBREINDEX (SYS_HF_Companies) DBCC DBREINDEX (SYS_HF_Company_Has_Admin
* s) DBCC DBREINDEX (SYS_HF_Company_Meta_Data) DBCC DBREINDEX (SYS_HF_Us
* er_Meta_Data) DBCC DBREINDEX (SYS_HF_Users) DBCC DBREINDEX (SYS_MF_App
* Variables) DBCC DBREINDEX (SYS_MI_Token) DBCC DBREINDEX (SYS_Page_Acce
* ss) DBCC DBREINDEX (SYS_Pages) DBCC DBREINDEX (SYS_Password_History)
* DBCC DBREINDEX (SYS_RF_Announcement) DBCC DBREINDEX (SYS_RF_BodyType)
* DBCC DBREINDEX (SYS_RF_Color) DBCC DBREINDEX (SYS_RF_Company_Meta_Data)
* DBCC DBREINDEX (SYS_RF_Company_Status) DBCC DBREINDEX (SYS_RF_DieselT
* ype) DBCC DBREINDEX (SYS_RF_EmissionFees) DBCC DBREINDEX (SYS_RF_Emiss
* ionRules) DBCC DBREINDEX (SYS_RF_Fuel_Type) DBCC DBREINDEX (SYS_RF_Hel
* pDetails) DBCC DBREINDEX (Sys_RF_Make) DBCC DBREINDEX (SYS_RF_Month)
* DBCC DBREINDEX (SYS_RF_MVClassification) DBCC DBREINDEX (SYS_RF_MVType)
* DBCC DBREINDEX (SYS_RF_MVType2) DBCC DBREINDEX (SYS_RF_Page_Groups)
* DBCC DBREINDEX (SYS_RF_Purpo
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBAFFF 008bb000
* Invalid Address 77F80000 77FFBFFF 0007c000
... <snipped>
* xpstar 09240000 09248FFF 00009000
* rsabase 092D0000 092F2FFF 00023000
* dbghelp 0AA80000 0AB7FFFF 00100000
*
* Edi: 0AA53937: 00000000 00000000 00000000 84004A00 98018901 C501B101
* Esi: 6AF1EDF0:
* Eax: 00000878:
* Ebx: FFFFE000:
* Ecx: 3FFFF800:
* Edx: FFFFE000:
* Eip: 0042D46D: CA8BA5F3 F303E183 DC7D8BA4 83D045FF 4D8B2CC7 E9D233E0
* Ebp: 0A1BFCC0: 0A1BFCE4 0042D5CD 71E428CC 71E40570 71E40988 0AA519A0
* SegCs: 0000001B:
* EFlags: 00010206: 00530053 0052004F 003D0053 00000034 0053004F 0057003D
* Esp: 0A1BFC28: 0AA519A0 71E4052C 00000000 00000010 71E408A0 00000010
* SegSs: 00000023:
* ************************************************** *****************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 0042D46D Module(sqlservr+0002D46D)
* 0042D5CD Module(sqlservr+0002D5CD)
* 0042D6C7 Module(sqlservr+0002D6C7)
* 00508750 Module(sqlservr+00108750)
* 0051EB18 Module(sqlservr+0011EB18)
* 0051E9E4 Module(sqlservr+0011E9E4)
* 0085EACA Module(sqlservr+0045EACA) (GetIMallocForMsxml+0006A08A)
* 004229A7 Module(sqlservr+000229A7)
* 0087B87B Module(sqlservr+0047B87B) (GetIMallocForMsxml+00086E3B)
* 0087E3C9 Module(sqlservr+0047E3C9) (GetIMallocForMsxml+00089989)
* 0059A449 Module(sqlservr+0019A449)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 7C57B3BC Module(KERNEL32+0000B3BC) (lstrcmpiW+000000B7)
* -------------------------------------------------------------------------------
*Dump thread - spid = 53, PSS = 0x55a35280, EC = 0x55a355b0
*Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQLlogSQLDump0020.txt
*** Problems occur intermittently, sometimes after our full backup which occurs every 0000h, sometimes 30 minutes after our transaction log dumps.
Please advice me on my next step. Thanks
Hi all,
I've read that the SQL dump can only be done directly into the local server. Is there a way to put directly the dump into another server via the network (not copying it but writing it during the process of backup).
Any help much apreciate.
Thanks
We are experiencng high cpu utilization across all 4 cpu's at the top of the hour when our transaction log dump job runs. Has anyone observed this bahavior before? Is there anything we can do to mitigate this? Thank You.
View 4 Replies View RelatedHi Forum,
I am new to microsoft SQL server as I am from Oracle background. I am preparing to the MCP certification for Exam 70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition. The exam shows as SQL server 2000 is there any exam for SQL server 2005.
Is it the write exam to give to start as fesher into microsoft platform. Iam into development not towards administration.
If any body has any Exam preparation questions please forward them to krishna.kanigelpula@gmail.com.
Regards,
Krishna.
I am attempting to remember the string to dump/restore a database to a different physical server.
I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?
Thanks
How can I check to make sure that my dumps are not corrupted? I have been using a utility from Microsoft called DSCAN5, but have found that this has some limitations.
View 1 Replies View RelatedI have a package which access a DB2 database and pulls data from a single table. I can't put a specific event on it, but the package has been causing a dump to occur on a rather regular basis. The really odd part is sometime when I add a data viewer on the output link of the OLE DB Source it works....then it starts to dump again a couple of executions later. There are not date/time values involved in the result set, just character strings. Default code page is set to 1252 and use default page is set to False....any ideas appreciated - this is really starting to drive me nuts!
Thanks
I'm going back and forth on an issue and was looking for some outside observations. I have over a dozen SQL NT servers, both 6.5 and 7.0. On some servers I'm dumping the databases and backing up the dumps with Veritas BackupExec, on 4 (two SQL 6.5, two v7) I'm backing up with the Veritas SQL Backup Agent.
Obviously, if you don't have room to dump a database, you must use the backup agent. That is the case on one of my servers and becoming so on a second. But my personal preference is to do the dump/backup.
(as a side note, one server is not backing up correctly with the backup agent but ultimately that box will require it due to DB growth, so it is something that I have to resolve)
I like the dump system for a few reasons. I find it easier to load from a dump, particularly a single table. Likewise, I find it easier to copy a database by loading from a dump rather than going from a backup, but that's mainly because of BackupExec being a little bit strange on redirecting restores.
Here's my clincher. I find restores via the backup agent to be ridiculously slow. Let's say I have a 5gig DB that has 1.5gig in it. The dump size will probably be somewhere below 2gig. The restore via backup agent carefully writes the entire 5gig even though 3.5g of that is empty. This takes a lot of time. Add to that the "post-restore DBCC". I had such a restore take something on the order of 13 HOURS which, needless to say, conflicted with my nightly DBCC's and backups.
OK. End of rant. Any suggestions or thoughts on the subject?
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View Related
I want to list out the pending transaction for transaction replication by publication.
Help needed.
I'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?
I created a Calculated measure in cube something like this : ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount]). To get only spend transactions. Now, I want to slice this measure with same hierarchy to find the amount distribution across different transaction types under spend transaction. But this query behaving like the measure doesn't have relation with measure.
you can think this as below query:
WITH
MEMBER SPEND AS ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent].&[SPEND],[Measures].[Transaction Amount])
SELECT NON EMPTY {SPEND} ON 0
,NON EMPTY ([TransType].[TransTypeHierarchy].[TransTypeCategoryParent]) ON 1
FROM [CUBE]
Hi
How do I make use of begin transaction and commit transaction in SSIS.
As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows:
Hresult:0x80004005
descriptionyntax error or access violation.
its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database.
Thanks,
Prashant
Error returned when trying to commit the transaction to a database that is a replication distributor. (sql2005 ctp16)
View 10 Replies View Related
Hi,
I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp
. My workflow is as followed
*********************************
For Each ADO.Record in Oracle (transaction=not supported)
If (Certain_Field_Value = 'A')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence A (Start a Transaction , transaction=required)
INSERT/UPDATE some records in SQLDB(transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'B')
Lookup Data in SQL DB with values from Oracle (transaction=not supported)
DO Sequence B (Start a Transaction , transaction = required)
INSERT/UPDATE some records in SQLDB (transaction=supported)
Finish Sequence A ( transaction should stop here)
UPDATE Oracle DB ( Execute SQLTask, transaction=not supported)
If (Certain_Field_Value = 'C')
------------
------------
End ForEach Loop
*************************************
My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction.
But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ".
What is it that I am doing wrong?
Regards
KyawAM
I'm getting this error:
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
http://support.microsoft.com/kb/839279 this didn't help
any suggestions?
I am executing a stored procedure something like this
Create Procedure TEST
@test1
@test2
AS
BeGIN TRANSACTION ONE
SELECT...
UPDATE..
....
....
....
TRUNCATE
etc and lot of Select,update and delete statements like this
.....
COMMIT TRANSACTION ONE
The Block of code which I have b/w BEGIN TRANSACTION AND COMMIT TRANSACTION ..Will it be rolled back
if it encounters any errors in the SELECT,UPPDATE,DELETE ..statements which I have with the transaction one.
IF not How do I roll back if it encounters any erros b/w the BEGIN TRANSACTION and END TRANSACTION.
Thanks
micky
Hi all,
In my ASP application, I've been using this code:
----------------------------------------------
dim conn, connectionstring
connectionstring= "Provider=SQLOLEDB.1; Data Source = blahblah; Initial Catalog = abc; User Id = osss; Password=xxx"
set conn = server.CreateObject("adodb.connection")
conn.open connectionstring
----------------------------------------------
It's just basically to set up the connection to the SQL server, it works on my other pages, but on a few pages, the last line (conn.open connectionstring) triggers this error:
"Microsoft OLE DB Provider for SQL Server error '8004d00a'
New transaction cannot enlist in the specified transaction coordinator. "
I've never encountered this error before and I couldn't think of any solution... please help!!!
Thanks so much,
Annie
I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)
For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')
So table testTable1 has a unique index on it..
Now try to run these statements:
--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION
SELECT * FROM testTable2
..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?