DB Engine :: Cannot Remove One Last Data File From TempDB
May 19, 2015
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
Error:- (1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 5042, Level 16, State 1, Line 1
The file 'tempdev1' cannot be removed because it is not empty.
Note: =>I restarted SQLServer from SSMS and then ran same commands mentioned above ,......and getting same error... => I executed above commands and restarted services...no change...
I have scenario where I have process that loades data into SQL server 2012 database by doing some manipulation on data like sorting , aggregation, etc. Once this process is completed it's not free up the Tempdb space. If I restart the database, then it does.
is there any way (apart from shirking) to release space for Tempdb, like writing some post SQL queries to delete/ truncate the data and logs from temp db?
Added four new equally sized .mdf files to a production tempdb last night and restarted the instance. I was hoping to remove the initial file as it's not required and does not match the size+growth of the others. The option to 'remove' it is greyed out in the files tab of the GUI so I'm assuming that ALTER DATABASE [tempdb] REMOVE FILE [tempdev] will also fail.
Do I need to migrate any data in this particular .mdf before it can be dropped? Or would the simplest approach be to change the size of this file to match the others and drop tempdev3?
I have a SQL Server 2000 database with a primary data file (MDF) and a secondary data file (NDF). I would like to remove the secondary file and only have the MDF. Is this possible?
We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file V->Tempdblog having templog.ldf file
hello i make user(name was my_user) in sql2000 and also 2005 and access it in two datebase (mydb and tempdb) as db_owner after i restart my computer in tempdb access will just public and not db_owner and i cant create temp tables what should i do?
(i think make grant user and deny access may help but it dosent seems good)
We are running SQL server 2003 with SP3. I'm trying to shrink a data files with the emptyfile option so I can eventually remove the file using the alter database command. However, I get the following error message when I run the alter database command:
Error: the file PRADATA4 cannot be removed because it is not empty.
The file that I'm trying to remove still has 62 extents on it. I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3. commands that I'm running are as follows:
1) USE PRA DBCC TRACEON(8901) DBCC SHRINKFILE ('PRADATA4', EMPTYFILE) DBCC TRACEOFF(8901)
2) USE PRA GO ALTERDATABASE PRA REMOVE FILE PRADATA4 GO
We have an App server where Microsoft reporting services (report Builder 2005) is hosted and the backend Database server which is used by the reporting services.
The clients create very complex reports (usually towards month end) and run against the database causing the tempdb grow expenentially, leading to perfromance degradation and worst case space issues.Our only solution is to reboot the server.
There should be many systems which sould be having similar scenarios like mine. How to handle the scenario.usually in my database tempdb should be below 15GB... but it had grown to 60GB in some instances.
I try to find some feedback regarding setting the TempDB files on a RAM disk.Specifically I am looking for "production results" that could show the difference/benefit of such an usage.The tests on physical server and VM I already made have shown a boost in overall SQL Server 2012 performance on SQL Server instances housing data for SharePoint 2013 and Dynamics AX 2012 R2.Graphic below show differences between 5 different configuration on the same physical server:
- Physical HD: Server with local HD - Physical SANEX1PRD: Server with TempDB files stored on a low-end SAN - Physical SAN1: Server with TempDB files stored on a high-end SAN (around 100000 IOps) - Physical SAN1 Jumbo: same setup with Jumbo Frame activated on NIC and DB engine - Physical RAMdrive: with TempDB files stored on a 16 GB soft RAM drive within OS memory
Results were really impressive for the DB engine housing Dynamics AX data. My colleagues from the SharePoint team told me it also boosted a bit overall SharePoint performances but they did not have any baseline comparison to show.If you have some feedback, results, links, whatever I am interested.Indeed before setting this to all our SQL Server 2012 instances I y rather collect some *real world* feedback.
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
In on of the server tempdb is not releasing the reserved space after completion of data loads,as of now 99% of free space available in data file,we tried to shrink the datafile ,and space has not been released.
Could a simple update statement on a user database ever caused space usage in tempdb? Assuming the update statement fires no triggers and not using any temp tables?
IE:
User DatabaseA Update TableX Set col1 = X
Reason I ask is tempdb filled up and the only thing I could see running at that time was the update statement.
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.
Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?
SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) - Enterprise Edition (64-bit)
I have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space. This is because we have removed a large amount of old content from sharepoint. The log file is fine. I have 60GB left in my drive that host the database files. I would like to shrink the datafile to get disk space back. I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so).
When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping. So i have to kill the process. what i should do to reduce this data file.
why it keep using up all the free space in the drive when i try to shrink the data file?
I have used Extented event to monitor the occurances of TempDB contention on Production server . I found there are several entried logged in in 30 mints .Now I am trying to determin if Tempdb contention on PFS, GAM or SGAM page then I will decide if I need to increase the number of TempDB data files on Production server . Currently , There are 8 TempDB Data files configured on its separate Disks .There are Page_IDs I found in the extented events for Tempdb files -
Page_ID =1 for PFS page Page_ID = 2 for GAM page Page_ID =3 for SGAM page
but I found the Below Page_IDs and I know there is a formula that you can use to identify if page is PFS,GAM or SGAM ? How should I use this formula and what should I look for to determine if page is PFS,GAM or SGAM ? Is there any threshold value for the duration of TempDB contention occured ?
today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
I have an Excel file with .csv extension . it has on sheet with name Sheet1.
Now, I'm trying to insert this Excel data into one #temp table. I tried with syntax:
---------------- Exec sp_configure 'show advanced options', 1; RECONFIGURE; GO Exec sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
[Code] ...
But, I'm getting error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
If I'm executing for .xls file this statement is working finr and rows are inserted into #temp table. How to take excel file of .csv extension??
how to eliminate a key lookup from the execution plan
SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET NOCOUNT ON SELECT COUNT(ph.lid) AS Total FROM PLB ph WHERE ph.lPhysician = @Physician AND ph.BSF = CAST(0 AS bit)
Today we received an issue on an application database on internal free space on the DB is 0% that was designed with as below
name fileid filename filegroup size maxsize growth usage XX 1 I:DataMSSQL.1MSSQLDataNew XX.mdf PRIMARY 68140032 KB Unlimited 0 KB data only XX_log 2 I:DataMSSQL.1MSSQLDataNew XX_log.LDF NULL 1050112 KB 2147483648 KB 102400 KB log only XX_2 3 I:DataMSSQL.1MSSQLDataNew XX_2.ndf PRIMARY 15458304 KB Unlimited 0 KB data only XX_3 4 I:DataMSSQL.1MSSQLDataNew XX_3.ndf PRIMARY 13186048 KB Unlimited 0 KB data only XX_4 5 I:DataMSSQL.1MSSQLDataNew XX_4.ndf PRIMARY 19570688 KB Unlimited 204800 KB data only XX_5 6 I:DataMSSQL.1MSSQLDataNew XX_5.ndf PRIMARY 19591168 KB Unlimited 204800 KB data only
2 of the secondary data files had its autogrowth enabled to unrestricted with 200MB and 3 of the data files including primary had its Autogowth turned OFF. Application use is complaining that there is no internal freespace on the DB.
What fails to understand us is that when the Auto growth was already TURNED OFF on 3 data files ( 1 primary and 2 secondary ) still why was the application trying to increase the space on the .mdf and .ndf files; as well when the Autogrowth is TURNED ON on 2 of the secondary data files, why was the DB not able to expand these file groups when the autogrowth is already turned off on 3 of its other files.
What more data i need to ensure i submit an analysis to this.
The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.
I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy ON PRIMARY ( NAME = Dummy_data, FILENAME = 'D:....DATADummy.mdf', SIZE = 250MB, FILEGROWTH = 25MB ) LOG ON ( NAME = Dummy_log, FILENAME = 'D:....DATADummy_log.ldf', SIZE = 50MB, FILEGROWTH = 5MB ) ; GO
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
Hello all I am new here so I am sorry if this question has been asked 1,000,000 times.
First let me describe my environment:
VM-Ware ESX 3.01 3.0Ghz (Dedicated to VM) 3.0 GB RAM (Dedicated to VM) Windows Server 2003 SP2 SQL Server 2005 SP2 Spotlight for SQL Server Quest SQL Watch
Now the issue:
We are in current development of a new manufacturing system so the above system is just a development box at this stage. The problem that I am having is that the tempdb log file seems to grow out of control, and if I do not have time to shrink it, I have to bounce the mssql service in order to do anything once the disk is completly full. At one point I even made a job to shrink the log file every 15 minutes to try and prevent the uncontrolled growth.
I have researched this everywhere and it really is starting to get frustrating. I am highly leaning towards the application that is utilizing the database is heavily dependant on #temp tables. So I found this query to return me the top colums being used in the tempdb.
SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC
And I get the table that is using up the most rows.
SELECT *
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#4440A7FE'
After doing this I figured out that there is something that is writing to the tempdb around every 15 seconds about 100000+ rows at a time. And the alarming thing about it is that the record "rowmodctr" is already over 1 billion. So that leads me to beleive that this has modified rows over 1 billion times since I last rebooted the server a week ago.
So the main question that I have is. Is there a way to query what is inside that '#4440A7FE' table, so that I can pin it down to a session?
Also I have already said to the development team to use "derived tables" instead of temp tables, but they just fight with me all the time and blame the problem on hardware and configuration.
Please please help me with this as it getting really frustrating I am so sick of shriking that file. And sometimes it grows more that 3GB in less than 15 minutes.
Hello when I start executing a transact SQL process, my tempdb file is about 5Mb and It's log file is 1Mb, when the process finishes the log file never gets cleaned again... so It uses a lot of hard disk space and I can't run the process again...
Pd. the process has a "begin tran" and a "commit" at the end...
I checked quickly one network and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
