December 28, 2011 at 8:01 pm
In the production we have only one primary file group for TEMP DB
name fileid filename filegroup size maxsize growth usage
tempdev 1 D:\TempDB\tempdb.mdf PRIMARY 4086976 KB Unlimited 10% data only
templog 2 D:\TempDB\templog.ldf NULL 181184 KB Unlimited 10% log only
tempdev_1 3 D:\TempDB\tempdev_1.ndf PRIMARY 3715392 KB Unlimited 10% data only
tempdev_2 4 D:\TempDB\tempdev_2.ndf PRIMARY 3715392 KB Unlimited 10% data only
tempdev_3 5 D:\TempDB\tempdev_3.ndf PRIMARY 3715392 KB Unlimited 10% data only
But i am getting the error of :temp DB log file is full.
Msg 1101, Level 17, State 10, Line 1
Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. 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.
Msg 9002, Level 17, State 4, Line 1
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.SORT temporary run storage: 140737922596864' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Please help me in this regard.
December 28, 2011 at 8:46 pm
Have you checked the disk(s) where tempdb is located to see if the disk itself is full? That is the first thing I would check if I got this error.
December 28, 2011 at 10:07 pm
Seems like a disk full type of error to me as well. I would check for free disk space.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2011 at 12:48 am
Yes, error it self clearly indicates that "insufficient disk space" autogrow is trying to expand the LDF file by 10% and unable to do due to space issue.
As a solution:
1. You can restart the sql server services if the server is not production and there are no implicatins.
2. If server is important and you are unable to make any space on D: drive you can create and add another LDF file on different drive.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
December 29, 2011 at 1:24 am
Could not allocate space for object 'dbo.SORT'
Also, looks like you had a huge sort spilling to tempdb. Could be an indexing issue or stale stats. I would try to investigate what made tempdb go to the roof and once you find the culprit, try to give that query a better access path to data.
This script could help you identifying the offending query: http://www.sqlservercentral.com/scripts/tempdb/72007/
Hope this helps
Gianluca
-- Gianluca Sartori
December 29, 2011 at 8:28 am
This will help you find the cause of the growth in tempdb.
http://www.sqlservercentral.com/articles/Log+growth/69476/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply