DB Engine :: Transaction Log Database Corruption Error
Jul 23, 2015
I am having an issue for transaction log database backup getting failed and throw me a following error. I never seen this corruption error before so Is there any solution for it?This error is from my Log file:-
Failed:(-1073548784) Executing the query "BACKUP LOG [Xe] TO Â DISK = N'D:XeXeXe_backup_201507230922.trn' WITH NOFORMAT, NOINIT, Â NAME = N'Xe_backup_20150723092224', SKIP, REWIND, NOUNLOAD, Â STATS = 10
" failed with the following error: "BACKUP detected corruption in the database log. Check the errorlog for more information. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.This is from SQL JOB error"-Â
Executed as user: SqlAdmin. ...ion 9.00.5324.00 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   Started:  9:22:20 AM  Progress: 2015-07-23 09:22:24.08   Source: {297F9C99-05AE-47BD-AA70-3E25DDD78CAB}
   Executing query "DECLARE @Guid UNIQUEIDENTIFIER    EXECUTE msdb..sp".: 100% complete  End Progress  Progress: 2015-07-23 09:22:24.91   Source: Back Up Database (Transaction Log)  Â
Â
[code]....
The MDF and LDF files are placed in SSD drive and tempdb files are placed in HDD drive. Snapshot isolation is enabled on the database. When a script is executed to insert data with NULL value to a table which has NOT NULL column, the transaction fails and then a log undo happens which fails and takes the database to suspect mode.
But when the MDF and LDF files are placed in HDD drive all this do not happen. The transaction just fails.
I have SQL Server Enterprise set up with Merge replication, it is being used to Sync with Windows Mobile Devices as the subscribers.
All the Windows Mobile subscribers have been synchronising fine up until recently, when 1 of the Subscribing Windows Mobile devices received this error. The day after, another device Synchronised fine the first time (note that the Synchronisation was a long one and the SDF grew in size dramatically while the synchronisation occured, but the Sync worked fine and the SDF dropped back to its initial size) The 2nd sync 5 mins later failed however so this Mobile device has the same error also.
Any suggestions as to the below are much appreciated
a) Why this error occurs? b)How do you resolve this error? c)What is the Repair utility mentioned in the error, and is it of use to me?
Thanks in advance, I am very greatful for any advice.
I got the sql2005 std edition at the msdn rollout, and the vs2005 studio. I uninstalled sql2000 and now, cannot connect to the database engine.
I get the following error message with a link to an page that offers no info. What's up and how can I get it to work? Please, help.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
I have been successfully using SQL Server 2005 developer edition for a couple of months now. However, when I started Microsoft SQL Server Management Studio this morning I could not connect to the €œDatabase Engine€? (I€™m using Windows Authentication). I can still connect with the €œIntegration Services€? and €œAnalysis Services€? on the same server. The server running on my local machine, so there shouldn€™t be any problems with the network. I am also able to connect with it through ODBC, it just seems to be the Server Management Studio which can€™t connect, and it only has problems with the €œDatabase Engine.€? Any help would be appreciated.David
The detailed error message says:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10061&LinkId=20476
------------------------------
Error Number: 10061
Severity: 20
State: 0
------------------------------
Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
I am running SQL 7.0 with Access 97 as a front end. I am having a problem linking one of the tables with an odbc connection. When I try to link the table, it gives me the error :
"The database engine can't find <name of table>. Make sure it is a valid parameter or alias name, that it doesn't include invalid characters or punctuation, and that the name isn't too long."
I have tried renaming the table, and it then will allow the connection, but if I rename it back to the original name, it doesn't work again. The table name is "Invoice_Line_Item". Changing the name permanently is not an option.
Has anyone else run across this problem, or know of a fix for it.
I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !
It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?
I finally got SQL Server 2005 installed on my Vista Ultimate computer and now I can't connect in SQL Server Management Studio.
I've got a named instance; use the built-in system account; windows authentication mode. I try to connect to
Server type: Database Engine Server name: RAYWOERNER-PCRAYWOERNER Authentication: Windows Authentication
and it fails. Please help.
Thanks,
Ray
Here are the technical details:
=================================== Cannot connect to RAYWOERNER-PCRAYWOERNER. =================================== Login failed for user 'RayWoerner-PCRay'. (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 ------------------------------ Server Name: RAYWOERNER-PCRAYWOERNER Error Number: 18456 Severity: 14 State: 1 Line Number: 65536
------------------------------ Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
Backing up all databases on a sql server that hosts secondary high availability databases as well as other databases. Â The other databases back up fine, but the high availability secondaries all get the same error:
BackupDiskFile::OpenMedia: Backup device 'F:MSSQLBackupdbnamedbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)
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 ran into the same problem ... Importing from Access into SQL 2005 using SSIS and get the error:
[Connection manager "SourceConnectionOLEDB"] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
I have a database was showing (Suspect). It looks not able to log into the SQL database. After I reboot the machine it turned to recovering for a while and then changed to suspect again. I did the following steps: 1. set database to emergency mode (Status = -32768) 2. after that I tried to do DBCC checkdb but I received “cannot open ‘database’…”
So I have to restore the database from latest backup but I lost one-hour data. My questions are: 1. how to recovery the corruption database instead of restoring from backup? 2. From following error log, does anyone know what is the reason causing database “suspect”?
The database is running at SQL7 and SP1. The database has 1g data (50% free space). The Log file has 600MB.
Thanks for any info/advice,
Stella Liu
Error Log: 2000-01-31 16:07:04.92 spid11 Using 'sqlimage.dll' version '4.0.5' Dump thread - spid = 11, PSS = 0x4935b0cc, EC = 0x4935b27c Stack Dump being sent to d:MSSQL7logSQL00007.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 01/31/00 16:07:59 spid 11 * * Input Buffer 210 bytes - * I N S E R T I N T O P P F R e p r e s e n t a t i v e s * V A L U E S ( 4 4 1 1 8 , 1 1 7 9 4 3 1 4 , { f n g e t i m a g e * ( 1 ) } ) e x e c s p _ d r o p o r p h a n s c o m m i t t r a * n * ************************************************** ***************************** ------------------------------------------------------------------------------- Short Stack Dump 0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b) 0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0) 0x005a6741 Module(sqlservr+1a6741) (Page::MakeInsertSpace+45) 0x0040df03 Module(sqlservr+df03) (PageRef::InsertRows+132) 0x005b0531 Module(sqlservr+1b0531) (XDES::UndoPageOperation+228) 0x00431ceb Module(sqlservr+31ceb) (XDES::RollbackToLsn+22a) 0x00430560 Module(sqlservr+30560) (XDES::Rollback+14e) 0x00431abf Module(sqlservr+31abf) (XCB::Rollback+2f9) 0x0051ac85 Module(sqlservr+11ac85) (clean_process+162) 0x005b22fd Module(sqlservr+1b22fd) (language_exec+5f9) 0x4106135c Module(opends60+135c) (execute_event+659) 0x4106164b Module(opends60+164b) (process_commands+f3) 0x4109285a Module(ums+285a) (ProcessWorkRequests+ed) 0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139) 0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce) 0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be) 2000-01-31 16:08:02.65 spid11 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000) 2000-01-31 16:08:03.34 kernel SQL Server Assertion: File: <page.cpp>, line=2809 Failed Assertion = 'sid >= m_slotCnt || m_slots[-sid].offset ==0'. 2000-01-31 16:08:03.51 spid11 Error: 3624, Severity: 20, State: 1. 2000-01-31 16:10:57.29 spid11 Error: 3314, Severity: 21, State: 4 2000-01-31 16:10:57.29 spid11 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (56:11888:81).. 2000-01-31 16:10:57.37 spid11 Error: 9001, Severity: 21, State: 1 2000-01-31 16:10:57.37 spid11 The log for database 'WebPDM' is not available.. 2000-01-31 16:12:39.73 spid11 Error: 3314, Severity: 21, State: 2 2000-01-31 16:12:39.73 spid11 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (55:12913:1).. 2000-01-31 16:12:44.28 spid6 Closing file d:mssql7datawebpdmdata.mdf. 2000-01-31 16:12:44.46 spid6 Closing file d:mssql7datawebpdmlog.ldf. 2000-01-31 16:12:44.60 spid6 Starting up database 'WebPDM'. 2000-01-31 16:12:44.60 spid6 Opening file d:mssql7datawebpdmdata.mdf. 2000-01-31 16:12:44.92 spid6 Opening file d:mssql7datawebpdmlog.ldf. 2000-01-31 16:12:51.65 spid6 Recovery progress on database 'WebPDM' (7): 14 percent. 2000-01-31 16:12:55.39 spid6 Recovery progress on database 'WebPDM' (7): 28 percent. 2000-01-31 16:12:58.14 spid6 Recovery progress on database 'WebPDM' (7): 42 percent. 2000-01-31 16:13:08.39 spid6 102 transactions rolled forward in database 'WebPDM' (7). 2000-01-31 16:13:08.39 spid6 Recovery progress on database 'WebPDM' (7): 57 percent. 2000-01-31 16:13:08.40 spid6 Recovery progress on database 'WebPDM' (7): 71 percent. 2000-01-31 16:13:08.59 spid6 Using 'sqlimage.dll' version '4.0.5' Dump thread - spid = 6, PSS = 0x41308084, EC = 0x41308234 Stack Dump being sent to d:MSSQL7logSQL00008.dmp ************************************************** ***************************** * * BEGIN STACK DUMP: * 01/31/00 16:13:18 spid 6 * * ************************************************** ***************************** ------------------------------------------------------------------------------- Short Stack Dump 0x77f67a2b Module(ntdll+7a2b) (ZwGetContextThread+b) 0x0077fc6b Module(sqlservr+37fc6b) (utassert_fail+1a0) 0x005a6741 Module(sqlservr+1a6741) (Page::MakeInsertSpace+45) 0x0040df03 Module(sqlservr+df03) (PageRef::InsertRows+132) 0x005b0531 Module(sqlservr+1b0531) (XDES::UndoPageOperation+228) 0x00431ceb Module(sqlservr+31ceb) (XDES::RollbackToLsn+22a) 0x005adfae Module(sqlservr+1adfae) (RecoveryMgr::UndoPass+1c8) 0x004d125a Module(sqlservr+d125a) (RecoveryMgr::RecoverDb+35c) 0x004ce9f7 Module(sqlservr+ce9f7) (DBTABLE::Startup+630) 0x004cdfd4 Module(sqlservr+cdfd4) (DBMgr::StartupDB+2c4) 0x006cc585 Module(sqlservr+2cc585) (TaskDbRestart::ProcessTskPkt+14f) 0x004d79e9 Module(sqlservr+d79e9) (GlobalTskMgrProc+117) 0x4109285a Module(ums+285a) (ProcessWorkRequests+ed) 0x41092d28 Module(ums+2d28) (ThreadStartRoutine+139) 0x7800bee4 Module(MSVCRT+bee4) (beginthread+ce) 0x77f04ee8 Module(KERNEL32+4ee8) (lstrcmpiW+be) 2000-01-31 16:13:18.95 spid6 CImageHelper::GetSym Error - The specified module could not be found.
0x00000000 Module(sqlservr+ffc00000) 2000-01-31 16:13:19.01 kernel SQL Server Assertion: File: <page.cpp>, line=2809 Failed Assertion = 'sid >= m_slotCnt || m_slots[-sid].offset ==0'. 2000-01-31 16:13:19.04 spid6 Location: page.cpp:2809 Expression: sid >= m_slotCnt || m_slots[-sid].offset ==0 SPID: 6 Process ID: 220 2000-01-31 16:13:19.07 spid6 Error: 3624, Severity: 20, State: 1. 2000-01-31 16:13:19.07 spid6 Error: 3314, Severity: 21, State: 3 2000-01-31 16:13:19.07 spid6 Error while undoing logged operation in database 'WebPDM'. Error at log record ID (56:11888:81).. 2000-01-31 16:14:50.73 spid6 Error: 3414, Severity: 21, State: 1 2000-01-31 16:14:50.73 spid6 Database 'WebPDM' (database ID 7) could not recover. Contact Technical Support..
Hi I'm new to SQL, and have a big problems that I need help with. I just hired on to as the Network Admin for the company, the company has an NT4.0 box with SQL 6.5 running. The previous administrator has never done a SQL backup of the dbase, so no import or restore can be done. Now the SQL application was corrupted, it would not regconize itself or can be register. After the reinstallation of the SQL 6.5, there are still a folder of data under Mssql. I assumed that this is the dbase but have no idea what the the dbase actually call, if browse through using explore you will see the files name VMData.dat 550 mb in size and VMLog.dat 25 mb in size. If anyone know how I can retrieved this dbase it would be great help, or if you know of a firm that I can contact that would work too. Thank you for your help in advance.
Hi, I created a nonclustered index on sysobjects table. After that, when I create a new object and sysobjects is modified, the database does not work properly. I used DBCC CHECKDB to repair it. It works but as soon as a new object is created, again database get corrupted (CHECKDB reports error). I cannot drop this index with DROP INDEX command. I'm wondered if an index on system table cannot be dropped, why it can be created? If it is allowed to create index, why it corrupts the database? I found this link: http://support.microsoft.com/kb/106122 Although it mentions SP_FixIndex can drop such indexes but there are no description about it. Any help would be greatly appreciated. Leila
I know that power failure can corrupt a sdf file (http://support.microsoft.com/kb/296076/en-us)
Apart from that, is there any other reason can corrupt a SqlCe2.0 sdf database?
Another problem I found in my sdf database is corrupted record. There is a record in the database has all field with "null" value. When I tried to compact this database in "Query Analyzer", it has cursor there forever. I can still open the database and view those record. I tried to delete this record by saying "delete from job where ... is null", that database is fixed!!! I can compact that dataase again.
Note: I have transaction when writing data to database and my application has quite a lot database corruption issue which is nightware now.
Does anybody know what causes this and how to avoid this problem?
Hi- I have questions about mobile database limits. The application is entirely C# / Compact Framework 2.0, using VS2005. Devices we support are all running WM5. We're trying to incorporate what seems to be a large database that the application needs to talk to offline from any network, so it needs to be stored & hosted within the database and hence we started working with the various generations of what is now SQL Server Compact Edition.
In order to profile the responsiveness I worked up a test database with the general collection of fields we are interested in. The main table has 80,000 rows with one primary key and one of the numeric fields indexed. There is a 'nickname' table which contains about 150,000 items with just a string for a nickname and the key that nickname goes with. With all the rows and an alpha index for the nickname field the database is about 42 mb. Which is pushing the amount of storage on the handheld, but of our two models one is fine and one still has about 30 mb free with the DB on the handheld.
My problem seems to be that one kind of device can work with this database and one cannot. The device I was using the prototype the application is a HP iPAC hx2490b. On that device more than once I have corrupted the database, but not yet as a reproducible process.
My other device is a Dell Axim X51. On that device I seem to corrupt the database with very simple actions. Just now I tried a query like (From the Query Analyzer 3.0)
Code Snippet
Select * from ReagentNicknamesTable where alt_names like 'alpha%'
Which should hit 2099 of the 150,000 records. I got about 400 back and then the table was corrupted. On this device I cannot recover the database as there isn't enough disk space for two copies of the DB. So corruption in the field would be a showstopper. But getting corruption from a simple query like this means I really can't use SSCE.
This problem with the dell showed up when I was using the original device sql that came with VS2005. (SQL Server Mobile?) The cabs & dll's were dated from 2005 and had a build number of 3.0.5206.0. I have since updated to the current version of SQL Server Compact Edition and the dlls have build number 3.0.5300.0. The IDE is set to Compact Framwork 2.0, sp1: cgacutil reports CF 2.0.6129.0.
So help! Have I hit some listed or unlisted limits to SSCE on the Compact Framework? thanks.
I am using alwayson on my SQL 2012 databases. I am using ola hallengren scripts for backing up databases. Full & diff db backups work fine, but the log is not getting backed up. The tran log backup job doesn't error out too. Trying to figure out what I may be missing?
I actually am just looking for some supporting documentation on some facets of SQL Server.As far as I have always known, when anyone does a READ from a SQL Server database (SELCT * from <TABLE>), SQL Server does not create a log record...since there's no data or database structure being modified. A colleague is under the impression READ's are logged operations.
I have a SQL Server 2005 Compact Edition database that synchronizes with a SQL Server 2005 Standard database via merge replication.
The problem I have is when I cancel a synchronization, the database is left in such a state that I cannot add any new records. I dug around a bit and have found that in the INFORMATION_SCHEMA.COLUMNS view, AUTOINC_MAX has been incorrectly set to the same value as AUTOINC_MIN for the primary keys, which explains the 'out of range' error I get in my program when running off such a database.
In contrast, a non-corrupted database has AUTOINC_MAX set to AUTOINC_MIN + 999, presumably because of my subscriber identity range of 1000.
If this is the only thing that has gone wrong, all I need to do is programmatically set the AUTOINC_MAX values to AUTOINC_MIN + 999, however I can't find out where to do this (and INFORMATION_SCHEMA.COLUMNS is a view so it can't be updated obviously.)
I have also heard that 'compacting' the database may help, but I don't know how to do this either!
today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
I have 4 servers, 2 each for application (Dev & Prod)
DEV 1 & DEV 2 are standalone serversÂ
Prod 1 & Prod 2 are Windows Clustered Servers.
From one application to other we do Distributed transactions. Dev 1 - Dev 2 or Dev 2 - Dev 1 can start DTC and working fine,but issue comes when Prod 1 - Prod 2 or Prod 2 - Prod 1. I get error message OLE DB provider "SQLNCLI" for linked server "xyz" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "xyz" was unable to begin a distributed transaction.
I have tested Dev 1 - Prod 1, Dev 1 - Prod 2, Dev 2 - Prod 1, Dev 2 - Prod 2 everything is working fine only Production servers are causing issue.
I enabled all settings needed for DTC on Cluster MSDTC service but no luck.
I received an alert from one of my two secondary servers (all servers are running 2012 SP1):
File 'E:SQLMS SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAMyDatabaseName_DateTime.tuf' is not a valid undo file for database 'MyDatabaseName (database ID 8). Verify the file path, and specify the correct file.
The detail in the job step shows this additional information:
*** Error: Could not apply log backup file 'MyDatabaseName_DateTime.trn' to secondary database 'MyDatabaseName'.(Microsoft.SqlServer.Management.LogShipping) ***
*** Error: Table error: Page (0:0). Test (m_headerVersion == HEADER_7_0) failed. Values are 0 and 1.
Table error: Page (0:0). Test ((m_type >= DATA_PAGE && m_type <= UNDOFILE_HEADER_PAGE) || (m_type == UNKNOWN_PAGE && level == BASIC_HEADER)) failed. Values are 0 and 0.
Table error: Page (0:0). Test (m_freeData >= PageHeaderOverhead () && m_freeData <= (UINT)PAGESIZE - m_slotCnt * sizeof (Slot)) failed. Values are 0 and 8192. Starting a few minutes later, the Agent Job named LSRestore_MyServerName_MyDatabaseName fails every time it runs. The generated log backup, copy, and restore jobs run every 15 minutes.
I fixed the immediate problem by running a copy-only full backup on the primary, deleting the database on the secondary and restoring the new backup on the secondary with NORECOVERY. The restore job now succeeds and all seems fine. The secondaries only exists for DR purposes - no one runs reports against them or uses them at all. I had a similar problem last weekend on a different database that is also replicated between the same servers. I've been here for over a year, and these are the first instances of this problem that I've seen. However, I've now seen it twice in a week on the same server.
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.
Hello, I am having a crystal report using datastored in a dataset. When I select one of items in a dropdownlist, it gives me this error message. Note that I used that code before in another web page using a different crystal report and a different dataSet and it worked successfully, but this time it doesn't work....anyone can tell me what causes this error and how to solve it??? NOTE: I am using a vb code behind in my .aspx page
Error in File C:DOCUME~1AM-TK-~1ASPNETLOCALS~1Temp emp_03ca344b-568e-4ea1-bea8-94f3ef92bbcd.rpt: Error in formula <Record_Selection>. '{StProd2.ItemDescription}' The result of selection formula must be a boolean. 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: CrystalDecisions.CrystalReports.Engine.FormulaException: Error in File C:DOCUME~1AM-TK-~1ASPNETLOCALS~1Temp emp_03ca344b-568e-4ea1-bea8-94f3ef92bbcd.rpt: Error in formula <Record_Selection>. '{StProd2.ItemDescription}' The result of selection formula must be a boolean.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[FormulaException: Error in File C:DOCUME~1AM-TK-~1ASPNETLOCALS~1Temp emp_03ca344b-568e-4ea1-bea8-94f3ef92bbcd.rpt: Error in formula <Record_Selection>. '{StProd2.ItemDescription}' The result of selection formula must be a boolean.] .F(String , EngineExceptionErrorID ) .A(Int16 , Int32 ) .@(Int16 ) CrystalDecisions.CrystalReports.Engine.FormatEngine.GetPage(PageRequestContext reqContext) CrystalDecisions.ReportSource.LocalReportSourceBase.GetPage(PageRequestContext pageReqContext) CrystalDecisions.Web.ReportAgent.u(Boolean N) CrystalDecisions.Web.CrystalReportViewer.OnPreRender(EventArgs e) System.Web.UI.Control.PreRenderRecursiveInternal() System.Web.UI.Control.PreRenderRecursiveInternal() System.Web.UI.Control.PreRenderRecursiveInternal() System.Web.UI.Page.ProcessRequestMain()
I have a requirement to delete 1 Million records from a table having 10 Million data and it's being queried on 24/7 basis (don't have a downtime). how can I achieve that?
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.
The error is :
An error has occurred during report processing. Cannot create a connection to data source '<data source name>'.
The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.
MS-Access database is located on a different server.
Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.
