July 3, 2015 at 5:50 am
(Not quite sure where best place is to post this, but here goes)
Dear Experts
First, the background...
Yesterday I filled up a drive while attempting a DBCC DBREINDEX on a 300 GB database.
The script I was using first used DBCC SHOWCONTIG to build a list of all tables that were >5% fragmented. It then stepped through the table and ran a reindex of all indexes of all candidates, using the current fill factor. All was well until it hit a table that was evidently too big and/or chaotic to be rebuilt in the available space. Here are the last couple of messages from the script:
Executing DBCC DBREINDEX ('UserPCTAccess', ' ', 0) - fragmentation currently 67%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Executing DBCC DBREINDEX ('RISC_PatientGPDetail', ' ', 0) - fragmentation currently 96%
The statement has been terminated.
Msg 1101, Level 17, State 12, Line 1
Could not allocate a new page for database 'SOMRISC2' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
On examining the drive (fortunately not the system drive!), I found, not surprisingly, that it had just a teeny bit of space left, like, 10 MB. The next morning I got a message from the DBA saying the drive was full because tempdb (which also has part of its PRIMARY filegroup on the same disk) had filled it up.
OK, so it looks as if both the main database and tempdb were fighting to find space on the drive. That leads to my question, which is: How do the sizes of a database, the size of tempdb, and the sizes of their respective logs, interact? I feel there is rather a gap in my understanding of the *general* picture of How It All Hangs Together, and that this is hindering me from organizing my files, file groups etc so as to prevent a recurrence of this problem.
Any enlightenment from any knowledgeable person would be very welcome.
Yours hopefully
Mark Dalley
July 3, 2015 at 5:55 am
Mark Dalley (7/3/2015)
How do the sizes of a database, the size of tempdb, and the sizes of their respective logs, interact?
They don't really.
A user database's size is based on the data in it. A user database's log size is based on the size of the largest transactions and on the time between log backups.
TempDB's size depends on the usage of temp objects (temp tables, table variables, work tables) as well as on sort and hash spills, the version store and a few other things that use TempDB. TempDB's log size depends on the largest transactions done in TempDB.
There's no formula that relates a user database's size to the size of it's log or the size of TempDB.
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
July 3, 2015 at 7:05 am
Hello Gail
Hmm, fair enough. I forgot to mention that the database in question only has simple logging enabled, so elapsed time since previous log backup shouldn't be an issue.
I have just gotten another email from the DBA saying that he thinks all my problems are caused by the script I was using...here it is:
--Begin Script--
/*Perform a 'USE ' to select the database in which to run the script.*/
USE SOMRISC2
SET NOCOUNT ON
-- Declare variables
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 5.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL
)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC DBREINDEX (''' + RTRIM(@tablename) + ''', '' '', 0'
+ ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC DBREINDEX (''' + RTRIM(@tablename) + ''', '' '', 0'
+ RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
--End Script--
Yes, I know it uses a cursor <blush>, and everyone says that cursors are evil, but are they really so evil that they make the size of the DB expand in this horrendous and disk-filling fashion? Surely a table containing a list of fragmented tables can't do any harm, even if I traverse it with a cursor?
Regards
Mark Dalley
July 3, 2015 at 7:14 am
Your DBA is right. You're doing index rebuilds, they require a lot of free space in the data file and a lot of space in the log file. If you don't have that space, the database will grow. If you don't have the disk space, the rebuild will fail with an error.
Your DBA should have regular index maintenance going on already, chat with him and make sure you're not redoing existing maintenance.
btw, DBCC DBREINDEX has been deprecated since SQL 2005 and shouldn't be used. Replacement is ALTER INDEX ... REBUILD. And cursors are not evil. Nothing in SQL (with the exception of autoshrink) is evil.
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
July 3, 2015 at 7:38 am
Re possible duplication of effort, I am co-ordinating with the DBA, who has just taken over the management of the DB. (I am more of an applications person, though I have a background in servers and networks from a previous job. My main mission is to speed up the database, which originated with a third party supplier.... Fortunately, I have a good relationship with them too.)
OK, I realize that DBREINDEX is deprecated. The DBAs script, which I will peruse when I get a chance, uses ALTER INDEX. It also seems to run faster than my one did.
BTW when you said these processes could cause a big jump in the size of the database, were you referring to the main DB, or tempdb, or both?
Mark Dalley
July 3, 2015 at 7:46 am
The database containing the indexes which you are rebuilding. If you specify the SORT_IN_TEMPDB option, then TempDB as well.
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
July 6, 2015 at 5:45 am
Hello Gail
Just got an email from the DBA saying that he had shrunk the DB as a maintenance measure after rebuilding the indexes. I thought - based purely on what I have read - that this was a sure fire way to fragment the indexes again, effectively undoing the good of the index rebuild. Is it possible to shrink without fragmenting, or should we steer away from shrinking altogether?
I need to be sure of my facts before I proceed.
Mark Dalley
July 6, 2015 at 5:54 am
Shrinking fragments indexes. When he shrank the DB, he would have re-fragmented every single index.
Shrinking is NOT a maintenance task. It should only be done after there has been some large data archives or deletes that has resulted in free space in the DB that is not expected to be reused in a reasonable about of time (~6 months in my opinion)
http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
July 6, 2015 at 8:34 am
Mark Dalley (7/6/2015)
Hello GailJust got an email from the DBA saying that he had shrunk the DB as a maintenance measure after rebuilding the indexes. I thought - based purely on what I have read - that this was a sure fire way to fragment the indexes again, effectively undoing the good of the index rebuild. Is it possible to shrink without fragmenting, or should we steer away from shrinking altogether?
I need to be sure of my facts before I proceed.
Mark Dalley
They should save some money and make you the DBA. At least you know some of the more important basics that DBAs should know even if you may not have done them in the past.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply