February 2, 2009 at 11:44 pm
Hi,
I am working in SS-2000 standard and WIN2003 standard and
We are using SCSI disk device and now free space 9GB (But in percentage 8%)
Ram-3GB and my server using 1.58 GB.
My Database Size is 9448 MB,Data file size 7705 MB,Auto growth size is 100MB U.G and this is Web application project.
I got below error message in sql error log
Error_log_error:
2009-02-02 00:00:47.21 backup Log backed up: Database: stdfqads, creation date(time): 2006/06/29(10:52:48), first LSN: 32270:9771:1, last LSN: 32538:27882:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e:\mssql\datafiles\MSSQL\
BACKUP\stdfqads_tlog_200902020000.TRN'}).
2009-02-02 02:17:17.49 spid1 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [e:\mssql\datafiles\MSSQL\data\stdfqads_Data.MDF] in database [stdfqads] (26). The OS file handle is 0x00000664. The offset of the latest long IO is: 0x00000143e70000
2009-02-02 02:22:17.49 spid54 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [e:\mssql\datafiles\MSSQL\data\stdfqads_Data.MDF] in database [stdfqads] (26). The OS file handle is 0x00000664. The offset of the latest long IO is: 0x000001d7d28000
2009-02-02 02:33:46.99 spid54 DBCC CHECKDB (stdfqads) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 3 minutes 46 seconds.
2009-02-02 03:04:04.93 backup Database backed up: Database: stdfqads, creation date(time): 2006/06/29(10:52:48), pages dumped: 865934, first LSN: 32794:11746:1, last LSN: 32794:11783:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK:{'e:\mssql\datafiles\MSSQL\BACKUP\stdfqads_db_200902020300.BAK'}
May I know the Reason why this error coming daily Help Me...
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 4, 2009 at 5:31 am
I would start by checking your disk queue lengths within perfmon, if the values are large then ascertain the current i/o throughput on those disks.
Identify all processes that may be using the disk, don't forget to include any SQL related i/o.
Go through that first.
February 5, 2009 at 12:25 am
Nicholas Cain,
Thanks for ur Replay.Today night also I got following error and additionally auto growth information.
------
2009-02-05 00:00:03.35 backup Log backed up: Database: stdfqads, creation date(time): 2006/06/29(10:52:48), first LSN: 33276:70:1, last LSN: 33276:73:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e:\mssql\datafiles\MSSQL\BACKU
P\stdfqads_tlog_200902050000.TRN'}).
2009-02-05 02:17:36.53 spid1 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [e:\mssql\datafiles\MSSQL\data\stdfqads_Log.LDF] in database [stdfqads] (26). The OS file handle is 0x0000067C.
The offset of the latest long IO is: 0x0000001cddd600
2009-02-05 02:18:26.94 spid55 Autogrow of file 'stdfqads_Log' in database 'stdfqads' took 66500 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
2009-02-05 02:22:37.99 spid2 SQL Server has encountered 9 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [e:\mssql\datafiles\MSSQL\data\stdfqads_Log.LDF] in database [stdfqads] (26). The OS file handle is 0x0000067C.
The offset of the latest long IO is: 0x0000001ce6ce00
2009-02-05 02:33:25.01 spid55 DBCC CHECKDB (stdfqads) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 3 minutes 24 seconds.
2009-02-05 03:04:53.30 backup Database backed up: Database: stdfqads, creation date(time): 2006/06/29(10:52:48), pages dumped: 872372, first LSN: 33518:361:1, last LSN: 33518:398:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'e
:\mssql\datafiles\MSSQL\BACKUP\stdfqads_db_200902050300.BAK'}).
I Checked my performon its all the counters running avg value and Its SCSI Disk.
Performon Values
Ave. Disk sec/Transfer-0.001
Avg. Disk Write Queue Length-0.010
Processor\% Processor Time-15
Disk Writes/sec-000
Disk Reads/sec--0.014
Physical Disk: % Disk time-0.715
Memory\Pages/sec--0.015
Please Give me Further suggestions.
Thanks,
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 5, 2009 at 6:11 am
2009-02-05 02:18:26.94 spid55 Autogrow of file 'stdfqads_Log' in database 'stdfqads' took 66500 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.
This indicates that the log is growing for the database.
What recovery model are you using? And do you have a shrinkdb running at around the same time?
February 5, 2009 at 6:14 am
When Backup is happening to same volume (or drive) and same time if you have enabled autogrow option of 20% then SQL Server tries to increase the log file 2 GB. Since there is a heavy IO this process may take longer time.
This issue is not so major unless it keeps repeating. If it repeats then move the backup to a drive which is in different volume than the data files volume.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 5, 2009 at 6:56 am
Hi,
Thanks Again My Recovery model is full and,Today I manually shrunk my log file but the IO ERROR is coming daily night time before running checkdb.
2009-02-02 02:22:17.49 spid54 SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [e:\mssql\datafiles\MSSQL\data\stdfqads_Data.MDF] in database [stdfqads] (26). The OS file handle is 0x00000664. The offset of the latest long IO is: 0x000001d7d28000
Job Detail
My Job is running Every night following schedule
1.full backup-3AM
2.log backup-12AM
(The size of Db_Backup around 7GB taken same Drive)
3.Check integrity including index -2.30 Am
4.optimization reorganize Pages -2.15 Am
Auto growth size of my DB 10 % both data and log file Database Size is 9448 MB,Data file size 7705 MB,Auto growth size is 10% U.G
Thanks,
Muthu,
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 5, 2009 at 7:13 am
Don't shrink the log file is my first recommendation, if it's going to grow again it really doesn't make much sense.
Backup the log a couple of times a day, it will help prevent it from getting too large.
Change the autogrowth value to a MB value rather than a percentage, as you have it right now you will have exponential growth, and that's not a good thing. Ideally you should be managing the sizes manually, that will help negate issues (particularly if you perform these at times when the server has minimum load).
Is your database in full recovery mode?
Do you really need to defrag every night?
February 6, 2009 at 9:12 am
but the IO ERROR is coming daily night time before running checkdb.
Muthu,
There is a correction, IO error is not occuring before checkDB! It is occuring during BACKUP when there is request for AUTO GROW of File.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
February 8, 2009 at 2:22 am
Hi,
Thanks for ur Replay. Right now I change the backup path Sakthi.
(Don't shrink the log file is my first recommendation)
Nicholas I accept ur recommendation
Instead of setting 10% growth. How much Can i set in terms MB ?
Thanks,
Muthu.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 9, 2009 at 5:36 am
It very much depends on how your data grows. It could be anywhere from 10MB to 1GB. First thing you should do is proactively manage your file growth. Look for when space is getting low, set up alerts and increase the size manually.
February 9, 2009 at 10:45 pm
Nicholas Cain (2/9/2009)
It very much depends on how your data grows. It could be anywhere from 10MB to 1GB. First thing you should do is proactively manage your file growth. Look for when space is getting low, set up alerts and increase the size manually.
Hi,
Thanks Right now i set growth manually but, how to set the alerts.
Thanks,
muthu.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 9, 2009 at 11:55 pm
You can use this command and built some alerts
DBCC SQLPERF(LOGSPACE)
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply