Database and Log File Size is Growing Very Fast!!!
hi guys,
i have database runs on sql server 2012 , noticed size increasing drastically. size of log file on 3x size of data file.
please can me on before database crashes.
thanks
me
hi
for data file growth , please check transactions running using below
-- -- -- -- -- -- --current running transaction -- -- -- -- -- -- -- -- -- -- use master select spid,er.percent_complete, cast(((datediff(s,start_time,getdate()))/3600) varchar) + ' hour(s), ' + cast((datediff(s,start_time,getdate())%3600)/60 varchar) + 'min, ' + cast((datediff(s,start_time,getdate())%60) varchar) + ' sec' running_time, cast((estimated_completion_time/3600000) varchar) + ' hour(s), ' + cast((estimated_completion_time %3600000)/60000 varchar) + 'min, ' + cast((estimated_completion_time %60000)/1000 varchar) + ' sec' est_time_to_go, dateadd(second,estimated_completion_time/1000, getdate()) est_completion_time, er.command,er.blocking_session_id, sp.dbid,lastwaittype, db_name(sp.dbid) dbname, substring(est.text, (er.statement_start_offset/2)+1, ((case er.statement_end_offset when -1 datalength(est.text) else er.statement_end_offset end - er.statement_start_offset)/2) + 1) querytext, text,cpu,hostname,login_time,loginame, sp.status,program_name,nt_domain, nt_username sysprocesses sp inner join sys.dm_exec_requests er on sp.spid = er.session_id cross apply sys.dm_exec_sql_text(er.sql_handle) est
for log file growth , first check filling log file
once done try shrink log file using : sql server transaction log file shrinking
for more details , can find querys : used dmv's in sql server
thanks saurabh sinha
http://saurabhsinhainblogs.blogspot.in/
please click mark answer button , vote helpful if reply solves problem
SQL Server > SQL Server Database Engine
Comments
Post a Comment