DB Engine :: Transaction Log Space Usage On TempDB Ever Increasing
Jun 15, 2015
today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
View 6 Replies
Oct 31, 2007
I'm hoping to find help here on the following issue. Here is the setup and situation. I'm running SQL Server 2005, including SSAS. SQL Server holds a database that is about 7GB in size and used to provide the data for my analysis project.
I successfully created cubes, deployed them and worked with them. Then all of the sudden I got error messages that all where related to tempdb and sounded like this:
<...
The operating system returned error incorrect page (expected 1:334039; actual 47:3211311) to SQL Server during a read at offset 0x000000a31ae000 in file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATA empdb.mdf'
...>
I run DBCC but nothing came up (please see below). After searching for hours, I increased the initial size of the tempdb and restarted. It was at about 2GB and I brought it up to 4GB. At first it worked but would then fail again.
My best guess is that is has to do with the amount of dimensions and measures that I use in my cube but compared to what I keep reading it is still very small in size.
I have 4 dimensions, 1 with 3-5 attributes and maybe a 150000 rows total in the view I used. Some of the tables hold more records though (~3 million).
Could it by that my server is simply not fast enough (I/O) to keep up with whatever SSAS is doing? I have a hard time to believe that.
Here is what DBCC CHECKDB ('mbox', REPAIR_REBUILD) came back with on my work DB:
<....
DBCC results for 'mBOX'.
Service Broker MSG 9675, State 1: Message Types analyzed: 14.
Service Broker MSG 9676, State 1: Service Contracts analyzed: 6.
Service Broker MSG 9667, State 1: Services analyzed: 3.
Service Broker MSG 9668, State 1: Service Queues analyzed: 3.
Service Broker MSG 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker MSG 9674, State 1: Conversation Groups analyzed: 0.
Service Broker MSG 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1208 rows in 11 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 137 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 151 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 1208 rows in 12 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 137 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 151 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 134 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 149 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1089 rows in 20 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 32 rows in 12 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 327 rows in 9 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 645 rows in 6 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 372 rows in 116 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 154 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 317 rows in 2 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'UHISTFCST'.
There are 2798023 rows in 89218 pages for object "UHISTFCST".
DBCC results for 'SS'.
There are 0 rows in 0 pages for object "SS".
DBCC results for 'MODEL'.
There are 3 rows in 1 pages for object "MODEL".
DBCC results for 'MASKDATA'.
There are 15397 rows in 109 pages for object "MASKDATA".
DBCC results for 'MASK'.
There are 7328 rows in 44 pages for object "MASK".
DBCC results for 'LOC'.
There are 230 rows in 5 pages for object "LOC".
DBCC results for 'LINREGSTATS'.
There are 1285 rows in 59 pages for object "LINREGSTATS".
DBCC results for 'LINREGPARAM'.
There are 29192 rows in 1310 pages for object "LINREGPARAM".
DBCC results for 'HISTSTREAM'.
There are 2 rows in 1 pages for object "HISTSTREAM".
DBCC results for 'HISTFCST'.
There are 3827608 rows in 75573 pages for object "HISTFCST".
DBCC results for 'HIST'.
There are 1666993 rows in 44095 pages for object "HIST".
DBCC results for 'FCST'.
There are 2126837 rows in 27584 pages for object "FCST".
DBCC results for 'EVENT'.
There are 6 rows in 1 pages for object "EVENT".
DBCC results for 'DMDUNIT'.
There are 21661 rows in 981 pages for object "DMDUNIT".
DBCC results for 'DMDGROUP'.
There are 26 rows in 1 pages for object "DMDGROUP".
DBCC results for 'DFUTOSKU'.
There are 29192 rows in 490 pages for object "DFUTOSKU".
DBCC results for 'DFUMAP'.
There are 64957 rows in 1580 pages for object "DFUMAP".
DBCC results for 'DFU'.
There are 136963 rows in 9349 pages for object "DFU".
DBCC results for 'Locations'.
There are 247 rows in 2 pages for object "Locations".
DBCC results for 'DBPARAM'.
There are 1 rows in 2 pages for object "DBPARAM".
DBCC results for 'CALDATA'.
There are 88308 rows in 1066 pages for object "CALDATA".
DBCC results for 'CAL'.
There are 156 rows in 2 pages for object "CAL".
DBCC results for 'ALLOCSTRAT'.
There are 14 rows in 2 pages for object "ALLOCSTRAT".
DBCC results for 'sysdiagrams'.
There are 1 rows in 1 pages for object "sysdiagrams".
DBCC results for 'TOP5080'.
There are 1325 rows in 7 pages for object "TOP5080".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'MaterialMaster'.
There are 33426 rows in 3463 pages for object "MaterialMaster".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'mBOX'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
....>
Now, it turns out my HD was really defragmented so I'm currently running a defrag but this is already driven by pure hope.
Sorry for the lengthy mail but I'm really hoping to find help around here.
Any suggestions is appreciated!
Happy halloween and regards,
Dirk
View 6 Replies
View Related
Feb 14, 2006
Edit 2007-8-9:
Added code to show database file sizes. Not really closely related to tables sizes, but a lot of the people who need this want to know why their database it so large, so it may help to know which files, especially the logs, are so large, and if the files have empty space in them.
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
create table #TABLE_SPACE_WORK
(
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE_USED
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MBnumeric(18,4)not null,
USED_GBnumeric(18,4)not null,
AVERAGE_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null,
)
declare @fetch_status int
declare @proc varchar(200)
select@proc= rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME=
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE= 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'
-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end --While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED=
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA=
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE=
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED=
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go
drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
CODO ERGO SUM
View 12 Replies
View Related
Jul 20, 2001
This database is running on SQL sever 6.5.
We are running an home-created stored procedure that includes several rounds of dropping and recreating indexes in a few tables, i.e., after dropping indexes, the tables are truncated, then a lot of records will be inserted into the table, and the indexes are recreated.
we are getting the following error in turns:
1. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server]Can't
allocate space for object '-841' in database 'tempdb' because the
'system' segment is full. If you ran out of space in Syslogs,
dump the transaction log. Otherwise, use ALTER DATABASE...
We then used EM to expand the tempdb, when reran the procedure, we got the following message:
2. ODBC error: Microsoft] ODBC SQL Server Driver] SQL Server] Cannot
drop the index 'dbo.ItemBalamce_CCB_Id',because it doesn't exist in the
system catalogs.
However, when I check the mentioned index, it is certainly there.
How could this happen?
After several rounds of running theis procedure, these two error messages appeared to occur alternatively.
If you have an answer or suggestion, please let me know ASAP.
Many thanks.
View 2 Replies
View Related
