Home » SQL Server

Unable to shrink Data file sql server 2008

Unable to shrink datafile. tried dbcc shrink commands no luck.I still see huge unused space.The databse is setup under Transactional Replication.On the other hand the replicated database is already shrunk .Any ideas?

9 Answers Found


Answer 1

When you say unable what exactly do you mean? i.e. do you get errors or does it timeout? If you try shrinking by only a small amount does that work? Can you see if you are being blocked? Also are you trying to shrink the log or data files?




Answer 2


Have you tried DBCC SHRINKFILE with TRUNCATEONLY option?



Answer 3

It doesnt time out or error out.I tried doing it in small chunks  but no luck.Its not blocked either.I get the result immedietly.Im trying to shrink the data files. The data data files Im trying to shrink belongs to SQL Partition filegroup.

Answer 4

Yes I tried the TRUNCATEONLY  option only. Doesnt work.

Answer 5

I hope  you do understand why it "does not work" even with TRUNCATEONLY option

Answer 6

I hope  you do understand why it "does not work" even with TRUNCATEONLY option

Answer 7

Sorry I dont understand.Please explain me.

Answer 8

Yep, in addition, there is enough that at least one page at the end has a data TRUNCATEONLY won't be usefull

Answer 9

During database maintenance period take database offline, then online.

Use Object Explorer GUI shrink file to shrink each file in the database. Image follows:

Log shrinking link: http://sqlusa.com/bestpractices2005/shrinklog/


Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM



I have used the GUI and the CLI to attempt to change SQL Server 2008 log file autogrowth property all to NO avail. In other words, there are NO error messages of any kind, indeed the CLI reports that the command completed successfully. But re-examining the database properties shows that NO change was effected.

For the CLI command, I have used the following with dots filled in for my database and logfile:


Why doesn't this work? How to make it work?




        As per mentioned in following URL I tried to Extract Data-tier Application   uing SQL Server 2008 Stabdard edution.


But on databse -> Task I could not find any menu option related to Extract Data-tier Application

Kindly assots me to find this option.


Thanking you,


Nilkanth Desai



Hi everybody, thanks in advance for your comments and help. Right now I am working to plan a migration from SQL Server 2000 to SQL Server 2008 R2, I have a SQL Server 2000 server with 20 databases and I need to move them to a SQL Server 2008 R2 server, the database compatibility level requiered is SQL Server 2000, so I was thinking to take a full backup from the source server (SQL 2000) and restore in SQL Server 2008, after the restore is done I should disconect users from the source server and take a diferential backup and restore that backup in the SQL Server 2008, in this way I think the cut of the service for the users is the minimun but I have a question is possible to do a Backup from SQL Server 2000 database and then restore that database in a SQL Server 2008 R2? I Have migrated databases in this way but copying the database files from the SQL Server 2000 a pasting them in the SQL Server 2008 and after that atacching thata databases. I hope your sugestions and comments thanks a lot.

Best Regards


When I go to ANY database (System, AdventureWorks*, user) in Managemant Studio, I right-click on the db, select Reports, select Standard Reports, and select Disk Usage.  The disk usage report shows the correct db on top, but always only shows AdventureWorks_Data.mdf when I expand Disk Space Used by Data Files section.  This happens to any db I try.  When I look at Database Properties/Files for that database, the logical and physical file names are correct.

Since the correct files appear in Database Properties/Files, I'm hoping it's just a bug in the report.  Same thing happens if I run a Disk Usage by Table report for ANY database,  The tables for AdventureWorks always only appear,

Thanks,  Mike

I want to build a process to sync the Data from SQL Server 2008 R2 to SQL Server CE
Found some basic useful information from 

So, I want to create a flow to sync all the data to SQL CE and let the application to choose the SQL CE if its in Offline mode.

I also found using "Sync Framework Database Synchronization" is the only solution to achieve this easily.Correct me if wrong and pls lemme know if any other productive and effective ways for this.
Please help me out giving some links to articles to achieve my task.



Hi SQL Experts ,

We have a very good oppourtinity to migrate a big chunk of DB2 instances on SQL Server .If any one of you have the experience in the same kind of project and can share the experience , that would be grand ...

I am looking for a methodology to convert data from DB2 to SQL ...I heard there is a tool that Microsoft acquired .But I not very sure .

Best Regards

I'm getting exactly the same problem described here: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/dcc851e7-35d7-492a-b15c-7dffe47110ed

Microsoft SQL Server Management Studio
Unable to start T-SQL Debugging. Could not attach to SQL Server process on 'myserver'. Click Help for more information.
OK   Help  

I've followed advice with regards to Windows Firewall, however it's disabled on both client and server.  I've tried using a SQL Server account but haven't had any more success.  I've also checked the windows event logs on both the client and the server and can't find anything..

Lastly (not sure if this is useful or not) I tried attaching to the Ssms.exe process from Visual Studio 2008 and I get the following output when I try debugging: 

C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.CommonIDE\\Microsoft.VisualStudio.CommonIDE.dll: Cannot find or open the PDB file.
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.DataWarehouse.VsIntegration.dll: Cannot find or open the PDB file.
'Ssms.exe': Loaded 'C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SQLDE.dll'
First-chance exception at 0x7c812a6b in Ssms.exe: 0x80070005: Access is denied.
A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in Microsoft.SqlServer.SqlTools.VSIntegration.dll

Is there anything else I can do to figure out why the debugger cannot attach?


I'm trying to install SQL 2005 64 bit server (complete) on my Windows 2008 Ent server (acts as DC) but is unable to select and install Reporting Services.

Read this posting http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1908061&SiteID=17

But don't feel that it is the answer to my problem.

Any ideas?



I get the following error when running SQL Server SSMS 2008 (32-bit) on my (Windows 7 64-bit) workstation against our SQL Server 2005 (32-bit) development server running Windows Server 2003 (32-bit):

TITLE: Microsoft SQL Server Management Studio


The Activity Monitor is unable to execute queries against server <my server\instance name>.
Activity Monitor for this instance will be placed into a paused state.
Use the context menu in the overview pane to resume the Activity Monitor.


Unable to find SQL Server process ID 2256 on server VTX-DEV-01 (Microsoft.SqlServer.Management.ResourceMonitoring)


I can expand the panes and all display data except Overview and Data File I/O. This is true for all instances on that server. The SSMS 2005 Activity Monitor (also on my workstation) works fine when connected to that server.

I checked for the registry setting to disable the performance counters on both my machine and the server, and it was not there. I started the Performance Counter DLL Host on my machine, but that made no difference. I can see all Processor counters on the server from my local Performance Monitor. I ran "lodctr /R" on both systems, but that didn't help. The only SQL counters I see on the server (from my workstation) are SQLServer:SSIS Pipeline and SQLServer:SSIS Service.

When I run Perfmon directly on the server, I see dozens number of Performance objects that have 5 digit numbers for names. That may be a part of the problem, but I don't know how to correct that.


SQL Server 2008 Enterprise (10.0.2531.0) installed on SQL Server 2003 R2 SP2.

I am logged in as sa and I try to grant sysadmin (or any server role) to a new account (either windows or sql authentication) and it fails without an error. If I use the gui in the management studio it says okay but when you check the role is not granted. If I use t-sql command (sp_addsrvrolemember) it doesn't generate an error but again nothing happens. The role is not granted.


I have been trying to figure out how to fix this problem for the past couple of days and I was unable to find an answer to my specific problem in the forum.

My OS is Windows XP Professional SP3 on MacBook Pro, using Parallels Desktop 4.0. Every single time Windows update tries to install SQL Server 2005 SP2, it fails. I am using Business Contact Manager with Outlook 2007 and SQL Server stops randomly and I am forced to restart the software. I am not sure if it is relevant to this specific problem but I thought I would make a note of this.

I have downloaded SP2 and tried to install it locally but that did not work. Here is the error message I get:

OS Version: Microsoft Windows XP Professional Service Pack 3 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No

Prerequisites Check & Status
SQLSupport: Passed

