Getting Error while shrinking the database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Getting Error while shrinking the database

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-01-22 : 03:10:55

Hi, i have one table which have 25 million records. Another table contains 75000 records... like that..more data is available.
i run one query to get 25 millino recors in another table having some process and inner join conditions with another table... Its occupying more space. My system configuration is not enough. so i cancelled the query. But the memory is still occupied. I tried to shrink the database by right click ond database -> tasks ->shrink ->Database
It giving following error. How can i shrink my database or how can i get the memory now?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Shrink failed for Database 'Diaspro'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3073&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

G. Satish

Carat
Yak Posting Veteran

92 Posts

Posted - 2009-01-22 : 06:30:25

SQL Server is probably busy to rollback your transaction. When this is done you can proceed.

I think its better to take a close look at your tables:

- Do you have indexes on your tables?
- Have you rebuild/reorganized your indexes?
- Have you updated the statistics?
- When you have the SQL Server Enterprise Edition you can use Partitioning to partition your tables, this will make it more manageable.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-22 : 11:56:19

You can't shrink like that in middle of operation. There are thousands of posts here saying shrinking is bad for database performance.Search for it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources