December 5, 2006 at 10:58 am
Hi,
We have a 50GB MS SQL Server database running on an Intel Based Machine running Win2K Server OS.
After every 3000 or so inserts each having 18MB of data SQL Server just stops VB application running on clients give a `Run-Time Error Timeout Expired' error and does nothing.
Whenever I increase manually `Space Allocated' column on Data Files tab of Database properties application starts inserting.
Why do I have to increase space as `Automatically Grow File' box is checked? There's also 130GB free space on volume on which database resides.
I ran
dbcc opentran ('database')
Transaction information for database
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : 1
Name : DML
LSN : (170917:434:1)
Start time : Nov 21 2006 3:29:56:750PM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
and got this error
Can any one Help?
December 5, 2006 at 11:34 am
Are you properly closing your connections. If not then you may have too many open connections on the server and this will cause issues. Check sp_who and see how many connections you have open.
December 5, 2006 at 1:16 pm
You can also run SP_who2 'active' to see what active connections are doing.
How is the VB application connecting to SQL Server? The are many timeouts some sql server some other sources.
December 5, 2006 at 3:11 pm
Make sure you have atleast 3-5 GB free space and make UNRESTRICTED FILEGROWTH is enbaled.
Make sure file growth is 5% or 1000/2000 MB...
And also check if there is any blocking goin on...
MohammedU
Microsoft SQL Server MVP
December 6, 2006 at 1:34 pm
The process tops running because SQL Server is trying to autogrow the database files. Doing this on a 50 GB database takes a while and the VB app is timing out while waiting ..... which rolls everything back.
December 7, 2006 at 7:41 am
I was thinking on the same lines too because ‘Automatically grow file’ box is checked and ‘By percent’ list has 9%. That means whenever SQL server wants to grow mdf file it’ll use (size of Database)*9/100 to get an increment which comes out to be 4600 MBs (approx.)
If I put 100 in ‘In megabytes’ box for ‘File growth’ would it help?
Can dbcc updateusage ('database') be helpful as well?
December 7, 2006 at 7:53 am
VB application is connecting via TCP/IP using a 'USER DSN.'
December 7, 2006 at 8:57 am
UpdateUsage won't help with his.
Making your growth rate small will speed up expansion, but it will cause it to occur more frequently which will hurt performance.
December 7, 2006 at 9:16 am
Thanks.....
December 7, 2006 at 9:20 pm
Probably adjusting your database data files size would be of help:
ALTER DATABASE [dbName] ON PRIMARY
( FILENAME = N'...location\dbName.mdf', SIZE = (...) KB)
LOG ON
( FILENAME = N'...location\dbName.ldf', SIZE = (...) KB)
By default, the ldf file is 25% in size from the size of your mdf. If your app has many inserts/updates/deletes then you should think to increaze that up to 80%, depending on the db activity.
It's good to have "auto growth" enabled but take care of the disk size.
Also it would be a good idea to create a second log file on some other drive (check BOL)
December 7, 2006 at 11:44 pm
That's not the correct syntax for changing the file sizes of the database.
Alter Database [dbName] Modify File (
Name = [LogicalDataFileName],
Size = [NewSize][KB|MB|GB|TB])
Alter Database [dbName] Modify File (
Name = [LogicalLogFileName],
Size = [NewSize][KB|MB|GB|TB])
25% of the data file(s) size is only the default at creation time, and only when an alternate file size wasn't specified by the Create statement.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply