Solved

SQL Timeouts During Quiet Periods

Posted on 2011-09-28
19
502 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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