How to fix performance degrades after DETACH -- del log -- ATTACH?

Greetings all,

My database is running on MSSQL 2005 under Windows Server 2003. The MDF 
was 350 MB and the LDF was about 3Gig. So I detach the database, delete 
the log file, and re-attach the database. The LDF is now less than 4 MB.

My application works well after the re-attachment, but the performance 
goes down substantially. The process which usually runs in 5-8 seconds 
seconds, now took almost 20 seconds to finish. I tried to restart the 
SQL Server. Even restart the server itself with no luck. I tried to 
change the Auto growth from 10% to 10Meg - still with the same 
disappointing performance.

Anybody knows what wrong with my database and how to fix it? Oh, and 
btw, the process is quite long query (about 300 lines) with some Select 
with subquery. Obviously I will see where I can optimize the code, but I 
wonder why it worked quite fast previously, and degrades now.

Thanks in advance. :)

Regards,
Willianto
0
Willianto
4/15/2010 2:34:58 AM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
524 Views

Similar Articles

[PageSpeed] 55

> So I detach the database, delete the log file, and re-attach the database

You played Russian roulette - I'm happy there wasn't a bullet in the 
chamber.


> The LDF is now less than 4 MB.

Wow, that is small for a 350MB database. I typically go for somewhere around 
30 - 50% of the data size. If I don't have any real usage stats to lean on, 
of course.


Did you compare the execution plans? Perhaps you now have a different plan 
and this has nothing to do with the new log file... Without looking at the 
plans, we are only guessing.

Or perhaps what you do requires space in the log and the log keeps growing 
and the slowdown is waiting for all those autogrow operations. Nothing beats 
pre-allocating storage! More details here: 
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Willianto" <willianto-remove-me@telkom-and-me.net> wrote in message 
news:usMpAQE3KHA.4028@TK2MSFTNGP05.phx.gbl...
> Greetings all,
>
> My database is running on MSSQL 2005 under Windows Server 2003. The MDF 
> was 350 MB and the LDF was about 3Gig. So I detach the database, delete 
> the log file, and re-attach the database. The LDF is now less than 4 MB.
>
> My application works well after the re-attachment, but the performance 
> goes down substantially. The process which usually runs in 5-8 seconds 
> seconds, now took almost 20 seconds to finish. I tried to restart the SQL 
> Server. Even restart the server itself with no luck. I tried to change the 
> Auto growth from 10% to 10Meg - still with the same disappointing 
> performance.
>
> Anybody knows what wrong with my database and how to fix it? Oh, and btw, 
> the process is quite long query (about 300 lines) with some Select with 
> subquery. Obviously I will see where I can optimize the code, but I wonder 
> why it worked quite fast previously, and degrades now.
>
> Thanks in advance. :)
>
> Regards,
> Willianto 

0
Tibor
4/15/2010 6:54:11 AM
Hello Sir,
First of all, thanks for your respond. :)

>> So I detach the database, delete the log file, and re-attach the database
> 
> You played Russian roulette - I'm happy there wasn't a bullet in the 
> chamber.

Aaah... didn't know that. I must've done something good in my life that 
the bullet is not there... :D

>...
> Or perhaps what you do requires space in the log and the log keeps 
> growing and the slowdown is waiting for all those autogrow operations. 

Well, you probably hit the problem there. See, the database is on the 
head office, and every night 20 sites send their data. Each site would 
send the actual INSERT INTO... command through our FTP server and a 
service runs on our server, download the command, and run it against the 
database. That might explain the grow.

But now, as we already shrink the log file, what should we do to fix the 
performance back to normal?

Looking forward to hearing more from you.

Regards,
Willianto
0
Willianto
4/15/2010 2:47:02 PM
> But now, as we already shrink the log file, what should we do to fix the 
> performance back to normal?

Grow it again (expand manually). It would only help if the small size is the 
problem per se, of course. I.e., if you can some process which does 
modifications and are victim of autogrow operation...

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Willianto" <willianto-remove-me@telkom-and-me.net> wrote in message 
news:#Zg#PpK3KHA.5028@TK2MSFTNGP06.phx.gbl...
> Hello Sir,
> First of all, thanks for your respond. :)
>
>>> So I detach the database, delete the log file, and re-attach the 
>>> database
>>
>> You played Russian roulette - I'm happy there wasn't a bullet in the 
>> chamber.
>
> Aaah... didn't know that. I must've done something good in my life that 
> the bullet is not there... :D
>
>>...
>> Or perhaps what you do requires space in the log and the log keeps 
>> growing and the slowdown is waiting for all those autogrow operations.
>
> Well, you probably hit the problem there. See, the database is on the head 
> office, and every night 20 sites send their data. Each site would send the 
> actual INSERT INTO... command through our FTP server and a service runs on 
> our server, download the command, and run it against the database. That 
> might explain the grow.
>
> But now, as we already shrink the log file, what should we do to fix the 
> performance back to normal?
>
> Looking forward to hearing more from you.
>
> Regards,
> Willianto 

0
Tibor
4/15/2010 7:03:19 PM
@Tibor:

After all the night-transaction run, my apps run very fast. The report 
now complete in 2 secs. the performance is faster than ever.

Btw, the log file is now about 100 meg.

Thank you Sir!

regards,
Willianto


Tibor Karaszi wrote:
>> But now, as we already shrink the log file, what should we do to fix 
>> the performance back to normal?
> 
> Grow it again (expand manually). It would only help if the small size is 
> the problem per se, of course. I.e., if you can some process which does 
> modifications and are victim of autogrow operation...
> 
0
Willianto
4/17/2010 4:16:30 PM
Reply:

Similar Artilces:

Large Attachments
Outlook Constanly trys to resend an email with an attachment that is too big to email server. As in the email is 12m and outgoing limit is 10m then outlook tries to send and fails then tries again and again. Is there any way to fix this?? By being nice to your sys admin and getting the outgoing limit raised temporarily to allow this to go through. "Jeromy Cullen" <jcullen@csiweb.com> wrote in message news:000f01c352bf$6b7e0d00$a301280a@phx.gbl... > Outlook Constanly trys to resend an email with an > attachment that is too big to email server. As in the > email ...

winmail.dat attachments for CRM-tracked emails
Hello, our users are experiencing an issue with CRM-tracked emails from Outlook when the email is sent to Lotus Notes recipients: Email created in Outlook 2003 on Windows XPSP2 with CRM 3.0 desktop or laptop client installed (CRM server running Windows Server 2003SP1, SQL Server 2005SP1) Email has an attachment (usually a PDF file) Email is tracked in CRM using 'Track in CRM' button on new message in Outlook Outlook is using a POP3 mailbox, not Exchange Server Recipient is not using Outlook, usually Lotus Notes Attachment is called winmail.dat and the sender's attachment is gon...

Newbie
I have installed Exchange 2003 (new install) on a new install of Windows Server 2003. When I went to add info for the SMTP server (using Exchange Management System - EMS) I navigated to Servers - Exchange1 - Protocols - SMTP There's no default SMTP server listed in there. How can I get one in there? -Fran- Did you have IIS installed already? <Fran> wrote in message news:vmrpu1h04d06lpot07kbqu78lnsflpqcaa@4ax.com... >I have installed Exchange 2003 (new install) on a new install of > Windows Server 2003. > > When I went to add info for the SMTP server (using Exch...

Saving e-mail including attachments problem
Saving Outlook e-mail including attachments has alwawys worked until recently. Can still save an e-mail but the attachemnts disappear. Please help! ...

Cannot preview of open any email with an attachment
I am unable to open or preview any email in Outlook 2003 that has an attachment. This includes old emails I have previously opened. The program locks up and I must to the Task Manager, End Task procedure to close the program. I have run "detect and repair" with no difference in performance. I have created a new .pst and imported my old .pst with no change. Any help would be most appreciated. ...

Attachments #118
Hello, I am reciving a lot of email with attachments. I would like save the files enclosed automatically in a directory, if the file extensions are .pdf,... Could do it? Thanks for all, SilviaLl. Look here: http://www.slipstick.com/addins/housekeeping.htm Housekeeping and Message Management Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvoa.com -- "When News breaks, we fix it!" KVOA Television, Tucson, AZ. NBC Channel 4 Still Cadillacin' - www.badnewsbluesband.com "Silviall" <slladoARROBAg...

Unable to open PDF file attachments
Summary: - OS: WINDOWS VISTA - EMAIL SERVICE: WINDOWS MAIL - ADOBE READER 9.3.2 - PROBLEM: UNABLE TO OPEN ANY PDF FILE ATTACHMENTS - ATTEMPTS: UNINSTALLED/REINSTALLED ADOBE READER MULTIPLE TIMES : CHECKED FORUMS : pdf. EXTENSION FILES DEFAULT PROGRAM ADOBE READER NEED HELP URGENTLY EMILY Take a look at this.... http://www.winhelponline.com/articles/236/1/Unable-to-open-PDF-file-attachments-directly-from-Windows-Mail-in-Windows-Vista.html -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "...

