March 12, 2014 at 12:46 am
If i try to DBCC DBREINDEX (Table_name, '', 70); i am getting the error msg.
how to shrink table size.
Given below are my database informations
GO
DBCC DBREINDEX (Table_name, '', 70);
GO
Server: Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object '(SYSTEM table id: -XXXXXXXXX)' in database 'YYYYYYYY' because the 'PRIMARY' filegroup is full.
The statement has been terminated.
Have set my primary and log size to use max
GO
sp_helpdb DBNAME
GO
name db_size owner dbid created status compatibility_level
--------------- ------------ ------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ----------------------------------------------------------------------------------------------------- -------------------
DBNAME 2884.69 MB sa 7 JAN 14 XXXX Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=XXX, Collation=SQL_Latin1_General_CP1_CS_AS, SQLSortOrder=51, IsAutoShrink, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 100
name fileid filename filegroup size maxsize growth usage
--------------------- ------ -------------------------------- -------------------------- ------------------ ------------------ ------------------ ---------
DBNAME 1 D:\RRRRRRR\DBNAME.mdf PRIMARY 2888896 KB Unlimited 10240 KB data only
DBNAME 2 E:\RRRRRRR\DBNAME_log.LDF NULL 13824 KB Unlimited 10240 KB log only
on using sp_spaceused i am getting the unallocated space as -970 MB
Why it is minus?
GO
SP_Spaceused
GO
database_name database_size unallocated space
---------------------- ------------------ ------------------
DBNAME 2884.69 MB -970.24 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3933624 KB 2028704 KB 1524176 KB 380744 KB
how to minimize the database size
March 12, 2014 at 12:56 am
how much space you have on your disk where this db is located?
March 12, 2014 at 1:05 am
200 GB for each drive
March 12, 2014 at 2:14 am
The error is complaining that the database is too small. Shrinking it (making it smaller) is not the solution, it's the complete opposite of the solution. You need to grow the file in question or ensure there's enough space for SQL to grow it automatically.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 2:47 am
There are enough space is there .
The db has been set to auto grow only.
only thing is database is set as Simple mode is this a problem ?
If i switch to Full mode will it solve.
March 12, 2014 at 2:52 am
yuvipoy (3/12/2014)
only thing is database is set as Simple mode is this a problem ?
No
If i switch to Full mode will it solve.
No.
The index rebuild is running out of space in whatever DB and file were indicated in the error message. Grow the file or add more disk space as appropriate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 3:50 am
I am having the disk size of near 200 GB only 10 GB is filled till date.
Just now i ran dbcc reindex
getting same error msg as Msg 1105, level7, state 2 line 1
:angry:
March 12, 2014 at 4:00 am
GilaMonster (3/12/2014)
Grow the file or add more disk space as appropriate.
and run a DBCC updateUsage to fix that negative free space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2014 at 4:03 am
on using sp_spaceused i am getting the unallocated space as -970 MB
Why it is minus?
GO
SP_Spaceused
GO
database_name database_size unallocated space
---------------------- ------------------ ------------------
DBNAME 2884.69 MB -970.24 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
3933624 KB 2028704 KB 1524176 KB 380744 KB
Microsoft does not guarantee that table and database space allocation information will be maintained accurately. In a high transaction OLTP database or after a large bulk copy of data has been performed, this can lead to inaccurate database space reporting when using the system stored procedure sp_spaceused.
USE This
exec sp_spaceused @updateusage = ‘true'
for details refer to the this link http://www.mssqltips.com/sqlservertip/1358/use-dbcc-updateusage-to-get-accurate-sql-server-space-allocation/
2ndly, as Gila suggested increase the db size manually.
March 12, 2014 at 4:38 am
I have already issued DBCC updateUsage and as well SP_spaceused with 'true'
Maxsize in size file is also set as -1 only in sysfiles
March 12, 2014 at 5:15 am
yuvipoy (3/12/2014)
200 GB for each drive
how much empty space u have ?
March 12, 2014 at 5:37 am
twin.devil (3/12/2014)
yuvipoy (3/12/2014)
200 GB for each drivehow much empty space u have ?
yuvipoy (3/12/2014)
I am having the disk size of near 200 GB only 10 GB is filled till date.:angry:
nearly 190 GB
March 12, 2014 at 5:42 am
The index rebuild is running out of space in whatever DB and file were indicated in the error message. Grow the file or add more disk space as appropriate.
I was able to insert new records in the table and table size also varies(increase in Kb size as well in rows).
only getting error when i ran DBCC DBREINDEX
March 12, 2014 at 7:01 am
try to set the size of the database manually i-e CurrentSize * 2 size
Following is the code to do it.
ALTER DATABASE DatabaseName
MODIFY FILE
(NAME = logical_file_name,
SIZE = FilesizeInMB);
and then run the try to dbcc reindex.
March 13, 2014 at 12:37 am
No still same issue 🙁
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply