Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.
The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.
We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.Hi all, I am a lowly help desk operator who has been thrown into the position of maintaining our SQL server until a new DBA is hired.
The problem is the transaction log has grown to 95GB. I truncated the log file and it shows 95 GB allocated, 588MB in use. I have researched and found that I need to use the DBCC SHRINKFILE command to free up the space, however, I have no clue how to use this command. I can't seem to find any documents on the syntax to use this with osql from the command line.
We are running out of space on the drive and everyone is looking at me to do something about it. Any help would be immensely appreciated.
See this ...http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653804&SiteID=1 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=653804&SiteID=1)|||This still doesn't help me much. What I am looking for in instructions on HOW to run the commands to shrink the log file. Do I run it from command line? and if so do I just type in "DBCC SHRINKFILE filename" ?|||First make sure there are no open transactions:
select * from master.dbo.sysprocesses where open_tran > 0
then run:
dbcc sqlperf(logspace)
Use the name retreived above and run dbcc shrinkfile
dbcc shrinkfile([database_Log])
exec sp_spaceused
if the size does not go down, you can always dump the transaction log
dump tran [databasename] with no_log
Please do research before executing the last line of code to fully understand what you are about to do.
Good luck,
Hope this helped|||You should run it in query analyzer, assuming you are using sql server 2000|||I don't have much to add beyond what Reghardt has already said, but I will mention that properly scheduled backups of your transaction log should handle keeping your filesize down quite nicely.
My guess is that there is no transaction log backup scheduled, or they are scheduled, but have been failing for a while now & havn't been monitored.
Here is a great guide that should help you with this.
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1166473,00.html
Shrinking your log-file will solve your problem now, but it will just happen all over again down the road if you don't set up proper backups.
In addition, it is a very good idea to put a threshold on all of your transaction logs. That way, if a transaction log starts growing too big, it will only affect the database it is assigned to, instead of filling up your entire server.|||This worked perfectly, I was able to get it down to 4.6 gigs. Appreciate all the help.
Showing posts with label operator. Show all posts
Showing posts with label operator. Show all posts
Monday, March 19, 2012
Monday, February 13, 2012
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
Hi prakal,
the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.
Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.
You would have your query done on the 32bit box as well.
|||I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Or, if you had all of these attributes as part of a hierarchy you could just do
|||WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
A recent thread on this forum linked to a Microsoft Support article that might be relevant:
Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"
SYMPTOMS
When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
This problem may occur if the following conditions are true:
Back to the top
CAUSE
This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.
|||Darren,
Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".
Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.
|||Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.
Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {
NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])
* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])
} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
This post may help. You may want to follow up on the Analysis Services forum.
|||Thanks for your reply Teo. I have already refered to this post and unfortunately I'm unable to use the Exists function in this context due to certain limitations in our application.
I have also posted the same issue on the Analysis services forum.
Thanks again for your help.
4294967296 Tuple limit and 32 bit Architecture
We have repeatedly encountered the 4,294,967,296 tuple limit in our MDX queries, when using the Non Empty Operator on a Cross Join of fields:
Example
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {([dimension1].[attribute1].Children,[dimension2].[attribute2].Children,[M2],[dimesnsion3].[attribute3].Children,[dimension4].[attribute4].Children,[dimension5].[attribute5].Children,[dimension6].[attribute6].Children)} ON ROWS FROM MyCube WHERE {([Date].[DateID].[M1])}
Error
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
We have followed Microsoft's best practices for Cube Design, while balancing the needs of our application.
Is it a coincidence that the 4,294,967,296 = 2^32 on the order of 4 GigaBytes and our server is of 32-bit architecture?. Will scaling up to a 64-bit architecture machine help? Then will our tuple limit be 2^64 which is on the order of 18 ExaBytes or 18 qunintllion bytes?
Hi prakal,
the 64 bit would be helpfull for you, but I think, that brut force isn't goot approach.
Do you really need to join all 6 dimensions? Why you didn't try to use nonemptycross join.
You would have your query done on the 32bit box as well.
|||I don't think 64bit will fix this, I believe the 2^32 is more of an SSAS engine limit, not a platform one. The nonemptycrossjoin is depreciated, but you could nonempty() your attribute sets before crossjoining them.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {NONEMPTY([dimension1].[attribute1].Children,[Measures].[count]),NONEMPTY([dimension2].[attribute2].Children,[Measures].[count]),NONEMPTY([M2], [Measures].[count]),NONEMPTY([dimesnsion3].[attribute3].Children, [Measures].[count]),NONEMTPY([dimension4].[attribute4].Children, [Measures].[count]),NONEMPTY([dimension5].[attribute5].Children, [Measures].[count]),NONEMPTY([dimension6].[attribute6].Children,[Measures].[count] )} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Or, if you had all of these attributes as part of a hierarchy you could just do
|||WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {[dimension1].[hierarchy1].Members} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
A recent thread on this forum linked to a Microsoft Support article that might be relevant:
Error message when you run a complex MDX query that contains the NON EMPTY clause in SQL Server 2005 Analysis Services: "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"
SYMPTOMS
When you use a user account to connect to an instance of Microsoft SQL Server 2005 Analysis Services, you receive the following error message when you run a complex MDX query:
The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.
This problem may occur if the following conditions are true:
Back to the top
CAUSE
This issue occurs because the optimized algorithm for the NON EMPTY clause cannot handle dimension security on measures. Therefore, SQL Server 2005 Analysis Services must use the default algorithm for the NON EMPTY clause. If the query is complex, you may receive the error message that the "Symptoms" section mentions.
|||Darren,
Thanks for your reply. I tried the query in your first solution, but I get the error "Two sets specified in the function have different dimensionality".
Which I guess answers your second solution, all the attributes in the query are a part of different dimensions and cannot be set as a hierarchy of one dimension.
|||Sorry, I overlooked the detail that there were different dimensions involved . You're right, this would negate using a user hierarchy.
Using NonEmpty on each attribute should work, maybe there was a bracket out of place. Normally I would use an explicit nested crossjoin() functions or the * operator for crossjoining multiple sets. Maybe start with 2 sets and build up to all 6.
WITH
MEMBER [Date].[DateID].[M1] AS
AGGREGATE({[Date].[DateID].&[20070101]:[Date].[DateID].&[20070103]})
SET [M2] AS
({[dimension7].[attribute7].[value7]})
SELECT {[Measures].[count]} ON COLUMNS,
NON EMPTY {
NONEMPTY([dimension1].[attribute1].Children,[Measures].[count])
* NONEMPTY([dimension2].[attribute2].Children,[Measures].[count])
} ON ROWS
FROM MyCube
WHERE {([Date].[DateID].[M1])}
Subscribe to:
Posts (Atom)