Skip to main content
143 questions
Newest Active Bountied Unanswered
Filter by
Sorted by
Tagged with
0 votes
1 answer
106 views

I'm trying to execute the following SQL Query: DECLARE @Start DATETIME SET @Start = DateAdd(Hour, -1,GetDate()) IF OBJECT_ID('tempdb..#logTable') IS NOT NULL DROP TABLE #logTable CREATE TABLE #...
edwio's user avatar
  • 333
3 votes
1 answer
227 views

I have a very simple query that I am testing by running ad-hoc. The execution plan indicates that data is spilling into tempdb because of poor estimates. I have been unable to resolve any of the three ...
James's user avatar
  • 33
0 votes
0 answers
163 views

Some time ago I noticed that one of our servers has old tempdb ndf files still in sys.master_files. Of course sys.databases or sys.database_files are actual. Problem is that after an instance restart ...
corwin's user avatar
  • 1
0 votes
1 answer
388 views

In my SQL Server, I have 9 user databases and I would like to find out which user database is using how much resources in tempdb, which database is using more and which less, and furthermore, I would ...
Zakir Hossain's user avatar
  • 1
0 votes
1 answer
413 views

We have an application making an async database connection in C# that is attempting to run two SELECT statements at the same time. Both statements are referencing the same temp tables but are only ...
Jason Boone's user avatar
  • 23
1 vote
0 answers
196 views

I'm trying to troubleshoot what is causing timeouts when querying specific table in production. I suspected that there are some uncommitted transactions, so I ran following query: SELECT trans....
Liero's user avatar
  • 27.8k
1 vote
1 answer
197 views

The documentation for sp_WhoIsActive states The most confusing of these columns are those related to tempdb. Each of the columns reports a number of 8 KB pages. The [tempdb_allocations] column is ...
J. Mini's user avatar
  • 1,766
0 votes
0 answers
761 views

We have successfully upgraded our server version to SQL Server 2019. One of the expected features that we dreamed about was memory-optimized TempDB metadata and we turned it on, first we decided to ...
Orlandis's user avatar
  • 1
2 votes
0 answers
762 views

We are using dbt in combination with SQL Server 2019 and the usage of CTEs are a huge performance drag for us. The main issue with the CTEs is, that they are deeply nested over several levels. E.g. ...
Chris's user avatar
  • 144
0 votes
1 answer
5k views

I have a server that is getting full, due to some temp table processing. We found that our tempdb file was at 33.8 GB and there was a another file at 33.8 GB called tempdb_mssql_2 Now then, we ran the ...
Mark Johnson's user avatar
  • 665
0 votes
1 answer
397 views

Checking my execution plan it shows a warning in a Top N Sort step Operator used tempdb to spill data during execution with spill level 1 and 4 spilled thread(s), Sort wrote 4609 pages to and read ...
user1589188's user avatar
  • 5,798
0 votes
1 answer
371 views

I got some problems with my SQL Server. Some external queries write into the Temp db and every 2-3 days it is full and we have to restart the SQL database. I got who is active on it. And also we can ...
Sascha S's user avatar
  • 1
1 vote
0 answers
197 views

I am in the position of "acting" DBA since our previous DBA left. I haven't had any formal training or the like in SQL Server. I've searched on TempDB autogrowth terms and can find a lot of ...
Mark's user avatar
  • 11
0 votes
1 answer
184 views

I am selecting the name, size, growth, maxsize, and physical_name from sys.master_files and sys.database_files. I've noticed that when I do this the growth value will be reported as 8192MB on both ...
Tanner's user avatar
  • 3
0 votes
1 answer
287 views

We are currently implementing Always Encrypted for some columns in our database, but we are hitting a limitation we cannot explain when joining with temporary tables. We looked at Microsoft ...
Marco's user avatar
  • 1
0 votes
0 answers
1k views

It takes about 3 days for the transaction log (tempdb) to get to the size of a production database (~130 GB). It has come up recently (~2 weeks ago) and there had been no problems up to that point. I'...
pr0t's user avatar
  • 1
1 vote
0 answers
569 views

Unable to connect to SQL Server because: 'tempdb transaction log was full due to active transaction'. There was no way to login to SQL Server to troubleshoot. only option was to restart SQL Server. ...
anu's user avatar
  • 21
0 votes
2 answers
2k views

In T-SQL, it's normally best to name all constraints you create, including check constraints. alter table mytable with check add constraint myconstraint check (mycol > 0) If you don't provide ...
Ed Avis's user avatar
  • 1,622
0 votes
1 answer
1k views

In Azure SQL I can query what temp tables currently exist by using the query - select * from tempdb.sys.tables; However, I am not able to find who created these. Surely there must be a simple way to ...
TS74's user avatar
  • 61
0 votes
0 answers
333 views

An application vendor representative asked me to grant dbowner access to tempdb for their application login; to be able to create objects in tempdb without "#" or "##" prefixes. I ...
Farshid SQLServer's user avatar
  • 3
0 votes
1 answer
2k views

If there are less number of data files in the tempdb then we would have noticed that there could be issue in performance and queries will go for pagelatch_up waitType. And we need to manually add the ...
Adarsh H D Dev's user avatar
  • 588
1 vote
0 answers
560 views

My question is the following: Scope: I am currently using Microsoft Dynamics Ax 2012 R3 and my database has the following settings in the properties of the tempdb in Microsoft Sql Server 2012. ...
Pablo Sanchez's user avatar
  • 31
0 votes
1 answer
693 views

Thanks for reading, am a bit confused by this, think it is down to the size of TempDB, but unsure why it is happening in this case: I have a MERGE statement: DECLARE @LastUpdate DATETIME SELECT @...
Rickd43's user avatar
  • 1
0 votes
0 answers
140 views

I have a perfomance issue regarding the update of Synonym table. I have two SQL Servers: Server A (2017 DEV) Server B (2008 R2 DEV) and they communicate through linked Server (collation compatible = ...
Emka's user avatar
  • 350
0 votes
1 answer
442 views

Can I track or get a report every 30 minutes to sum the tempdb usage by user id for AZureSQlDW? Any suggestions are highly appreciated Thanks, Cherishma.
princy's user avatar
  • 3
0 votes
0 answers
471 views

I need to insert a very large number of rows into a table from a query, which puts me at risk of exceeding space on tempdb. I am in need of a solution that can avoid this issue, and am considering the ...
baobobs's user avatar
  • 723
1 vote
0 answers
2k views

Could not allocate space for object 'dbo.SORT temporary run storage: 142590947360768' in database 'tempdb' because the 'PRIMARY'..... This error message happened 3 days ago. How can I track down ...
Vel's user avatar
  • 75
0 votes
2 answers
94 views

How can I alter tempdb inside a select? I want a single query is that possible? SELECT cust_ac_no, ord_no, ref_no, net_svc_id, job_type, ord_status, ord_status_date, ord_crt_date INTO tempdb.....
Drin's user avatar
  • 21
0 votes
0 answers
206 views

Does anyone know what really happened here? All of the sudden database was not accessible. How to find transactions that cause tempdb filled up? Seems like it happened right after SQL profiler ...
Serdia's user avatar
  • 4,460
0 votes
0 answers
127 views

I have some jobs (stored procedures ) running in SQL server. The jobs are working good every day. The point is that the database should increase the size every day until next month but since 30/09 ...
Luxpbi's user avatar
  • 23
0 votes
1 answer
231 views

Below I have a code to get info about t-log backups for each database in the server. I want to exclude tempdb from this list. exec sp_MSforeachdb 'SELECT server_name, sysdb.name AS DatabaseName, bkup....
Rauf Asadov's user avatar
  • 113
0 votes
0 answers
1k views

I've been investigating high write latency figures in tempdb on a data warehouse server (about 600ms!). As part of that investigation, I've identified a query that is causing significant internal ...
FrugalShaun's user avatar
  • 176
1 vote
2 answers
1k views

Should I expect to be able to see tempdb tables in SSMS? e.g. If I run this code, should I expect to be able to see the table in SSMS? -- Drop the table if it already exists IF (SELECT OBJECT_ID(...
Allan F's user avatar
  • 2,346
2 votes
3 answers
2k views

In our solution we are creating some physical tables in "tempDB" for an activity. But recently we are facing an issue where these physical tables are getting deleted automatically. We would like to ...
Prakash's user avatar
  • 555
0 votes
1 answer
7k views

So my question is that even after the job runs and I'm enforcing the mdf (main tempdb file) to be shrunk to about 10mb or so why is it NOT doing it? I have tried to run this job after my most heavy ...
junketsu's user avatar
  • 531
-1 votes
1 answer
524 views

When I run the below code on a Temp database it does work, but not when I run it on a normal table. Very weird and not sure what I am missing. CREATE TABLE #Test (Username VARCHAR(20)) insert into #...
Etienne's user avatar
  • 7,261
0 votes
0 answers
2k views

I am facing a weird problem and I would be glad if anyone can help me. In our company we use SSRS to send daily Reports to the Same Management. We Have 3 Types of Reports, which of 3 is executed ...
Gio's user avatar
  • 1
4 votes
2 answers
33k views

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 ...
Navin's user avatar
  • 73
0 votes
1 answer
64 views

I am creating a form for an assignment where when the form is completed the data entered for each applicant is saved to a DB. Each property for the applicants is created in a Applicant class, some are ...
Boots09's user avatar
  • 3
0 votes
0 answers
136 views

following situation: We have an azure server with db and the TempDB drive ran full. At this moment we could find a process with much resources need in activity monitor. The process was not killable ...
Dominik's user avatar
  • 13
0 votes
0 answers
1k views

I am using a MERGE Query that is INSERTING over 800 Million records into a table from another table in the same database (conversion project). We run into this error below when it get's to this ...
MikeGen18's user avatar
  • 49
0 votes
0 answers
81 views

We saw some substantial log growth on both tempdb and one of the user databases. Our database is in Snapshot isolation and we take transaction log backups every 15 minute. First 'TL log full error ...
Nish's user avatar
  • 21
0 votes
0 answers
45 views

In SQL Server Management Studio, I have a bunch of temporary tables, they look like they were created by the system: Can I drop them? How can I get more information about them? For example, what ...
Joth's user avatar
  • 165
0 votes
0 answers
523 views

We find, for our application, locating TempDB files on RAMDisk improves performance significantly. But we don't want to allocate more RAM to RAMDisk than necessary. Nor do we want SQL Server to fail ...
Paul Davis's user avatar
  • 23
0 votes
1 answer
980 views

I have a TEMPDB database with 8 files but, they have different sizes which is not recommended, as follow: TempDB Files I have a plan to resize to the same size as recommended with 20GB each, the ...
RFerreira's user avatar
  • 69
1 vote
1 answer
956 views

I have a query which retrieves around 184K rows and stores into Temporary table. Now, In second query I am first retrieving all the data from Temporary table and pivoting it. While pivoting I am ...
Jay Desai's user avatar
  • 871
0 votes
2 answers
567 views

I'd like to get active user session count before rebooting instance, exclude any background sessions. Just thinking to use following query but I'm not sure how to exclude select session for tempdb by ...
Sachiko's user avatar
  • 964
1 vote
0 answers
36 views

My tempdb data file has grown significantly in size and I'd like to get an understanding of who made the change, this is a fairly recent event and there are no autogrowth events in the disk usage ...
Krishn's user avatar
  • 893
0 votes
1 answer
338 views

In sql server 2014, can we find the queries which spilled into tempdb using dmvs ?
jshikha's user avatar
  • 3
0 votes
1 answer
976 views

The following query causes the SQL Server 2014 TEMPDB.MDF to grow to 40Gb and takes about an hour to run. Similar queries for columns that are not NVARCHAR(MAX) take a few minutes. The table being ...
Jon Mason's user avatar
  • 3

1
2 3