Entourage won't forward attachments
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: Exchange I have a user that is no longer able to forward attachments. When she clicks Forward on a message that has attachments the message is preserved but the attachments are lost. I know she could use &quot;Forward as Attachment&quot; but then the person on the receiving end of the message has to open the original message to get to the attachment. These are just normal files in the attachments, jpeg's, pdf's ect. Also, the machine is running fully updated 13.0.4 entourage EWS conne...

Downloading of Attachments from a POP3 Server
Is there a way to configure Outlook 2007 to download headers and bodies of emails with attachments but not download the attachments? When needing to use a dial-up connection, having this ability will allow reading emails but not tie up the connection for hours downloading the attachments. Thanks! -- System Administrator Sprotte + Watson Architecture and Planning Vista, CA System Administrator wrote: > Is there a way to configure Outlook 2007 to download headers and > bodies of emails with attachments but not download the attachments? > When needing to use a dial-up c...

Outlook07-message with mp3 attachment won't send/stalls in outbox
)I can recieve mp3 attachments, multiple pictures, files, etc. but when I try to send more than 3 pictures or one mp3 (9.43 to 13.0mb size), outlook stalls and doesn't allow me to send any emails with these attachments. I can send regular files with no problem but not these. It is frustrating, does anyone know how I can fix this in Outlook so it will work? Thanks -- jani jani wrote: > )I can recieve mp3 attachments, multiple pictures, files, etc. but when I try > to send more than 3 pictures or one mp3 (9.43 to 13.0mb size), outlook stalls > and doesn't all...

Slowness opening Outlook attachment
it would take about 2-3 min to open outlook attachment don't matter what the size is. >-----Original Message----- >it would take about 2-3 min to open outlook attachment >don't matter what the size is. >. >has anybody have fix for that? ...

Why hasn't MS fixed this yet.....?
When you create horizontal bar charts, they always come in backwards, so to fix it you have to flip the data. Same with Stacked Collumns. Also, if you have paired bars they come in flipped as well! I've worked with Excel 97 through XP, you think they would have fixed this by now! Sounds like you are looking for a left handed cup! Bernard "jmorris" <anonymous@discussions.microsoft.com> wrote in message news:E9B2A931-D858-4445-8CDC-C3A25071C58B@microsoft.com... > When you create horizontal bar charts, they always come in backwards, so to fix it you have to flip the data. ...

Moving Exchange logs and database
I am attempting to move the log and database files for Exchange 2003. I have followed all the steps listed here: http://support.microsoft.com/?kbid=821915 However, the log files are still being written to the old log folder. Are there any subsequent steps that need to be followed? Is it just a matter of waiting? Thank you for your help, Nick Are you sure you're looking at the right logs? Did you make the change for all storage groups? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" <nicenick@gmail.com> wrote in message news:1158769...

Domain name not always needed to log into OWA?
This is something I've wondered about for a while. Why is it that only some of our new Exchange Server installs insist on the OWA login <up to 13 chars. of domain name>\<user name> while others have no trouble with only <user name>? "Julie" <julie@somewhere.org> wrote: >This is something I've wondered about for a while. > >Why is it that only some of our new Exchange Server installs insist on >the OWA login <up to 13 chars. of domain name>\<user name> while >others have no trouble with only <user name>? Probably b...

can send but can receive- event log shows loop within Exchange
Hi all, When I try to send email to my exchange 2003 server from an external email account I never get the email. I get the following error message in my event log. ------------------------ A non-delivery report with a status code of 5.3.5 was generated for recipient rfc822;adrown@[192.168.2.249] (Message-ID <200411090532.iA95WemV015823@gatekeeper.com>). Causes: A looping condition was detected. (The server is configured to route mail back to itself). If you have multiple SMTP Virtual Servers configured on your Exchange server, make sure they are defined by a unique incoming port and t...

