Shrinkfile Vs Shrinkdatabase
Nov 27, 2001Hi:
Need to shrink a few large databases and has anyone had more success with using DBCC Shrinkfile compared to DBCC Shrinkdatabase?
Thanks,
ADVERTISEMENT
SHRINKDATABASE.....
Nov 27, 2007I 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
Shrinkdatabase Questions
Feb 26, 2002I 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
DBCC Shrinkdatabase
May 1, 2000Does 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 RelatedDbcc Shrinkdatabase
Sep 10, 2003When 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
Dbcc Shrinkdatabase
Sep 28, 2006I'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.
SHRINKDATABASE And LOG File
Oct 22, 2004Hi 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 RelatedDBCC SHRINKDATABASE
May 12, 2008Hi,
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
DBCC SHRINKDATABASE
Feb 15, 2006Hi,
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
Shrinkdatabase Progess
Jul 28, 2007Hi there,
How can I see the progress of DBCC SHRINKDATABASE on SQL Server 2000?
Thanks in advance.
Regards,
MKarumuru
DBCC SHRINKDATABASE
Jun 15, 2006Has 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
DBCC SHRINKDATABASE Duration
Nov 8, 2006Is 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 RelatedShrinkdatabase In Sql 2000 Vs Sql 2005
May 17, 2007what 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 RelatedShrinkdatabase Taking Forever...
Mar 17, 2008Hi 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!
Advice On DBCC SHRINKDATABASE
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
HELP: Shrinkdatabase Not Freeing Unused Space
Feb 7, 2002I 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
Tran Log Backup Conflicts With Shrinkdatabase?
Apr 2, 2008Hi:
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
How To Capture Results Of A DBCC SHRINKDATABASE?
Feb 28, 2008So, 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
Dbbs Shrinkdatabase Execution Times?
Oct 26, 2007Hi 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
Before Full Backup Is It Ok Running DBCC SHRINKDATABASE
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.
DBCC SHRINKFILE
Apr 5, 2001Hi All,
I am having a problem with "dbcc shrinkfile (datafile, emptyfile)". It does not totally empty the whole data file, any idea? It seems like it always leaves 0.06MB behind.
Dbcc Shrinkfile
Jul 5, 2001Hi
Any info on dbcc shrinkfile would be useful.
I have tlogs that are over 3G.
What are the adverse affects of running dbcc shrinkfile.
Regards
S
Shrinkfile Not Working
Oct 8, 2000I started a database with a log at size 250MB. The log expanded to 2GB before I truncated. It is has a few MB of data and the rest empty. I can't get it to shrink. I've tried DBCC SHRINKFILE (filename) and the GUI front end for shrink database. The data files rearranged and shrunk, but the log file did not. I've had this before and have never figured out what's wrong. Can't get it back to original size at create.
View 2 Replies View RelatedDBCC SHRINKFILE
Oct 18, 1999Hello,
I am wondering if there will be any performance issues concerning executing the DBCC SHRINKFILE action against a production database? So far all I have read, and it would make sense, would be to disable the scheduled Transaction Log Task to ensure that no backup is running against the database. Does anyone else have any more or other information about this subject?
Thanks in Advance, Daimon
Shrinkfile Hangs Up
Apr 19, 2000Anyone else had this problem ?
I've run dbcc check commands on a 5Gig database which has 2 Gig of unallocated space, with no problems with the tables or database.
However when i try to run dbcc shrinkfile ( on the DATA file ) from Query Analyser the command just runs indefinitely with no response.
Duncan
PS SQL 7 sp 2
Dbcc Shrinkfile
Nov 28, 2001Hi!
We made a mistake to run dbcc shrinkfile at the same time with database backup. We stoped and restarted services and run dbcc shrinkfile again. No success.
System doesn't shrink file at all.
What we can do?
Thank you,
Elena.
Dbcc Shrinkfile
Oct 19, 2006Hi All,
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.
Dbcc Shrinkfile
Dec 29, 2005I used dbcc shrinkfile to shrink transaction log, but it worked for only one day. When I checked the properties, transaction log was back to the size I started with. TL was 1586 MB and I set the target size to 1 MB. Any idea why it happened?
View 1 Replies View RelatedDbcc Shrinkfile
Oct 19, 2006Hi All,
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.
DBCC Shrinkfile
Aug 29, 2006Hello,
I am attempting to shrink the disk size of my db log files, using the following command;
dbcc shrinkfile (filename,truncateonly)
The command completes successfully, but the log file size remains unchanged. Am I missing something here? Should the file size be reduced?
DBCC ShrinkFile
Aug 1, 2007
How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?
Thanks
DBCC Shrinkfile/database
May 14, 2001Hi,
Are there any effects(negative) of running dbcc shrinkdatabase/file on a production box at low/high usage time or high/low activity period of db?
TIA
DBCC SHRINKFILE Not Working
Jan 25, 2001I have a log that has grown unchecked for a long time. I truncated it, used DBCC SHRINKFILE on it, and backed it up. It has not shrunken. I still have a database with an allocated size of 506 MB with 435 MB unused.
I have seen messages that others have posted where they have used DBCC SHRINKFILE without success. It was recommended that they use the sp_force_shrink_log script that is available on the www.sqlpass.org web page.
Has anyone used this script that can tell me how and where to run it? I'm new to this. I tried creating a script using Jobs in the Enterprise Manager and got an error that the command was too long.
Need help!
Thanks,
Veronica