Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

SQL Timeouts During Quiet Periods

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
NewFourWalls
Asked:
NewFourWalls
  • 7
  • 4
  • 4
  • +2
4 Solutions
 
tangchunfengCommented:
check db log and os events
maybe not the problem of db,
check the network or other batch work during that period
0
 
AnujSQL Server DBACommented:
Check whether any maintenance jobs like index rebuild, DBCC commands or Backups are running on 20:00 - 22:00.
0
 
MrNeticCommented:
Check maintenance plans running at that hour.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
NewFourWallsAuthor Commented:
@ 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
 
AnujSQL Server DBACommented:
Check Job activity monitor on SQL Server Agent and check for the lastruntime column
0
 
MrNeticCommented:
You guys are fast on answering questions :)
0
 
AnujSQL Server DBACommented:
@MrNetic You are also part of this group, so it applies to you too :)
0
 
NewFourWallsAuthor Commented:
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
 
MrNeticCommented:
What is the value of autogrow for this db... ?
Can do that by right button click on management studio, properties.
0
 
NewFourWallsAuthor Commented:
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
 
AnujSQL Server DBACommented:
You should backup your log and truncate your log file this will help you to reduce the log file size.
0
 
NewFourWallsAuthor Commented:
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
 
MrNeticCommented:
DO you really need full recovery model ? If not you should set the DB RECOVERY MODEL = SIMPLE and shrink the log to 1024MB :)
0
 
NewFourWallsAuthor Commented:
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
 
NewFourWallsAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
tangchunfengCommented:
backup transaction log with truncate option
0
 
NewFourWallsAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 7
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now