Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Timeouts During Quiet Periods

Posted on 2011-09-28
19
Medium Priority
?
526 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
[X]
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
  • 7
  • 4
  • 4
  • +2
19 Comments
 
LVL 6

Accepted Solution

by:
tangchunfeng earned 700 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 500 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 600 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 200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

596 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