0

I'm getting SQL Timeout Exception when trying to update or insert new rows into database when database size exceeds 10GB while am using SQL Server Enterprise with no limit on database size, the problem disappears when deleting old records, then comes back when data size reaches 10GB again.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Moe Say
  • 51
  • 2
  • 6
  • This is unlikely related to the engine edition. The symptoms suggest large scans by queries so the duration is proportional to the table size. Get the execution plan of a problem query, upload [here](https://www.brentozar.com/pastetheplan/), and add that link to your question if you need help with tuning. – Dan Guzman Dec 29 '19 at 13:13
  • If it really happens exactly at 10 GB, then you probably don't have the "Enterprise" edition, but rather an **Express** edition installed (which has a limit at 10GB database size). Find out by running: `SELECT @@VERSION` against your database - what does it report back?? – marc_s Dec 29 '19 at 13:32
  • @marc_s, I think [a different error](https://stackoverflow.com/questions/3159136/what-happens-when-you-hit-the-sql-server-express-4gb-10gb-limit) is raised if the db size limit is reached. Timeout errors occur on the client rather than the server. – Dan Guzman Dec 29 '19 at 16:31
  • Another possibility is the files are set to autogrow and the queries timeout because the growth takes too long. In that case, you can either enable [instant file initialization](https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization) or expand the file(s) manually so that no autogrow is required. – Dan Guzman Dec 29 '19 at 16:34
  • this is the result from query: SELECT @@VERSION Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor) – Moe Say Dec 30 '19 at 11:11
  • @DanGuzman how can I expand the files manually? – Moe Say Dec 30 '19 at 11:15
  • @MoeSay, use T-SQL [ALTER DATABASE](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options). This is an example from the documentation: `ALTER DATABASE AdventureWorks2012 MODIFY FILE (NAME = test1dat3, SIZE = 200MB);` – Dan Guzman Dec 30 '19 at 11:18

0 Answers0