Tag: SQL2008

Report server log file eating up diskspace – ReportServerTempDb

I got a 400Gb log file for my birthday today.

This 400gb made me mad, It’s just a temp db. And all processes are down this morning. Looking around on the internet, this seems to be a common known problem, some even  state this problem is acknowledged by Microsoft.i can be.. engineer

After reading some blogs on possible solutions. I decided I need to solve the disk problem first. First checks you need to do before you start the troubleshoot:

You need to rule out that you didn’t bring this over yourself, the ReportTempDb needs to be in Simple mode, this means there are no logbackups and the it should automatically reclaim space in your logfiles, well, this is how it should work. But in this case the logspace keeps growing. Indicating something is wrong with the reportserver, when it already is in Simple mode.

Also It could be worthwhile to check which reports are costing you, since not only your log space is invaded by report servers, your CPU is probably a victim too..

	COUNT(s.SegmentId) as CountOfSegmentId
	[ReportServerTempDB].dbo.Segment s 
	[ReportServerTempDB].dbo.ChunkSegmentMapping m 
	m.SegmentId = s.SegmentId
	[ReportServerTempDB].dbo.SegmentedChunk c 
	c.ChunkId = m.ChunkId
	[ReportServerTempDB].dbo.SessionData sd 
	sd.SnapshotDataID = c.SnapshotDataId 
	CountOfSegmentId DESC;

After these checks, we have 2 routes to go, but today we’ll will start with the quick and dirty one, solve the disk shortage, since all processes are put to a stop due to this ‘Stay Puft’ logfile. We need to shrink this beast, I should tell you, shrinking your database is pure evil and risk taking. You should never ever ever do this! …Unless even Microsoft says it’s ok. And it’s just tempdata. It should’ve just deleted itself.

First, we are gonna run a full backup of the ReportserverTempDb, to an external location, ofcourse. Once this is done, we canstart with the clean up of the logfile.

Bring your database level to full mode, this allows you to alter the filegroups.

Right click tthe ReportServerTempDb database Go to the option shrink –> files and check the available free space and check the release unused space button.

This should give you some space, in my case, 400Gb, whoohoo, Dobby is free! Don’t forget to put your ReportServertempDb back into simple mode!

You could also shrink the log file with the ‘shrink file’ option, but like I said earlier, it’s better to avoid this option, because it could lead to faulty logfiles when you need to recover. But I case of this ReportServerTempDb the risk is low.

Now that we have got some diskspace back, we need to dig deeper to find out the cause of this log file eating up all your precious disk space. Next topic will be how we can fix this Stay Puft log file growth.

Use of MAX data types when over >8000 characters. (error exceeds the maximum allowed for any data type (8000).

changes/upgrades in your favorite software, become real gems at the moment you run into a problem. Like, finding out a nvarchar doesn’t go over >8000.

Since SQL2005 the keyword ‘max’ was added for the datatypes nvar, varchar and varbinary. Before the introduction of  the (MAX) data types, 8000 was the absolute ‘max’, and your next best option was to use a text field, but a textfield exceeding 8000 will be handled as an out of row page and could not be used as a variable. Various of workarounds where available  online, but it was never pretty.

In SQL2005 the (n)var/char data types allows variables to be as big as the (n)text datatype can be. You can do anything to them that you could with regular varchar types, but behind the scenes they function like the text data type with values less than 8000 characters in-row and values greater than 8000 characters stored in out-of-row pages. These types can be declared as local variable.

Remember, if over 8000 characters needed,Us the (MAX) data type, SQL does not allow you to use f.e. VARCHAR(10000).



What if I still want to limit the entry ?
MAX only defines more then 8000 of the character input. If you want to set a maximum of characters, like 10.000, use a CHECK CONSTRAINT To create a column of nvarchar data type with a maximum of 10.000 characters, declare the column as nvarchar (MAX) and add a check constraint to the column by checking the length of the column and making sure it is less than or equal to 10.000 characters.

CREATE TABLE [dbo].[Testdb] ( [VarChar10000] VARCHAR(MAX) )
ALTER TABLE [dbo].[Testdb]     ADD CONSTRAINT [MaxLength10000]
CHECK (DATALENGTH([VarChar10000]) <= 10000)

What happpened to TEXT?
VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) are the replacements of TEXT, NTEXT and IMAGE data types. These datatypes are deprecated after, so avoid using these data types when using < SQL Server 2005.

Wanna see some numbers?

(I borrowed the calculations below from the Microsoft Knowledgebase)

The calculation for the maximum storage size for VARCHAR(MAX), NVARCHAR and VARBINARY(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB – 1 bytes).  The storage size is the actual length of data entered + 2 bytes.  The data entered can be 0 characters in length. Note: characters in an NVARCHAR data type uses two bytes, the actual maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.