overload in sql a server Processs is taking much t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

overload in sql a server Processs is taking much t

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-18 : 16:33:10

Good afternoon, i need ypor help pls, is there a proces taking much time, in average takes 5 or 6 horuss, now is taking 14 horas, there no more processes running, and its taking a massive delete for about 40 millons of rows

We escalate to our DBA but he says its is working and we should cancel it, but is an overahead of this process

What i saw in Sqwl servers log is frequently is yielding error like:Autogrow of file 'dm1_elite_Log23' in database 'dm1_elite' took 69700 milliseconds. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

Aditionally i found alarm WARNING: EC ba906538 waited 300 seconds on Latch 2d2...

To gather more info there ia processes installed by the DBA thar shrinks logs every 10 minutes, dont know it has something to do with degradation

Thanks for your help in Advanced

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 16:54:22

Looks like you've got an inexperienced DBA. The shrink job needs to be DELETED. Autogrowing the files is a huge performance hit, and you are stuck in an endless loop of the DBA shrinking and your process expanding it. What is the autogrowth set to? And how big is the file?

But you do need to take a step back and redesign your delete. Do the delete in batches instead. This is an old article I wrote on the subject: http://weblogs.sqlteam.com/tarad/archive/2003/10/15/305.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-18 : 20:58:30

Thanks a lot , so you meant the shrink job that compacts logs should never longer be used while Production is Running or sqlserver processes that involves massive deletion or insertion ?

to answers your questions executing sp_helpdb dm1_elite most of the logs asre set to 0 KB, some are 2097152 KB, 52secondly4288 KB, here is how is set for each datafile, Id appreciate your help
in advanced:
name db_size owne dbid created status compatibility_level
dm1_elite_Log11 136 Y:\MSSQL\LOG\dm1_elite_Log11.ldf NULL 131016 KB Unlimited 0 KB log only
dm1_elite_Log13 137 Y:\MSSQL\LOG\dm1_elite_Log13.ldf NULL 131016 KB Unlimited 0 KB log only
dm1_elite_Log14 138 Y:\MSSQL\LOG\dm1_elite_Log14_Log.LDF NULL 28562560 KB 28672000 KB 524288 KB log only
dm1_Elite_primary10 139 A:\MSSQL\Data\dm1_Elite_primary10_Data.NDF PRIMARY 31268800 KB 51200000 KB 262144 KB data only
dm1_elite_Log15 140 Q:\MSSQL\log\dm1_elite_Log15.ldf NULL 131016 KB Unlimited 0 KB log only
dm1_elite_Log16 141 Q:\MSSQL\log\dm1_elite_Log16.ldf NULL 131016 KB Unlimited 0 KB log only
dm1_elite_Data4_2 142 Y:\MSSQL\data\dm1_elite_Data4_2.ndf DATA4 8241152 KB Unlimited 0 KB data only
dm1_elite_Data5_2 143 Y:\MSSQL\data\dm1_elite_Data5_2.ndf DATA5 6348800 KB Unlimited 0 KB data only
dm1_elite_Normales_1 144 Y:\MSSQL\data\dm1_elite_Normales_1.ndf DatosNormales 14409728 KB Unlimited 0 KB data only
dm1_elite_Data_p2 145 Y:\MSSQL\data\dm1_elite_Data_p2.ndf PRIMARY 4587520 KB Unlimited 0 KB data only
dm1_elite_data_p_1 146 Q:\MSSQL\Data\dm1_elite_data_p_1.ndf PRIMARY 35137344 KB Unlimited 131072 KB data only
dm1_elite_data_p_2 147 Y:\MSSQL\data\dm1_elite_data_p_2.ndf PRIMARY 12582912 KB Unlimited 0 KB data only
Dm1_DatosNormalesDos_6 148 Y:\MSSQL\data\Dm1_DatosNormalesDos_6.ndf DatosNormalesDos 2938880 KB Unlimited 0 KB data only
Dm1_DatosNormalesDos_7 149 Z:\MSSQL\data\Dm1_DatosNormalesDos_7.ndf DatosNormalesDos 17631232 KB Unlimited 0 KB data only
dm1_elite1_121 150 Q:\MSSQL\Data\dm1_elite1_121.ndf DATA5 22897664 KB Unlimited 0 KB data only
dm1_elite1_122 151 Q:\MSSQL\Data\dm1_elite1_122.ndf DATA5 17956864 KB Unlimited 0 KB data only
dm1_elite1_123 152 Q:\MSSQL\Data\dm1_elite1_123.ndf DATA5 24772608 KB Unlimited 0 KB data only
Dm1_DatosNormalesDos_8 153 Q:\MSSQL\Data\Dm1_DatosNormalesDos_8.ndf DatosNormalesDos 11010048 KB Unlimited 0 KB data only
Dm1_DatosNormalesDos_80 154 Q:\MSSQL\Data\Dm1_DatosNormalesDos_80.ndf DatosNormalesDos 11403264 KB Unlimited 0 KB data only
dm1_elite_indices300 155 Q:\MSSQL\Data\dm1_elite_indices300.ndf INDICES 47898624 KB Unlimited 0 KB data only
dm1_elite1_124 156 Z:\MSSQL\data\dm1_elite1_124.ndf DATA5 18448384 KB Unlimited 0 KB data only
dm1_elite60_data5_1 157 A:\MSSQL\Data\dm1_elite60_data5_1.ndf DATA5 20447232 KB Unlimited 262144 KB data only
dm1_indices_data41 158 A:\MSSQL\Data\dm1_indices_data41.ndf INDICES 58020352 KB Unlimited 262144 KB data only
dm1_elite_Log17 159 D:\MSSQL\log\dm1_elite_Log17.ldf NULL 31253312 KB 31365120 KB 1048576 KB log only
dm1_elite_Log18 160 Q:\MSSQL\log\dm1_elite_Log18.ldf NULL 655304 KB Unlimited 0 KB log only
dm1_elite_Log19 161 U:\MSSQL\dm1_elite_Log18.ldf NULL 4846592 KB 30720000 KB 524288 KB log only
dm1_elite_data400 162 V:\SQLData\dm1_elite_data400.ndf PRIMARY 61950976 KB Unlimited 0 KB data only
dm1_elite_Log20 163 V:\SQLLogs\dm1_elite_Log20.ldf NULL 655304 KB Unlimited 0 KB log only
DatosNormales_122 164 V:\SQLData\DatosNormales_122.ndf DatosNormalesDos 62537728 KB Unlimited 65536 KB data only
Data5_200 165 V:\SQLData\Data5_200.ndf DATA5 286720000 KB Unlimited 65536 KB data only
Indices_200 166 V:\SQLData\Indices_200.ndf INDICES 147390464 KB Unlimited 65536 KB data only
dm1_elite_data401 167 V:\SQLData\dm1_elite_data401.ndf PRIMARY 163692544 KB Unlimited 131072 KB data only
Data2_file200 168 V:\SQLData\Data2_file200.ndf DATA2 77815808 KB Unlimited 65536 KB data only
DM1_HISTORICO5 169 V:\SQLData\DM1_HISTORICO5.ndf HISTORICO 2048000 KB Unlimited 0 KB data only
dm1_elite_Log21 170 V:\SQLLogs\dm1_elite_Log21.ldf NULL 1704128 KB Unlimited 524288 KB log only
dm1_elite_data4_1 171 V:\SQLData\dm1_elite_data4_1.ndf DATA4 15360000 KB Unlimited 65536 KB data only
dm1_Datos_Normales_2 172 V:\SQLLogs\dm1_Datos_Normales_2.ndf DatosNormales 51200000 KB Unlimited 131072 KB data only
Dm1_DatosNormalesDos_9 173 V:\SQLData\Dm1_DatosNormalesDos_9.ndf DatosNormalesDos 70964800 KB Unlimited 102400 KB data only
dm1_Historico6 174 V:\SQLData\dm1_Historico6.ndf HISTORICO 20480000 KB Unlimited 65536 KB data only
Datos_Normales_U1 175 U:\Datos_Normales_U1.ndf DatosNormales 20480000 KB Unlimited 102400 KB data only
dm1_elite_data1_00 176 V:\SQLData\dm1_elite_data1_00.ndf DATA1 20480000 KB Unlimited 102400 KB data only
dm1_elite_Log22 177 D:\MSSQL\log\dm1_elite_Log22.ldf NULL 20480000 KB Unlimited 1048576 KB log only
dm1_elite_Log23 178 D:\SQLServer\DM1_ELITE\LOGS\dm1_elite_Log23.ldf NULL 20480000 KB Unlimited 1048576 KB log only
dm1_elite_ind1 179 A:\MSSQL\Data\dm1_elite_ind1.ndf DATA1 25600000 KB Unlimited 10240 KB data only
dm1_elite_ind2 180 A:\MSSQL\Data\dm1_elite_ind2.ndf DATA2 1024 KB Unlimited 10240 KB data only
dm1_elite_ind3 181 A:\MSSQL\Data\dm1_elite_ind3.ndf DATA3 1024 KB Unlimited 10240 KB data only
dm1_elite_ind4 182 A:\MSSQL\Data\dm1_elite_ind4.ndf DATA4 10240000 KB Unlimited 10240 KB data only
dm1_elite_ind6 183 A:\MSSQL\Data\dm1_elite_ind6.ndf DATA6 1024 KB Unlimited 10240 KB data only
Dm1_Detalle_4 184 A:\MSSQL\Data\Dm1_Detalle_4.ndf DETALLE 36454400 KB Unlimited 10% data only

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-19 : 12:17:11

