Shrinkdatabase Questions
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
ADVERTISEMENT
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
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 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
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
Mar 3, 2006
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
View 1 Replies
View Related
Aug 27, 1999
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
View 1 Replies
View Related
Sep 2, 1999
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure.
The procedure will be called from an ASP page ...
My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie:
exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
thanks for any help on this subject ...
Rob
View 1 Replies
View Related
Aug 27, 1999
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
View 1 Replies
View Related
Aug 4, 1999
I have two questions,
1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any,
2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance!
Daimon Russell
daimon_r@hotmail.com
View 1 Replies
View Related
Nov 5, 2007
Hi, does anyone know how to explain how SQL transactions get mapped into TDS and TDS gets mapped into TCP/IP packets?
Or can you please point me in the right direction? I need to figure out how TDS and TCP/IP relate?
We're using replication and are having some latency issues and I'm trying to find out how SQL handles TDS and TCP/IP etc...
Thanks for your time.
View 6 Replies
View Related
Feb 7, 2002
1. When we create DTS in SQL Server through DTS designer, where are they stored physically?
2. What would be the best way to modify a DTS without using DTS designer?
3. Is there any other way to create DTS apart from DTS designer and Visual Basic?
4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
thanks,
sanjay.
View 2 Replies
View Related
May 4, 2001
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
View 3 Replies
View Related
Jun 17, 2004
I have two questions.
1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases?
2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
View 1 Replies
View Related
Jun 21, 2004
Hi All,
I am new in SQL SERVER 2000.I have few questions -
1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG?
2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE?
3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP?
4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
View 1 Replies
View Related
Aug 31, 2004
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use
'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)?
suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
How should I do?
Thanks,All.
View 4 Replies
View Related
Sep 9, 2004
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
View 3 Replies
View Related
Mar 15, 2004
I have to judge the aptitude of a few trainees for SQL DBA training ....
Can the gurus suggest some questions for the same ....
View 14 Replies
View Related
