Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Timeouts During Quiet Periods

Posted on 2011-09-28
19
507 Views
Last Modified: 2012-05-12
Hello Experts,

I have an MS SQL 2008 database running a few web based systems. No major usage. maybe 20 users at any one time. During peak hours there doesnt seem to be a problem, but around 20:00 and 22:00 each evening, my scripts that try to update the SQL database timeout. Select queries run fine.

Does anyone know what might be up or where i can look?

Is someone trying to hack it? How do i check?

Thanks,


D.
0
Comment
Question by:NewFourWalls
  • 7
  • 4
  • 4
  • +2
19 Comments
 
LVL 6

Accepted Solution

by:
tangchunfeng earned 175 total points
ID: 36755602
check db log and os events
maybe not the problem of db,
check the network or other batch work during that period
0
 
LVL 15

Expert Comment

by:Anuj
ID: 36813001
Check whether any maintenance jobs like index rebuild, DBCC commands or Backups are running on 20:00 - 22:00.
0
 
LVL 5

Expert Comment

by:MrNetic
ID: 36813601
Check maintenance plans running at that hour.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:NewFourWalls
ID: 36813705
@ tangchunfeng - How do i check these logs? I have little experience with MS SQL.

@ anujnb - There is an hourly backup using SQLBackupAndFTP software, which runs 08:00 - 19:00, as far as i am aware there is no other backups running.

@ MrNetic - how do i check the maintenance plans?
0
 
LVL 15

Assisted Solution

by:Anuj
Anuj earned 125 total points
ID: 36813754
Check Job activity monitor on SQL Server Agent and check for the lastruntime column
0
 
LVL 5

Expert Comment

by:MrNetic
ID: 36813776
You guys are fast on answering questions :)
0
 
LVL 15

Expert Comment

by:Anuj
ID: 36814417
@MrNetic You are also part of this group, so it applies to you too :)
0
 

Author Comment

by:NewFourWalls
ID: 36814711
Hi, so I am now viewing the log and around that time an AutoGrow seems to be occurring.

09/28/2011 22:20:06,spid11s,Unknown,Autogrow of file 'MultiDrop-Assist_log' in database 'MultiDrop-Assist' took 182069 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

09/28/2011 22:17:04,spid62,Unknown,Autogrow of file 'MultiDrop-Assist_log' in database 'MultiDrop-Assist' was cancelled by user or timed out after 30389 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

09/28/2011 20:19:48,spid11s,Unknown,Autogrow of file 'MultiDrop-Assist_log' in database 'MultiDrop-Assist' took 214439 milliseconds.  Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

I am really not experienced with MS SQL, please could you advise how to stop this happening. Should i increase the database size? If so how do i do it? It looks like this is occurring every day, is this normal?

Any help would be much appreciated.

Thanks,


D.
0
 
LVL 5

Expert Comment

by:MrNetic
ID: 36814841
What is the value of autogrow for this db... ?
Can do that by right button click on management studio, properties.
0
 

Author Comment

by:NewFourWalls
ID: 36814985
I think by looking at that, they database is currently 313 with 1MB growth.

I just noticed that the size of the Log for this database is 70GIG!!! Is that normal??

Screenshot
0
 
LVL 15

Expert Comment

by:Anuj
ID: 36815450
You should backup your log and truncate your log file this will help you to reduce the log file size.
0
 

Author Comment

by:NewFourWalls
ID: 36815874
I am using SQL 2008, i can't seem to find a Trancate option for the log. I have tried re-sizing the file to 200MB but it just did an auto file grow again according to the log.

It is located on a fairly large hard drive so space is not a problem right now but it will be in another 12 months based on the growth :(

How can i resize this file to stop the timeouts occurring.
0
 
LVL 5

Assisted Solution

by:MrNetic
MrNetic earned 150 total points
ID: 36816454
DO you really need full recovery model ? If not you should set the DB RECOVERY MODEL = SIMPLE and shrink the log to 1024MB :)
0
 

Author Comment

by:NewFourWalls
ID: 36816467
No, I do not need the full recovery mode. The database name is; MultiDrop-Assist, what SQL do i need to run to set it to Simple?
0
 

Author Comment

by:NewFourWalls
ID: 36816498
And once I have set this to simple I then;

Right Click DB >> Tasks >> Shring >> Files

Then use the settings on the screenshot?

Shrink Settings
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 36821755
>>what SQL do i need to run to set it to Simple? <<
ALTER DATABASE [MultiDrop-Assist] SET RECOVERY SIMPLE

To shrink this one time do the following:
CHECKPOINT
CHECKPOINT
CHECKPOINT
DBCC SHRINKFILE('YourTransactionLogFileGoesHere', 1000)
-- Repeat SHRINKFILE if you have more than one Transaction Log file.

If you don't know the name your Transaction Log(s) do this:
SELECT name
FROM sys.master_files mf
WHERE DB_NAME(database_id) = 'MultiDrop-Assist'
      AND type = 1
0
 
LVL 6

Expert Comment

by:tangchunfeng
ID: 36833612
backup transaction log with truncate option
0
 

Author Closing Comment

by:NewFourWalls
ID: 36890397
Thanks everyone. Best experience on EE to date! Great help!

I hope I have shared the points fairly. Wish i could of given you all more.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36895080
>>backup transaction log with truncate option<<
Perhaps you did not notice that the author is using SQL Server 2008:
I am using SQL 2008, i can't seem to find a Trancate option for the log.
SQL Server 2008 (thankfully) no longer supports BACKUP LOG ... WITH TRUNCATE_ONLY.  This is all covered in the documentation for BACKUP in SQL Server BOL:
BACKUP (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186865(v=SQL.100).aspx
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question