Need to Attach the database with out LOG FILE.BUT IT FAILS...

Question

Dear Friends,

I have sql server 2008 r2 standard edition running on the windows server 2008.

we have a software whose backend is SQL SERVER and front end is VB. we are using the software since 10 years and the database size is 450 MB and the LOG FILE size is 2.5 GB. The Application is working faster on the server. But if we connect the application from the client it take minutes to pull the data or save the data(approx 4 min for each transaction )...

The Lan Speed has been tested and its directly connected to the server with wired network and the speed of the switch is 1GB. 

And also i am trying to attach the database without a log file but it gives the following error...

COMMAND :

Use master
go
create database Analyst_Mamzar
On Primary (FileName = N'D:\TEST DATA FILE\Analyst_Mamzar.mdf')
for Attach_Rebuild_LOG
GO

OUTPUT:

File activation failure. The physical file name "E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Analyst_Mamzar.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Analyst_Mamzar'. CREATE DATABASE is aborted.

---------------------------------

I have removed the read only from the database and given full control permission for the user.

Please let me know the procedure to speedup the access of application in client PC and Procedure to attach the database without a log file or procedure to clear the log file.........

Regards,

Mohammed Irshad.

Answer 1

1. Have you tried to attach your database instead of creating it?

use master

go

exec sp_attach_db 'Analyst_Mamzar','D:\TEST DATA FILE\Analyst_Mamzar.mdf','D:\TEST DATA FILE\Analyst_Mamzar.ldf'

2. For the application speed... most problems come from one or several of these categories :

Client hardware 

Server hardware

Connectivity

Application design

You'll to walk through all of them to pinpoint the slowness cause(s).

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Answer 2

"And also i am trying to attach the database without a log file but it gives the following error..."

Why ? you should do that only if the log file is damaged and only if the database is cleanly shut off

APP slow ?

- How much RAM does the server have ? 415 MB database is so tiny; it can be all cached under an ordinary PC

- Something doesn't look normal with that tiny database size ; this could be a blocking issue so you should check for blocking while running the app. - How many clients connect to that database same time ?

you can use this code to check for blocking

with Blocking as

(

SELECT [Spid] = session_Id
, blocked ,waittime
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.

)


select * from blocking where blocked <> 0

union all

select * from blocking where spid in (select blocked from blocking where blocked <> 0)

- Bad app code may cause slow processing of records ; this used to be common of OLD VB apps using ado cursors. You should profile the APP performance and identify the slowest operations.

- Do you perform any database maintenance ?

---

Please click Vote As Helpful , Propose As Answer and/or Mark As Answer if you think this post helps!

Answer 3

Attaching the database without a log file will not affect the performance, and it is also not the right way to reduce the size of the log file. Read this about handling large transaction log files: http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

As for performance: This is a separate issue from the large transaction log file. This has to be treated as any perfromance tuning work you do. You say the app is slower when executed on a client machine with a network to the SQL Server compared to when executed on the same machine as the SQL Server. That show that the problem is not in the database engine and you should talk to the app vendor/developers why their application is slow when connecting over a network. It is probably a badly designed application whcih sends lots of data back and forth between the client and the server.

---

Tibor Karaszi, SQL Server MVP | web | blog