Will Database Shrinking Affect The Data?
Aug 6, 2004Hi all,
does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?
Thanx
ADVERTISEMENT
Shrinking Database File After Removing Data
Apr 20, 2007Hi there,
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Kind Regards
Can Too Many Tables In The Database Affect Performance?
May 23, 2008We have more than 2000 tables in the database. Can existence of so many tables affect the performance on SQL SERVER 2005?
View 1 Replies View RelatedAny Affect With Database If I Upgrade MS SQL7 To SQL2000 ?
Mar 17, 2001Im using MS SQL7 as database software ,but now I plan to upgrate SQL7 to SQL2000 . I would like to know that are there any affect on the old database that already use with sql7 after I upgrade to MSSQL2000 ?
Thanks
Does A Large Amount 'image' Data Affect Overall SQL Performance?
Jul 20, 2007Recently we added a new table into our SQL2000 database specifically to store scanned in images of documents. This new table contains a PK field, a couple of datetime fields, a couple of char(1) fields and one 'image' field.
Before adding this table, the database size was approx 6GB. Six months after adding this new table, the database has grown to 18GB - 11GB of this is due to the scanned in images.
Would this new table affect the SQL performance with regards to accessing other data in the database that has nothing related to the new table?
If so, would moving this new table into it's own database be recommended?
Thanks
Rod
DB Engine :: Does It Affect Database Integrity Of 2 Databases Have Same Logical Name In Instance
Aug 13, 2015In our production we have a database by name MyDb.The application team wanted another database by the name MyDbOld which contains the data of Mydb 1 month old. So I created a database MyDbOld in other instance(test) and restored MyDbOld from the one month backup of MyDb in production server. In test instance I backed MyDbOld as MyDbOld.bak...
In the production instance I created a new DB by the name MyDbOld and restored the MyDbOld in the production from the backup of MyDbold.bak in the test instance. Now I have 2 databases in the production instance ie MyDb and 2)MyDbOld. However I find the logical names of both the databases to be the same although the database name and the physical file name to be different.My questions are-:
1) Does it in any manner affect the integrity of the 2 databases if the 2 databases are in the same instance and have the same logical name?
2) Would the dml or data retrieval operations in the 2 databases have any conflict in any manner what so ever?
3) Having a unique database name, and unique file names for the physical files for any database in an instance what is the purpose and significance of additionally having a logical name for a database?
4) Which I could restore a database from the backup of another database in the same instance and at the same time change the logical and physical name of the files to correspond to the new database.
Shrinking Database
Apr 13, 2008Hi all,I've deleted a lot of albums but the size of personal.mdf isn't shrinking - how do i go about acheiving this? I've tried to shrink it in sql management express but the file is read only..
please help!
thanks
Shrinking Database
Jan 8, 2001I have a 1.2 gig db which has truncate checkpoint set and over 850 meg of free
space. WHen I shrink it through either DBCC or EM I see no change. ANy
ideas?
Thanks
Shrinking Of Database
Dec 30, 1999i havae a database size of 1000mb with used as 507.50 and unused as 492.50. i tried with commands like dbcc shrink file and dbcc shrink database but i could not get the result as what i need. I need to shrink in such a way that it should have 25% of the used space as free space. could any one help
View 4 Replies View RelatedHelp For Shrinking Database
Jun 20, 2006Hi guys.I have a huge database file. When I run sp_spaceused this are theresults:db_size 1337,31 Mbunallocated 14,62 Mbreserved 1088456 Kbdata 258992 Kbindex_size 6224 Kbunused 823240 KbNow, when I run dbcc shrinkfile(database_name, 50) or whatever value ofpercent, it says there is not enough free space in db.Please advice how to shrink the size of that file and why is it so big?Thanks in advanceZvonko
View 3 Replies View RelatedNotification After Shrinking Database
Mar 26, 2001Hi All,
I am working on a client product that shrinks the database in MS SqlServer. After shrinking the database files, it does not send any email notification to the users.
My question is: Is there any way in SqlServer to sense that the database has been shrunk and send a notification to the users (may be through an Alert or a Job or something). Please let me know the steps to do that.
Thanks
Sri
Shrinking Database Problem
Jun 4, 2003I have a SQL 2000 database that I would like to shrink.
Recovery Model = Simple
Data file space allocated = 78 Gig
Space free = 41 Gig
Similar problems with Trans logs sometimes
As I understand it, I can't shrink the database below the allocated size. How do I get that space back ?
Any extra insights and explanations about this topic in general would be appreciated .... Thanks in advance.
Error While Shrinking Database
Oct 13, 2005Hi,
When I am trying to shrink a database, I got the following error.
Executed as user: NT AUTHORITYSYSTEM. ...he PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491894). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491893). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491892). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491891). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491890). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491889). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:490615). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8...
Please help me to resolve this issue
Thanks & Regards,
Eldho Abraham
Shrinking Database Files
Jul 23, 2005I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?
View 6 Replies View RelatedShrinking Database Log Used For Mirroring
Nov 22, 2006Can anyone offer some advice on how to reduce the size of a databaselog. The database is mirrored on another server using the logs. Thanks
View 5 Replies View RelatedProblem Shrinking A Database
Jul 20, 2005Hi all.I'm having trouble shrinking a database. In short, using the SQL QueryAnalyzer,I enter the following commands:DBCC SHRINKDATABASE (database, 80)GOThe command returns the expected 'DBCC execution completed.' response, butthe databaseremains the same size when I run 'sp_helpdb database'. Am I missingsomething?Thanks in advance,Jaeger
View 2 Replies View RelatedShrinking The Master Database Logfile
Feb 9, 2001Hello
Can anyone help, I have a master database on my server which has an extremely large log file size. I need to shrink this down and so I have tried using dbcc shrinkfile (master, 50, truncateonly) and dbcc shrinkdatabase although it does not seem to reduce in size
Please can anyone help
Resizing/ Shrinking Database Devices
May 3, 1999Is there a way to shrink database devices in SQL Server 6.5 Other than dropping and restoring?
View 1 Replies View RelatedSQL 2012 :: Shrinking Database For Dev Environment?
Nov 26, 2014I've read lots about why you shouldn't normally shrink databases in posts such as this:
[URL]
But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.
I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.
Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.
Shrinking Database Files In Production
May 15, 2008Hi,
We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.
thanks
Shrinking SQL2000 Database W 20 Datafiles.
Jul 23, 2005Hello,I am trying to clean up a database I inherited.I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. Ihave been able to remove unneeded data and am now trying to clean up.If I do a Shrink on each datafile would able to recover on average 2gbout of 4g, however I would prefer to have 10 full datafiles and 10empty. (or better yet 5 full 8GB datafiles and 15 empty)Can someone point me in the right direction on how to move the dataaround so that dont have 20 partially filled datafiles?I have noticed that I can shrink a single file and use the "empty thefile option (and move data to other files in the group)." option. Ihave already done this to the last 2 datafiles as a test but not surehow to do this on a large scale. I have also set the 1st 10 datafilesto be able to grow to 8 GB.For lack of a better way to say this, Is there a way to defrag orreorganize the data ables so everything "moves to the front".BTW, I have already run a maintenance plan to reorganize the data andindex pages.
View 2 Replies View RelatedDB Engine :: Database Files Shrinking
Jul 6, 2015I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?
View 7 Replies View RelatedProblem Shrinking Data File
Dec 3, 2007(SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.
What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.
Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.
(DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)
Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.
Thank you in advance for any input!
DBCC Shrinkfile Not Shrinking Data File
Jan 31, 2002On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
View 1 Replies View RelatedTransact SQL :: Shrinking Data Files In AlwaysOn
Jun 21, 2015I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCCÂ command but it is taking too much time. is there any other option for deallocating the space.
View 7 Replies View RelatedWhat Could Affect 'allow Updates' Option
Mar 19, 2002every morning I have 7-10 identical messages in error log
1.
Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2.
Error: 15457, Severity: 0, State: 1
3.
Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
It is standby server with custom log shipping and DTS transfering logins every 15 min.
What could cause this error ?
Tempdb - Does It Affect Performance ?
Aug 7, 2001We think we're having performance problems, and among the areas of investigations is the tempdb database. Since it resets itself after SQL is restarted, is there a way to find out how big it has grown in the past ? Does leaving it at the default size cause a performace hit ?? Right now it's 8.75 MB, with 7.38 MB available, which sounds pretty harmless.
Any thoughts ?
SQL Profiler - How Does It Affect SQL Performance?
Jun 23, 2006Everywhere I read, it states that running SQL Profiler can affect performance of your SQL Server. My question is - how much of an impact will it really make? Will I see a 1% degredation in peformance? 5%? 50%? I haven't been able to find a good answer. We currently have SQL Profiler running all day long for almost 3 years, and the databases are still humming.
Is it the amount of data you are requesting from the trace that affects performance? There are some compliance tools out there (Idera Compliance Manager, IPLocks, etc) that run a profiler trace to get data. There are other DBAs in my organization who don't want to use them because "profiler traces will degrade my SQL Server performance". How true is this really.
Any help I can get would be extremely appreciated.
Thanks.
Does Multiplication With 1 Affect Query Performance?
May 20, 2008Does multiplication with 1 affect query performance?I have a a stored procedure that converts results to another unit if required. In alternative 1 below, the results are returned with a separate select statement if no conversion is necessary - in other words, no multiplication with a conversion factor is required. However, the code is not very nice since I need to repeat the select statement again in case a conversion is required, this time including the conversion factor.Alternative 2 uses cleaner-looking code. The conversion factor is set to 1 if no conversion is required, and a single SELECT statement is used to return the data. The @factor variable is defined as a float.I would rather use alternative 2, but I wonder if there is any performance penalty for doing that if no conversion is required since the results are always multiplied with the @factor? Or can SQL server somehow understand that @factor = 1 and no multiplication is required?--- Alternative 1: ---IF @fromunit_sid = @tounit_sid-- Return unconverted results
SELECT ISNULL(ls_totalWaterConsumption,0) AS ls_totalWaterConsumption,ls_theoreticalWaterConsumption AS ls_theoretical_WaterConsumption,ls_totalWaterConsumption - ls_theoreticalWaterConsumption AS ls_extra_WaterConsumption FROM Results WHERE scenario_id = @scenario_idELSEBEGIN
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT -- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_idEND --- Alternative 2: ---IF @fromunit_sid = @tounit_sidSET @factor = 1ELSE
-- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
SELECT ISNULL(ls_totalWaterConsumption * @factor,0) AS ls_totalWaterConsumption, ls_theoreticalWaterConsumption * @factor AS ls_theoretical_WaterConsumption, (ls_totalWaterConsumption - ls_theoreticalWaterConsumption) * @factor AS ls_extra_WaterConsumptionFROM Results WHERE scenario_id = @scenario_id And another question: is using an IF function considerably faster than making a call to another stored procedure?In alternative 2 above I use an IF statement to check if @fromunit_sid = @tounit_sid, and . But in fact the function getConversionFactor that I'm calling does exactly the same thing: if I pass in identical from- and to-values, it simply returns 1, so I could omit the IF statement completely and just use alternative 3. But is it slower?--- Alternative 3 -- Get conversion factor
EXEC getConversionFactor @fromunit_sid, @tounit_sid, @factor OUTPUT
-- Get the converted results
...
If One Of The BCP Fail. Does It Affect The Rest Of The Process??
Dec 8, 1998Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
When Or Where Does To May Columns Or Tables Affect Performance?
May 4, 2001I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
View 1 Replies View RelatedAffect Of Deleting Error Messages
Jun 10, 2004I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?
View 6 Replies View RelatedWill Multiple Instances Affect Performance?
Mar 27, 2006I have two instances installed on my server. I have one database on the default instance and two very large reporting databases on the other instance.
Will having the default instance running affect the performance of the non-default instance where my reporting databases are?