August 6, 2012 at 3:05 am
Hi ,
Last week[Thursday] the d size is arround 30 GB. But today i wondered the database size is around 47 GB .
Can you please let me know what are the causes to increase the database size??
I am sure on weekends there is no scope to insert these much data[around 17 GB]?
Thanks
Lavanya
August 6, 2012 at 3:09 am
Any weekend maintenance job like index rebuild?
August 6, 2012 at 3:09 am
Do you have any weekend processes like rebuild, reorganise indexes etc?
What is the autogrowth setting of the DB in question?
Do you keep a record of the space used on a daily basis?
The below will loop through each DB and show you free space, used space, total space.
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
' ',CHAR(13) + CHAR(10)
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
August 6, 2012 at 3:11 am
The database is in simple recover model but the mdf file size is 32 Gb and ldf file size is 15 gb .I try to shrink the file but no free space there . How can i reduce the log file size?
Thanks
Lavanaya
August 6, 2012 at 3:13 am
I would recommend reading this http://www.sqlservercentral.com/articles/Administration/64582/ before you do anything.
If the log file jumped to that size, it was for a reason and would probably do it again.
As detailed already, do you have any maintenance jobs set to do anything to indexes etc?
August 6, 2012 at 3:14 am
Antony,
we have 2 servers one server is updating some data into another.
within SET XACT_ABORT on and off.
after this job added we have tremendous increase in log file growth with just max 20 - 30 records insert and update in 3 tables.
there is no significance growth in table size but why the log is growing in that manner.
i cannot remove SET XACT_ABORT on and off as it has to update the primary server also.
Regards
Durai Nagarajan
August 6, 2012 at 3:20 am
Well something needed that space, if you dont know what then I would recommend tracing the server, but if it only happens once in a blue moon then unless you had something logging what was going on, you will be hard pressed to see what caused it.
As a long shot you could try looking in the default trace, if your server has a fairly low usage and the trace events havn't been purged
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
Would tell you when the log file grew, so you could try and pin point it to a process.
August 6, 2012 at 3:26 am
thanks and i'll read what you have given and comback incase needed.
Regards
Durai Nagarajan
August 6, 2012 at 3:47 am
Yes , Every weekend we are running rebuild index and update stats.
2.Mdf file intial size is 31,642 auto growth 200MB unrestricted growth.
LDF file intila size is 16,376 by 100m mb Restricted growth to 2,097,152.
Every day i am monitoring the disk space except last three days .
Can you please suggest me on this ??
August 6, 2012 at 3:55 am
when i try to run the dbcc opentran() on that particular database its showing the below open transaction
Transaction information for database
Oldest active transaction:
SPID (server process ID): 1236s
UID (user ID) : -1
Name : DTCXact
LSN : (250855:13949:24)
Start time : Jul 30 2012 11:10:22:270AM
SID : 0x87f9e2e0be6b2343afe8cd5943c9fd
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
August 6, 2012 at 3:59 am
Lavanyasri (8/6/2012)
when i try to run the dbcc opentran() on that particular database its showing the below open transactionTransaction information for database
Oldest active transaction:
SPID (server process ID): 1236s
UID (user ID) : -1
Name : DTCXact
LSN : (250855:13949:24)
Start time : Jul 30 2012 11:10:22:270AM
SID : 0x87f9e2e0be6b2343afe8cd5943c9fd
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Wowza
You have a DTC transaction which is still active since the 30th July, I would investigate that as top priority.
May also want to google "killing a negative spid" as you might find the transaction jumps to SPID a negative SPID when stopping the transaction.
August 6, 2012 at 4:11 am
How can we find out which transaction was running still 30 th july ??
Can we please provide the update asap .......
August 6, 2012 at 4:15 am
You have all the information you need in DBCC OPENTRAN, it gives you the SPID, so all you need to do now is query the DMV's to find out what the transaction is doing and where it came from etc.
August 6, 2012 at 4:38 am
I spid is looks different 1236s. Can you provide some DMVs to find the Querys which is causing this
August 6, 2012 at 4:43 am
try this i use this to see any block or not.
SELECT
s.spid, login_time, status,case when blocked = '0' then '' else convert(varchar,blocked) end 'blocked',text, hostname,ObjectName = OBJECT_NAME(objectid,s.dbid),
cpu , waittime, physical_io,
DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, cmd
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50
order by login_time
Regards
Durai Nagarajan
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply