Hi there.
We're using SQL Server 2000 on a Windows 2000 box with dual
hyperthreaded Intel CPUs, raided hard disks and 2GB RAM. It should
certainly be able to handle our currently fairly modest demands. We
have all the latest security patches. Parallelism is disabled.
We're currently experiencing unacceptable slowness for inserts to a
particular table, Account_Transaction (see the DDL at the end of this
message). As you can see, the main features are a BIGINT auto-increment
primary key, and three foreign keys. This table currently has 7 million
rows and another million are inserted each month. We are currently
unhappy with the length of time it takes to insert into this table as
this operation is at the heart of our application.
Here is an example insert, called from within a stored procedure:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(
@.invoice_id,
@.transaction_type_id,
@.qty,
@.amount,
@.amount_commission,
@.tax_percentage,
@.currency_code_home,
@.points,
@.line_id,
@.comment,
@.balance,
@.external_id
)
Looking at this query's SP:StmtCompleted event from the profiler we can
see the query took 375ms to complete and performed 76 reads (and
apparently zero writes, although the data was definitely inserted). We
think this is far too long.
What is strange though is that if we run the exact same statement
OUTSIDE of the calling procedure in query analyzer, substituting
literals for the variables, then it apparently completes in just 63ms,
although it performs the same number of writes:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
If we now try the same two things on our test database, running on a a
bog-standard desktop machine, with only 300000 rows in the
Account_Transaction table, then both queries execute in similar times of
around 70ms.
So my question is - what is causing the slowness, and how can we speed
it up? How do we make the insert operation from within the SP take the
same amount of time as it takes when run on its own?
Any help appreciated,
Mike C
CREATE TABLE [dbo].[Account_Transaction] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[invoice_id] [bigint] NOT NULL ,
[transaction_type_id] [int] NOT NULL ,
[qty] [int] NOT NULL ,
[amount] [money] NOT NULL ,
[tax_percentage] [decimal](9, 3) NULL ,
[commission] [float] NOT NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_id] [int] NOT NULL ,
[points] [int] NOT NULL ,
[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[balance_before] [money] NOT NULL ,
[balance_after] [money] NULL ,
[external_id] [bigint] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
[msrepl_tran_version]
GO
CREATE INDEX [IX_Account_Transaction] ON
[dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] ADD
CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[Account_Invoice] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
(
[line_id]
) REFERENCES [dbo].[Line] (
[id]
),
CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
(
[transaction_type_id]
) REFERENCES [dbo].[Transaction_Type] (
[id]
)
GOMike
1) Make sure that your table has no triggers
2) Try drop the indexes just prior to INSERTING and re-create them after
Another thing might help that if you do it by using SP which accepted
parameters try to re-assign these parameters to local variable
CREATE PROC myProc
@.par INT
AS
DECLARE @.lpar INT
SET @.lpar =@.par
INSERT INTO Table VALUES (@.lpar )
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GO|||It sounds like Mike is speaking of single transactional inserts and not bulk
inserts, so dropping / recreating indexes on the table would not be
practical.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Unqxm%23HGHA.528@.TK2MSFTNGP12.phx.gbl...
> Mike
> 1) Make sure that your table has no triggers
> 2) Try drop the indexes just prior to INSERTING and re-create them after
>
> Another thing might help that if you do it by using SP which accepted
> parameters try to re-assign these parameters to local variable
>
> CREATE PROC myProc
> @.par INT
> AS
> DECLARE @.lpar INT
>
> SET @.lpar =@.par
> INSERT INTO Table VALUES (@.lpar )
>
>
> "Mike Chamberlain" <none@.hotmail.com> wrote in message
> news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||Consider if the stored procedure is re-compiling each time it is executed.
This could explain the overhead when executing the insert from the procedure
while it still performs the same number of writes as the test insert from
Query Analyzer.
INF: Conditions for Stored Procedure Recompilation
http://support.microsoft.com/defaul...kb;en-us;104445
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/defaul...kb;en-us;308737
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GOsql
Showing posts with label disks. Show all posts
Showing posts with label disks. Show all posts
Wednesday, March 28, 2012
Saturday, February 25, 2012
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF&quo
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF
Hi,
I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.
We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.
All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476
BUTTONS:
OK
I tried the following steps but it always failed:
- create a new db with the same name of the lost db;
- put the db in emergency mode;
- stop sql service and replace the mdf file;
- start sql service;
- Run Dbcc checkdb('mydb')
we got the error below:
Msg 945, Level 14, State 2, Line 1
Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Any HELP please ?
Thanks,
Tarek Ghazali
Sql Server MVP
Well, the obvious question is where is your most recent backup of the database?
This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.
You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.
You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.
Seriously restore from backup is your best option at this point.
|||Hi,
The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.
This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):
The log cannot be rebuilt because the database was not cleanly shut down.
Thanks for your reply,
Tarek Ghazali
SQL Server MVP
|||Try this undocumented stuff provided by Kevin [MS].==========
1. Back up the .mdf/.ndf files at first!!!
2. Change the database context to Master and allow updates to system tables:
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go
3. Set the database in Emergency (bypass recovery) mode:
select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran
4. Stop and restart SQL server.
5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:
DBCC rebuild_log('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).
6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:
sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go
7. Turn off the updates to system tables by using:
sp_configure 'allow updates', 0
reconfigure with override
Go
============|||
Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).
Tarek - you should call Product Support to help with recovering this.
Thanks
Subscribe to:
Posts (Atom)