I was able to find a few posts on this topic, but none of them quite
seemed to fit the situation, so I'm hoping that someone else might be
able to help me here.
I have a client who is using SQL 2005 (sorry, don't have the exact
build with me). They run a weekly process which causes TempDB to grow
to over 100GB before it fails due to a full disk. Once it's grown to
that size we can't seem to shrink it again short of restarting the
server.
The database is set to Simple recovery mode and I believe that it is
set to auto shrink.
Here are some things that found out/tried:
DBCC SHRINKFILE (tempdev, 50000) does nothing.
DBCC OPENTRAN returns no transactions.
If I look in TempDB for any temporary tables, I get a couple dozen.
They all have zero rows in them though. I didn't think to look at the
columns that they contain, but maybe that will give me an indication
of their use. I used SELECT OBJECT_NAME(id), rowcnt FROM
tempdb..sysindexes WHERE OBJECT_NAME(id) LIKE '#%'
sp_spaceused shows that almost none of the space is actually being
used.
I've looked for reindexing operations in their code and didn't see
any, but there's quite a bit of code there. While there are some
operations against some very large tables, I didn't see any obvious
cartesian products or sorts either. Again though, there's a lot of
code and I haven't profiled much of it.
My plan right now is to reboot, set up a trace to track both file
growths and SQL statements and then see if I can find which
statement(s) are causing the TempDB to grow to be so large. Any
suggestions on additional things to track? Even given this though, I
don't know if that will help me with the shrinking issue, except to
possibly prevent it from being necessary in the future.
Any advice or suggestions welcome. Please let me know if I've left out
any important information. I always seem to forget at least one
obviously important bit of information. :-)
We're running our database on SQL Server 7 and we've setup the tempdb to grow automatically.Gradually,it has grown upto 11390 MB and the used space is only 14 MB.It's not shrinking when we used the shrinkdb option.We cannot move the tempdb to RAM as it's very big.Can anyone help us in this...
How do you shrink the tempdb in SQL Server 7 after you allocated a large space to it but later realized you allocated too much.... I need to reclaim the space allocated to it but when I try to change the size or do a shrinkdb it either tells me I cannot shrink it smaller than allocated size or it shrinks tempdb to current (which is too large)... I looked online but could not find anything...
Any suggestions or assistance would be much appreciated... up to my eyes in tempdb..... Bill.
Hello friends.. We are getting below error every day.we have job Shrink TempDB which runs every hour..but once a day we are getting this error.Is this error becasue of other jobs also running by that time which uses huge resource from DB..I cant understand why???.. can any body have an idea??
Executed as user: NT AUTHORITYSYSTEM. ...97031). The PageId in the page header = (1:1056620160). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297030). The PageId in the page header = (1:-1839155936). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297029). The PageId in the page header = (1:1057229472). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:297023). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:297022). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297021). The PageId in the page header = (1:1056467424). [SQLSTATE 42000] (Error 8909) Table error: Object ID -720718983, index ID 0, page ID (1:297015). The PageId in the page header = (2:1071640160). [SQLSTATE 42000] (Error 8909) Table error: Object ID -7207189... The step failed.
I have a install of SQL 2005 that is about 5 months old. The tempdb.mdf and master.mdf files have ballooned to a huge size almost filling my hard drive. How do I reset them to a reasonable size?
I have a problem with my TEMPDB Database.It gets extra size throughimporting data into one of my databases in same server.How can I shrink my TempDB Databse?Thanks
I'm running Sql 2000 with many users logged in. My tempdb database has grown to a large size 3/4 of it free space. Is there a way to shrink the database while users are logged on. I tried using the enterprise mgr. to shrink the tempdb database, but it did not work. How can I shrink this? It's using a ton of free space.
I have a tempdb that was created at 1Gig. I don't know why but I want to shrink it below the original creation size. Is there a way to shrink this file or create a new file and delete the old?
I have tried shinkfile and shrink database with no luck.
A customer on SQL7 has a 40GB tempdb (which is usually 2-5GB) which has filled the drive. ShrinkDb/ShrinkFile do nothing as the db has only 2GB of free space. How can I truncate the data or blitz tempdb and create a new (2-5GB) one (Stopping the server, deleteing the .mdf, starting the server creates a new tempdb with the same size as the old one!)
How do you shrink the tempdb in SQL Server 7 after you allocated a large space to it but later realized you allocated too much.... I need to reclaim the space allocated to it but when I try to change the size or do a shrinkdb it either tells me I cannot shrink it smaller than allocated size or it shrinks tempdb to current (which is too large)... I looked online but could not find anything...
Any suggestions or assistance would be much appreciated... up to my eyes in tempdb..... Bill.
SELECT session_id, SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count, SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage where internal_objects_alloc_page_count >10 and session_id> 50 GROUP BY session_id;
[Code] ....
Database MDF is 27806 MB and I tried to shrink but unable to shrink. It is production server.I do not want Restart sql server.There is no open transaction.
If you have problems shrinking a transaction log no matter what commands you issue, here's a way to shrink the tranlog:
1. Right click on the properties of the database and go to the options tab. 2. Change the recovery mode to simple. 3. Right click again on the database - go to all tasks - shrink database. 4. Shrink the database. 5. Change the recovery mode back to what you had it as.
I found this out by trial and error as I could not find any documentation on it and no matter what I tried, I could not get the transaction log to shrink.
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks! - Mike
I have a SQL 7.0 sp3 server and I am trying to shrink a production database for use on other servers. The database/log sizes are 9601mb / 138mb with 772mb / 128mb free. I truncate a table that contains binary data. After this the database/log sizes are 9601mb / 138mb with 6634mb / 111mb free. The next step is to use EM to issue a shrink db. After this the database/log sizes are 6807mb / 378mb with 9.56mb / 365mb free.
After the use of truncate the the data in the datafile is about 2967mb. After the shrink db the data in the datafile is about 6797mb.
The size of the data grew after the shrink db was issued. I looked at the tables and the space is largely associated with two tables that have a binary field, but the number or rows are still the same. I ran a checkdb just to make sure that there was not a problem and no problems were reported. I have used this process many times and this is the first time I have come up with these results. Any suggestions? Thanks, Glen
Actually I have a 8.5 Gb database but the correct size that I need to due disk space is 5 Gb. When I make a backup, the size of the dump is 4Gb, so I wonder if I create a 4- 4.5Gb my dump db will fit in it, but not. Other way is create antoher db with 4 - 4.5 Gb and transfer via sql-transfer or sql-bcp but I got a message that my device is too short.
So I ask : What are the correct ways to reduce the size of a database ?
We just migrated our DB from one SQL Server to Another. Both DB's used to be the same size at 11GB. The new SQL Server is a whopping 33GB, and I can't seem to find out why. The new DM has fewer tables (the data is identical).
Does anyone know how I can compact or shrink my DB, know why or how it grew so much and didn't go back down to normal size, and maybe where I can search properties or change settings to find out what is going on and prevent it from happening again.
I know MS Access has a shrink utility, but don't know where it is in SQL Server.
Newbie question about reclaiming some disk space on our SQL server.
We currently have a db that has the following stats:
space allocated: 34734 MB space free: 843.66
of this the current size of .mdf is 33877 MB and the space used is 33873 MB
the drive that holds this database is running out of space, what is the best method for me to use to reclaim some disk space?
I have read books online and am a little bit confused as to whether or not using shrinkfile on the .mdf will free up space to the OS? Do I need to do anything else before or after such as reindex? Also what is the overhead as we only have about 10% free on the drive.
I am trying to shrink a log file (.LDF), but it will not work. I am typing: BACKUP LOG "Database1" WITH TRUNCATE_ONLY dbcc shrinkfile ("Database1_log", 50) The microsoft site says this should work: http://support.microsoft.com/kb/272318/ ...but I can't get the file to shrink, at all? I'm sure the SQL services need to be running. I am logged in as my Administrator / sa. What could I be missing? Trying to shrink it too much? (THANKS).
A few of the databases I have are take up a lot of space, but when I look at the actual space that's being used, it's very small. I've tried shrinking them but they still remain at this bloated size. Is there any other way to reduce their size?
Txn log is nearly 10GB with only 60MB actually used. Disk has 150MB remaining. SQL 7.0 SP2. I've truncated the log several times. I'm aware that the active portion of the log can be at the end and have added many records to cycle the active portion round to the start of the log file. How big are these log segments?
I've run dbcc shrinkfile with and without a target size.
I've checked DBCC OPENTRAN. No open txns exist.
What else can I try? I've looked through similar postings and haven't spotted anything.
On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB. After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it. These are the statements I executed:
backup log ODS with truncate_only dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
