.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL 2005 can't defrag a database

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server


I'm running SQL Server 2005 and when I run defrag.exe I find that my main database file has well over 1000 fragmentations in a 37GB file.  I've tried running defrag but it always reports back that it cannot defrag the file.  There is about 54% free space on the drive and I stopped all SQL services before trying to defrag but no luck.

Any suggestions on how to defrag this file?

One thing I did think of and tried on a test database was to backup the database, detach it, delete the files and then do a restore of the database.  That seems to have worked but I haven't fully tested the database after restore.  Does this approach make sense or is there some risk I haven't thought of?  It sure seems faster than doing a defrag and it does test my disaster recovery abilities.

Thanks in advance,


View Complete Post

More Related Resource Links

Data Joins The Team: Introducing Visual Studio 2005 Team Edition for Database Professionals


Here Brian Randell presents everything you need to know to get started with Visual Studio 2005 Team Edition for Database Professionals.

Brian A. Randell

MSDN Magazine February 2007

Connect to existing sql 2005 database


Hello There,

I had successfully restored sql 2000 database to sql 2005. It works like charm

Now i am creating a database model in MVC

I follow these steps

Right click on model > ADO.NET entity data model > Generate from database > New connection (Microsoft sql server) > server name > select or enter database name

But no server name AND database name show in both drop down lists. When i mannually enter database name (which exists in sql 2005) then click on TEST CONNECTION. It gives follwoing error

"A network-related or instance-specific error while establishing a connection to sql server. the server was not found or was not accessible. verify that the instance name is correct and that sql server is configured to allow remote connections.

Kindly help me with this


SQL 2005 Database Problem

When trying open up a report from a customer database.  I recieved the following error: Please contact technical support with the following information:   Error ID: ERHIG030000340 Error message: There was a problem with the SQL or table when trying to open the display The table name is "taOrderHistory" The SQL is: Underlying problem: Column 'taOrderHistory.OrderHistoryID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at RadWebNet.rwDataSupport.OpenData(String SQLIn, rwConnection ConnIn, OpenDSReturnType ReturnWhat, Int32 TimeOut, Object[] ParamsIn) in D:\Misc Projects\RadWeb\RadwebV4-7-1\rwData.v

Failure in restoring an database from tape - SQL Server 2005

Hello everyone, we are trying to restore one of our datebase from tape using the T-Sql commands. Using the GUI results in an timeout. The command we are using to start the restore is: RESTORE DATABASE RECOV FROM Tape = '\\.\tape0' WITH  FILE=2, RECOVERY, NOUNLOAD, STATS=5, MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\data\RECOV.mdf', MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\log\RECOV_log.ldf', REPLACE the restore proces start without any problem, only ending with this: Processed 2598808 pages for database 'xxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Data' on file 2. Processed 3 pages for database 'xxxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Log' on file 2. Msg 3229, Level 16, State 2, Line 1 Request for device '\\.\tape1' timed out. Msg 3213, Level 16, State 1, Line 1 Unable to unload one or more tapes. See the error log for details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. In the eventlog, the following is recorded: BackupTapeFile::UnloadTape: PrepareTape(UNLOCK) failure on backup device '\\.\tape0'. Operating system error 21(The device is not ready.). For as far we can judge, the tapedrive is functioning without problems. Using the same tape in an other tapedrive on an different server results in the same error. Hardware used: HP Proliant Server (DL580) with additional SCSI interface card HP StorageWorks LTO 4 tapedriv

Unable to install SP3 on SQL 2005 due to Database Services language being blank

Hi all,   I have an issue that requires me to upgrade to a later SP (currently on RTM), but when I try to install SP3 Database Services cannot be ticked. Upon inspection I see that its reporting "This update requires language ENU.  The language for this product instance MSSQLSERVER is . Download the upadte for langage .", when I check teh Language field to the right it is blank, other parts however are fine and can be upgraded. Can you advise please as I don't know how to manually set the language (which is reported inside SQL itself as English (United Kingdom)) and I cannot un/reinstall. Thanks, John

My Sql server 2005 Database in recovery mode for 3 -4 hours every day

Hi , Some of our Sql Server 2005 databases remain in recovery mode after midnight for almost 3 to 4 hours. So when my application access them error "Databse in recovery" and for same database this happens again and again every night. Please suggest why and how this can be solved?

how to use sql database remotely(sql server 2005)

hi friends... when i want to connect my pc to sql server then the error comes........ A network-related or instance-specific error occurred while stabling a connection to sql server, The server was not found or was not accessible. Verify that the instance name is correct and that sql server is configured to allow remote connections,(provider: Named Pipes Provider,error:40-Could not open a connection to Sql server)(Microsoft Sql Server,Error:233)   so plz check it and give me advise how can i connect sql server remotely   thanks friends    

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Can SSAS 2005 run with Sql Server 2008 database engine?

Are there any known issues of running SSAS 2005 with SQL Server 2008 database engine?

Restore 2005 database on 2008 R2 Trial

I've downloaded SQL Server 2008 R2 Trial and installed it on Windows Server 2008 trial running under Virtual PC on XP. I've made backups of databases on my SQL Server 2005 and tried to restore them using a) the wizard - results in a messagebox saying "Specified cast is not valid" and no backup sets are available in the Restore Database window. b) using T-SQL - results in Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:0) in database "dp" as read from the backup set. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Using the wizard and RESTORE on 2005 for the same database backup works fine. c) Detach on 2005 and Attach on 2008 results in: SQL Server detected a logical consistency-based I/O error: incorrect pageid... Detach and Attach worked fine on 2005 and DBCC CHECKDB revealed no errors. Thanks for any info...

Is there a way to backup all my store procedures under a particular SQL 2005 database?

hi all, Is there a script, doc, VBS script, SQL statement out there which will read all my store procedures and save them as a TXT file? Currently I have to save all of them in text files before I update them when I get a new web site release. Thank you  JCD

how to restore msdb database on sql 2008 from sql 2005 backup?

Currently, I am unable to do it. It gives me the error than I cannot be restored because it was created by a different version of the server. What is the work-around? I hate scripting all my maintenance plans and alerts, there are tonns of themJulieShop

database size limit about SQL Server 2005 Express

I know that there is a size limit (4 GB) for SQL Server 2005 Express, but I am not quite sure if the limit is applied for only ONE database data file or for ALL the database data, suppose I have two databases DB1 and DB2, so which of the following statement is correct? A. The size limit for DB1 is 4GB, and the size limit for DB2 is also 4GB, so if DB1 is 3G and DB2 is 3.5G,that will be OK. B. The total size for both DB1 and DB2 can not be larger than 4GB, which means if DB1 is 3G, then DB2 can not be large than 1G.

Shrink database log file sql server 2005

Hi, I am trying to shrink log file for a testing database.This database will be restored with production database periodically.When i tried to create backup of the log file Processed 0 pages for database 'hcbeta', file 'HC_log' on file 6. The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. BACKUP LOG successfully processed 0 pages in 0.423 seconds (0.000 MB/sec). I tried EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 Resulted with error:Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication. So tried to publish the database using EXEC sp_dboption 'hcbeta', 'Publish', 'true' resulted error mesage:Msg 15242, Level 16, State 1, Procedure sp_dboption, Line 138 Database option 'Publish' is not unique. and retrived duplicate_options as "merge Publish" ,"Publish" Plesae help me in shrinking my database log file. Thanks, Adi.

migration MSDE 2000 SP3 database to SQL 2005 SP3: low performance on 2005 Express

I've detach MSDE 2000 SP3 database. I've attach into SQL 2005 Express SP3 and SQL 2005 Standard SP3. I've change compatibility level to 90. I've update statistics with EXEC sp_MSforeachtable @command1="UPDATE STATISTICS ? WITH FULLSCAN" The execution time of a TSQL into the same hardware (1 CPU, Quad Core) are: SQL 2005 Express SP3: 20" SQL 2005 Standard SP3: 0" The execution plan is different. Why?

could not start the database engine service in Sql Server 2005

Dear All,   I am not able to  start the sql server 2005 database engine services... Sql server 2005 server is installed in one machine(SERVER)  and we have 10 client machine connecting to the server     When i try to start the service from the Service.msc in the SERVER i am getting the following error   =================================== Cannot connect to KEYSKILL. =================================== An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider) ------------------------------Error Number: 2Severity: 20State: 0 ------------------------------Program Location:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverI

How to delete a sqlexpress database from the visual 2005

Hello, I created a database in my local sqlexpress server, using the 'Create New SQL server database ..." option in the Server Explorer of VS 2005. How can i delete this database now using VS 2005 ? it seems that the 'Delete' option, only remove the link in the 'Data Conncetions ' folder. Thanks and Regards Denis
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend