Shrinkdatabase 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
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 RelatedShrinkfile 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,
Dbcc 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
Install Sql 2005 Instance With Reporting Service 2005 On Sql 2000 With RS 2000 Server
Aug 18, 2006Hi
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
Please help us.
Regards,
Sankar N
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 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.
SQL 2005 Thinks A SQL 2000 Backup Is Corrupt, But SQL 2000 Restores Just Fine
Jul 19, 2007I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Any ideas?
Thanks
Mike Mattix
Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)
May 19, 2008Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedProblems With SQL 2000 And 2005 On Same Machine - Can't Connect On 2000
Mar 13, 2006Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
View 1 Replies View RelatedMerging 2000 And 2005 Databases, Save As 2000
Apr 30, 2008I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
TIA
Log Shipping From 2005 In 2000 Compatibility Mode --&&> 2000 Can I Do This?
Dec 18, 2007I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
Thanks in advance.
SQL 2000 To SQL 2005 Works For One 2000 Server But Not The Next
Jun 15, 2006
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
ANY IDEAS?
Sql 2005 Sql 2000 DB Diagrams, Re-install Sql 2000, Need Help.....
Jan 19, 2007Most of our sql servers ar still sql 2000. Our programmers created many sql 2000 database diagrams using EM. But they can not access them under sql 2005. (They now have only sql 2005 tools installed on their boxes.)
Question: can we reinstall the sql 2000 client tools on their boxes without affecting the current sql 2005 install on their boxes?
Question: is there any workaround negating the need to ihstall the sql 2000 client?
TIA,
barkingdog
VS 2005/SQL Server2005 - Direct Connection From Win Mobile Emulator To Desktop SQL Server 2000 Or 2005
Aug 10, 2007Hello Everyone,
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied.
SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then
MessageBox.Show("Open")
End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
Your advice and help is very appreciated
Thank you
Ika
Load Backup From 2000 To 2005 Or Recreate Structure On 2005?
Apr 23, 2008Hello,
I would like to ask you if there is better to recreate database structure on 2005 from 2000 and move data or to just load
2000 backup.
Currently I loaded the backup, but I am wondering if there might be slightly better performance on 2005 when recreating structure on 2005 to loading 2000 backup?
Does loading 2000 backup create 2005 binary structure?
Thanks for help in advance
How Do SQL 2000 Service Packs Play A Role In Upgrading?i.e.Can SQL 2000 Standard With No SP Be Upgraded To SQL 2005 Standard
Aug 2, 2006How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??
View 1 Replies View RelatedCan I Install VS.net 2005 / VS.net 2002 / SQL 2005 / SQL 2000 In One PC Win2000 Sp4?
Dec 1, 2005P4 2.8G Hz, 512 M RAM.thanks.
View 3 Replies View RelatedWhat Will Going From Latin1_General_Bin To Latin1_General_Bin2 In SQL 2005 Do To Replication From 2000 To 2005?
Aug 3, 2006If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
Thanks,
Grant