Fixed Assets Import (6 months ago)
Hello: It really is a moot point. The situation is over, my clients are happy, and I am able to import fixed assets just fine. But, six months ago and right before service pack 1 for GP 9.0 was released, for the first time ever I was unable to import fixed assets. The FA Import Tool would show--on the "surface"--that all of my assets imported. But, the fact of the matter was that the assets did NOT import. And, there were no error messages givens to why! I had opened up a case with Microsoft, at that time. They admitted that they could not solve the mystery. And, they s...

Promted for opening of attachment form Outlook
I have tasks linked to notes in One Note, whenever I click the attachment to open the notebook, I get a "Opening Mail Attachment" you should only open attachments from trustworthy.......... and the " always ask before opening" box is checked and grayed out so I cant change it. Although not the biggest problem in the world, it is an annoyance. Any thoughts on how to eliminate this? -------- Original-Nachricht -------- > I have tasks linked to notes in One Note, whenever I click the attachment to > open the notebook, I get a "Opening Mail...

print attachement
Hello, I would like Outlook to check for a specific attachement in incoming email (for example all attached word documents in format "ras*.doc") and to print the attached document automatically. Is is possible using rules ? do I need a script ? any idea how to do it ? Thank you ...

attachments #45
I cannot open Word or excell attachments from my email. I can open jpeg attachments. Any ideas? I am on outlook 2000 Buddy "buddy@busylad.com" <anonymous@discussions.microsoft.com> wrote in message news:051d01c3d63d$bf42c8c0$a301280a@phx.gbl... > I cannot open Word or excell attachments from my email. I > can open jpeg attachments. Any ideas? I am on outlook 2000 > Buddy You do have Word and Excel installed, don't you? ...

Removing attachment from reply
I want to reply to a message with an attachment without sending the attachment back, simply and easily "crownnut" <crownnut@discussions.microsoft.com> wrote in message news:B8F6825E-69E1-410E-846F-4D8139AAB456@microsoft.com... > I want to reply to a message with an attachment without sending the > attachment back, simply and easily Reply shouldn't keep the attachment by default. Replies do not include the attachment. Forwards include attachments. -- Ron Sommer MS MVP-Mail "crownnut" <crownnut@discussions.microsoft.com> wrote i...

What does the Exchange 2003 DST patch fix in Exchange?
Does Exchange not look at the OS and hardware clock? http://support.microsoft.com/?id=926666 Why is the update required for Exchange? Microsoft calendar software such as Outlook depends on the operating system time zone information to display time information. However, Collaboration Data Objects (CDO)-based programs such as Outlook Web Access depend on separate internal time zone tables. This update modifies these internal time zone tables to match the changes in time zone settings. Without this update, calendar items in CDO-based programs and in Outlook Web Access will operate as i...

Printing Attachments
I print my emails to PDF at times, I would like the file attachment information to print also(just the file names so I know what was sent) seems like it used to do this but not anymore. Change in settings? Sorry wrong group - this was for outlook 2003 "LarryH" <larry@lkfire.com> wrote in message news:BeudnUnGaKYdS5jUnZ2dnUVZ_hmdnZ2d@shawneelink.net... >I print my emails to PDF at times, I would like the file attachment >information to print also(just the file names so I know what was sent) >seems like it used to do this but not anymore. Change in settings? &...

Timing-Out Error Message for 1 MB Attachment
I'm getting a Timing-Out Error Message when I send an attachment that's about 1.0 MB. It started after some (Microsoft, I think) updates and it seems like it should be easy to change some criteria in Outlook, but haven't found it yet. I can still send the attachment thru BellSouth/DSL mailbox, so why won't Outlook handle it? Any help would be appreciated. ...

Fixed Asset Change Years
Hi, We have recently changed our Financial Periods so that for 2009 it will run from July 08 - Dec 09. It was previously set up as July 08 - June 09. The year 2010 and subsequent years will be from Jan - Dec. We have rebuilt our Fixed Asset calendar and have an issue regarding depreciation. Having run projections up until December 2009 it depreciates each month correctly up until December where it backs out the depreciation for months July - November. Does anyone know why it would do that? Thanks, Andy ...

This Operation Cannot Be Performed
Hello: This Operation Cannot Be Performed. This is the constant message that I am getting from Microsoft. I turn on Money and I get that message after I sign on. I get it whenever I come to the front page. I get it whenever I try to select a transaction that I have already put into Money. What is extremely wierd is that I am able to use it to some extent. I can download statements and put them into Money but for every transaction I accept there is a error message. Now, it restarts right after I end Money and wants me to send a error report. Which, if I do, then it freezes my co...