Shrinking should never be done as a scheduled event. Shrinking should only ever be done manually or only in very specific circumstances, such as a massive delete has occurred and you won't need that space for quite some time.

I can't read your output. I just need to know how big dm1_elite_Log23 is and what the autogrowth setting is set to for that specific file.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-19 : 20:58:15

OK Thanks alot , the autogrowth setting for dm1_elite_Log23 is set to 1048576 KB (growth column uisng sp_helbdb), maxsize Unlimited, size 20480000 KB

For dm1_elite_Log22' in database 'dm1_elite' took 60620 milliseco
the autogrowth setting for dm1_elite_Log22 is set to 1048576 KB too

Thanks gor your support in advanced

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-21 : 13:14:14

what should i do now with these values ?

Thanks for your support

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-22 : 11:04:09

Acording to autogrowth setting is set to for dm1_elite_Log23 Our DBA has to do some adjusments with these values ?
is there any way to track down or trace how large everylog is growing everyday ?

Thanks for your help in advanced

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-22 : 12:25:03

The autogrowth is set to 1GB, which is fine for a 20GB file. I'd probably look at your IO subsystem as 6 seconds seems much too long to grow a file by 1GB.

I would instruct the DBA to stop shrinking the files on a scheduled basis. He/she needs to do some reading on this topic. There are many, many articles.

Here are two articles on the subject from two SQL Server experts:
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

I would do these steps:
1. Stop shrinking the files on a scheduled basis
2. Redesign your delete process to do it in batches
3. Increase the frequency of the log backups to 5 minutes or less
4. Monitor the log size after 2-3 days of the above steps

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources