Shrinkdatabase Taking Forever...
Mar 17, 2008
Hi all,
2 weeks ago I deleted about 200GB of data from a 300GB+ database. It's a custom DB we want to use to test few things. We wanted a smaller size DB for our testing and since we didn't have any we grabbed a production backup, removed sensitive data and ran a large archiving script on it... Anyway so far so good but our data file was still the same size as before.
So we started a shrinkdatabase... it has been running for 2 weeks now! After about 1 week I interrupted the shrinkdatabase process and ran a
dbcc shrinkdatabase('DB', truncateonly)
just to see if the data file will get reduced a bit or not. It did get reduced by about 20GB. I assume that
dbcc shrinkdatabase('DB', 0)
has free up enough pages at the end of the data file so a truncateonly was able to free up some space... Anyway after this we started the
dbcc shrinkdatabase('DB', truncateonly)
again... still running...
The database was never shrank before and every index is highly fragmented... Is that why it's taking so long? Am I actually going to have to wait for another few weeks before that thing finishes??
Anyone has experience running shrink on large DBs?
thanks!
View 14 Replies
ADVERTISEMENT
Mar 1, 2006
I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage, dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM dbo.tblErrorLevel INNER JOIN dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.
View 2 Replies
View Related
May 14, 2008
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..
; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update
thanks
View 8 Replies
View Related
Aug 23, 2007
what can I do?
all queries that used to work are taking forever now???
what can I do?
is there a max size for the db that I may have reached
please advise asap
View 20 Replies
View Related
Dec 14, 2007
I am using RS 2005 and SQL Server 2005. I am having a table with about 6 million rows. I am extracting about 2 milliion rows for a report. When i run the report as a single user the report comes up in 6-7 minutes but when i run the report with 2 users the report takes forever to come up.
The statistics are different each time sometimes 19 minutes sometimes 30 minutes. The report connects to the db with the same dbuser id for both the people running the report. The stored procedure being invoked uses temp tables and also indexes are created on the fly for these temp tables.
The moment 2 people are running the report and when i run an SP_WHO2 i see that one process id that is being started by reportserver blocks another process being run by reportserver.
Timeouts are not happening the report justs goes on forever to come up. Any help? Also if you need any more information please do let me know I will be glad to give them.
The report is a matrix report and there are 4 levels of grouping on the report.
Thanks in advance
View 1 Replies
View Related
May 3, 2000
I have a simple update/initialization query (set integer column = 0 on all rows) that's been running for over 28 hours. There are just over 27 million rows in the table. In current activity it shows that the transaction is open but it's sleeping, and in locks it shows 1 DB S mode lock, 766 page X mode locks, 1 page U mode lock, and one table X mode lock. Server is 7.0 with 1.7 gig ram. Anyone have any ideas as to why it's taking so long? Table is about 7 gig in size; can't get to it in Enterprise Manager without locking it up...
View 3 Replies
View Related
Apr 9, 2008
Hi there i have a sql server 7 database that i copied across to another server this time running windows 2003 and sql server 8. I have a routine that runs every night on each machine. on the old machine it take about 2 hours to do. on this new machine it is taking up to 5.5 hours to do the exact same job. the results are the same but the time delay could become an issue later on so i would like to nip it in the bud now.
Does anyone ahe any suggestions as to why the code would run so much slower on a newer and better spec machine.
I have copied everything across so there is no difference in tables or stored procedures. is there an optimisation tool i can run ??
has anyone got any ideas ?
View 6 Replies
View Related
Jan 15, 2008
Hi All,
I have 2 reports that report on baiscally the same thing, just group differently.
Report 1 groups summary phone call stats by Department, Day, and Hour - which are all drop down options.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see daily stats ... the daily stats can then be expanded to see the hourly stats.
Kinda Like:
-------------------------
-Department 1
-10/1/2007
12:00 AM
1:00 AM
+10/2/2007
+Department 2
-------------------------
Report 2 shows the same summary stats by department and extension - which is also a drop down option.
This means that the department summary stats are shown when the reports are rendered and can be expanded to see summary stats for each extension.
Kinda Like:
-------------------------
-Department 1
Ext 2005
Ext 2008
+Department 2
-------------------------
The queries for these reports run from the Management Studio in about 10 seconds each with the Report 1 query returning about 800 rows for the month of October 2007 and the Report 2 query returning about 30 rows for the same date range.
When the reports are rendered, Report 1 (with 800 rows) is rendered in about 20 seconds, while Report 2 (with only 30 rows) takes about 5 minutes to render.
The reports themselves are very similar, with the only difference being the grouping. It is weird that the report that returns the samller Dataset is actually taking longer to render.
One thing I did try was running the queries from the Data tab of the .rdl files (in visual studio) and the query for Report 2 took about 4 minutes to return data, while (as I mentioned above) it ran in about 10 seconds in Management Studio.
Has anyone else run into this?
Any suggestions?
Thanks in advance!
-Matt
View 1 Replies
View Related
May 16, 2008
USE GLPDEMO
GO
select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'
DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)
DECLARE TCursor CURSOR for
SELECT TriggerName, TableName from Temp_TablesAndTriggers
OPEN TCursor
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
WHILE @@FETCH_STATUS = 0
select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable
EXECUTE sp_executesql @exestr;
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
CLOSE TCursor
DEALLOCATE TCursor
--DROP TABLE #Temp_TablesAndTriggers
View 4 Replies
View Related
Nov 27, 2007
I want to shrink the log file in SQL Server 2005 (SP2).
What is the bestway in 2005? Any enhancements in SQL Server 2005?
Thank you,
Smith
View 4 Replies
View Related
Feb 26, 2002
I just inherited a database nightmare - and I've got a few questions I hope you folks can help with.
I ran a shrinkdatabase command from the enterprise manager (I should have used DBCC I realize now), and then my session died. The process is still going - but this is a huge database, and I'd like to know how long it will run. Is there any way to translate physical io (or anything else) to % complete?
This is being run to open up space on a disk array that is completely full. However, since kicking that off, additional storage has mysteriously been made available. <sigh> I assume that killing the shrink wouldn't involve rollbacks - but might leave the database in an unpredictable state. Is this correct?
Any other recommendations to speed this along? And, yes, I've got all the users off the system.
Thanks in advance for you insights...
Bart
View 1 Replies
View Related
May 1, 2000
Does anyone have any idea how long DBCC SHRINKDATABASE takes to run? I have a 20GB database with 10.5 GB used. I know hardware is part of the equation, but I don't know the specifics of the machine this particular DB is on, other than it is an 8 cpu compaq.
View 1 Replies
View Related
Nov 27, 2001
Hi:
Need to shrink a few large databases and has anyone had more success with using DBCC Shrinkfile compared to DBCC Shrinkdatabase?
Thanks,
View 1 Replies
View Related
Sep 10, 2003
When using the above comand on our database, I receive the error:
Transaction (Process ID 81) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Is that because we have users in the database as Im trying to shrink it ?
I re-ran the query and received the same messages.
thank you
View 1 Replies
View Related
Sep 28, 2006
I've been testing this in our test environment. Our database has grown to 18GB so I want to make it smaller.
I used
DBCC SHRINKDATABASE (dbname, TRUNCATEONLY)
because i want to free up space that the file takes up on the server. Am I misunderstanding this? Should I not expect my .mdf to shrink?
Thanks.
View 6 Replies
View Related
Oct 22, 2004
Hi all, after using "DBCC SHRINKDATABASE (MyDB, TRUNCATEONLY )" command my log file stoped growing. It still remains 1024KB after 4-5 days of activity. What might be the problem?
View 1 Replies
View Related
May 12, 2008
Hi,
should i use the SHRINK DATABASE maintenance plan once a month on all USERS DB?
if so what parameters should i put this the "Shrink Database when i grows beyond XXX MB" and the "Amount of free space to remain after shrink"
THX
View 2 Replies
View Related
Feb 15, 2006
Hi,
I have strange problem:
1) When I run "DBCC SHRINKDATABASE" (from my application) on computer with SQL Server 2000, it works fine.
2) When I run "DBCC SHRINKDATABASE" (from my application) on computer with MSDE that is connected the network with other computer with SQL Server 2000, it works fine.
3) When I run "DBCC SHRINKDATABASE" (from my application) on computer with MSDE that isn't connected the network, it stops response. I have wait for 15 minuts but it still doesn't work, neither the database is clear before. Why?
4) Also when I run "DBCC SHRINKDATABASE" (from my application) on computer with MSDE that is connected the network without other computer with SQL Server 2000, it stops response. I have wait for 15 minuts but it still doesn't work, neither the database is clear before. Why?
May be the "DBCC SHRINKDATABASE" need some DLLs that installed on SQL Server 2000 only and doesn't deliver with MSDE? May be this is SQL-DMO or/and SQL-NS?
My appliation created by C# in VS.NET 2003. I am using OLEDB.
Thank's
Alexei
View 1 Replies
View Related
Jul 28, 2007
Hi there,
How can I see the progress of DBCC SHRINKDATABASE on SQL Server 2000?
Thanks in advance.
Regards,
MKarumuru
View 5 Replies
View Related
Jun 15, 2006
Has anyone used the
"DBCC SHRINKDATABASE"
in a VB.NET project to shrink a DB.
If YES,
Please, if you dont mind, paste the code in this forum.
Tnx
View 4 Replies
View Related
Nov 8, 2006
Is there anyway to tell how long this will run for -- or how far it has got? I have a large database that has just had most of the data removed. The command has been running for 8 hours and I have just stopped it to let something else run quickly. Any way of telling how much longer it will take?
View 7 Replies
View Related
May 17, 2007
what is the difference in shrinkdatabase between sql 2000 and sql 2005. i am just doing a plain shrink, no reorg. it runs very fast in 2000 and runs forever in 2005.
View 1 Replies
View Related
Mar 3, 2008
Hi everyone:
I have a database that appears like it's much bigger than it should be. Looking at its properties in Enterprise Manager, it's 13GB big, but it says 7GB is available in free space. It's like it's grown, data was deleted, and it was never shrunk back down.
So I'm considering running a DBCC SHRINKDATABASE but am worried about the ramifications. Here are my concerns:
- Is the data in the database in any danger? Is the SHRINKDATABASE function safe?
- Can SHRINKDATABASE be run with the DB still in use? I'll run it after hours, but want to know if I need to put it in admin mode
- Will SHRINKDATABASE even do what I want?
- I've read the SHRINKDATABASE can fragment indexes. Is this true? If yes, how do I avoid it?
Thanks!
Norm
View 6 Replies
View Related
Feb 7, 2002
I am working with a large database that has its tables stored on a secondary filegroup. I'm trying to shrink the size of the files but I can't seem to get the system to free up the unused space. I've tried shrinkdatabase and shrinkfile both with and without the truncateonly option. Has anyone else had this problem? Is there a workaround? Any help would be greatly appreciated.
Thank you.
Cathy
View 2 Replies
View Related
Apr 2, 2008
Hi:
I have maintenance plan on DBABC backup log to .trn job to run every 90 minutes (daily).
in order to keep the log file small, I also set up a job (T-SQL) to run at 4:15 am to backup log ABC with truncate_only, then run dbcc shrinkdatabase (DBABC, 10)
it looks "backup log ABC with truncate_only" has conflicts with the every90 minutes backup transaction log.
Question: could I keep the backup transaction log every90 minutes, but still could shrink the log file. The log file is growing very fast.
Or I have to use differential backup instead of backup tran log?
thanks
David
View 4 Replies
View Related
Feb 28, 2008
So, basically I'm trying to do an insert into exec(dbcc shrinkdatabase)
Code Snippet
DECLARE
@SQL VARCHAR(1024)
,@DBName VARCHAR(512)
SET @DBName= 'admin'
IF OBJECT_ID('tempdb.dbo.#ShrinkDB') IS NOT NULL
DROP TABLE #ShrinkDB
CREATE TABLE #ShrinkDB
(
DbId INT
,FileID INT
,CurrentSize BIGINT
,MinimumSize BIGINT
,UsedPages BIGINT
,EstimatedPages BIGINT
)
SET @SQL=
'
INSERT INTO #ShrinkDB
(DbId,FileId,CurrentSize,MinimumSize,Usedpages,EstimatedPages)
EXEC(''DBCC SHRINKDATABASE(' + @DBName + ')'')
'
EXEC(@SQL)
SELECT * FROM #ShrinkDB
and receive the following:
Cannot perform a shrinkdatabase operation inside a user transaction. Terminate the transaction and reissue the statement.
I've tried adding a begin tran and commit tran around it, doesn't help ...
Is there any way around this? Is there any other way to capture the output of a shrink database from a procedure perspective?
Thanks
View 4 Replies
View Related
Oct 26, 2007
Hi guys,
I identified a problem today with one of our development DB's where tempororary tables were not being cleaned up by some stored procedures, and this lead to a large tempdb size (about 2 gigs data and a translog of 25 gigs!).
I'm currently running a dbcc shrinkdatabase on it, but its been running for over an hour now. In my experience with smaller DB's, this process normally takes a few minutes. Can anyone give me a ballpack figure as to how long I can expect this to run for? Are we talking an hour, a few hours, a day, a few days?
Thanks in advance
Matt
View 4 Replies
View Related
Apr 16, 2008
Hi
I have a full backup Job in SQL Server2000 server(designed by some body).
Step1.DBCC SHRINKDATABASE
Step2.DBCC SHRINKFILE -->It is shrinking log file
and
BACKUP DATASE()
Is it OK Shrinking Database and log files before full backup..
Your advice is appreciated.
View 10 Replies
View Related
Jan 9, 2008
I created a profiler to run on a remote server in local. Then I logout. After two hours, I login again. The profiler was closed. I don't know when and why. Did someone have same problem? Is this normal?
Thanks
ZYT
View 8 Replies
View Related
Sep 30, 2004
I'm doing an update on a table with about 113m rows, the update-statement is fairly simple: update tab set col = null where col is not null.
The col column is mostly null.
Sysprocesses shows three rows for this statement: 1 CXPACKET (its a dual processor, 2000 box with sp3 installed), 2 PAGEIOLATCH_SH (waitresource is filled). My guess would be that the where-clause is executed in a seperate process blocking the update.
I changed the statement into update [...] set col = null; sysprocesses shows one row with PAGEIOLATCH_SH. Executing forever.
I checked other processes including those outside sqlserver but none are using the db, let alone accessing the table involved. Even restarted sqlserver to be sure there's no dead process blocking the update. Didn't help.
So I added a search condition to the where-clause, involving a clustered index in order to reduce the rowcount. The execution plan shows a 97% hit on the clustered index, but sysprocesses shows the three rows again...
So far the profiler didn't help me out either: there's a SP: CacheInsert on the update-statement... then nothing.
What should I do?
View 6 Replies
View Related
Dec 9, 2004
I have a job that is running for 2 days straight and the status reads: Performing Completion action.
I have tried to disable/Stop the job, but can't.
I have also tried to start the job which it won't because it is still running.
Is there a table that I can manual delete the schedule # or something along those lines to start all over again.
Thanks
Oh the normal time for the job to run is only 7 seconds.
Lystra
View 2 Replies
View Related
Feb 22, 2006
Hi, sorry if this has been asked before but I'm pretty strapped for time...
I have two tables: [photos] and [photoFolders]
[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...
[photos]
photoId
photoName
folderId
photoDescription
cUserId
[photoFolders]
folderId
folderName
eventDate
cUserId
Any help would be GREATLY appreciated
We're all going to hell... I guess I'll see you there!
View 15 Replies
View Related
Jan 13, 2006
Lately I did a mass update on all our scripts and addeddbo. in front of all tables and other objects.There is a function that returns a table and the function isI think 300 lines. There are a lot of UNIONs, EXISTS,NOT EXISTS, etc...Anyhow there's a single place where if i add dbo. in frontof the table this function which is being called from an SP,when i run the SP from the QA or from the ASP applicationit runs forever. As soon as i remove the dbo. again it runsin 6-8 seconds. Here's the thing this same table reference existsa few lines below in the 2nd UNION for example andhaving a dbo. in front doesn't cause any problems.Even more confusing if i add Databasename.dbo.TableNameand run it again, i don't run into any problem.So it's almost like if i specify dbo. in front of the table, somehowSQL Server or our code is getting lost and searching for thistable in other databases?Has anyone run or seen such a problem?I am sure I can make changes to the code and end upwriting a different code but before I make changes I wouldlike to find out more about my mystery problem.I am running SQL 2000 SP4 and the same problem occuredon two different machines. Win 2000 Pro and Win 2000 Server.Any ideas, suggestions?Thank you
View 4 Replies
View Related