Products Detected                         Language  Level  Patch Level       Platform  Edition
Express Database Services (MSSMLBIZ)      ENU       SP1    2005.090.2047.00  x86       EXPRESS

Products Disqualified & Reason
Product                                   Reason

Processes Locking Files
Process Name          Feature               Type          User Name                  PID

Product Installation Status
Product                   : Express Database Services (MSSMLBIZ)
Product Version (Previous): 2047
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9Express_Hotfix_KB921896_SQLEXPR.EXE
SQL Express Features      : SQL_Data_Files,SQL_Engine,SQL_SharedTools
Error Number              : 11055
Error Description         : Unable to install Windows Installer MSI file

     Unable to install Windows Installer MSI file
     Exit Code Returned: 11055

I would very much appreciated if you someone can provide me guidance.

Thank you in advance for your time.


Hi there!

I have a database called 'GSDQ'.

It is 50GB in size of which 30GB is the datafile and 20GB is the log file.

I need to shrink the log file but am unable to get it any smaller. When I run the disk usage report it tells me that 96.6% of the log file is unused. But running the following doesnt seem to change it.


I have tried doing a full backup and then a shrinkfile as well as a log backup and shrinkfile, but it doesnt seem to affect it.

when I look at database properties, the initial size for the log is set to 20GB - I have tried to reduce this by entering a new number and clicking ok, but it just ignores it. Theres no error or anything but when i open the properties again it says its 20GB.

Any idea why it wont shrink?



shrinkfile (gsdq_log,1)

Hi All,

I am unable to shrink my Database log file. I am getting following error message

Cannot shrink log file 2 (WSS_Content) because all logical log files are in use.

FYI - I am using following commands to shrink the SQL database




Any help in this regard will be greatly appreciated...


Is it possible to upgrade SQL Server 2008 to Sql Server 2008 R2 on a SBS 2008 Server

In preparing for our upgrade to SQL Server 2008, we are exploring using table partitioning for some of our larger tables.  I have read a white paper on partitioning and I feel like I have the concept down, but where I am struggling is figuring out the best file group and file layouts for our databases.

In 2008 you can have multiple files under each file groups and I am trying to get a handle on this concept.



Here is what I have in mind so far. 

Creating a file group for the larger tables or maybe a file group per very large table.  Also partitioning the large tables.
I am planning on partitioning the tables by month using a date data type field.

       2.   Compressing older partitions where the data is not changed anymore.

       3.   Keep the number of file groups relatively small for manageability. 


In the white papers I have looked at, I really don’t see a best practice for how to lay the data out.  We are going to be on a new server and fresh data disks so we want to lay the data out correctly. 

 Do you know of any white papers or resources that may help in deciding the best way to lay the data out?



Hi all,

I am working on Data migration using SSIS can anybody Please tell me how to migrate data from tally Data Base to Sql Server using SSIS 2008 and provide links that we migrate data from data sources to SQL server

please help me

Thanks in advance


Sunil yoganna


Hi all,

i working on Data Migration now i want migrate data from AS400 data base to Sql Server 2008

please help me and  if their is any sample package please provide me the link  and i want to migrate data from Cobal as well as MS access data base

thanks in Advance


Sunil Yoganna


Hi all,

i am working on data migration project now i want migrate data from SQL Server,FoxPro,Cobal& AS400.

any body please tell me  the tips tricks how to migrate  from this data base to SQL Server 2008 if their is any link regarding this pleas help me

out any Document with  examples for the above examples will be great help thanks in Advance.



Sunil Yoganna


Having installed Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007, the Data Mining tab is not displaying in the Ribbons tab.

I've ensured the .NET programmability component is installed for Office via Office setup (Control Panel - Add/Remove Programs) and have checked the add-in has not been disabled via Excel Options -> Add-Ins -> Manage: Disabled Items.

Any help would be greatly appreciated.



Does Master Data Service has some in built mechanism to deal with cleansing of data? Or, is there some cleansing service available that works with SQL Server 2008 R2 Master Data Services?

<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure