Solved

SQL Timeouts During Quiet Periods

Posted on 2011-09-28
19
497 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now