Hello,
This morning I got issue when my customer change “Posting Date” in Navision. The error said like below.
The following SQL Server error(s) occured while accessing the User Setup table:
845,”42000″,[Microsoft[[ODBC SQL Server Driver][SQL Server]Time-out occured while waiting for buffer latch type 4 for page (1:9), database ID 7.
SQL:
UPDATE “<database name>”.”dbo”.”<company name>$User Setup” WITH REPEATABLEREAD SET “Allow Posting To”={ts ‘2018-02-27 00:00:00.000′} WHERE {“User ID”='<user id>’) AND (“timestamp”<=0x0000000012B3355F)
After check free space of database file, our suspect is there is Auto Grow process in SQL Server because the free space is not enough anymore. After Auto Grow Process has been finished, the user can change “Posting Date” in Navision.
To prevent Auto Grow in our SQL Server Database, we recommend to check free space of database file periodically. When free space is less than 1000 MB, we suggest to increase the database file size.
You can use query below to check your database file size and its free space.
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
You can use query below to increase your database file size.
ALTER DATABASE [<your database name>]
MODIFY FILE
(NAME = <logical name of your database file or name in query above>, SIZE = <neededsize>MB)
Example 1 for increase mdf file:
ALTER DATABASE [<your database name>]
MODIFY FILE
(NAME = mydb_Data, SIZE = 50MB)
Example 2 for increase ndf file:
ALTER DATABASE [<your database name>]
MODIFY FILE
(NAME = mydb_1_Data, SIZE = 50000MB)
Example 3 for increase ldf file:
ALTER DATABASE [<your database name>]
MODIFY FILE
(NAME = mydb_Log, SIZE = 100000MB)
Hope this information is useful for you too.
God bless 🙂
3,144 total views, 2 views today
Recent Comments